mysql - sqlalchemy FetchedValue 和 primary_key

Question

我正在尝试创建一个使用 UUID_SHORT() 作为主键的表。我有一个触发器，可以在您执行插入操作时插入一个值。我无法让 sqlalchemy 将列识别为 primary_key 而不会提示不提供默认值。如果我确实包含默认值，即使声明了 server_default=FetchedValue()，它也会在刷新后使用该默认值。我似乎能让事情正常工作的唯一方法是如果该列不是主键。

我正在使用 Pyramid、SQLAlchemy ORM 和 MySQL。

这是模型对象:

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))

在 initializedb.py 中我有:

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

运行 ../bin/initialize_mainserver_db development.ini 给我以下错误:

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

使用 mysql 客户端运行手动插入会导致一切正常，因此问题似乎出在 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

这是我作为解决方法所做的...

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""")

在患者类中:

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

因此，只有当有人直接访问数据库而不是通过 python 代码时，触发器才会执行某些操作。希望没有人这样做 patient1 = Patient(patient_id=0, details = None) 因为 SQLAlchemy 将使用“0”值而不是触发器产生的值