如何在SQLAlchemy中使用uid ?

Is there a way to define a column (primary key) as a UUID in SQLAlchemy if using PostgreSQL (Postgres)?

如果使用PostgreSQL (Postgres)，在SQLAlchemy中是否可以定义一个列(主键)作为UUID ?

8 个解决方案

#1

I wrote this and the domain is gone but here's the guts....

我写这个域消失了但这是勇气....

Regardless of how my colleagues who really care about proper database design feel about UUID's and GUIDs used for key fields. I often find I need to do it. I think it has some advantages over autoincrement that make it worth it.

不管我的同事多么关心正确的数据库设计，他们都觉得UUID和GUIDs是用于关键字段的。我经常发现我需要做这件事。我认为它比自动增值有一些优势，使它值得。

I've been refining a UUID column type for the past few months and I think I've finally got it solid.

在过去的几个月里，我一直在改进UUID列类型，我认为我终于实现了它。

from sqlalchemy import types
from sqlalchemy.dialects.mysql.base import MSBinary
from sqlalchemy.schema import Column
import uuid


class UUID(types.TypeDecorator):
    impl = MSBinary
    def __init__(self):
        self.impl.length = 16
        types.TypeDecorator.__init__(self,length=self.impl.length)

    def process_bind_param(self,value,dialect=None):
        if value and isinstance(value,uuid.UUID):
            return value.bytes
        elif value and not isinstance(value,uuid.UUID):
            raise ValueError,'value %s is not a valid uuid.UUID' % value
        else:
            return None

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

    def is_mutable(self):
        return False


id_column_name = "id"

def id_column():
    import uuid
    return Column(id_column_name,UUID(),primary_key=True,default=uuid.uuid4)

# Usage
my_table = Table('test',
         metadata,
         id_column(),
         Column('parent_id',
            UUID(),
            ForeignKey(table_parent.c.id)))

I believe storing as binary(16 bytes) should end up being more efficient than the string representation(36 bytes?), And there seems to be some indication that indexing 16 byte blocks should be more efficient in mysql than strings. I wouldn't expect it to be worse anyway.

我认为以二进制形式(16字节)存储应该比字符串表示(36字节?)更有效，而且似乎有一些迹象表明，在mysql中索引16字节块比字符串更有效。我也不认为情况会更糟。

One disadvantage I've found is that at least in phpymyadmin, you can't edit records because it implicitly tries to do some sort of character conversion for the "select * from table where id =..." and there's miscellaneous display issues.

我发现的一个缺点是，至少在phpymyadmin中，您不能编辑记录，因为它隐式地尝试对“select * from table where id =…”进行某种字符转换，并且存在各种显示问题。

Other than that everything seems to work fine, and so I'm throwing it out there. Leave a comment if you see a glaring error with it. I welcome any suggestions for improving it.

除此之外，一切似乎都很好，所以我把它扔出去了。如果你看到一个明显的错误，请留下评论。我欢迎任何改进它的建议。

Unless I'm missing something the above solution will work if the underlying database has a UUID type. If it doesn't, you would likely get errors when the table is created. The solution I came up with I was targeting MSSqlServer originally and then went MySql in the end, so I think my solution is a little more flexible as it seems to work fine on mysql and sqlite. Haven't bothered checking postgres yet.

如果底层数据库具有UUID类型，上面的解决方案将会工作，除非我漏掉了一些内容。如果没有，那么在创建表时很可能会出现错误。我想到的解决方案最初是针对MSSqlServer，最后是MySql，所以我认为我的解决方案更灵活一点，因为它在MySql和sqlite上运行得很好。还没检查过邮箱呢。

#2

See also the recipe for Backend-agnostic GUID Type in the SQLAlchemy documentation for column types.

在列类型的SQLAlchemy文档中，还可以看到与后端无关的GUID类型的配方。

#3

The sqlalchemy postgres dialect supports UUID columns. This is easy (and the question is specifically postgres) -- I don't understand why the other answers are all so complicated.

sqlalchemy postgres方言支持uid列。这很简单(问题是具体的postgres)——我不明白为什么其他的答案都这么复杂。

Here is an example:

这是一个例子:

from sqlalchemy.dialects.postgresql import UUID
from flask_sqlalchemy import SQLAlchemy

db = SQLAlchemy()

class Foo(db.Model):
    id = Column(Integer, primary_key=True)
    uuid = Column(UUID, unique=True, nullable=False)

#4

I've used the UUIDType from the SQLAlchemy-Utils package: http://sqlalchemy-utils.readthedocs.org/en/latest/data_types.html#module-sqlalchemy_utils.types.uuid

我使用了SQLAlchemy-Utils包中的UUIDType: http://sqlalchemy-utils.readthedocs.org/en/latest/data_types.html#module- sqlalchemy_utils.types.uid

#5

If you are happy with a 'String' column having UUID value, here goes a simple solution:

如果您对“String”列具有UUID值感到满意，这里有一个简单的解决方案:

def generate_uuid():
   return str(uuid.uuid4())

class MyTable(Base):
   __tablename__ = 'my_table'

   uuid = Column(String, name="uuid", primary_key=True, default=generate_uuid)

#6

In case anyone is interested, I've been using Tom Willis answer, but found useful to add a string to uuid.UUID conversion in the process_bind_param method

如果有人感兴趣，我一直在使用Tom Willis answer，但是我发现向uuid添加一个字符串很有用。process_bind_param方法中的uid转换

class UUID(types.TypeDecorator):
    impl = types.LargeBinary

    def __init__(self):
        self.impl.length = 16
        types.TypeDecorator.__init__(self, length=self.impl.length)

    def process_bind_param(self, value, dialect=None):
        if value and isinstance(value, uuid.UUID):
            return value.bytes
        elif value and isinstance(value, basestring):
            return uuid.UUID(value).bytes
        elif value:
            raise ValueError('value %s is not a valid uuid.UUId' % value)
        else:
            return None

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

    def is_mutable(self):
        return False

#7

Here is an approach based on the Backend agnostic GUID from the SQLAlchemy docs, but using a BINARY field to store the UUIDs in non-postgresql databases.

这里有一种基于来自SQLAlchemy文档的后端不可知的GUID的方法，但是使用一个二进制字段将uid存储在非postgresql数据库中。

import uuid

from sqlalchemy.types import TypeDecorator, BINARY
from sqlalchemy.dialects.postgresql import UUID as psqlUUID

class UUID(TypeDecorator):
    """Platform-independent GUID type.

    Uses Postgresql's UUID type, otherwise uses
    BINARY(16), to store UUID.

    """
    impl = BINARY

    def load_dialect_impl(self, dialect):
        if dialect.name == 'postgresql':
            return dialect.type_descriptor(psqlUUID())
        else:
            return dialect.type_descriptor(BINARY(16))

    def process_bind_param(self, value, dialect):
        if value is None:
            return value
        else:
            if not isinstance(value, uuid.UUID):
                if isinstance(value, bytes):
                    value = uuid.UUID(bytes=value)
                elif isinstance(value, int):
                    value = uuid.UUID(int=value)
                elif isinstance(value, str):
                    value = uuid.UUID(value)
        if dialect.name == 'postgresql':
            return str(value)
        else:
            return value.bytes

    def process_result_value(self, value, dialect):
        if value is None:
            return value
        if dialect.name == 'postgresql':
            return uuid.UUID(value)
        else:
            return uuid.UUID(bytes=value)

#8

You could try writing a custom type, for instance:

您可以尝试编写自定义类型，例如:

import sqlalchemy.types as types

class UUID(types.TypeEngine):
    def get_col_spec(self):
        return "uuid"

    def bind_processor(self, dialect):
        def process(value):
            return value
        return process

    def result_processor(self, dialect):
        def process(value):
            return value
        return process

table = Table('foo', meta,
    Column('id', UUID(), primary_key=True),
)