CODEWITHSUNDEEP
pythonmysqlormsqlalchemyclass-constructors
Erfan
Erfan

Reputation: 42946

Create MySQL table with sqlalchemy ORM, from dynamically changing dictionary

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:

Upvotes: 0

Views: 341

Answers (1)

Ahmet Bilgin
Ahmet Bilgin

Reputation: 154

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

Upvotes: 2

Related Questions