I use a fake object in place of a database connection which gives fake responses when the correct SQL query is sent to it.
Example:
db = Fake().expect_query("SELECT * FROM users", result=[(1, 'Bob'), (2, 'Joe')])
Then you do:
db.query("SELECT * FROM users")
and get back the result.
In Python if you do this in a context manager, you can ensure that all expected queries actually were issued, because the Fake object can track which ones it already saw and throw an exception on exit.
The upside of this is, you don't need any database server running for your tests.
update: This pattern is usually called db-mock or something like this. There are some packages out there. I built it a few times for companies I worked for.
Example:
db = Fake().expect_query("SELECT * FROM users", result=[(1, 'Bob'), (2, 'Joe')])
Then you do:
db.query("SELECT * FROM users")
and get back the result.
In Python if you do this in a context manager, you can ensure that all expected queries actually were issued, because the Fake object can track which ones it already saw and throw an exception on exit.
The upside of this is, you don't need any database server running for your tests.
update: This pattern is usually called db-mock or something like this. There are some packages out there. I built it a few times for companies I worked for.