I have noticed that running `cursor.execute` with ...
# prefect-integrations
k
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
Which database driver/dialect are you using?
k
Psycopg2
r
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
Yes, I'm using the execute method based on my example above
Copy code
@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
Ah, got it. You're using the connection directly. Does it work if you do this instead?
Copy code
@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
That was the first method I used actually and that also did not work.
r
Does it work if you tell it to commit?
Copy code
@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
Alright, let me try that
Copy code
AttributeError: 'Connection' object has no attribute 'commit'
I was able to make it work by just using the engine directly