I am using the SQL Alchemy core version 1.1 and I can't seem to be able to get transaction working inside my falcon (python) application. I have followed the documentation to my knowledge correctly.
edit: database postgresql -> psycopg2cffi
def __init__(self, *args, **kwargs):
self.__conn_url__ = settings.get_db_url()
self.db_engine = create_engine(self.__conn_url__)
self.db_engine.echo = False
self.metadata = MetaData(self.db_engine)
self.connection = self.db_engine.connect()
self.organization_types_table = Table('organization_types', self.metadata, autoload=True)
self.organization_type_names_table = Table('organization_type_names', self.metadata, autoload=True)
def post(self, json_data):
transaction = self.connection.begin()
print(transaction)
try:
results = self.organization_types_table.insert().\
values(date_created=datetime.datetime.now()).\
execute()
organization_types_id = results.inserted_primary_key
results = self.organization_type_names_table.insert().\
values(organization_types_id=organization_types_id[0],
lang=json_data['lang'],
name=json_data['name']).\
execute()
transaction.commit()
print("I didn't rollback")
return results
except:
transaction.rollback()
print("I rollback :D")
raise
If I run this code multiple times inserting the same objects. It should only work for the first time due to a index constraint not allowing duplications.
The results I will get according to my print() statements:
If I look inside my tables I can clearly see that organization_types_table contains 3 records while organization_type_names contains 1. Why isn't organization_types_table not rolling back according to the transaction?
You need to do
self.connection.execute(query)
instead of
query.execute()
query.execute() executes query on the engine, which acquires a new connection, instead of using the one on which you have a transaction open.