SQLAlchemy commit sql execution after iterating through results with fetchall method

user_13662178 0
Viewed 1879

I'm running into issues with reading results created through OUTPUT statements in SQLAlchemy. I originally had an issue reading the primaryKey of an inserted row, but was able to get it to work using the following code setting autocommit to false:

sqlString = """ INSERT INTO workflow.{table}  ({columns})
                OUTPUT Inserted.{primaryKey}
                VALUES ({values})""".format(table=self.table, columns=columns, primaryKey=self.primaryKey, values=values)

    with self.dict_db['engine'].connect().execution_options(autocommit=False) as connection:
      result = connection.execute(sqlString)
      primaryKey = result.fetchone()[0]
      result.cursor.commit()

Now I am writing a delete statement where I want to do something similar, however after iterating through my results, the cursor object is set to None so I can no longer commit. I have tried both using a for loop that iterates over results and results.fetchall() and in both cases I cannot commit because the cursor is None. Here is my code as it stands:

    sqlString = """ DELETE FROM workflow.{table} OUTPUT Deleted.{primaryKey} {where} """.format(table=self.table, primaryKey=self.primaryKey, where=whereStatement)

    with self.dict_db['engine'].connect().execution_options(autocommit=False) as connection:
      result = connection.execute(sqlString)
      # cursor exists
      primaryKeyList = [item[0] for item in result.fetchall()]
      # cursor is now None
      result.cursor.commit()

The fact that this doesn't work is making me rethink both DB executions. Am I doing something drastically wrong here or is there some small syntax I am missing?

Note: the self.dict_db['engine'] is created with sqlalchemy create_engine

sqlalchemy sql python
1 Answers

Create a transaction using the connection (using connection.begin() and commit that.

with self.dict_db['engine'].connect().execution_options(autocommit=False) as connection:
  txn = connection.begin()
  result = txn.execute(sqlString)
  # I'm not sure a cursor exists here, the result doesn't need one for fetchall()
  primaryKeyList = [item[0] for item in result.fetchall()]
  txn.commit()

Even better, the object returned from connection.begin() implements the context manager protocol, so you can use this simpler version and be sure the transaction is committed if there is no exception, or rolled back if there is:

with self.dict_db['engine'].connect().begin() as connection: 
  # connection here is really a Transaction, but all the connection methods work the same
  result = connection.execute(sqlString)
  primaryKeyList = [item[0] for item in result.fetchall()]
  # transaction is committed as the with block exits

See Using Transactions in the SQLAlchemy docs for more details.

user_67918020
Related