sqlfixtures 0.0.3

Create test fixtures against an SQL database

Create database entries for your test scripts.

Example usage

First, set up a SQLite database. This should work with almost any SQL database. We’re using sqlite3 because it is built in.

import sqlite3  # or any db-api compatible database

conn = sqlite3.connect(":memory:")
conn.execute(
    """
    CREATE TABLE users (id INTEGER PRIMARY KEY, name text, active int)
    """
)
conn.execute(
    """
    CREATE TABLE groups (id INTEGER PRIMARY KEY, name text)
    """
)
conn.execute(
    """
    CREATE TABLE group_members (user_id INT, group_id INT)
    """
)

Now import sqlfixtures and create a sqlfixtures.SQLFixture object:

import sqlfixtures
fix = sqlfixtures.SQLFixture(conn)

Inserting data directly

Inserting data from dicts is the simplest way to start using sqlfixtures:

# Create a SQLFixture object to work with
fix = SQLFixture(dbconn)

# Roll back all rows inserted at the end of the test
with fix.rollback():

    # Insert rows
    fix.insert("users", {"name": "Angus"}) as user:
    assert user.name == "Angus"
    print("Inserted user", user)

This outputs:

Inserted user {'id': 1, 'name': 'Angus', 'active': None}

Updating works in a similar way:

fix.insert("users", {"name": "Angus"}) as user:
assert user.name == "Angus"
fix.update("users", {"name": "Alice"}, where={"id": user.id}) as user:
assert user.name == "Alice"
print(user)

Outputting:

{'id': 1, 'name': 'Alice', 'active': None}

Using the declarative API allows more expressivity:

angus = sqlfixtures.Insertable("users", name="angus", active=1)
alice = sqlfixtures.Insertable("users", name="alice", active=1)
order = sqlfixtures.Insertable("orders", user_id=angus.id)

fix = SQLFixtures(dbconn)
with fix.apply([angus, alice, order]) as items:
    assert items[0].name == "Angus"
    assert items[1].name == "Alice"

Reverting fixtures

Very often when using data fixtures, you want to revert the database back to the original state afterwards. SQLFixtures offers a few strategies for this:

fix = SQLFixture(dbconn)

with fix.undo():
    fix.insert(...)

with fix.rollback():
    fix.insert(...)

with fix.savepoint():
    fix.insert(...)

with fix.commit():
    fix.insert(...)

The shortcut method SQLFixture.apply allows you to apply insertable fixtures with a revert strategy:

alice = sqlfixtures.Insertable("users", name="alice", active=1)
fix = SQLFixture(dbconn)
with fix.apply(alice, "undo", strict=False):
    ...

Each revert strategy takes a strict argument, defaulting to True, causing errors encountered while reverting the fixture to raise an exception. If False, any errors will be logged only and not raised.

SQLFixtures.rollback([strict=True])

Roll back the database transaction on exit.

Use this if your application code can run in the same transaction as the fixture setup.

SQLFixtures.savepoint([strict=True])

Create a database savepoint on start and roll it back on exit. This is similar to rollback(), except that the transaction can continue outside of the fixture.

Use this if your application code can run in the same transaction as the fixture setup.

SQLFixtures.undo([strict=True])

Generate inverse operations for every statement run. For example every record inserted will result in a corresponding ‘DELETE’ statement being issued. This is more fragile (because it relies on matching the exact rows inserted, so changes to those rows will cause it to fail), but works independently of any database transactions.

Use this if your application code expects to commit or rollback the database transaction

SQLFixtures.commit([strict=True])

A variant on undo which commits the database transaction after insertion and again after cleanup.

Use this if your application commits or rolls back the transaction and you don’t want fixture data to be affected, or if you need fixture data to be visible from other database connections.

rollback and savepoint generally the safest to use, however they will break if your application commits or rolls back the transaction before sqlfixtures exits.

SQLFixtures.undo attempts to identify rows based on matching the entire inserted row. If your application modifies any field of your inserted fixture data, the reverter will no longer be able to identify the inserted fixture to remove it again. You can work around this by specifying pkcols in Insertable:

user = Insertable("users", pkcols=["id"], name="Bob", email="[e-mail]")

Now SQLFixtures will use the value of the id field when reverting the fixture with undo.

Custom revert code

Sometimes it is useful to revert objects that your application creates, which can be done by calling add_revert:

.. code:: python

fix = SQLFixture(dbconn)

with fix.apply(Insertable(“users”, username=”test_user”), “undo”):

fix.add_revert(“DELETE FROM posts”)

The declarative API - sqlfixture.Insertable

Using sqlfixtures.Insertable allows more expressivity:

angus = sqlfixtures.Insertable("users", name="angus", active=1)
alice = sqlfixtures.Insertable("users", name="alice", active=1)
order = sqlfixtures.Insertable("orders", user_id=angus.id)

fix = SQLFixtures(dbconn)
with fix.apply([angus, alice, order]) as items:
    assert items[0].name == "Angus"
    assert items[1].name == "Alice"

Pass multiple Insertables as a list, a dict, or an object graph of Insertables.

The return type will match the type passed in. If you pass a dict, you will get back a dict populated with the inserted data.

with fix.apply({"angus": angus, "alice": alice}) as users:
    print(users)

# users is a dict containing the inserted data:
{
    "angus": {'id': 1, 'name': 'Angus', 'active': 1},
    "alice": {'id': 2, 'name': 'Alice', 'active': 1}
}

Referencing inserted ids to create foreign key relationships

The declarative API allows you to reference columns that are populated by the database, for example using an auto-increment id field as a foreign key in another table:

administrators = sqlfixtures.Insertable("groups", name="administrators")
alice_is_admin = sqlfixtures.Insertable(
    "group_members", user_id=alice.id, group_id=administrators.id
)
with fix.apply([alice, administrators, alice_is_admin]) as rows:

    # The row inserted by `alice_is_admin` will contain the database-generated
    # keys for user_id and group_id
    # [
    #     {'id': 1, 'name': 'Alice', 'active': 1},
    #     {'id': 1, 'name': 'administrators'},
    #     {'user_id': 1, 'group_id': 1}
    # ]

    assert rows[2].user_id == rows[0].id
    assert rows[2].group_id == rows[1].id

Using Insertables as templates

Once an insertable object has been created it can be copied and customized just by calling it:

User = sqlfixtures.Insertable("users", active=1)
angus = User(name="Angus")
alice = User(name="Alice")
with fix.apply([angus, alice]) as users:
    assert users[0].name == "Angus"
    assert users[1].name == "Alice"
    print(users)

[{'id': 1, 'name': 'Angus', 'active': 1}, {'id': 2, 'name': 'Alice', 'active': 1}]

Generating values from callables

Values can also be set from callable objects. This is useful when using insertables as templates:

names = iter(["alice", "bob", "carol"])
User = sqlfixtures.Insertable("users", active=1, name=lambda: next(names))
with fix.apply([User(), User(), User()]) as users:
    assert users[0].name == "alice"
    assert users[1].name == "bob"
    assert users[2].name == "carol"

The callable may reference other properties through Reference arguments:

from sqlfixtures import SELF
names = iter(["alice", "bob", "carol"])
user = sqlfixtures.Insertable(
    "users",
    name=lambda: next(names),
    email=lambda name=SELF.name: f"{name}@example.com"
)

If the callable takes a single argument with no default value, the insertable itself will be substituted:

user = sqlfixtures.Insertable(
    "users",
    name="fred",
    email=lambda this_user: this_user.name
)

Nesting Insertables

Insertables can contain other insertables, to populate related tables. One-to-one relationships like this:

angus = sqlfixtures.Insertables(
    "users",
    name="Angus",
    address_id=sqlfixtures.Insertables(
        "addresses",
        line1="1 Anyroad",
        line2="Aberdeen"
    ).id
)

One-to-many like this:

alice = sqlfixtures.Insertables(
    "users",
    name="Alice",
    pets=[
        sqlfixtures.Insertables(
            "pets",
            user_id=sqlfixtures.PARENT.id,
            name="Zebedee",
            pet_type="zebra"
        )
    ]
)

Notice how the id is referenced differently in the two cases.

• Insertables that are nested inside other insertables can reference the parent’s id using the special PARENT object (user_id=sqlfixtures.PARENT.id)

• The container can directly reference the id of the child Insertable (pet_id=sqlfixtures.Insertable(...).id).

Using nested insertables as templates

Use a double underscore (__) to reference attributes of child insertables when creating an Insertable from a template:

User = sqlfixtures.Insertable(
    "users",
    address=sqlfixtures.Insertable("addresses"),
    address_id=address.id
)
angus = User(name="Angus", address__line1="1 Anyroad")

Insertable.after: breaking circular dependencies

Circular dependencies occur where you have 2 or more tables with foreign keys that reference each other. In this case you need to use Insertable.after to break the loop.

For example, the following code causes an unresolvable loop:

Insertable(
    "foo",
    bar_id=Insertable("bar", foo_id=PARENT.id).id
)

This can be rewritten so as to break the loop:

Insertable(
    "foo",
    bar=Insertable("bar", foo_id=PARENT.id)
).after(bar_id=SELF.bar.id)

The after method causes an UPDATE to be run after both records are inserted, somthing like this:

INSERT INTO foo (bar_id) VALUES (NULL) RETURNING foo_id
INSERT INTO bar (foo_id) VALUES (<foo_id>) RETURNING bar_id
UPDATE foo SET bar_id = <bar_id> WHERE foo_id = <foo_id>