Load YAML data fixtures for SQLAlchemy
Clone or download
Latest commit 89b1567 Jul 26, 2018



Load YAML data fixtures for SQLAlchemy

https://api.shippable.com/projects/5945f25f4fdc63070017442d/badge?branch=master https://api.shippable.com/projects/5945f25f4fdc63070017442d/coverageBadge?branch=master

This package allows you to define some data in YAML and load them into a DB. The yaml data should correspond to SQLAlchemy declarative mappers.


- User:
  - __key__: joey
    username: joey
    email: joey@example.com
      name: Jeffrey

  - __key__: dee
    username: deedee
    email: deedee@example.com

- Profile:
  - user: dee
    name: Douglas

- Group:
  - name: Ramones
    members: [joey.profile, dee.profile]
  • The root of YAML contains a sequence of mapper names e.g. - User, - Profile etc
  • The order of these names should follow relationship dependencies
  • Every name should contain a sequence of instances
  • Each instance is a mapping of attribute -> value
  • the attributes are taken from the mapper __init__() (usually an attributes maps to a column)
  • The special field __key__ can be used to identify this instnace in a relationship reference e.g. The Profile.user
  • Note that any __key__ MUST be globally unique
  • In a to-one relationship the data can be directly nested in the parent data definition
  • References can access attributes using a dot notation, e.g. joey.profile
  • to-many relationships can be added as a list of references

The mapper definition for this example is in the test file.


pip install sqla-yaml-fixtures


This module expose a single function load(ModelBase, session, fixture_text)


  • ModelBase is SQLAlchemy declarative base
  • session is SQLAlchemy session
  • fixture_text is a string containg the YAML fixtures
from sqlalchemy import create_engine
from sqlalchemy import Column, Integer, String
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import Session

import sqla_yaml_fixtures

BaseModel = declarative_base()

class User(BaseModel):
    __tablename__ = 'user'
    id = Column(Integer, primary_key=True)
    username = Column(String(150), nullable=False, unique=True)
    email = Column(String(254), unique=True)

def main():
    engine = create_engine('sqlite://')
    connection = engine.connect()
    session = Session(bind=connection)

    fixture = """
    - User:
      - username: deedee
        email: deedee@example.com
      - username: joey
        email: joey@example.commit
    sqla_yaml_fixtures.load(BaseModel, session, fixture)

    print('\n'.join(u.username for u in session.query(User).all()))

if __name__ == '__main__':

Note: the load() function performs a session.commit().

load() returns an instance of Store. Using this object get() method you can passing a key as argument you get a reference to the object added into the database. This is useful to easily get attributes that are generated by the database.

store = sqla_yaml_fixtures.load(BaseModel, session, fixture)
my_obj = store.get('dee')
print('Created object id: {}'.format(my_obj.id))

Command Line

For basic usage there is also command line. Example:

$ python -m sqla_yaml_fixtures --db-url sqlite:///dev.db --db-base mypkg.models:Base --reset-db --alembic-stamp fixture.yaml

All available options:

$ python -m sqla_yaml_fixtures --help
usage: sqla_yaml_fixtures [-h] --db-base DB_BASE --db-url DB_URL [--yes]
                          [--reset-db] [--alembic-stamp] [--jinja2]
                          FILE [FILE ...]

load fixtures from yaml file into DB

positional arguments:
  FILE               YAML file with DB fixtures

optional arguments:
  -h, --help         show this help message and exit
  --db-base DB_BASE  SQLAlchemy Base class with schema metadata in the format
  --db-url DB_URL    Database URL in the format
  --yes              Do NOT ask for confirmation before applying fixtures
  --reset-db         Drop DB schema and data and re-create schema before
                     loading fixtures
  --alembic-stamp    Perform `alembic stamp head`
  --jinja2           load fixture files as jinja2 templates