Using SQLAlchemy how do I populate rows after creating the db using db.create_all()

Question

I have a create_all call in my SQLAlchemy app.py

@app.before_first_request
def create_tables():
    db.create_all()

And define a basic user in a user.py model file:

class UserModel(db.Model):
    __tablename__ = 'users'

    id = db.Column(db.Integer, primary_key = True)
    username = db.Column(db.String(80))
    password = db.Column(db.String(80))

    def __init__(self, username, password):
        self.username = username
        self.password = password

I want to pre-populate some default data, in this example some base users:

    admin = self('admin', 'test')
    guest = self('guest', 'test')

Can I input them in the app.py somehow? Or in another create_data.py type file?

use db.session.add() or even db.session.add_all([admin, guest]) will work flask-sqlalchemy.pocoo.org/2.1/quickstart/… — Deusdeorum
– Deusdeorum, Commented Dec 18, 2016 at 8:42

Stijn Diependaele · Accepted Answer · 2016-12-18 20:25:50Z

9

You could create an example_data.py file like this:

from user import UserModel

def db_load_example_data(app, db):
    admin = UserModel('admin', 'test')
    guest = UserModel('guest', 'test')
    with app.app_context():
        db.session.add(admin)
        db.session.add(guest)
        db.commit()

and then in your startup script you can call this

from example_data import db_load_example_data

db_load_example_data(app, db)

answered Dec 18, 2016 at 20:25

Stijn Diependaele

6085 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

snakecharmerb · Accepted Answer · 2025-05-26 09:18:00Z

Populate the table in an after_create event handler. The code will execute after the table has been created by db.create_all(). In this example I have inserted the data directly into the model's __table__, rather than using a session to create UserModel instances. This is so that the data is inserted in the same transaction as that which creates the table. Using a session (db.session.add(Usermodel(..)) seems to work, but uses a different connection which could potentially cause problems.

#Place this code after the model definition.

@db.event.listens_for(UserModel.__table__, 'after_create')                                                                                                                                                                                                                    
def initialise_values(target, connection, **kw):   
    # target is the newly created "users" table.                                                                                                                                                                                                                           
    ins = target.insert()                                                                                                                                                                                                                                                     
    values = [                                                                                                                                                                                                                                                                
        {'username': 'admin', 'password': 'admin'},                                                                                                                                                                                                                           
        {'username': 'guest', 'password': 'guest'},                                                                                                                                                                                                                           
    ]                                                                                                                                                                                                                                                                         
    connection.execute(ins, values)

Collectives™ on Stack Overflow

Using SQLAlchemy how do I populate rows after creating the db using db.create_all()

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related