Create a table from a SQLAlchemy model with a different name?

Question

I am importing data from csv files into a table created using the SQLAlchemy declarative api.

I receive updates to this data which I want to stage in a temporary table with the same structure for preprocessing.

E.g:

from sqlalchemy import Column,Integer
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

class MyModel(Base):
    __tablename__ = "mymodel"
    test_column = Column(Integer,primary_key=True)

I can use MyModel.__table__.create() to create this table.

Can I use a similar construct to create another table with the same model and a different name?

What would be the recommended way to achieve this?

Somewhat related: http://stackoverflow.com/questions/19163911/dynamically-setting-tablename-for-sharding-in-sqlalchemy. — alecxe, Sep 02 '15 at 17:21
concrete table inheritance might help, but might also be overkill: http://docs.sqlalchemy.org/en/rel_1_0/orm/extensions/declarative/inheritance.html#concrete-table-inheritance — Lee, Sep 02 '15 at 19:31
I prefer comment from @alecxe, but if you would like to be adventurous, you could do: `tb2 = MyModel.__table__._clone(); tb2.name = "my_temp_table_name"; tb2.create()`. But this uses non-public `_clone()`, and you might have to put up with implications. — van, Sep 03 '15 at 06:57
@van you were right not to depend on _clone(). In my testing it ended up calling _clone() of Immutable which returns the original instance in sqlalchemy version 1.0.8. — amckinley, Sep 09 '15 at 17:00

score 0 · Answer 1 · answered Sep 10 '15 at 23:02

0

Just extend your existing table and change its name

class StagingMyModel(MyModel):
    __tablename__ = "staging_mymodel"

answered Sep 10 '15 at 23:02

Ciaran Liedeman

779
5
13

1

That's what I thought, too, but you get the error `NoForeignKeysError: Can't find any foreign key relationships between 'mymodel' and 'staging_mymodel'`. – TheDudeAbides Dec 28 '21 at 23:19

TheDudeAbides · Answer 2 · 2021-12-29T00:24:25.593

This worked for me:

from sqlalchemy import Column,Integer
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

class MyBase(Base):
    __abstract__ = True

    test_column = Column(Integer,primary_key=True)

class MyModel(MyBase):
    __tablename__ = "mymodel"


class MyStagingModel(MyBase):
    __tablename__ = "mymodel_staging"

Reference: https://docs.sqlalchemy.org/en/14/orm/declarative_tables.html#using-deferredreflection

See also https://sparrigan.github.io/sql/sqla/2016/01/03/dynamic-tables.html for an approach that probably jibes with what you were originally thinking of with ModelName.__table__.create().

Create a table from a SQLAlchemy model with a different name?

2 Answers2