SQLAlchemy 创建动态表和列 [英] SQLAlchemy create dynamic tables and columns

问题描述

我正在尝试根据我检索的数据动态创建数据库表和列.我检索数据库列表、列名称和属性列表，例如列类型、primary_key/unique、可为空以及其他元数据.我正在尝试使用这些信息来动态创建表格，并且一直在使用论坛帖子来更好地了解如何实现这一点.所以我想根据我检索的信息创建表 - 数据库和列信息(列名和列类型、主键和可为空的信息.检索到的这些信息可能每天或每周更改.论坛帖子 #1 - Sqlalchemy 动态创建表和映射类

I'm trying to create db tables and columns dynamically in line with the data I retrieve. I retrieve a list of databases, a list of column names and attributes such as column type, primary_key / unique, nullable as well as other metadate. I'm trying to use this information to create the tables dynamically and have been using the forum posts to get a better idea of how to achieve this. So I want to create the tables based on the information I retrieve - database and the column information (colnames and column type, primary key and nullable information. This information retrieved could change daily or weekly. Forum post #1 - Sqlalchemy dynamically create table and mapped class

postgresql_db = engine(...)

post_meta = sql.MetaData(bind=postgresql_db.engine)

post_meta.reflect(schema='customers')

connection = postgresql_db.engine.connect()

col_names = ['id', 'fname', 'lname', 'age']
ctype = ['Integer', 'String', 'String', 'Integer']
pk = ['True', 'False', 'False', 'False']
nulls = ['No', 'No', 'No', 'No']

class test(object):

     test = Table('customers', post_meta,
              *(Column(col, ctype, primary_key=pk, nullable=nulls)
           for col, ctype, pk, nulls in zip(col_names, ctype, pk, nulls))

test.create()

有一条错误信息:AttributeError: 'list' 对象没有属性_set_parent_with_dispatch似乎无法确定此错误究竟指的是什么.

There is an error message: AttributeError: 'list' object has no attribute _set_parent_with_dispatch Can't seem to identify what this error is referring to exactly.

追溯:

Traceback (most recent call last):
  File "C:/Users/xxx/db.py", line 247, in <module>
    main()
  File "C:/Users/xxx/db.py", line 168, in main
    for col, ctype, pk, nulls in zip(col_names, ctype, pk, nulls)
  File "C:/Users/xxx/apidb.py", line 168, in <genexpr>
    for col, ctype, pk, nulls in zip(col_names, ctype, pk, nulls)
  File "C:\Python27\lib\site-packages\sqlalchemy\sql\schema.py", line 1234, in __init__
    self._init_items(*args)
  File "C:\Python27\lib\site-packages\sqlalchemy\sql\schema.py", line 79, in _init_items
    item._set_parent_with_dispatch(self)
AttributeError: 'list' object has no attribute '_set_parent_with_dispatch'

知道我做错了什么吗?

推荐答案

这里有很多不正确的地方.

There are many things that are not correct here.

nullable 参数应该具有 bool 类型，但您正在尝试传递 str 对象nulls，pk 和 primary_key 参数也是一样的.

nullable parameter in Column initializer should have type bool, but you are trying to pass a str object nulls, same thing for pk and primary_key parameter.

此外，您试图在理解中覆盖名称 ctype、pk、nulls，这是不正确的，并引发了给定的异常.您应该在理解中重命名从 zip 生成的对象.

Furthermore, you are trying to overwrite names ctype, pk, nulls in comprehension, which is not correct and raises given exception. You should rename objects generated from zip in comprehension.

SQLAlchemy 无法识别字符串 'Integer', 'String'，它们不是有效的 Column类型.

SQLAlchemy won't recognize strings 'Integer', 'String', they are not valid Column types.

如果你想反映名为'customers'的特定表，应该使用参数only，而不是schema，并且它应该是名字的list.

If you want to reflect specific table called 'customers', it should be done using parameter only, not schema, and it should be list of names.

你也不需要 test 类.

你的代码看起来像

from sqlalchemy import MetaData, Table, Column, Integer, String

postgresql_db = engine(...)

post_meta = MetaData(bind=postgresql_db.engine)

post_meta.reflect(only=['customers'])

connection = postgresql_db.engine.connect()

columns_names = ['id', 'fname', 'lname', 'age']
columns_types = [Integer, String, String, Integer]
primary_key_flags = [True, False, False, False]
nullable_flags = [False, False, False, False]

test = Table('customers', post_meta,
             *(Column(column_name, column_type,
                      primary_key=primary_key_flag,
                      nullable=nullable_flag)
               for column_name,
                   column_type,
                   primary_key_flag,
                   nullable_flag in zip(columns_names,
                                        columns_types,
                                        primary_key_flags,
                                        nullable_flags)))

test.create()

最后，如果你做 post_meta.reflect(only=['customers']) 并且它工作，给定的表可以简单地通过

Finally, if you do post_meta.reflect(only=['customers']) and it works, given table can be obtained simply by

test = post_meta.tables['customers']

无需从头构建.

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