Potential issue with SQLAlchemy/cx_Oracle (under Python 3)

[Thijs Engels]

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

[Thijs Engels]

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

[Michael Bayer]

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})

[Thijs Engels]

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

[Michael Bayer]

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.

Here is a cx_Oracle script that reproduces the error - however it only reproduces it sporadically.

import cx_Oracle

connection = cx_Oracle.Connection("scott/tiger@localhost:1521/xe")

cursor = connection.cursor()

cursor.execute("create table users (id integer primary key, name varchar(20), "
"extra varchar(20) not null)")
try:
# this should be 'id', not b'id'. But strange that
# it fails only sporadically.
cursor.setinputsizes(**{b'id': cx_Oracle.NUMBER})
cursor.execute(
"INSERT INTO users (id, name, extra) VALUES (:id, :name, :extra)",

{'extra': 2013, 'id': 2, 'name': 'Ed Jones'}
)

finally:
cursor.execute("drop table users")

[Michael Bayer]

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.

[Thijs Engels]

I can confirm the additional argument for create_engine() works.

Thank you very much for the quick response, answer and fix!

Thijs