ResultProxy.inserted_primary_key incorrect value when using empty string

Issue #1941 resolved
Former user created an issue

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.

Comments (5)

  1. Mike Bayer repo owner
    • assigned issue to
    • changed component to sql

    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.

  2. Mike Bayer repo owner

    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.

  3. Former user Account Deleted
    • removed status
    • changed status to open

    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?

  4. Mike Bayer repo owner

    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.

  5. Log in to comment