How to Generate Fixtures from Database with SqlAlchemy

Question

I'm starting to write tests with Flask-SQLAlchemy, and I'd like to add some fixtures for those. I have plenty of good data for that in my development database and a lot of tables so writing data manually would get annoying. I'd really like to just sample data from the dev database into fixtures and then use those. What's a good way to do this?

For people seeing this in the future: I wound up writing a script that samples my database and writes fixtures to a file from that which I load in on demand. It's unique to my schema and definitely not the best approach. Hopefully someone comes up with something better. I also tried to use the Fixture module (http://farmdev.com/projects/fixture/), but didn't have much luck getting it to work. — Eli, Jul 08 '15 at 23:08
I'm looking for the same thing, would love to find something... — pip, May 16 '16 at 17:04

score 4 · Answer 1 · answered May 16 '17 at 21:02

i would use factory boy

to create a model factory you just do:

import factory
from . import models

class UserFactory(factory.Factory):
    class Meta:
        model = models.User

    first_name = 'John'
    last_name = 'Doe'
    admin = False

then to create instances:

UserFactory.create()

to add static data just give as kwarg to create

UserFactory.create(name='hank')

so to seed a bunch of stuff throw that in a for loop. :)

score 0 · Answer 2 · answered Dec 12 '15 at 05:36

0

If you need to handle fixtures with SQLAlchemy or another ORM/backend then the Fixture package may be of use: Flask-Fixtures 0.3.3

That is a simple library that allows you to add database fixtures for your unit tests using nothing but JSON or YAML.

answered Dec 12 '15 at 05:36

Saeed

661
6
12

luisgc93 · Answer 3 · 2022-01-22T22:21:37.777

While Kyle's answer is correct, we still need to provide the model factory with a database session, otherwise we would never actually commit to the db. Also, factory boy has a dedicated class SQLAlchemyModelFactory for interacting with SQLAlchemy.

https://factoryboy.readthedocs.io/en/stable/orms.html#sqlalchemy

The whole setup could look something like this:

import pytest
import os
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
from factory.alchemy import SQLAlchemyModelFactory


engine = create_engine( os.getenv("SQLALCHEMY_DATABASE_URI"))

SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine)


# this resets our tables in between each test
def _reset_schema():  
    db = SessionLocal()
    for table in Base.metadata.sorted_tables:
        db.execute(
            'TRUNCATE {name} RESTART IDENTITY CASCADE;'.format(name=table.name)
        )
        db.commit()


@pytest.fixture
def test_db():
    yield engine
    engine.dispose()
    _reset_schema()



@pytest.fixture
def session(test_db):
    connection = test_db.connect()
    transaction = connection.begin()
    db = scoped_session(sessionmaker(bind=engine))
    try:
        yield db
    finally:
        db.close()
    transaction.rollback()
    connection.close()
    db.remove()


class UserFactory(SQLAlchemyModelFactory):
    class Meta:
        model = models.User

    first_name = 'John'
    last_name = 'Doe'
    admin = False


@pytest.fixture(autouse=True)
def provide_session_to_factories(session):
    # usually you'd have one factory for each db table
    for factory in [UserFactory, ...]:
        factory._meta.sqlalchemy_session = session

How to Generate Fixtures from Database with SqlAlchemy

3 Answers3