How can I load initial data into a database using sqlalchemy

Question

I want to be able to load data automatically upon creation of tables using SQLAlchemy.

In django, you have fixtures which allow you to easily pre-populate your database with data upon creation of a table. This I found useful especially when you have basic "lookup" tables e.g. product_type, student_type which contain just a few rows or even a table like currencies which will load all the currencies of the world without you having to key them in over and over again when you destroy your models/classes.

My current app isn't using django. I have SQLAlchemy. How can I achieve the same thing? I want the app to know that the database is being created for the first time and hence it populates some tables with data.

Answer 1

I used the event listener to prepopulate database with data upon creation of a table.

Let's say you have ProductType model in your code:

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

Base = declarative_base()

class ProductType(Base):
    __tablename__ = 'product_type'
    id = Column(Integer, primary_key=True)
    name = Column(String(100))

First, you need to define a callback function, which will be executed when the table is created:

def insert_data(target, connection, **kw):
    connection.execute(target.insert(), {'id': 1, 'name':'spam'}, {'id':2, 'name': 'eggs'})

Then you just add the event listener:

event.listen(ProductType.__table__, 'after_create', insert_data)