SQLAlchemy: how to create dynamic columns multiple times

Question

I succeeded to create a sql table with columns defined dynamically, thanks to python class reflexion. But I cannot run the code more than one time. For instance, the following import_file , should create a static table and a dynamic table with specific columns. It works if I run it one time. But the second time it crashs and returns the following error :

Table 'dynamic' is already defined for this MetaData instance

Code example :

from sqlalchemy import Column, Integer, String, Float, Boolean, Table
from sqlalchemy import create_engine, MetaData
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker, relationship
from sqlalchemy.orm import clear_mappers

import os

def import_file(filename, columns):
    path =  filename
    if os.path.exists(path):
        os.remove(path)

    engine = create_engine(f"sqlite:///{path}", echo=True)

    clear_mappers()
    Base = declarative_base()

    class StaticTable(Base):
        __tablename__ = "static"
        id = Column(Integer, primary_key=True)
        name = Column(String)


    class DynamicTable(Base):
        __tablename__ = "dynamic"
        id = Column(Integer, primary_key=True)

    for c in columns:
        setattr(DynamicTable,c,Column(String))

    Base.metadata.create_all(engine)


import_file("test.db", columns = ["age","test"]) # WORKS
import_file("test2.db", columns= ["id","age","foo","bar"]) # NOT WORKING

I try to use sqlalchemy.orm.clear_mappers, but unsucessfully. Any idea how can I resolve it ?

SQLAlchemy does not allow you to define a model for a database table twice. You are redefining the models on every call of your function. — Klaus D., Dec 17 '18 at 01:59
So, suppose I would like to create many sqlite file with different tables shemas and same name. How can I do it ? — DrIDK, Dec 17 '18 at 09:04

score 0 · Answer 1 · answered Sep 05 '19 at 08:30

I think that it is not full you code, because conflicts on Base.metadata

Mappers for link on orm classes and tables, you have defined tables.

You can try somethink like that

import_file("test.db", columns = ["age","test"])
Base.metadata.clear()
sqlalchemy.orm.clear_mappers()
import_file("test2.db", columns= ["id","age","foo","bar"])

SQLAlchemy: how to create dynamic columns multiple times

1 Answers1