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?
You can do this using Alembic migration scripts.
- Create initial tables / drop existing indexes
- Loading data
- Add Indexes