具有动态表模式的 SQLAlchemy ORM [英] SQLAlchemy ORM with dynamic table schema

问题描述

我试图让 SQLAlchemy ORM 创建类 Field 来描述我的数据库中的所有字段:

from sqlalchemy.ext.declarative import declarative_baseBase = declarative_base()类字段(基础):__tablename__ = '字段'__table_args__ = {'schema':'SCM'}id = 列(字符串(20)，primary_key=True)

问题在于表 fields 描述了不同模式中的不同字段，即

SCM.fieldsTDN字段...

我需要类 Field

• 使用对象fieldset初始化，然后才能从db读取记录
• 在读取表 .fields 之前由 fieldset.get_schema() 确定的模式.

像这样:

session.query(Field(fieldset))).filter(Field.id=='some field')

但是，添加

def __init__(self, fieldset)经过

到 class Field 结果为

<块引用>

__init__() 接受 1 个位置参数...

• 我可以将所有 fields 表合并到一个架构中并添加列schema_name"，但我仍然需要 Field 链接到其字段集.

这是否可以使用 SQLAlchemy ORM 来完成，或者我应该切换到 SqlAlchemy 核心，在那里我可以更好地控制对象实例化?

解决方案

所以问题是可以解决的，解决方案记录为 增强基础

from sqlalchemy import Column, Integer, String从 sqlalchemy.ext.declarative 导入clarified_attr, declarative_base类字段:__tablename__ = '字段'@declared_attrdef __table_args__(cls):# 模式将是类名的一部分，这对我有用# 示例:类 FieldSCM -->带有模式 SCM 的字段返回 dict(schema=cls.__name__[5:].upper())id = 列(字符串(20)，primary_key=True)Field = declarative_base(cls=Field)类字段集:def __init__(self, schema):self.fieldtype = type('Field' + schema.upper(), (Field,), {})

概念证明:

FieldSet('ork').fieldtype.__table__

<块引用>

Table('fields', MetaData(bind=None), Column('id', String(length=20), table=, primary_key=True, nullable=False), schema='ORK')

I am trying to task SQLAlchemy ORM to create class Field that describes all fields in my database:

from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()

class Field(Base):
    __tablename__ = 'fields'
    __table_args__ = {'schema':'SCM'}
    id = Column(String(20), primary_key=True)

The issue is that table fields describes different fields in different schemas, i.e.

SCM.fields
TDN.fields
...

I need class Field to

• Be initialized with object fieldset before records can be read from db
• Schema determined by fieldset.get_schema() before table <schema>.fields is read.

Something like this:

session.query(Field(fieldset))).filter(Field.id=='some field')

However, adding

def __init__(self, fieldset)
    pass

to class Field results in

__init__() takes 1 positional argument...

• I could lump all fields tables into one schema and add column 'schema_name' but I still need Field have link to its fieldset.

Can this be done using SQLAlchemy ORM or should I switch to SqlAlchemy Core where I would have more control over object instantiation?

解决方案

So the problem is solvable and the solution is documented as Augmenting the Base

from sqlalchemy import Column, Integer, String
from sqlalchemy.ext.declarative import declared_attr, declarative_base

class Field:
    __tablename__ = 'fields'

    @declared_attr
    def __table_args__(cls):
        # schema will be part of class name, which works for me
        # Example: class FieldSCM --> Field with schema SCM
        return dict(schema=cls.__name__[5:].upper())

    id = Column(String(20), primary_key=True)

Field = declarative_base(cls=Field)


class FieldSet:
    def __init__(self, schema):
        self.fieldtype  = type('Field' + schema.upper(), (Field,), {})

Proof of concept:

FieldSet('ork').fieldtype.__table__

Table('fields', MetaData(bind=None), Column('id', String(length=20), table=, primary_key=True, nullable=False), schema='ORK')

这篇关于具有动态表模式的 SQLAlchemy ORM的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！