Re: [sqlalchemy] IntegrityError following the tutorial using OracleDB

2017-04-21 Thread mike bayer 



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

On Fri, Apr 21, 2017 at 1:52 AM, Matei Micu  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 "" %
(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':
}
2017-04-21 03:47:01,337 INFO sqlalchemy.engine.base.Engine ROLLBACK
Traceback (most recent call last):
   File "t.py", line 33, in 
 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/sqlalc

Re: [sqlalchemy] IntegrityError following the tutorial using OracleDB

2017-04-21 Thread Simon King 
On Fri, Apr 21, 2017 at 1:52 AM, Matei Micu  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 "" %
> (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':
> }
> 2017-04-21 03:47:01,337 INFO sqlalchemy.engine.base.Engine ROLLBACK
> Traceback (most recent call last):
>   File "t.py", line 33, in 
> 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