Is there a way to create tables in a DB using python Dictionary?

Question

I have a large dictionary of items - ~400 columns. I have a script to detect and create the type of each item so I have {'age' = INT, "Name" = String,etc..) but I'm not sure how to use that to create a table in SQLAlchemy or directly creating the query?

I am using postgres but I am familiar with mysql & sqlite so anything that works for those I would be able to apply to my usecase.

Does this answer your question? [Creating Table from dictionary in SQLAlchemy](https://stackoverflow.com/questions/44937601/creating-table-from-dictionary-in-sqlalchemy) — Błotosmętek, Mar 05 '20 at 21:03
Thank you @Błotosmętek I saw that question when I searched for a answer but I didn't see the connection in how to create the table itself. They seem to know the number of columns and type in advance, it seemed? — Lostsoul, Mar 05 '20 at 21:09

Błotosmętek · Accepted Answer · 2020-03-05T22:12:26.963

1

What about this:

from sqlalchemy import create_engine, Table, Column, MetaData
metadata = MetaData()
fields = (Column(colname, coltype) for colname, coltype in your_dict.items())
t = Table(name, metadata, *fields)
engine = create_engine(database)
metadata.create_all(engine)

You need to have objects from sqlalchemy.sql.sqltypes rather than strings as values in your_dict:

from sqlalchemy.sql.sqltypes import String, Integer

See https://docs.sqlalchemy.org/en/13/core/type_basics.html for the whole list.

edited Mar 05 '20 at 22:12

answered Mar 05 '20 at 21:24

Błotosmętek

12,717
19
29

I tried that but i am getting this error : sqlalchemy.exc.ArgumentError: 'SchemaItem' object, such as a 'Column' or a 'Constraint' expected, got 'String' – Lostsoul Mar 05 '20 at 21:52
here's a example of how my your_dict dictionary looks like {'Code': 'String', 'Type': 'String', 'Name': 'String', 'Exchange': 'String'} – Lostsoul Mar 05 '20 at 21:53
Thank you so much it worked. So cool and saved me(and hopefully others) a ton of time when creating alot of columns. – Lostsoul Mar 05 '20 at 22:20

Is there a way to create tables in a DB using python Dictionary?

1 Answers1