https://prefect.io logo
Title
k

kasteph

03/27/2023, 8:57 AM
I have noticed that running
cursor.execute
with the SqlAlchemy block doesn't persist inserts. The block can connect to the db just fine and even fetch records just fine. But it can neither do inserts nor updates. FWIW, I'm executing from a jinja template like so:
cursor.execute(insert_records.render())
1
r

Ryan Peden

03/27/2023, 11:24 AM
Which database driver/dialect are you using?
k

kasteph

03/27/2023, 3:19 PM
Psycopg2
r

Ryan Peden

03/27/2023, 3:38 PM
Hmm, that's strange. SQLAlchemy normally auto-commits when using the psycopg2 driver. Does it work if you use the block's execute method instead of using the cursor directly?
k

kasteph

03/27/2023, 3:38 PM
Yes, I'm using the execute method based on my example above
@flow
def contracts(network: str):
    logger = get_run_logger()

    connector: SqlAlchemyConnector = SqlAlchemyConnector.load(network)
    with connector.get_connection() as c:
        <http://logger.info|logger.info>(f"connected to sqlalchemy db: {network}")

        <http://logger.info|logger.info>("updating contracts rollup")
        c.execute(update_contracts_tpl.render())
^ this is the exact code snippet
r

Ryan Peden

03/27/2023, 3:44 PM
Ah, got it. You're using the connection directly. Does it work if you do this instead?
@flow
def contracts(network: str):
    logger = get_run_logger()

    connector: SqlAlchemyConnector = SqlAlchemyConnector.load(network)
    <http://logger.info|logger.info>(f"connected to sqlalchemy db: {network}")
    <http://logger.info|logger.info>("updating contracts rollup")
    connector.execute(update_contracts_tpl.render())
k

kasteph

03/27/2023, 3:45 PM
That was the first method I used actually and that also did not work.
r

Ryan Peden

03/27/2023, 3:47 PM
Does it work if you tell it to commit?
@flow
def contracts(network: str):
    logger = get_run_logger()

    connector: SqlAlchemyConnector = SqlAlchemyConnector.load(network)
    with connector.get_connection() as c:
        <http://logger.info|logger.info>(f"connected to sqlalchemy db: {network}")

        <http://logger.info|logger.info>("updating contracts rollup")
        c.execute(update_contracts_tpl.render())
        c.commit()
It should auto commit - but if this works, it will give useful feedback on what's going wrong.
k

kasteph

03/27/2023, 3:47 PM
Alright, let me try that
AttributeError: 'Connection' object has no attribute 'commit'
I was able to make it work by just using the engine directly