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?

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.