FetchedValue, or similar, support for PK columns

[Former user]

I'm wanting to create a table which has a primary key that is automatically created by a trigger. Instead of it working I get an error saying "mysql.connector.errors.DatabaseError: 1364: Field 'patient_id' doesn't have a default value". If I explicitly give it a value then SQLAlchemy uses that value instead of fetching what the trigger ends up putting in as a replacement.

The server_default docs say: "Use FetchedValue to indicate that an already-existing column will generate a default value on the database side which will be available to SQLAlchemy for post-fetch after inserts. This construct does not specify any DDL and the implementation is left to the database, such as via a trigger."

here's some sample code that creates the situation:

from sqlalchemy import (Column, BigInteger, Binary, create_engine)
from sqlalchemy.schema import FetchedValue
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import (scoped_session, sessionmaker)


DBSession = scoped_session(sessionmaker())
Base = declarative_base()

class Patient(Base):
    __tablename__ = 'patient'
    patient_id = Column(BigInteger(unsigned=True), server_default=FetchedValue(), primary_key=True, autoincrement=False) #default=text("uuid_short()")
    details = Column(Binary(10000))


if __name__ == '__main__':
    engine = create_engine('mysql+mysqlconnector://root@127.0.0.1/mainserver?charset=utf8&use_unicode=0', echo=True)
    DBSession.configure(bind=engine)
    Base.metadata.drop_all(engine)
    Base.metadata.create_all(engine)
    DBSession.execute("CREATE TRIGGER before_insert_"+Patient.__tablename__+" BEFORE INSERT ON `"+Patient.__tablename__+"` FOR EACH ROW SET new.`"+Patient.__tablename__+"_id` = UUID_SHORT();")
    session = DBSession()
    patient1 = Patient(patient_id=1, details = None)  # works but outputs 1 instead of value set by trigger
    #patient1 = Patient(details = None)  # throws error because it's expecting default
    session.add(patient1)
    session.flush()
    session.commit()
    print(patient1.patient_id)

this throws "mysql.connector.errors.DatabaseError: 1364: Field 'patient_id' doesn't have a default value"

if you change it to

    patient1 = Patient(patient_id=1, details = None)

then you get

2012-11-02 15:01:24,439 INFO sqlalchemy.engine.base.Engine SELECT patient.patient_id AS patient_patient_id, patient.details AS patient_details 
FROM patient 
WHERE patient.patient_id = %(param_1)s
2012-11-02 15:01:24,439 INFO sqlalchemy.engine.base.Engine {'param_1': 1}
Traceback (most recent call last):
  File "bug.py", line 28, in <module>
    print(patient1.patient_id)
  File "/sites/metrics_dev/lib/python3.3/site-packages/sqlalchemy/orm/attributes.py", line 168, in __get__
    return self.impl.get(instance_state(instance),dict_)
  File "/sites/metrics_dev/lib/python3.3/site-packages/sqlalchemy/orm/attributes.py", line 451, in get
    value = callable_(passive)
  File "/sites/metrics_dev/lib/python3.3/site-packages/sqlalchemy/orm/state.py", line 285, in __call__
    self.manager.deferred_scalar_loader(self, toload)
  File "/sites/metrics_dev/lib/python3.3/site-packages/sqlalchemy/orm/mapper.py", line 1719, in _load_scalar_attributes
    raise orm_exc.ObjectDeletedError(state)
sqlalchemy.orm.exc.ObjectDeletedError: Instance '<Patient at 0x7f9e79f4d750>' has been deleted, or its row is otherwise not present.

but mysql shows the following:

mysql> select * from patient;
+-------------------+---------+
| patient_id        | details |
+-------------------+---------+
| 94732327996882994 | NULL    |
+-------------------+---------+
1 row in set (0.00 sec)

Obviously SQLAlchemy is trying to retrieve the object using a primary key of "1".

[Former user]

couldn't see where to mention this, but:

>>> sqlalchemy.__version__
'0.7.9'

Also missed mentioning a component... This is all being done with a MySQL backend but I guess it's related to the schema definition language.

[Mike Bayer]

"this throws "mysql.connector.errors.DatabaseError?: 1364: Field 'patient_id' doesn't have a default value" is generated by your MySQL database, not SQLAlchemy, which suggests your trigger is not working as expected. So you first need to get the trigger working independently of everything else, using a plain DBAPI cursor in conjunction with cursor.execute("INSERT INTO table ..."). The trigger might not be working at all, or might not be working in conjunction with the MySQL-python DBAPI.

On the SQLAlchemy side, there may or may not be issues using FetchedValue with a primary key column, as usually an integer primary key column is always "fetched" if not present, however that implies the autoincrement flag is True which I see you can't set here due to the CREATE TABLE. So FetchedValue compatibility with primary key here may be an issue at that level.

However, there's yet another level that could still stop this feature in its tracks, which is that primary key columns can't be "fetched" in the usual way, they have to be delivered to us either via the DBAPI itself using the cursor.lastrowid value, or via special functions that tell us what the last inserted PK was. Either one of these methods needs to be available to us in order for the trigger-as-primary-key approach to work. The MySQL dialect currently uses cursor.lastrowid, so if MySQL-Python doesn't function correctly when a trigger is used, we might have to add additional dialect behaviors in order to detect this.

But in any case the first step is to get your trigger working without SQLAlchemy being in use.

[Former user]

Replying to zzzeek:

"this throws "mysql.connector.errors.DatabaseError?: 1364: Field 'patient_id' doesn't have a default value" is generated by your MySQL database, not SQLAlchemy, which suggests your trigger is not working as expected. So you first need to get the trigger working independently of everything else, using a plain DBAPI cursor in conjunction with cursor.execute("INSERT INTO table ..."). The trigger might not be working at all, or might not be working in conjunction with the MySQL-python DBAPI.

Actually, I thought that as well because it seemed to execute the trigger with no error, but there was no following COMMIT afterwards (perhaps creating a trigger is done outside the transaction?). However, right after the code crashes I go into mysql and am able to do "INSERT patient(details) VALUES (NULL)" and it successfully creates a new row with a UUID_SHORT for the primary key. So the actual mysql doesn't have a problem with the operation and the trigger is working properly.

[Former user]

This probably doesn't make any difference, but I'm using mysqlconnector to interface with mysql as it seems to be the only one I could get working with Python3. That's not the default mysql connector.

[Mike Bayer]

I have lots of issues with mysqlconnector - each time I go to test it, it seems, I have to report a new bug on their bugtracker. Last I tried, the current release was non-functional due to an issue they are fixing now.

[Former user]

Replying to zzzeek:

I have lots of issues with mysqlconnector - each time I go to test it, it seems, I have to report a new bug on their bugtracker. Last I tried, the current release was non-functional due to an issue they are fixing now.

FUN! :) Okay, can you suggest a different connector that actually works with Py3? I think I tried several of the ones in the SQLAlchemy docs/wiki and that was the only one I got to work (using "work" losely here).

[Former user]

Okay, did some investigating into the mysqlconnector and found out what was happening by entering some print() statements. It does the same as this in the client:

mysql> INSERT INTO patient (details) VALUES (NULL);
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> show warnings;
+---------+------+-------------------------------------------------+
| Level   | Code | Message                                         |
+---------+------+-------------------------------------------------+
| Warning | 1364 | Field 'patient_id' doesn't have a default value |
+---------+------+-------------------------------------------------+
1 row in set (0.00 sec)

Then in throws the warning as an exception.

Is there a way in SQLAlchemy to switch off passing warnings into exceptions?

[Mike Bayer]

I'm not seeing the warning actually throwing an exception, however that solution here, as I indicated earlier, does not fix the problem because we aren't able to get at these new UUID values as generated inline.

I only see the warning with MySQLdb, not mysql-connector-python. I'm running Python 2, because mysql-connector with Python 3 builds are failing, but Python version isn't terribly important here.

In both cases, we aren't given any means of getting this newly generated value back, so this trigger cannot be used in conjunction with an ORM that needs primary key values.

#import MySQLdb

#conn = MySQLdb.connect(host='localhost', user='scott', passwd='tiger', db='test')

from mysql import connector
conn = connector.connect(host='localhost', user='scott', passwd='tiger', db='test')

cursor = conn.cursor()

cursor.execute("drop table if exists patient")

cursor.execute("""
    create table patient (
        patient_id BIGINT UNSIGNED primary key,
        details varchar(100)
    )
""")

cursor.execute("""
    CREATE TRIGGER before_insert_patient BEFORE INSERT ON `patient`
    FOR EACH ROW SET new.`patient_id` = UUID_SHORT()
""")

for i in range(3):
    cursor.execute("insert into patient (details) values ('some detail')")
    print cursor.lastrowid

    cursor.execute("Select last_insert_id()")
    print cursor.fetchall()

    cursor.execute("select patient_id from patient")
    print cursor.fetchall()

output here with either driver is:

0
[(0,)]((0,))
[(94738459163361297,)]((94738459163361297,))
0
[(0,)]((0,))
[(94738459163361298,)]((94738459163361297,),)
0
[(0,)]((0,))
[(94738459163361298,), (94738459163361299,)]((94738459163361297,),)

so we can confirm rows are being inserted, but there's no means of getting back that PK through this means. This is a big reason FetchedValue() with primary keys is not a real priority, as it is not useful in most situations in any case.

So here I'd forego, or just ignore, the trigger, and pass the SQL function directly as the "default" where it will be invoked separately as "SELECT UUID_SHORT()" before each INSERT:

from sqlalchemy import *
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import Session

Base = declarative_base()

class Patient(Base):
    __tablename__ = 'patient'
    patient_id = Column(BigInteger(unsigned=True), default=func.uuid_short(), primary_key=True, autoincrement=False)
    details = Column(Binary(10000))

engine = create_engine("mysql://scott:tiger@localhost/test", echo=True)

Base.metadata.drop_all(engine)

Base.metadata.create_all(engine)

s = Session(engine)

s.add(Patient(details='some details'))
s.commit()

There's absolutely no downside to this, as we otherwise would need to invoke a function after each INSERT to get that value back, if there were such a way. So this method actually uses less overhead than the trigger.

Alternatively, to skip the extra statement completely you can use a Python-side generation function. MySQL's implementation could be copied exactly, or this is a quick version that just uses a modulus of a built-in uuid4:

def new_id():
    return uuid.uuid4().int % 10000000000000

class Patient(Base):
    __tablename__ = 'patient'
    patient_id = Column(BigInteger(unsigned=True), default=new_id, primary_key=True, autoincrement=False)
    details = Column(Binary(10000))

[Former user]

Okay, I think I understand now. Thank you!

I ended up doing something like what you stated with a default=text("uuid_short()"). However, I'm concerned about other processes accessing the database and that's why I wanted to use a trigger. I modified the trigger to only insert a uuid_short() when no value is passed in during the insert.

Please feel free to close this bug as I don't think I have the authorization to do that.

[Mike Bayer]

added new documentation regarding this in d2a99b2043e976fd673f7ce4119bfe776a4310a9

[Mike Bayer]

Removing milestone: 0.8.xx (automated comment)