时间戳

首页 > 解决方案 > 为 SQLAlchemy 表提供自定义参数

python - 为 SQLAlchemy 表提供自定义参数

问题描述

我想使用来自 SQLAlchemy 的 CockroachDB 的 Column Families declarative_base。我想我需要将它添加到__table_args__，但我不知道如何。

问题基本上是：是否可以从 SQLAlchemy 为 CREATE TABLE 提供自定义（底层方言不支持）选项？

标签： pythonsqlalchemycockroachdb

解决方案

One possibility is to create a custom compilation extension:

from sqlalchemy.ext.compiler import compiles
from sqlalchemy.schema import ColumnCollectionConstraint
from sqlalchemy.sql.elements import quoted_name


class Family(ColumnCollectionConstraint):

    def __init__(self, name, *columns, **kwgs):
        name = quoted_name(name, kwgs.pop("quote", None))
        super().__init__(*columns, **kwgs)
        self.name = name


@compiles(Family)
def compile_family(element, ddlcompiler, **kwgs):
    name = ddlcompiler.preparer.quote(element.name)
    columns = ", ".join([
        ddlcompiler.sql_compiler.process(col, include_table=False, **kwgs)
        for col in element.columns
    ])
    return f"FAMILY {name} ({columns})"

With the extension the following:

from sqlalchemy import Column, Integer
from sqlalchemy.schema import CreateTable
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

class Test(Base):
    __tablename__ = 'test'
    id = Column(Integer, primary_key=True)
    x = Column(Integer)
    __table_args__ = (Family('xs', x),)

print(CreateTable(Test.__table__))

outputs:

CREATE TABLE test (
        id INTEGER NOT NULL, 
        x INTEGER, 
        PRIMARY KEY (id), 
        FAMILY xs (x)
)

推荐阅读