https://prefect.io logo
Powered by
Title
t

Tibs

01/05/2023, 1:16 PM
Hello, for prefect_sqlalchemy integration, is it possible to execute multiple statements queries?
a

Andrew Huang

01/05/2023, 4:55 PM
Perhaps execute_many?
t

Tibs

01/05/2023, 6:23 PM
Not really, I am looking for something like this:
a

Andrew Huang

01/05/2023, 6:34 PM
I am not sure I understand what multi=True does, but you can pass any arbitrary kwargs to execute: https://github.com/PrefectHQ/prefect-sqlalchemy/blob/main/prefect_sqlalchemy/database.py#L726 oh I see now; it does all the statements in one go.
So perhapss: 
from prefect_sqlalchemy import SqlAlchemyConnector
            with SqlAlchemyConnector.load("MY_BLOCK") as database:
                results = database.fetch_all("SELECT * FROM customers WHERE name = :name", parameters={"name": "Me"}, multi=True)
if not you can also get a connection: https://github.com/PrefectHQ/prefect-sqlalchemy/blob/main/prefect_sqlalchemy/database.py#L394 and run any methods you like
t

Tibs

01/06/2023, 9:51 AM
Will try this, however, just updated to prefect-sqlalchemy 0.2.2, and I get the following: ModuleNotFoundError: No module named 'prefect.blocks.abstract' when using from prefect_sqlalchemy import sqlalchemy_execute, sqlalchemy_query
@Andrew Huang I see version 0.2.2 is a bit different, I was sing sqlalchemy_execute with DatabaseCredentials to execute queries before.
Can the SqlAlchemyConnector be configured with a DatabaseCredentials object?
a

Andrew Huang

01/06/2023, 4:29 PM
I think you may need to update prefect version
No it cannot. The former is a v2 version
t

Tibs

01/09/2023, 10:34 AM
@Andrew Huang thanks
I actually found an option for this using this database connection: 
def create_db_credentials(database: str):
    sqlalchemy_credentials = DatabaseCredentials(
        driver=SyncDriver.MYSQL_PYMYSQL,
        username=,
        password=,
        host=,
        port=,
        database=database,
        connect_args={"client_flag": CLIENT.MULTI_STATEMENTS}, "autocommit": True
    )
    return sqlalchemy_credentials
{"client_flag": CLIENT.MULTI_STATEMENTS} for PyMySQL allows me to execute multiple statements queries.
I needed to enable autocommit because queries were not committed and result were not showing in the database tables
Think it's because I am not using an async connector? Is this intended?
a

Andrew Huang

01/09/2023, 4:47 PM
I suppose SQLite commits automatically; can you submit an issue?
t

Tibs

01/10/2023, 7:18 AM
@Andrew Huang sure
3 Views
#prefect-integrationsJoin Slack
Powered by