用于在SQLAlchemy中构建表列和关系的函数

Question

我正在尝试使用SQLAlchemy组合一个函数来构建表及其关系。问题是，我正在处理具有许多列和许多外键的表。有些关系是多对一的，但另一些关系是多对多的。由于有许多方面对数据库的结果感兴趣，因此将来肯定会添加额外的列。因此，能够从函数构建表是可取的。这是我看了几天后得到的：

from sqlalchemy import Column, String, Integer, ForeignKey, MetaData, Table, create_engine
from sqlalchemy.orm import relationship, backref, sessionmaker
from sqlalchemy.ext.declarative import declarative_base


db = "alchemy_database_test.db"
db_memory = 'sqlite:///' + db
engine = create_engine(db_memory)

Base = declarative_base()
post_meta = MetaData(bind=engine)

#Example of names to build tables and columns
col_names = ['productID', 'productShortName', 'productName','quantity']
col_types = [Integer, String, String, Integer]
primary_key_flags = [True, False, False, False]
nullable_flags = [False, False, False, False]

# Lists to build relationship statements
## relationship name
name_of_target = ['platforms','regions','references', 'protocols']
# name of the target table containing the foreign ID to link
name_of_target_table = ['platform','region','reference','protocol']

# Since I am using a single list of foreign keys, and many-to-one has a different 
# syntax compared to many-to-many relationships (or so I think), I'm building two
# lists and filling in with None to make them the same size and iterate 
# simultaneously through both

## for many to one relationships
name_of_target_column = ['platformID','regionID',None,None]
## for many to many relationships
name_of_column_m2m = [None,None,'referenceID','protocolID']

def create_sqltable(post_meta,name,col_names,col_types,primary_key_flags,
   link_tos=[], link_to_table=[], link_to=[], many_to_many=[]):

   test = Table(name, post_meta,

               #here i'm creating the columns and assigning primary key
               #and auto-increment, if needed. This works well.

               *(Column(c_names, c_types,
                       primary_key=primary_key_flag,
                       autoincrement=primary_key_flags,
                       nullable=nullable_flag)
               for c_names,
                   c_types,
                   primary_key_flag,
                   nullable_flag in zip(col_names,
                                           col_types,
                                           primary_key_flags,
                                           nullable_flags)),

               #I'm trying to do the same with the relationship statement
               # building a statement for each foreign key by assigning
               # the name of the relationship, the target table, and 
               # either a 'backref' or a 'secondary' argument depending 
               # if it is a maney-to-one or a many-to-many relationship

               *(relationship(lk_tos, #name of the relationship
                       lk_to_table, #name of the target table
                       secondary=mny_to_mny, #name of the column in the target
                                             # table (many2many relationship)
                       backref=lk_to_col, #name of the column in the target 
                                          # table (many2one relationship)
                       cascade="all,delete")
               for lk_tos, # name of relationship
                   lk_to_table, # name of target table
                   lk_to_col, # name of column in target table (many2one)
                   mny_to_mny in zip(link_tos,
                                           link_to_table,
                                           link_to,
                                           many_to_many))) # column in target table(many2many)
   test.create()

#calling the function with the lists declared earlier
create_sqltable(post_meta,'table_name',col_names, col_types, primary_key_flags,
       name_of_target, name_of_target_table, name_of_target_column, name_of_column_m2m)

我收到以下错误：

---------------------------------------------------------------------------
TypeError                                 Traceback (most recent call last)
~/OneDrive - 3M/Projects/Searchable database/Code/Relational-Database/database_wd_v3.py in 
      59     ['table1','table2','customer','lamp','crystals'],
      60     ['table1ID','table2ID','customerID',None,None],
----> 61     [None,None,None,'lampID','crystalID'])

~/OneDrive - 3M/Projects/Searchable database/Code/Relational-Database/database_wd_v3.py in create_sqltable(post_meta, name, col_names, col_types, primary_key_flags, link_tos, link_to_table, link_to, many_to_many)
     51                                             link_to_table,
     52                                             link_to,
---> 53                                             many_to_many)))
     54     test.create()

~/OneDrive - 3M/Projects/Searchable database/Code/Relational-Database/database_wd_v3.py in (.0)
     48                     lk_to_table,
     49                     lk_to_col,
---> 50                     mny_to_mny in zip(link_tos,
     51                                             link_to_table,
     52                                             link_to,

TypeError: relationship() got multiple values for argument 'secondary'

因为这是我第一次尝试SQLite和SQLAlchemy，所以我猜这是我遇到的最小的问题。但是，在消除了值传递，甚至只是直接键入字符串(即secondary = 'referenceID')之后，我无法纠正这个问题。

谢谢你的帮助。

Answer 1

您在relationship()的参数中有一个问题。当您查看文档时，您将看到第一个参数是目标表，第二个参数是多对多关系的辅助表。函数签名的重要部分是：

def sqlalchemy.orm.relationship(argument, secondary=None, ...

在你的代码中，你可以这样调用它：

relationship(lk_tos, #name of the relationship
                       lk_to_table, #name of the target table
                       secondary=mny_to_mny, #name of the column in the target
                                             # table (many2many relationship)
                       backref=lk_to_col, #name of the column in the target 
                                          # table (many2one relationship)
                       cascade="all,delete")

这里的关键点是传递两个位置参数(lk_tos和lk_to_table)，根据SQLAlchemy定义，这两个参数将分别进入argument和secondary。但是，您可以传递一个额外的关键字参数作为secondary=mny_to_mny。这意味着secondary将获得值lk_to_table和mny_to_mny。这解释了您看到的错误。

我建议将多对多关系从一对多关系中分离出来。这应该会给你更多的灵活性来正确地调用函数。

不幸的是，我无法在我的脑海中解开你的代码，给你一个适当的新代码建议。