SQLAlchemy create dynamic tables and columns

Question

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()

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:

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'

Any ideas what I am doing wrong?

• Sam Hartman: Please include the full traceback including line numbers etc.
• Azat Ibrakov: i recommend you to start working with SQLAlchemy from tutorial
• OAK: @AzatIbrakov thanks for the information and you solution worked for me. I've been reading the tutorial off and on for a while, possibly I need to aquire more knowledge on a wider base.
• Azat Ibrakov: it means that you are passing list object ctype as argument to Column initializer's type_ parameter but it supposed to be one of TypeEngine subclasses (like String and Integer)
• Azat Ibrakov: seems like SQLAlchemy failing on statement self.type._set_parent_with_dispatch(self) in Column initializer where type is set to your ctype list object

Answer 1

There are many things that are not correct here.

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.

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 won't recognize strings 'Integer', 'String', they are not valid Column types.

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.

Also you don't need class test.

Your code can look like

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()

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

test = post_meta.tables['customers']

without constructing from scratch.

• OAK: thats for the solution. The mistakes were quite basic, thanks for pointing them out
• Azat Ibrakov: do you have access to source database?
• OAK: no. All information is retrieved through the end points, I don't have direct access to the source database.
• Mason Choi: How can I add something to this newly created table?
• Azat Ibrakov: @MasonChoi: you should ask a new question since it is not related to OP