[sqlalchemy] Potential issue with SQLAlchemy/cx_Oracle (under Python 3)
Hello,
I am currently investigating whether all my current Python work/scripts
will work with Python 3 (v3.3.0). And I am running into an issue which
seems to be related to this. I have made an example (or actually took
the one from the documentation) to illustrate the issue.
Currently running Python 3.3.0 with SQLAlchemy 0.7.10 and cx_Oracle
5.1.2 (all 32 bit, on Windows)
-
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String
from sqlalchemy.orm import sessionmaker
# engine = create_engine('sqlite:///:memory:', echo=True)
engine = create_engine('oracle://scott:tiger@localhost:1521/xe',
echo=True)
Base = declarative_base()
Session = sessionmaker()
Session.configure(bind=engine)
session = Session()
class User(Base):
__tablename__ = 'users'
id = Column(Integer, primary_key=True)
name = Column(String(32))
extra = Column(Integer, nullable=False)
def __init__(self, id, name, extra):
self.id = id
self.name = name
self.extra = extra
def __repr__(self):
return User('%d','%s', '%d') % (self.id, self.name,
self.extra)
Base.metadata.create_all(engine)
ed_user = User(2, 'Ed Jones', 2013)
session.add(ed_user)
session.commit()
-
Not willing to be dependent on sequences required by Oracle I added the
id explicitly.
Running this script on 3.3 results in this error message:
sqlalchemy.exc.IntegrityError: (IntegrityError) ORA-01400: cannot insert
NULL into (SCOTT.USERS.ID)
'INSERT INTO users (id, name, extra) VALUES (:id, :name, :extra)'
{'extra': 2013, 'name': 'Ed Jones', 'id': 2}
(more details available at request)
This seems odd, as the insert statement indicates the column id is
assigned with a correct value (2).
Same script, again Python 3, but now with SQLite (in memory): no errors.
Using Python 2 (v2.7.3) and Oracle; again no errors.
Is it correct to assume this would be related to SQLAlchemy?
Kind regards,
Thijs
--
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 http://groups.google.com/group/sqlalchemy?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] Potential issue with SQLAlchemy/cx_Oracle (under Python 3)
On Fri, Feb 8, 2013, at 11:47, Thijs Engels wrote:
Is it correct to assume this would be related to SQLAlchemy?
I forgot to mention that a raw insert using cx_Oracle directly is fine
for both Python 2.7 and 3.3.
-
import cx_Oracle
connection = cx_Oracle.Connection(scott/tiger@localhost:1521/xe)
cursor = connection.cursor()
cursor.prepare(INSERT INTO users (id, name, extra) VALUES (:id, :name,
:extra))
cursor.execute(None, {'extra': 2013, 'id': 2, 'name': 'Ed Jones'})
connection.commit()
-
Thijs
--
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 http://groups.google.com/group/sqlalchemy?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] Potential issue with SQLAlchemy/cx_Oracle (under Python 3)
On Feb 8, 2013, at 5:47 AM, Thijs Engels wrote:
Running this script on 3.3 results in this error message:
sqlalchemy.exc.IntegrityError: (IntegrityError) ORA-01400: cannot insert
NULL into (SCOTT.USERS.ID)
'INSERT INTO users (id, name, extra) VALUES (:id, :name, :extra)'
{'extra': 2013, 'name': 'Ed Jones', 'id': 2}
(more details available at request)
This seems odd, as the insert statement indicates the column id is
assigned with a correct value (2).
Same script, again Python 3, but now with SQLite (in memory): no errors.
Using Python 2 (v2.7.3) and Oracle; again no errors.
Is it correct to assume this would be related to SQLAlchemy?
I've not attempted running cx_oracle itself with Python 3 at all, and haven't
made any attempt to test SQLAlchemy's dialect with python 3 + cx_Oracle.
What happens if you run a plain cx_Oracle script in Python 3 that runs the
equivalent statement, that is,
import cx_Oracle
conn = cx_Oracle.connect(username, password, dsn)
cursor = conn.cursor()
cursor.execute(insert into users (id, name, extra) values (:id, :name,
:extra), {extra:2013, name: Ed Jones, id: 2})
--
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 http://groups.google.com/group/sqlalchemy?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] Potential issue with SQLAlchemy/cx_Oracle (under Python 3)
On Fri, Feb 8, 2013, at 15:49, Michael Bayer wrote:
On Feb 8, 2013, at 5:47 AM, Thijs Engels wrote:
Running this script on 3.3 results in this error message:
sqlalchemy.exc.IntegrityError: (IntegrityError) ORA-01400: cannot insert
NULL into (SCOTT.USERS.ID)
'INSERT INTO users (id, name, extra) VALUES (:id, :name, :extra)'
{'extra': 2013, 'name': 'Ed Jones', 'id': 2}
(more details available at request)
This seems odd, as the insert statement indicates the column id is
assigned with a correct value (2).
Same script, again Python 3, but now with SQLite (in memory): no errors.
Using Python 2 (v2.7.3) and Oracle; again no errors.
Is it correct to assume this would be related to SQLAlchemy?
I've not attempted running cx_oracle itself with Python 3 at all, and
haven't made any attempt to test SQLAlchemy's dialect with python 3 +
cx_Oracle.
What happens if you run a plain cx_Oracle script in Python 3 that runs
the equivalent statement, that is,
import cx_Oracle
conn = cx_Oracle.connect(username, password, dsn)
cursor = conn.cursor()
cursor.execute(insert into users (id, name, extra) values (:id, :name,
:extra), {extra:2013, name: Ed Jones, id: 2})
I knew that question was coming; I forgot to mentioned in my original
email that I did try this:
-
import cx_Oracle
connection = cx_Oracle.Connection(scott/tiger@localhost:1521/xe)
cursor = connection.cursor()
cursor.prepare(INSERT INTO users (id, name, extra) VALUES (:id, :name,
:extra))
cursor.execute(None, {'extra': 2013, 'id': 2, 'name': 'Ed Jones'})
connection.commit()
-
Works fine with both Python 2.7 and 3.3. Running the (even more) direct
statement as in your email end up with the same results.
More than willing to try things out if the feedback on this is
considered useful.
Thijs
--
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 http://groups.google.com/group/sqlalchemy?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
[sqlalchemy] orm_exc.NoResultFound safe with autoflush?
We often use this pattern: try: session.query().one() except orm_exc.NoResultFound: gracefully deal with it If the query() execution causes an autoflush, I just want to make sure that an autoflush will never raise orm_exc.NoResultFound, or we could be catching the wrong error. Were that the case, to be safe, we'd always need: session.flush() try: session.query().one() except orm_exc.NoResultFound: gracefully deal with it -- 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 http://groups.google.com/group/sqlalchemy?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] Potential issue with SQLAlchemy/cx_Oracle (under Python 3)
On Fri, Feb 8, 2013, at 18:43, Michael Bayer wrote: On Feb 8, 2013, at 12:31 PM, Michael Bayer wrote: On Feb 8, 2013, at 10:29 AM, Thijs Engels wrote: Works fine with both Python 2.7 and 3.3. Running the (even more) direct statement as in your email end up with the same results. More than willing to try things out if the feedback on this is considered useful. your program will work right now if you pass auto_setinputsizes=False to create_engine(). An encoding step in setinputsizes() will be removed in the SQLAlchemy dialect to resolve. this fix is in tip and will be in 0.8.0. I can confirm the additional argument for create_engine() works. Thank you very much for the quick response, answer and fix! Thijs -- 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 http://groups.google.com/group/sqlalchemy?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
