python - 配置 MySQL 以允许生成主键值

Question

这可能是一个相对简单的问题。但是，为了完整起见，我将包括我的所有代码。

我正在使用找到的类 here 为我的 MySQL 表生成字母数字主键.但是，当我向数据库上传一行时，出现此错误:

FlushError: Instance <User at 0x1d47110> has a NULL identity key.  If this is an auto-generated value, check that the database table allows generation of new primary key values, and that the mapped Column object is configured to expect these generated values.  Ensure also that this flush() is not occurring at an inappropriate time, such as within a load() event.

所以，这是我使用来自 SQLAlchemy 源代码的 GUID 代码时的代码:

用户.py

from app import db
from app.custom_db.GUID import GUID

class User(db.Model):
  __tablename__ = 'users'
  id = db.Column(GUID(), primary_key = True)
  name = db.Column(db.String(40), unique = True)
  email_stub = db.Column(db.String(30), unique = True)

  def __init__(self, name, email_stub):
    self.name = name
    self.email_stub = email_stub

  def __repr__(self):
    return '<User %r>' % self.name

当我只使用 db.Integer 时，它工作正常。但是，就像我说的，我想使用字母数字 uuid4() 主键。我怎样才能让我的 MySQL 数据库在我这样做时不报错？

Answer 1

你需要分配一个default generation function到主键。在这里，我们还将使用 backend-agnostic GUID type 的简化版本示例以便我们看到真实的 uuid:

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

Base = declarative_base()

class GUID(TypeDecorator):
    impl = String(32)

    def process_bind_param(self, value, dialect):
        if value is not None:
            return "%.32x" % value
        else:
            return MNone

    def process_result_value(self, value, dialect):
        if value is None:
            return value
        else:
            return uuid.UUID(value)

class User(Base):
    __tablename__ = 'users'
    id = Column(GUID(), primary_key=True, default=uuid.uuid4)
    name = Column(String(40), unique=True)

e = create_engine("mysql://scott:tiger@localhost/test", echo=True)
Base.metadata.drop_all(e)  # just for testing here
Base.metadata.create_all(e)

s = Session(e)

s.add_all([User(name='u1'), User(name='u2'), User(name='u3')])
s.commit()

for guid in s.query(User.id):
    print(guid)