python - sqlalchemy.exc.编程错误 : (ProgrammingError) can't adapt type 'UUID'

Question

我的 table 是

   categories = table('categories',
                       Column('uuid', UUID(), default=uuid.uuid4,
                              primary_key=True,
                              unique=True, autoincrement=False),
                       Column('name', String),
                       Column('parent', String),
                       Column('created_on', sa.types.DateTime(timezone=True),
                              default=datetime.utcnow())
    )

当我尝试插入数据时，我看到了

sqlalchemy.exc.ProgrammingError: (ProgrammingError) can't adapt type 'UUID' 'INSERT INTO categories (uuid, name, parent, created_on) VALUES (%(uuid)s, %(name)s, %(parent)s, %(created_on)s)' ({'created_on': datetime.datetime(2013, 3, 31, 4, 12, 26, 801940), 'name': 'Alcohol & Bars', 'parent': 'Food & Drink', 'uuid': UUID('860e5bae-b770-425f-8672-c15c49508a1f')}, {'created_on': datetime.datetime(2013, 3, 31, 4, 12, 26, 801940), 'name': 'Coffee & Tea', 'parent': 'Food & Drink', 'uuid': UUID('de6ad60e-a076-483d-90e9-93916c537583')},

然后我把我的 table 改成

categories = table('categories',
                   Column('uuid', UUID(), default=str(uuid.uuid4()),
                          primary_key=True,
                          unique=True, autoincrement=False),
                   Column('name', String),
                   Column('parent', String),
                   Column('created_on', sa.types.DateTime(timezone=True),
                          default=datetime.utcnow())
)

然后我开始将错误视为

sqlalchemy.exc.IntegrityError: (IntegrityError) duplicate key value violates unique constraint "categories_pkey"

    DETAIL:  Key (uuid)=(cb80a166-adce-4a6e-9e1a-c210d9b86732) already exists.
     'INSERT INTO categories (uuid, name, parent, created_on) VALUES (%(uuid)s, %(name)s, %(parent)s, %(created_on)s)' ({'created_on': datetime.datetime(2013, 3, 31, 4, 14, 22, 732107), 'name': 'Alcohol & Bars', 'parent': 'Food & Drink', 'uuid': 'cb80a166-adce-4a6e-9e1a-c210d9b86732'}, {'created_on': datetime.datetime(2013, 3, 31, 4, 14, 22, 732107), 'name': 'Coffee & Tea', 'parent': 'Food & Drink', 'uuid': 'cb80a166-adce-4a6e-9e1a-c210d9b86732'}, {'created_on': datetime.datetime(2013, 3, 31, 4, 14, 22, 732107), 'name': 'Dessert', 'parent': 'Food & Drink', 'uuid': 'cb80a166-adce-4a6e-9e1a-c210d9b86732'}, {'created_on': datetime.datetime(2013, 3, 31, 4, 14, 22, 732107), 'name': 'Fast Food', 'parent': 'Food & Drink', 'uuid': 'cb80a166-adce-4a6e-9e1a-c210d9b86732'}, {'created_on': datetime.datetime(2013, 3, 31, 4, 14, 22, 732107), 'name': 'Groceries', 'parent': 'Food & Drink', 'uuid': 'cb80a166-adce-4a6e-9e1a-c210d9b86732'}, {'created_on': datetime.datetime(2013, 3, 31, 4, 14, 22, 732107), 'name': 'Other', 'parent': 'Food & Drink', 'uuid': 'cb80a166-adce-4a6e-9e1a-c210d9b86732'}, {'created_on': datetime.datetime(2013, 3, 31, 4, 14, 22, 732107), 'name': 'Restaurants', 'parent': 'Food & Drink', 'uuid': 'cb80a166-adce-4a6e-9e1a-c210d9b86732'}, {'created_on': datetime.datetime(2013, 3, 31, 4, 14, 22, 732107), 'name': 'Snacks', 'parent': 'Food & Drink', 'uuid': 'cb80a166-adce-4a6e-9e1a-c210d9b86732'}  ... displaying 10 of 43 total bound parameter sets ...  {'created_on': datetime.datetime(2013, 3, 31, 4, 14, 22, 732107), 'name': 'Other', 'parent': 'Financial', 'uuid': 'cb80a166-adce-4a6e-9e1a-c210d9b86732'}, {'created_on': datetime.datetime(2013, 3, 31, 4, 14, 22, 732107), 'name': 'Tax Preparation', 'parent': 'Financial', 'uuid': 'cb80a166-adce-4a6e-9e1a-c210d9b86732'})

为什么 uuid.uuid4() 总是给出相同的值？我该如何解决这个问题？

谢谢

Answer 1

要让您的第一个代码示例正常工作，您需要设置 UUID(as_uuid=True)(请参阅 UUID 上的文档)，否则 DBAPI 适配器需要一个字符串值。

您的第二个示例不起作用，因为 default=str(uuid.uuid4()) 存储了一个字符串值，该值在定义表对象时生成一次，并且每次都使用 (因此重复值异常)。您需要在此处传递一个函数，以便在每次插入行时对其进行评估:

default=lambda: str(uuid.uuid4())

同样代表default=datetime.utcnow()，需要是default=datetime.utcnow。另一件事是主键不需要 unique=True 约束，因为它已经保证是唯一的。