Usage: postgres (PostgreSQL) 9.4.5
I just sqlite3 db to postgresql db. For some reason, because of this migration, when I try to create a user, an error occurs regarding user_id (which is the primary key). This was not a problem with sqlite3 . I spent time looking through questions with documents and glass, but remain confused.
Inside api.create_user() :
api.create_user(username ='lola ', firstname ='cats ', lastname ='lcatk', email=' cags@falc.com ')
sqlalchemy db Model:
class User(Base): __tablename__ = 'users' #user_id = Column(Integer, primary_key=True) #changed to: id = Column(Integer, primary_key=True) username = Column(String(50)) firstname = Column(String(50)) lastname = Column(String(50)) email = Column(String(300)) password = Column(String(12)) institution = Column(String(50)) def __init__(self, username, firstname, lastname, email): self.username = username self.firstname = firstname self.lastname = lastname self.email = email def __repr__(self): return "<User(username ='%s', firstname ='%s', lastname ='%s', email='%s')>" % (self.username, self.firstname, self.lastname, self.email)
pyramid views.py:
@view_config(#code supplying the template and etc.) def save_assessment_result(request): with transaction.manager: username = request.params['username'] firstname = request.params['firstname'] lastname = request.params['lastname'] email = request.params['email'] user = api.create_user(username, firstname, lastname, email) #mode code to commit and etc. transaction.commit() return HTTPCreated()
postgres Server.log:
ERROR: null value in column "user_id" violates not-null constraint DETAIL: Failing row contains (null, lola , cats , lcatk, cags@falc.com , null, null, 2015-10-19 23:02:21.560395). STATEMENT: INSERT INTO users (username, firstname, lastname, email, password, institution, created_on) VALUES ('lola ', 'cats ', 'lcatk', ' cags@falc.com ', NULL, NULL, '2015-10-19T23:02:21.560395'::timestamp) RETURNING users.user_id
Tracking:
015-10-19 19:02:21,563 ERROR [pyramid_debugtoolbar][Dummy-3] Exception at http://0.0.0.0:6432/save_assessment_result File "/Users/ack/code/venv/NotssWEB/notssweb/views/default.py", line 61, in save_assessment_result assessment = api.retrieve_assessment(assessment_id) File "/usr/local/lib/python2.7/site-packages/notssdb/api/object.py", line 112, in retrieve_assessment filter(Assessment.assessment_id == something_unique).one() File "/usr/local/lib/python2.7/site-packages/sqlalchemy/orm/query.py", line 2473, in one ret = list(self) File "/usr/local/lib/python2.7/site-packages/sqlalchemy/orm/query.py", line 2515, in __iter__ self.session._autoflush() File "/usr/local/lib/python2.7/site-packages/sqlalchemy/orm/session.py", line 1292, in _autoflush util.raise_from_cause(e) File "/usr/local/lib/python2.7/site-packages/sqlalchemy/util/compat.py", line 199, in raise_from_cause reraise(type(exception), exception, tb=exc_tb) File "/usr/local/lib/python2.7/site-packages/sqlalchemy/orm/session.py", line 1282, in _autoflush self.flush() File "/usr/local/lib/python2.7/site-packages/sqlalchemy/orm/session.py", line 2004, in flush self._flush(objects) File "/usr/local/lib/python2.7/site-packages/sqlalchemy/orm/session.py", line 2122, in _flush transaction.rollback(_capture_exception=True) File "/usr/local/lib/python2.7/site-packages/sqlalchemy/util/langhelpers.py", line 60, in __exit__ compat.reraise(exc_type, exc_value, exc_tb) File "/usr/local/lib/python2.7/site-packages/sqlalchemy/orm/session.py", line 2086, in _flush flush_context.execute() File "/usr/local/lib/python2.7/site-packages/sqlalchemy/orm/unitofwork.py", line 373, in execute rec.execute(self) File "/usr/local/lib/python2.7/site-packages/sqlalchemy/orm/unitofwork.py", line 532, in execute uow File "/usr/local/lib/python2.7/site-packages/sqlalchemy/orm/persistence.py", line 174, in save_obj mapper, table, insert) File "/usr/local/lib/python2.7/site-packages/sqlalchemy/orm/persistence.py", line 781, in _emit_insert_statements execute(statement, params) File "/usr/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 914, in execute return meth(self, multiparams, params) File "/usr/local/lib/python2.7/site-packages/sqlalchemy/sql/elements.py", line 323, in _execute_on_connection return connection._execute_clauseelement(self, multiparams, params) File "/usr/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1010, in _execute_clauseelement compiled_sql, distilled_params File "/usr/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1146, in _execute_context context) File "/usr/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1341, in _handle_dbapi_exception exc_info File "/usr/local/lib/python2.7/site-packages/sqlalchemy/util/compat.py", line 199, in raise_from_cause reraise(type(exception), exception, tb=exc_tb) File "/usr/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1139, in _execute_context context) File "/usr/local/lib/python2.7/site-packages/sqlalchemy/engine/default.py", line 450, in do_execute cursor.execute(statement, parameters) IntegrityError: (raised as a result of Query-invoked autoflush; consider using a session.no_autoflush block if this flush is occurring prematurely) (psycopg2.IntegrityError) null value in column "user_id" violates not-null constraint DETAIL: Failing row contains (null, lola , cats , lcatk, cags@falc.com , null, null, 2015-10-19 23:02:21.560395). [SQL: 'INSERT INTO users (username, firstname, lastname, email, password, institution, created_on) VALUES (%(username)s, %(firstname)s, %(lastname)s, %(email)s, %(password)s, %(institution)s, %(created_on)s) RETURNING users.user_id'] [parameters: {'username': u'lola ', 'firstname': u'cats ', 'lastname': u'lcatk', 'institution': None, 'created_on': datetime.datetime(2015, 10, 19, 23, 2, 21, 560395), 'password': None, 'email': u' cags@falc.com '}] 2015-10-19 19:02:21,564 DEBUG [notssweb][Dummy-3] route matched for url http://0.0.0.0:6432/_debug_toolbar/exception?token=a30c0989db02aeff9cd2&tb=4459323984; route_name: 'debugtoolbar', path_info: u'/_debug_toolbar/exception', pattern: '/_debug_toolbar