Re: [sqlalchemy] IntegrityError following the tutorial using OracleDB

mike bayer Fri, 21 Apr 2017 07:01:18 -0700


On 04/21/2017 04:30 AM, Simon King wrote:

On Fri, Apr 21, 2017 at 1:52 AM, Matei Micu <micuma...@gmail.com> 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:ASCIIPIC@127.0.0.1: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



agree, that's the issue here.


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 sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] IntegrityError following the tutorial using OracleDB

Reply via email to