postgresql 如何使用SQLAlchemy在Postgres数据库中创建具有UUID列的表？

sqserrrh 于 2023-08-04 发布在 PostgreSQL

关注(0)|答案(1)|浏览(135)

我已经使用SQLALchemy 2.0的MappedAsDataclass和DeclarativeBase模型创建了一些表，如下所示：

class Base(MappedAsDataclass, DeclarativeBase):
    """subclasses will be converted to dataclasses"""

class Post(Base):
    __tablename__ = 'allposts'
    post_id: Mapped[int] = mapped_column(init=False, primary_key=True)
    collection_id: Mapped[int] = mapped_column(ForeignKey("collections.collection_id"), default=None, nullable=True)
    user: Mapped[str] = mapped_column(default=None, nullable=True)
    title: Mapped[str] = mapped_column(default=None, nullable=True)
    description: Mapped[str] = mapped_column(default=None, nullable=True)
    date_added: Mapped[datetime.datetime] = mapped_column(default=None, nullable=True)
    date_modified: Mapped[Optional[datetime.datetime]] = mapped_column(default=None, nullable=True)
    tags: Mapped[list]  = mapped_column(ARRAY(TEXT, dimensions=1), default=None, nullable=True)
    views: Mapped[int] = mapped_column(default=0, nullable=True)

Post.__table__

Base.metadata.create_all(engine)

字符串
我想使用GUID作为PK，但在使用DeclarativeBase/MappedAsDataclass通过SQLALchemy创建UUID类型列时遇到了麻烦
我尝试这样的东西来制作UUID列，但没有运气：

from sqlalchemy.dialects.postgresql import UUID

class Post(Base):
    __tablename__ = 'allposts'
    post_id: Mapped[int] = mapped_column(init=False, primary_key=True)
    guid: Mapped[uuid.uuid1] = mapped_column(default=None, nullable=True)
    collection_id: Mapped[int] = mapped_column(ForeignKey("collections.collection_id"), default=None, nullable=True)
    user: Mapped[str] = mapped_column(default=None, nullable=True)
    title: Mapped[str] = mapped_column(default=None, nullable=True)
    description: Mapped[str] = mapped_column(default=None, nullable=True)
    date_added: Mapped[datetime.datetime] = mapped_column(default=None, nullable=True)
    date_modified: Mapped[Optional[datetime.datetime]] = mapped_column(default=None, nullable=True)
    tags: Mapped[list]  = mapped_column(ARRAY(TEXT, dimensions=1), default=None, nullable=True)
    views: Mapped[int] = mapped_column(default=0, nullable=True)

型
我尝试了uuid.uuid1的不同版本，但如果这就是问题所在，我没有猜对。
这里有一些SQLAlchemy UUID列的示例，但我似乎不知道如何将其转换成我正在使用的模型。

postgresql

来源：https://stackoverflow.com/questions/76741476/how-can-i-create-a-table-with-a-uuid-column-in-a-postgres-db-using-sqlalchemy

1条答案

按热度按时间

bkhjykvo1#

下面是一个如何在PostgreSQL服务器上执行此操作的示例：

import uuid

from sqlalchemy import create_engine, select, text, types
from sqlalchemy.orm import (DeclarativeBase, Mapped, MappedAsDataclass,
                            mapped_column, sessionmaker)

DATABASE_URL = "postgresql+psycopg2://test_username:test_password@localhost:5432/test_db"
engine = create_engine(
    DATABASE_URL,
    echo=True,
)
Session = sessionmaker(
    bind=engine,
)

class Base(MappedAsDataclass, DeclarativeBase):
    pass

class Post(Base):
    __tablename__ = 'post'
    guid: Mapped[uuid.UUID] = mapped_column(
        types.Uuid,
        primary_key=True,
        init=False,
        server_default=text("gen_random_uuid()") # use what you have on your server
    )

if __name__ == "__main__":
    Base.metadata.create_all(engine)

    with Session() as session:
        post1 = Post()
        post2 = Post()
        session.add_all([post1, post2])
        session.commit()

    with Session() as session:
        for post in session.scalars(select(Post)):
            print(post)

字符串
这里，它使用了一个服务器默认值，让服务器为我们生成UUID。

CREATE TABLE post (
        guid UUID DEFAULT gen_random_uuid() NOT NULL,
        PRIMARY KEY (guid)
)

型
结果将是：

Post(guid=UUID('b8b01f06-8daf-4c2c-9a7b-c967c752a7bf'))
Post(guid=UUID('7b94c8a3-ff0e-4d09-92e9-caf4377370e1'))

型

赞(0）回复(0）举报 2023-08-04

我来回答

postgresql 如何使用SQLAlchemy在Postgres数据库中创建具有UUID列的表？

1条答案

相关问题

热门标签

最新问答