ResultProxy.inserted_primary_key incorrect value when using empty string

[Former user]

So let's say you have a table with two columns: id and name, where id is the primary key. You go to insert a new row doing something like: table.insert(values={"id": "", "name": "Kevin"})

It will insert it into the database fine and it will get assigned an id of say 1. But in the ResultProxy.inserted_primary_key, the id returned will be an empty string.

If you were to set id to None or to not specify id at all, then inserted_primary_key will return the correct id.

[Mike Bayer]

empty strings are not accepted by the DBAPI for Integer columns:

from sqlalchemy import *

e = create_engine('sqlite://', echo=True)
m = MetaData()

t = Table('t', m, Column('id', Integer, primary_key=True), Column('name', String))
m.create_all(e)
r = e.execute(t.insert(values={'id':'', 'name':'foo'}))



sqlalchemy.exc.IntegrityError: (IntegrityError) datatype mismatch u'INSERT INTO t (id, name) VALUES (?, ?)' ('', 'foo')

if your DBAPI (critically, not specified here) is not doing this, this is most likely a bug with that DBAPI.

[Mike Bayer]

Reopen this ticket with info about DBAPI version and such, otherwise I can't reproduce it. Inserting blank strings for integer PK cols is not a really clear use case in any case.

[Former user]

I'm unsure how to check the DB-API version, but your example code gives me the same output you pasted. I am using mysql and not sqlite3. Is this a mysql thing?

[Mike Bayer]

it could be a MySQLdb thing, sure. You got MySQLdb from here: http://sourceforge.net/projects/mysql-python/ .

If you run the test with MySQLdb (the DBAPI), it gives you this warning:

/Users/classic/dev/sqlalchemy/lib/sqlalchemy/engine/default.py:299: Warning: Incorrect integer value: '' for column 'id' at row 1 cursor.execute(statement, parameters)

There's really nothing I can see us changing here - if you specified a value for the primary key column, it makes no sense for us to go ask the DBAPI what value it ultimately used for the column - you passed it in. That MySQLdb is only raising a warning and not raising a full exception for an invalid value, like pysqlite, psycopg2, oracle, oursql, pretty much everything else does, that's a design flaw on MySQLdb's part.