Create MySQL table with sqlalchemy ORM, from dynamically changing dictionary

Question

I want to create a table in my MySQL database, from a dictionary which will dynamically change over time.

The dictionary looks as followed, which specify's the name + type of the columns to be created. Which is a settings file which the user fills in before the project is run.

dct = {'ID':'String',
       'Benefit':'Float',
       'Premium':'Float'}

I know how to create this by hardcoding this in a mapping class as followed:

from sqlalchemy import create_engine, Column, String, Float
from sqlalchemy.ext.declarative import declarative_base

engine = create_engine('mysql+mysqldb://...')

Base = declarative_base()

class Table(base):
    __tablename__ = 'example'

    id = Column(String(30), primary_key=True)
    benefit = Column(Float)
    Premium = Column(Float)

Question: How would I create these tables without hardcoding the names and types, but substracting these from the dictionary.

I also tried to construct a class from dict:

class Policy:

    def __init__(self, dictionary):
        for k, v in dictionary.items():
            setattr(self, k, v)

But didn't knew how to implement this further.

Similar questions:

1
2

it's not considered good practice to try and dynamically create variables in the `local` or `global` scope from a string which is what it seems like the desired behavior is — gold_cy, Sep 24 '19 at 12:28
I understand, but im otherwise not sure how to solve this problem. The settings are stored in a `json` which the user has filled in, and the table has to be created from the file. With the specified names + types. — Erfan, Sep 24 '19 at 12:30
Similar question #2 is what you need, with the addition of translating your `dct` to a dict of actual `Column` instances. — Ilja Everilä, Sep 25 '19 at 20:13

score 2 · Answer 1 · answered Mar 15 '21 at 18:00

from here SQLAlchemy create dynamic tables and columns

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

Create MySQL table with sqlalchemy ORM, from dynamically changing dictionary

1 Answers1