Metadata-Version: 2.4
Name: sqlfixtures
Version: 0.0.3
Summary: Create test fixtures against an SQL database
Author-email: Oliver Cope <oliver@redgecko.org>
License: Apache
Classifier: Programming Language :: Python :: 3
Classifier: License :: OSI Approved :: Apache Software License
Classifier: Operating System :: OS Independent
Requires-Python: >=3.12
Description-Content-Type: text/x-rst
License-File: LICENSE.txt
Requires-Dist: embrace
Dynamic: license-file

SQLFixtures
===========

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.

.. code:: python

    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:

.. code:: python

    import sqlfixtures
    fix = sqlfixtures.SQLFixture(conn)


Inserting data directly
-----------------------

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

.. code:: python

    # 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:

.. code:: python

    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:

.. code:: python

    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:

.. code:: python

    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:

.. code:: python

    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="bob@example.com")

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:

.. code:: python

    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.


.. code:: python

    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:

.. code:: python

    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:

.. code:: python

    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:

.. code:: python

    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:

.. code:: python

    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:

.. code:: python

    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:

.. code:: python

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

One-to-many like this:

.. code:: python

    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:

.. code:: python

    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:

.. code:: python

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

This can be rewritten so as to break the loop:

.. code:: python

    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:

.. code:: sql

    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>
