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',…

Accepted Answer

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.

SQLAlchemy create dynamic tables and columns

Solution

Problem