sqlalchemy FetchedValue and primary_key

Question

I'm trying to create a table that uses a UUID_SHORT() as a primary key. I have a trigger that inserts a value when you do an insert. I'm having trouble making sqlalchemy recognize a column as a primary_key without complaining about not providing a default. If I do include a default value, it will use that default value even after flush despite declaring server_default=FetchedValue(). The only way I can seem to get things to work properly is if the column is not a primary key.

I'm using Pyramid, SQLAlchemy ORM, and MySQL.

Here's the model object:

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

in initializedb.py I have:

with transaction.manager:
    patient1 = Patient(details = None)
    DBSession.add(patient1)
    DBSession.flush()
    print(patient1.patient_id)

running ../bin/initialize_mainserver_db development.ini gives me the following error:

2012-11-01 20:17:22,168 INFO  [sqlalchemy.engine.base.Engine][MainThread] BEGIN (implicit)
2012-11-01 20:17:22,169 INFO  [sqlalchemy.engine.base.Engine][MainThread] INSERT INTO patient (details) VALUES (%(details)s)
2012-11-01 20:17:22,169 INFO  [sqlalchemy.engine.base.Engine][MainThread] {'details': None}
2012-11-01 20:17:22,170 INFO  [sqlalchemy.engine.base.Engine][MainThread] ROLLBACK
Traceback (most recent call last):
  File "/sites/metrics_dev/lib/python3.3/site-packages/sqlalchemy/engine/base.py", line 1691, in _execute_context
    context)
  File "/sites/metrics_dev/lib/python3.3/site-packages/sqlalchemy/engine/default.py", line 333, in do_execute
    cursor.execute(statement, parameters)
  File "/sites/metrics_dev/lib/python3.3/site-packages/mysql/connector/cursor.py", line 418, in execute
    self._handle_result(self._connection.cmd_query(stmt))
  File "/sites/metrics_dev/lib/python3.3/site-packages/mysql/connector/cursor.py", line 345, in _handle_result
    self._handle_noresultset(result)
  File "/sites/metrics_dev/lib/python3.3/site-packages/mysql/connector/cursor.py", line 321, in _handle_noresultset
    self._warnings = self._fetch_warnings()
  File "/sites/metrics_dev/lib/python3.3/site-packages/mysql/connector/cursor.py", line 608, in _fetch_warnings
    raise errors.get_mysql_exception(res[0][1],res[0][2])
mysql.connector.errors.DatabaseError: 1364: Field 'patient_id' doesn't have a default value

Running a manual insert using the mysql client results in the everything working fine, so the problem seems to be with SQLAlchemy.

mysql> insert into patient(details) values (null);
Query OK, 1 row affected, 1 warning (0.00 sec)

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

mysql> show triggers;
+-----------------------+--------+---------+-------------------------------------+--------+---------+----------+----------------+----------------------+----------------------+--------------------+
| Trigger               | Event  | Table   | Statement                           | Timing | Created | sql_mode | Definer        | character_set_client | collation_connection | Database Collation |
+-----------------------+--------+---------+-------------------------------------+--------+---------+----------+----------------+----------------------+----------------------+--------------------+
| before_insert_patient | INSERT | patient | SET new.`patient_id` = UUID_SHORT() | BEFORE | NULL    |          | root@localhost | utf8                 | utf8_general_ci      | latin1_swedish_ci  |
+-----------------------+--------+---------+-------------------------------------+--------+---------+----------+----------------+----------------------+----------------------+--------------------+
1 row in set (0.00 sec)

Answer 1

Here's what I did as a work-around...

DBSession.execute(
"""CREATE TRIGGER before_insert_patient BEFORE INSERT ON `patient`
    FOR EACH ROW BEGIN
        IF (NEW.patient_id IS NULL OR NEW.patient_id = 0) THEN
            SET NEW.patient_id = UUID_SHORT();
        END IF;
    END""")

and in the patient class:

patient_id = Column(BigInteger(unsigned=True), default=text("uuid_short()"), primary_key=True, autoincrement=False, server_default="0")

So, the trigger only does something if someone accesses the database directly and not through the python code. And hopefully no one does patient1 = Patient(patient_id=0, details = None) as SQLAlchemy will use the '0' value instead of what the trigger produces

Answer 2

For completeness, here are two additional possible solutions for your question (also available here), based on your answer. They are slightly simpler than your solution (omitting passing parameters with correct default values) and using SQLAlchemy constructs for defining the triggers.

#!/usr/bin/env python3

from sqlalchemy import BigInteger, Column, create_engine, DDL, event
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
from sqlalchemy.schema import FetchedValue
from sqlalchemy.sql.expression import func

Base = declarative_base()

class PatientOutputMixin(object):
    '''
    Mixin to output human readable representations of models.
    '''
    def __str__(self):
        return '{}'.format(self.patient_id)

    def __repr__(self):
        return str(self)


class Patient1(Base, PatientOutputMixin):
    '''
    First version of ``Patient`` model.
    '''
    __tablename__ = 'patient_1'

    patient_id = Column(BigInteger, primary_key=True,
                        default=func.uuid_short())

# the following trigger is only required if columns are inserted in the table
# not using the above model/table definition, otherwise it is redundant
create_before_insert_trigger = DDL('''
CREATE TRIGGER before_insert_%(table)s BEFORE INSERT ON %(table)s
FOR EACH ROW BEGIN
    IF NEW.patient_id IS NULL THEN
        SET NEW.patient_id = UUID_SHORT();
    END IF;
END
''')

event.listen(Patient1.__table__, 'after_create',
             create_before_insert_trigger.execute_if(dialect='mysql'))
# end of optional trigger definition


class Patient2(Base, PatientOutputMixin):
    '''
    Second version of ``Patient`` model.
    '''
    __tablename__ = 'patient_2'

    patient_id = Column(BigInteger, primary_key=True,
                        default=0, server_default=FetchedValue())

create_before_insert_trigger = DDL('''
CREATE TRIGGER before_insert_%(table)s BEFORE INSERT ON %(table)s
FOR EACH ROW BEGIN
    SET NEW.patient_id = UUID_SHORT();
END
''')

event.listen(Patient2.__table__, 'after_create',
             create_before_insert_trigger.execute_if(dialect='mysql'))


# test models

engine = create_engine('mysql+oursql://test:test@localhost/test?charset=utf8')

Base.metadata.bind = engine
Base.metadata.drop_all()
Base.metadata.create_all()

Session = sessionmaker(bind=engine)

session = Session()

for patient_model in [Patient1, Patient2]:
    session.add(patient_model())
    session.add(patient_model())
    session.commit()

    print('{} instances: {}'.format(patient_model.__name__,
                                    session.query(patient_model).all()))

Running the above script produces the following (sample) output:

Patient1 instances: [22681783426351145, 22681783426351146]
Patient2 instances: [22681783426351147, 22681783426351148]