primary key values are ignored when using insert(values=<a tuple>)

Using sqlalchemy 0.3.11.

The following test case works for databases which have positional input parameters (sqlite), but the test case fails for database which have named input parameters (Oracle). The test case takes a db uri as a parameter to the script.

#!/usr/bin/env python
from sys import argv
from sqlalchemy import *

db_uri = argv[1](1)
metadata = BoundMetaData(db_uri)

hubs_t = Table('hubs', metadata,
                Column('hub_id', SmallInteger, primary_key=True),
                Column('hub_code', String(2), nullable=False),
                Column('hub_name', String(16), nullable=False),
                Column('tz', String(16), nullable=False))
metadata.create_all()
hubs = Table('hubs', metadata, mustexist=True)
session = create_session()
metadata.engine.echo = True

hubs.insert(values=(1, 'us', 'US', 'US/Eastern')).execute()

Here are the successful results with sqlite:

$ ./test_case.py sqlite://
2008-01-25 11:10:57,534 INFO sqlalchemy.engine.base.Engine.0x..50 INSERT INTO hubs (hub_id, hub_code, hub_name, tz) VALUES (?, ?, ?, ?)
2008-01-25 11:10:57,534 INFO sqlalchemy.engine.base.Engine.0x..50 ['us', 'US', 'US/Eastern'](1,)
2008-01-25 11:10:57,534 INFO sqlalchemy.engine.base.Engine.0x..50 COMMIT

Here is the unsuccessful run with oracle:

$ ./test_case.py oracle://username:password@INSTANCE
2008-01-25 11:12:13,869 INFO sqlalchemy.engine.base.Engine.0x..90 INSERT INTO hubs (hub_id, hub_code, hub_name, tz) VALUES (:hub_id, :hub_code, :hub_name, :tz)
2008-01-25 11:12:13,869 INFO sqlalchemy.engine.base.Engine.0x..90 {'hub_id': None, 'hub_code': 'us', 'tz': 'US/Eastern', 'hub_name': 'US'}
2008-01-25 11:12:13,889 INFO sqlalchemy.engine.base.Engine.0x..90 ROLLBACK
Traceback (most recent call last):
  File "./test_case.py", line 18, in <module>
    hubs.insert(values=(1, 'us', 'US', 'US/Eastern')).execute()
  File "/opt/cnet-python/modules/cnet-python-sqlalchemy/0.3.11-2_64el5/lib/sqlalchemy/sql.py", line 1208, in execute
    return self.compile(bind=self.bind, parameters=compile_params).execute(*multiparams, **params)
  File "/opt/cnet-python/modules/cnet-python-sqlalchemy/0.3.11-2_64el5/lib/sqlalchemy/sql.py", line 1098, in execute
    return e.execute_compiled(self, *multiparams, **params)
  File "/opt/cnet-python/modules/cnet-python-sqlalchemy/0.3.11-2_64el5/lib/sqlalchemy/engine/base.py", line 780, in execute_compiled
    return connection.execute_compiled(compiled, *multiparams, **params)
  File "/opt/cnet-python/modules/cnet-python-sqlalchemy/0.3.11-2_64el5/lib/sqlalchemy/engine/base.py", line 568, in execute_compiled
    self._execute_raw(context)
  File "/opt/cnet-python/modules/cnet-python-sqlalchemy/0.3.11-2_64el5/lib/sqlalchemy/engine/base.py", line 581, in _execute_raw
    self._execute(context)
  File "/opt/cnet-python/modules/cnet-python-sqlalchemy/0.3.11-2_64el5/lib/sqlalchemy/engine/base.py", line 599, in _execute
    raise exceptions.SQLError(context.statement, context.parameters, e)
sqlalchemy.exceptions.SQLError: (DatabaseError) ORA-01400: cannot insert NULL into ("UMPIRE"."HUBS"."HUB_ID")
 'INSERT INTO hubs (hub_id, hub_code, hub_name, tz) VALUES (:hub_id, :hub_code, :hub_name, :tz)' {'hub_id': None, 'hub_code': 'us', 'tz': 'US/Eastern', 'hub_name': 'US'}

Note how the hub_id value gets transformed from a value of 1 to a value of None.

At the earlier points, the 1 is in the data:

> /opt/cnet-python/site-packages-2.5/sqlalchemy/sql.py(3182)_process_colparams()
-> return parameters
(Pdb) p parameters
{'hub_id': _BindParamClause('hub_id', 1, type=SmallInteger()), 'tz': _BindParamClause('tz', 'US/Eastern', type=String(length=16,convert_unicode=False)), 'hub_name': _BindParamClause('hub_name', 'US', type=String(length=16,convert_unicode=False)), 'hub_code': _BindParamClause('hub_code', 'us', type=String(length=2,convert_unicode=False))}

However, after the compile method on line 1065 of sqlalchemy/sql.py runs, then the data is transformed so that hub_id's value becomes None.

I stepped through the code a bit, and I think it may be related to the fact that hub_id is marked as the Primary Key: I saw reference to primary_key in the code that was went through. I also saw a dictionary with the value {'hub_id':None} being passed around. I think the parameters object and this one-item dictionary may get merged at one point, and this {'hub_id':None} overwrote the 'hub_id': _BindParamClause('hub_id', 1, type=SmallInteger()) value.

I have a work-around to this, so this isn't an urgent fix. For a workaround, I am doing:

def tcols(table, *values):
    """Construct a dict of column names/values. Values are supplied
    in table column order."""
    return dict(zip(table.c.keys(), values))

i = hubs.insert()
i.execute(tcols(hubs, 1, 'us', 'US', 'US/Eastern'))

FYI, I am using this code to pre-populate a database with test data. That would initialize the database with config data before running unit tests on code that's actually using ORM code.

Comments (8)