sqlalchemy multiple flush before commit

0
votes

I want insert records to 3 tables. The codes is below.

    sessionDB.autocommit = False
    # insert Address
    addrRecord = Addres(street=street, city=city, zip_code=zip_code)
    sessionDB.add(addrRecord)
    sessionDB.flush()

    # insert customer
    cRecord = Customer(email=email, passwords=password, kind='individual', aID=addrRecord.aID)
    sessionDB.add(cRecord)
    sessionDB.flush()

    # insert Hcustomer
    homeCRecord = HomeCu(cID=cRecord.cID, fname=first_name, lname=last_name, age=age, marriage=marriage, remain=remain)
    sessionDB.add(homeCRecord)

    print (addrRecord.city, cRecord.kind, homeCRecord.lname)
    sessionDB.commit()

The Addres's key is aID and Customer's key is cID. They are auto increment. In Customer, there is a foreign key constrained by Addres's aID. HomeCu's foreign key cID is constrained by Customer's cID. So I want to insert Addres, Customer, HomeCu sequentially, during which I have to get the keys without doing commit. I used flush() to get the keys but failed at commit operation. Why during the commit operation, the value like email, password become None, but the print sentence is doing well? I have been struggling on this for hours. You help will be greatly appreciated. Thanks a lot!!!

Here is the error log:

Traceback (most recent call last):
File
"/usr/local/Cellar/python3/3.6.3/Frameworks/Python.framework/Versions/3.6/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1193, in _execute_context context)
File "/usr/local/Cellar/python3/3.6.3/Frameworks/Python.framework/Versions/3.6/lib/python3.6/site-packages/sqlalchemy/engine/default.py", line 509, in do_execute cursor.execute(statement, parameters)

File "/usr/local/Cellar/python3/3.6.3/Frameworks/Python.framework/Versions/3.6/lib/python3.6/site-packages/pymysql/cursors.py", line 170, in execute result = self._query(query)
File "/usr/local/Cellar/python3/3.6.3/Frameworks/Python.framework/Versions/3.6/lib/python3.6/site-packages/pymysql/cursors.py", line 328, in _query conn.query(q)
File "/usr/local/Cellar/python3/3.6.3/Frameworks/Python.framework/Versions/3.6/lib/python3.6/site-packages/pymysql/connections.py", line 516, in query self._affected_rows = self._read_query_result(unbuffered=unbuffered)
File
"/usr/local/Cellar/python3/3.6.3/Frameworks/Python.framework/Versions/3.6/lib/python3.6/site-packages/pymysql/connections.py", line 727, in _read_query_result result.read()
File "/usr/local/Cellar/python3/3.6.3/Frameworks/Python.framework/Versions/3.6/lib/python3.6/site-packages/pymysql/connections.py", line 1066, in read first_packet = self.connection._read_packet()

File "/usr/local/Cellar/python3/3.6.3/Frameworks/Python.framework/Versions/3.6/lib/python3.6/site-packages/pymysql/connections.py", line 683, in _read_packet packet.check_error()
File "/usr/local/Cellar/python3/3.6.3/Frameworks/Python.framework/Versions/3.6/lib/python3.6/site-packages/pymysql/protocol.py", line 220, in check_error err.raise_mysql_exception(self._data)

File "/usr/local/Cellar/python3/3.6.3/Frameworks/Python.framework/Versions/3.6/lib/python3.6/site-packages/pymysql/err.py", line 109, in raise_mysql_exception raise errorclass(errno, errval) pymysql.err.IntegrityError: (1048, "Column 'email' cannot be null") The above exception was the direct cause of the following exception: Traceback (most recent call last):
File "/Volumes/Code/Previous
Content/grad/2018_2019/db/final/app/modelController.py", line 164, in registerIndividual('534 Tilling St', 'New York', '15233', '[email protected]', '123', 'Zimo', 'Tang', 23, 755.23, 0)
File
"/Volumes/Code/Previous
Content/grad/2018_2019/db/final/app/modelController.py", line 129, in registerIndividual sessionDB.commit()
File "/usr/local/Cellar/python3/3.6.3/Frameworks/Python.framework/Versions/3.6/lib/python3.6/site-packages/sqlalchemy/orm/session.py", line 943, in commit self.transaction.commit()
File "/usr/local/Cellar/python3/3.6.3/Frameworks/Python.framework/Versions/3.6/lib/python3.6/site-packages/sqlalchemy/orm/session.py", line 467, in commit self._prepare_impl()
File "/usr/local/Cellar/python3/3.6.3/Frameworks/Python.framework/Versions/3.6/lib/python3.6/site-packages/sqlalchemy/orm/session.py", line 447, in _prepare_impl self.session.flush()
File "/usr/local/Cellar/python3/3.6.3/Frameworks/Python.framework/Versions/3.6/lib/python3.6/site-packages/sqlalchemy/orm/session.py", line 2254, in flush self._flush(objects)
File "/usr/local/Cellar/python3/3.6.3/Frameworks/Python.framework/Versions/3.6/lib/python3.6/site-packages/sqlalchemy/orm/session.py", line 2381, in _flush transaction.rollback(_capture_exception=True)

File "/usr/local/Cellar/python3/3.6.3/Frameworks/Python.framework/Versions/3.6/lib/python3.6/site-packages/sqlalchemy/util/langhelpers.py", line 66, in exit compat.reraise(exc_type, exc_value, exc_tb)

File "/usr/local/Cellar/python3/3.6.3/Frameworks/Python.framework/Versions/3.6/lib/python3.6/site-packages/sqlalchemy/util/compat.py", line 249, in reraise raise value
File "/usr/local/Cellar/python3/3.6.3/Frameworks/Python.framework/Versions/3.6/lib/python3.6/site-packages/sqlalchemy/orm/session.py", line 2345, in _flush flush_context.execute()
File "/usr/local/Cellar/python3/3.6.3/Frameworks/Python.framework/Versions/3.6/lib/python3.6/site-packages/sqlalchemy/orm/unitofwork.py", line 395, in execute rec.execute(self)
File "/usr/local/Cellar/python3/3.6.3/Frameworks/Python.framework/Versions/3.6/lib/python3.6/site-packages/sqlalchemy/orm/unitofwork.py", line 560, in execute uow
File "/usr/local/Cellar/python3/3.6.3/Frameworks/Python.framework/Versions/3.6/lib/python3.6/site-packages/sqlalchemy/orm/persistence.py", line 181, in save_obj mapper, table, insert)
File "/usr/local/Cellar/python3/3.6.3/Frameworks/Python.framework/Versions/3.6/lib/python3.6/site-packages/sqlalchemy/orm/persistence.py", line 830, in _emit_insert_statements execute(statement, multiparams)
File "/usr/local/Cellar/python3/3.6.3/Frameworks/Python.framework/Versions/3.6/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 948, in execute return meth(self, multiparams, params)
File "/usr/local/Cellar/python3/3.6.3/Frameworks/Python.framework/Versions/3.6/lib/python3.6/site-packages/sqlalchemy/sql/elements.py", line 269, in _execute_on_connection return connection._execute_clauseelement(self, multiparams, params)
File
"/usr/local/Cellar/python3/3.6.3/Frameworks/Python.framework/Versions/3.6/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1060, in _execute_clauseelement compiled_sql, distilled_params

File "/usr/local/Cellar/python3/3.6.3/Frameworks/Python.framework/Versions/3.6/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1200, in _execute_context context)
File "/usr/local/Cellar/python3/3.6.3/Frameworks/Python.framework/Versions/3.6/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1413, in _handle_dbapi_exception exc_info
File "/usr/local/Cellar/python3/3.6.3/Frameworks/Python.framework/Versions/3.6/lib/python3.6/site-packages/sqlalchemy/util/compat.py", line 265, in raise_from_cause reraise(type(exception), exception, tb=exc_tb, cause=cause)
File
"/usr/local/Cellar/python3/3.6.3/Frameworks/Python.framework/Versions/3.6/lib/python3.6/site-packages/sqlalchemy/util/compat.py", line 248, in reraise raise value.with_traceback(tb)
File "/usr/local/Cellar/python3/3.6.3/Frameworks/Python.framework/Versions/3.6/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1193, in _execute_context context)
File "/usr/local/Cellar/python3/3.6.3/Frameworks/Python.framework/Versions/3.6/lib/python3.6/site-packages/sqlalchemy/engine/default.py", line 509, in do_execute cursor.execute(statement, parameters)

File "/usr/local/Cellar/python3/3.6.3/Frameworks/Python.framework/Versions/3.6/lib/python3.6/site-packages/pymysql/cursors.py", line 170, in execute result = self._query(query)
File "/usr/local/Cellar/python3/3.6.3/Frameworks/Python.framework/Versions/3.6/lib/python3.6/site-packages/pymysql/cursors.py", line 328, in _query conn.query(q)
File "/usr/local/Cellar/python3/3.6.3/Frameworks/Python.framework/Versions/3.6/lib/python3.6/site-packages/pymysql/connections.py", line 516, in query self._affected_rows = self._read_query_result(unbuffered=unbuffered)
File
"/usr/local/Cellar/python3/3.6.3/Frameworks/Python.framework/Versions/3.6/lib/python3.6/site-packages/pymysql/connections.py", line 727, in _read_query_result result.read()
File "/usr/local/Cellar/python3/3.6.3/Frameworks/Python.framework/Versions/3.6/lib/python3.6/site-packages/pymysql/connections.py", line 1066, in read first_packet = self.connection._read_packet()

File "/usr/local/Cellar/python3/3.6.3/Frameworks/Python.framework/Versions/3.6/lib/python3.6/site-packages/pymysql/connections.py", line 683, in _read_packet packet.check_error()
File "/usr/local/Cellar/python3/3.6.3/Frameworks/Python.framework/Versions/3.6/lib/python3.6/site-packages/pymysql/protocol.py", line 220, in check_error err.raise_mysql_exception(self._data)

File "/usr/local/Cellar/python3/3.6.3/Frameworks/Python.framework/Versions/3.6/lib/python3.6/site-packages/pymysql/err.py", line 109, in raise_mysql_exception raise errorclass(errno, errval) sqlalchemy.exc.IntegrityError: (pymysql.err.IntegrityError) (1048, "Column 'email' cannot be null") [SQL: 'INSERT INTO customer (cID, email, passwords, kind, aID) VALUES (%(cID)s, %(email)s, %(passwords)s, %(kind)s, %(aID)s)'] [parameters: {'cID': 31, 'email': None, 'passwords': None, 'kind': None, 'aID': None}] (Background on this error at: http://sqlalche.me/e/gkpj)

Process finished with exit code 1

1
pythonsqlalchemy
You go to great lengths to describe your models and constraints but the best thing you could do is include a minimal version of them that allows us to easily reproduce the issue. – SuperShoot
is this issue is solved as i am facing the same error – sahasrara62

1 Answers

0
votes
addrRecord = sessionDB.query(Addres).filter(Addres.street == street).filter(Addres.city == city) \
                .filter(Addres.zip_code == zip_code).first()
            cRecord = Customer(email=email, passwords=password, kind='individual', aID=addrRecord.aID)
            homeCRecord = HomeCu(fname=first_name, lname=last_name, age=age, marriage=marriage, remain=remain)
            homeCRecord.customer = cRecord
            sessionDB.add(cRecord)
            sessionDB.add(homeCRecord)

I didn' t use flush finally. I think using flush for multiple time will cause error. I add the relationship in instance. Hope this can help someone.