https://prefect.io
Join Slack
Hello, for prefect_sqlalchemy integration, is it possible to execute multiple statements queries?
# prefect-integrations
t
Hello, for prefect_sqlalchemy integration, is it possible to execute multiple statements queries?
a
Perhaps execute_many?
t
Not really, I am looking for something like this:
a
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:
Copy code
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
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
I think you may need to update prefect version
No it cannot. The former is a v2 version
t
@Andrew Huang thanks
I actually found an option for this using this database connection:
Copy code
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
I suppose SQLite commits automatically; can you submit an issue?
t
@Andrew Huang sure
10 Views
Open in Slack
PreviousNext
Powered by

Prefect Community

Bring your towel and join one of the fastest growing data communities. Welcome to our second-generation open source orchestration platform, a completely rethought approach to dataflow automation.

Powered by