基于表库动态创建sqlAlchemy元类 [英] Dynamically creating sqlAlchemy Metaclass based on table library
问题描述
我们在ERP系统中有多个客户.每个客户端都有其自己的数据库.在模式方面,数据库是相同的.
We have several clients in a ERP system. Each client has it's own database. The databases are identical in terms of schema.
不要问我为什么,但是ERP db没有正式定义的PK,因此不可能反映数据库.相反,我们发现使用表声明详细说明PK来声明元类,和自动加载的作品.例子:
Do not ask me why, but the ERP db does not have formally defined PKs, and so it is not possible to reflect the database... In stead we found that declaring a Metaclass, with a table declaration, detailing PK, and autoload works. An example:
class Customers(Base):
__table__ = Table('Customers', Base.metadata,
Column('UniqueNo', Integer, primary_key=True),
schema = 'databaseName.schema',
autoload = True
快速不在schema =
部分上.每个数据库的架构都是相同的,但是架构的名称(以及数据库名称本身)是不同的.像这样在元类中定义架构,使我们能够跨数据库查询,能够
A quick not on the schema =
part. The schema is the same for each database, but the naming of the schema (as well as the db name itself) is different. Defining the schema in the Metaclass like this, enables us to query accross the databases, being able to
在创建代码结构时,最简单的方法是执行Metaclass声明.每个数据库都有一个.py文件,并且在每个文件中执行相同的Metaclass声明,仅更改架构,并在类名后添加后缀以避免命名混乱.像这样:
When creating the code structure, the simplest way to do the Metaclass declaration is manually. Having one .py file for each database, and doing the same Metaclass declaration within each file, changing only the schema, and adding a suffix to the class name to avoid naming confusion. Like so:
client1.py
client1.py
class Customers_1(Base):
__table__ = Table('Customers', Base.metadata,
Column('UniqueNo', Integer, primary_key=True),
schema = 'Dbclient1.client1Schema',
autoload = True
client2.py
client2.py
class Customers_2(Base):
__table__ = Table('Customers', Base.metadata,
Column('UniqueNo', Integer, primary_key=True),
schema = 'Dbclient2.client2Schema',
autoload = True
这样做是可行的,但是我们希望我们可以通过仅基于一个ERPTables.py文件动态创建元类来减少代码量.示例:
Doing it this way works, but our hope is that we could reduce the amount of code by dynamically creating the Metaclasses based on only one ERPTables.py file. Example:
ERPTables.py
class Customers(Base):
__table__ = Table('Customers', Base.metadata,
Column('UniqueNo', Integer, primary_key=True),
autoload = True
这使我们走上元类的道路,这是陌生的领域.我已经到了可以动态创建元类声明的地步.但是:注册声明是我的理解不够的地方.我走了这么远:
This leads us down the road of Metaclasses, which is unfamiliar territory. I have gotten to the point where I am able to dynamically creating the metaclass declaration. But: registering the declaration is where my understanding falls short. I have come this far:
from sqlalchemy import Table
import ERPTables
import inspect
def iterate_ERPTables_Tables():
for table in inspect.getmembers(ERPTables):
if isinstance(table[1], Table) :
return table[0], table[1]
dbSchemas = {'_1': 'Dbclient1.client1Schema', '_2': 'Dbclient2.client2Schema'}
tables = [iterate_TableTest_Tables()]
for key in dbSchemas:
for table in tables:
cls = type(table[0] + key, (Base, ), {'__tablename__': table[0], '__table__': table[1]})
break
此代码有效!唯一的问题是SA元类被命名为cls.因此,break
.没有它,我们试图声明具有相同类名的多个元类.
This code works! The only problem being that the SA Metaclass gets named cls. Hence the break
. Without it we are trying to declare several Metaclasses with the same class name.
我尝试了几种方法来解决此问题,例如尝试使用不熟悉的元类透视图:
I have tried several approaches to resolve this, like attempts to use the unfamiliar metaclass perspective:
dbSchemas = {'_1': 'Dbclient1.client1Schema', '_2': 'Dbclient2.client2Schema'}
tables = [iterate_TableTest_Tables()]
for key in dbSchemas:
for table in tables:
type(table[0] + key, (Base, ), {'__tablename__': table[0], '__table__': table[1]}).__new__
要全面破解黑客,请执行以下操作:
To full blown hack work arounds:
dbSchemas = {'_1': 'Dbclient1.client1Schema', '_2': 'Dbclient2.client2Schema'}
tables = [iterate_TableTest_Tables()]
for key in dbSchemas:
for table in tables:
exec("%s = %s" % (table[0] + key, type(table[0] + key, (Base, ), {'__tablename__': table[0], '__table__': table[1]})))
但是我所有的尝试都不成功,因此,在绳索的尽头,我求助于SO,希望有人能告诉我如何解决这个问题!
But all of my attemtps have been unsuccsessful and so, being at the end of the rope, I turn to SO in the hope that someone can show me how to solv this!
PS:万一有人想知道的话,到目前为止,我还没有解决如何将dbSchemas
词典中的模式注入到元类中.我希望能找到一种方法,但一次却有一个问题!
PS: In case anyone is wondering, I have not, as of yet, solved how to inject the schema from the dbSchemas
dictionnary into the Metaclass. I am hoping to find a way, but one problem at a time!
推荐答案
如果要导出对类的引用,可以将它们添加到globals()
:
If you want to export the reference to the classes, you can add them to globals()
:
globals()[table[0] + key] = type(...)
这篇关于基于表库动态创建sqlAlchemy元类的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!