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?

It would be easier to see what your specific problem is if you posted your create table code. — Jason S, Aug 04 '14 at 16:42

Jason S · Answer 1 · 2014-08-05T15:16:18.230

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)

Completely refresh SQLAlchemy with dynamic table generation

1 Answers1