Completely refresh SQLAlchemy with dynamic table generation

Question

I need to create many similar databases in different locations in a for-loop. In the beginning of the loop, I create the engine to a new path_sql_db on disk.

    engine = sa.create_engine("sqlite:///{}".format(path_sql_db), echo=0, listeners=[util_sa.ForeignKeysListener()])
    Session = sa.orm.sessionmaker(bind=engine)
    session = Session()

Then I have my Tables in several modules inherit from DB_Base which was defined in an external module;

from sqlalchemy.ext.declarative import declarative_base
DB_Base = declarative_base()

The problem is that during my next iteration of the for-loop, I can't create my tables since they still exist somewhere?

InvalidRequestError: Table 'vector_var01' is already defined for this MetaData instance.  
Specify 'extend_existing=True' to redefine options and columns on an existing Table object.

I've tried to MetaData.drop_all() from the engine;

meta = sa.MetaData(bind = engine)
meta.reflect()
meta.drop_all()
session.close()

And also from the Base;

DB_Base.metadata.bind = engine
DB_Base.metadata.reflect()
DB_Base.metadata.drop_all()

With no success, I'm still just flailing around in the dark here.

Which MetaData instance is the error referring to? How can I completely reset the state of my database code?

EDIT

Ok I tracked down the problem. I'm trying to dynamically generate ORM tables. I am studying optimization routines and storing design space variables in their own tables, one row per possible value of the variable.

Minimal example causing error;

from sqlalchemy import *
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
Base = declarative_base()

class Foo(Base):
    __tablename__ = 'foo'
    id = Column(Integer, primary_key=True)
    name = Column(String(50))

def generate_variable_table_class(name):
    """This is a helper function which dynamically creates a new ORM enabled class
    The table will hold the individual values of each variable
    Individual values are stored as a string
    """

    class NewTable( Base ):
        __tablename__ = "vector_{}".format(name)
        id = Column(Integer, primary_key=True)
        value = Column(String(16), nullable=False, unique=True)
        def __init__(self,value):
            self.value = str(value)

        def __str__(self):
            return self.value

        def __repr__(self):
            return self.value    


    NewTable.__name__ = "vector_ORM_{}".format(name)

    return NewTable


if __name__ == "__main__":

    for name in 'asfd', 'jkl', 'xyz':
        print("For loop: ",name)
        engine = create_engine(r'sqlite:///c:\testdelete\{}.sql'.format(name))
        Base.metadata.create_all(engine)
        Session = sessionmaker(bind=engine)
        session = Session()

        bunch_o_foos = [Foo(name = i) for i in range(10)]
        session.add_all(bunch_o_foos)
        session.commit()
        for foo in bunch_o_foos:
            print(foo.id)


        variables = [generate_variable_table_class(i) for i in range(10)]

Which is actually the same as this question; Dynamic Python Class Definition in SQLAlchemy. Is this not possible?

Answer 1

As a minimal example:

from sqlalchemy import *
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()

class Foo(Base):
    __tablename__ = 'foo'
    id = Column(Integer, primary_key=True)
    name = Column(String(50))

for name in 'asfd', 'jkl', 'xyz':
    engine = create_engine('sqlite:///{}'.format(name))
    Base.metadata.create_all(engine)

Runs fine. drop_all means drop tables, probably not what you want. And once you use bind you're tying the metadata object to that specific engine. Although:

Base.metadata.bind = engine
Base.metadata.create_all()

Also works in the minimal example.

EDIT

Based on the sample case, you're getting the error because you're attempting to define a class with the same table name (e.g. vector_0) using the same Base subclass, which has a MetaData object attached to it, which can only have one of each table name.

• In your new simple case, there is no difference between the tables per database, so you should move the calls to generate_variable_table_class out of your main loop and only make them once.

• If you have per-database behavior, you could use a new Base each time (i.e. move Foo into a function too!) (Also, the calls to generate_variable_table_class should be above create_all not at the end)

• Even then, sqlalchemy doesn't like that they are all named NewTable. The declarative ORM evaluates your class definitions, so it sees NewTable before you set __name__ afterward. A solution would be to not use the declarative system (see "Classical Mappings" in the docs).

• But another way would be to extend the declarative metaclass to handle name-changing. The declarative_base function takes an explicit metaclass argument so this seems within the spec of the framework. To use the one below, you would set __name__ = "vector_ORM_{}".format(name) inside the NewTable definition. If you want to be really clean update __qualname__ too, though sqlalchemy doesn't use it anywhere.

from sqlalchemy.ext.declarative import declarative_base, DeclarativeMeta

class NamingDeclarativeMeta(DeclarativeMeta):
    def __init__(cls, classname, bases, dict_):
        if '__name__' in cls.__dict__:
            cls.__name__ = classname = cls.__dict__['__name__']
        DeclarativeMeta.__init__(cls, classname, bases, dict_)

Base = declarative_base(metaclass=NamingDeclarativeMeta)