On Fri, Apr 21, 2017 at 1:52 AM, Matei Micu <[email protected]> wrote: > I'm following the tutorial from > http://docs.sqlalchemy.org/en/latest/orm/tutorial.html , from my > understanding the primary key should be added when > a session flushes the content ( in the tutorial the first flush is triggered > by a query ). When I try to follow the tutorial I get a > IntegrityError > > Here is my code. > import sqlalchemy > from sqlalchemy.ext import declarative > from sqlalchemy.orm import sessionmaker > from sqlalchemy.ext.declarative import declarative_base > from sqlalchemy import Column, Integer, String > > url = "oracle+cx_oracle://ASCIIPIC:[email protected]:58639/xe" > engine = sqlalchemy.create_engine(url, convert_unicode=True, echo=True) > engine.execute("DROP TABLE users") > > Base = declarative_base() > > class User(Base): > __tablename__ = 'users' > > id = Column(Integer, primary_key=True) > name = Column(String(50)) > fullname = Column(String(50)) > password = Column(String(50)) > > def __repr__(self): > return "<User(name='%s', fullname='%s', password='%s')>" % > (self.name, self.fullname, self.password) > > Base.metadata.create_all(engine) > ed_user = User(name='ed', fullname='Ed Jones', password='edspassword') > > Session = sessionmaker(bind=engine) > session = Session() > session.add(ed_user) > > # As per tutorial, here the session should flush the content and assign > # an id to the user > our_user = session.query(User).filter_by(name='ed').first() > > > Here is the outputand traceback: > > mmicu@nuc1 $ python -i test.py > 2017-04-21 03:47:01,191 INFO sqlalchemy.engine.base.Engine SELECT USER FROM > DUAL > 2017-04-21 03:47:01,191 INFO sqlalchemy.engine.base.Engine {} > 2017-04-21 03:47:01,193 INFO sqlalchemy.engine.base.Engine SELECT CAST('test > plain returns' AS VARCHAR(60 CHAR)) AS anon_1 FROM DUAL > 2017-04-21 03:47:01,193 INFO sqlalchemy.engine.base.Engine {} > 2017-04-21 03:47:01,194 INFO sqlalchemy.engine.base.Engine SELECT CAST('test > unicode returns' AS NVARCHAR2(60)) AS anon_1 FROM DUAL > 2017-04-21 03:47:01,194 INFO sqlalchemy.engine.base.Engine {} > 2017-04-21 03:47:01,196 INFO sqlalchemy.engine.base.Engine DROP TABLE users > 2017-04-21 03:47:01,196 INFO sqlalchemy.engine.base.Engine {} > 2017-04-21 03:47:01,212 INFO sqlalchemy.engine.base.Engine COMMIT > 2017-04-21 03:47:01,216 INFO sqlalchemy.engine.base.Engine SELECT table_name > FROM all_tables WHERE table_name = :name AND owner = :schema_name > 2017-04-21 03:47:01,216 INFO sqlalchemy.engine.base.Engine {'name': > u'USERS', 'schema_name': u'ASCIIPIC'} > 2017-04-21 03:47:01,268 INFO sqlalchemy.engine.base.Engine > CREATE TABLE users ( > id INTEGER NOT NULL, > name VARCHAR2(50 CHAR), > fullname VARCHAR2(50 CHAR), > password VARCHAR2(50 CHAR), > PRIMARY KEY (id) > ) > > > 2017-04-21 03:47:01,268 INFO sqlalchemy.engine.base.Engine {} > 2017-04-21 03:47:01,332 INFO sqlalchemy.engine.base.Engine COMMIT > 2017-04-21 03:47:01,334 INFO sqlalchemy.engine.base.Engine BEGIN (implicit) > 2017-04-21 03:47:01,335 INFO sqlalchemy.engine.base.Engine INSERT INTO users > (name, fullname, password) VALUES (:name, :fullname, :password) RETURNING > users.id INTO :ret_0 > 2017-04-21 03:47:01,335 INFO sqlalchemy.engine.base.Engine {'fullname': u'Ed > Jones', 'password': u'edspassword', 'name': u'ed', 'ret_0': > <cx_Oracle.NUMBER with value None>} > 2017-04-21 03:47:01,337 INFO sqlalchemy.engine.base.Engine ROLLBACK > Traceback (most recent call last): > File "t.py", line 33, in <module> > our_user = session.query(User).filter_by(name='ed').first() > File > "/home/mmicu/Git/asciipic/.venv/test/local/lib/python2.7/site-packages/sqlalchemy/orm/query.py", > line 2755, in first > ret = list(self[0:1]) > File > "/home/mmicu/Git/asciipic/.venv/test/local/lib/python2.7/site-packages/sqlalchemy/orm/query.py", > line 2547, in __getitem__ > return list(res) > File > "/home/mmicu/Git/asciipic/.venv/test/local/lib/python2.7/site-packages/sqlalchemy/orm/query.py", > line 2854, in __iter__ > self.session._autoflush() > File > "/home/mmicu/Git/asciipic/.venv/test/local/lib/python2.7/site-packages/sqlalchemy/orm/session.py", > line 1375, in _autoflush > util.raise_from_cause(e) > File > "/home/mmicu/Git/asciipic/.venv/test/local/lib/python2.7/site-packages/sqlalchemy/util/compat.py", > line 203, in raise_from_cause > reraise(type(exception), exception, tb=exc_tb, cause=cause) > File > "/home/mmicu/Git/asciipic/.venv/test/local/lib/python2.7/site-packages/sqlalchemy/orm/session.py", > line 1365, in _autoflush > self.flush() > File > "/home/mmicu/Git/asciipic/.venv/test/local/lib/python2.7/site-packages/sqlalchemy/orm/session.py", > line 2139, in flush > self._flush(objects) > File > "/home/mmicu/Git/asciipic/.venv/test/local/lib/python2.7/site-packages/sqlalchemy/orm/session.py", > line 2259, in _flush > transaction.rollback(_capture_exception=True) > File > "/home/mmicu/Git/asciipic/.venv/test/local/lib/python2.7/site-packages/sqlalchemy/util/langhelpers.py", > line 66, in __exit__ > compat.reraise(exc_type, exc_value, exc_tb) > File > "/home/mmicu/Git/asciipic/.venv/test/local/lib/python2.7/site-packages/sqlalchemy/orm/session.py", > line 2223, in _flush > flush_context.execute() > File > "/home/mmicu/Git/asciipic/.venv/test/local/lib/python2.7/site-packages/sqlalchemy/orm/unitofwork.py", > line 389, in execute > rec.execute(self) > File > "/home/mmicu/Git/asciipic/.venv/test/local/lib/python2.7/site-packages/sqlalchemy/orm/unitofwork.py", > line 548, in execute > uow > File > "/home/mmicu/Git/asciipic/.venv/test/local/lib/python2.7/site-packages/sqlalchemy/orm/persistence.py", > line 181, in save_obj > mapper, table, insert) > File > "/home/mmicu/Git/asciipic/.venv/test/local/lib/python2.7/site-packages/sqlalchemy/orm/persistence.py", > line 835, in _emit_insert_statements > execute(statement, params) > File > "/home/mmicu/Git/asciipic/.venv/test/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", > line 945, in execute > return meth(self, multiparams, params) > File > "/home/mmicu/Git/asciipic/.venv/test/local/lib/python2.7/site-packages/sqlalchemy/sql/elements.py", > line 263, in _execute_on_connection > return connection._execute_clauseelement(self, multiparams, params) > File > "/home/mmicu/Git/asciipic/.venv/test/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", > line 1053, in _execute_clauseelement > compiled_sql, distilled_params > File > "/home/mmicu/Git/asciipic/.venv/test/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", > line 1189, in _execute_context > context) > File > "/home/mmicu/Git/asciipic/.venv/test/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", > line 1402, in _handle_dbapi_exception > exc_info > File > "/home/mmicu/Git/asciipic/.venv/test/local/lib/python2.7/site-packages/sqlalchemy/util/compat.py", > line 203, in raise_from_cause > reraise(type(exception), exception, tb=exc_tb, cause=cause) > File > "/home/mmicu/Git/asciipic/.venv/test/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", > line 1182, in _execute_context > context) > File > "/home/mmicu/Git/asciipic/.venv/test/local/lib/python2.7/site-packages/sqlalchemy/engine/default.py", > line 470, in do_execute > cursor.execute(statement, parameters) > sqlalchemy.exc.IntegrityError: (raised as a result of Query-invoked > autoflush; consider using a session.no_autoflush block if this flush is > occurring prematurely) (cx_Oracle.IntegrityError) ORA-01400: cannot insert > NULL into ("ASCIIPIC"."USERS"."ID") > [SQL: u'INSERT INTO users (name, fullname, password) VALUES (:name, > :fullname, :password) RETURNING users.id INTO :ret_0'] [parameters: > {'fullname': u'Ed Jones', 'password': u'edspassword', 'name': u'ed', > 'ret_0': <cx_Oracle.NUMBER with value None>}] >>>> > > > > I use python2.7, SQLAlchemy==1.1.9. >
I don't have Oracle to test with, but perhaps you need to define a Sequence for your primary key column: http://docs.sqlalchemy.org/en/latest/dialects/oracle.html#auto-increment-behavior Hope that helps, Simon -- SQLAlchemy - The Python SQL Toolkit and Object Relational Mapper http://www.sqlalchemy.org/ To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description. --- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To post to this group, send email to [email protected]. Visit this group at https://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
