Create table using SQLAlchemy, but defer creating indexes until data is loaded

I have a python file that SQLAlchemy uses to define all tables in a given database, including all applicable indexes and foreign key constraints. The file looks something like this:

Base = declarative_base()

class FirstLevel(Base):
    __tablename__ = 'first_level'
    first_level_id = Column(Integer, index=True, nullable=False, primary_key=True, autoincrement=True)
    first_level_col1 = Column(String(100), index=True)
    first_level_col2 = Column(String(100))
    first_level_col3 = Column(String(100))

class SecondLevel(Base):
    __tablename__ = 'second_level'
    second_level_id = Column(Integer, index=True, nullable=False, primary_key=True, autoincrement=True)
    first_level_id = Column(None, ForeignKey(FirstLevel.first_level_id, onupdate='cascade', ondelete='cascade', deferrable=True), index=True, nullable=False)
    second_level_col1 = Column(String(100), index=True)
    second_level_col2 = Column(String(100))
    second_level_col3 = Column(String(100))

class ThirdLevel(Base):
    __tablename__ = 'third_level'
    third_level_id = Column(Integer, index=True, nullable=False, primary_key=True, autoincrement=True)
    first_level_id = Column(None, ForeignKey(FirstLevel.first_level_id, onupdate='cascade', ondelete='cascade', deferrable=True), index=True, nullable=False)
    second_level_id = Column(None, ForeignKey(SecondLevel.second_level_id, onupdate='cascade', ondelete='cascade', deferrable=True), index=True, nullable=False)
    third_level_col1 = Column(String(100), index=True)
    third_level_col2 = Column(String(100))
    third_level_col3 = Column(String(100))

...

      

I can use this file to create a new schema in the postgres database by running the following command:

engine = create_engine('postgresql://username:password@path_to_database')
Base.metadata.create_all(engine)

      

The problem is that I have to load a huge amount of data into this newly created database and it takes a long time unless I drop the indexes and foreign key constraints. But manually deleting and manually re-creating them after I've finished inserting all the data is a big problem and removes much of the usability of SQLAlchemy for creating the database schema.

I was wondering if there is a way to use SQLAlchemy to create tables in the database, load data, and reuse the ORM SQLAlchemy to create all indexes and foreign key constraints?

+3


source to share


1 answer


You can do this using Alembic migration scripts.



  • Create initial tables / drop existing indexes
  • Loading data
  • Add Indexes
+2


source







All Articles