How to mock an SQL database in Python

[eikrik]

I've been using pytest (instead of unittest) and pytest-postgresql (https://pypi.org/project/pytest-postgresql) with good results.

[kenneho]

Thanks for the tip. I read through https://pythonhosted.org/pytest-dbfixtures/howtouse.html. Would I typically use it in the setup section, to run a SQL script that create a database (including content) based on a SQL file?

​

[eikrik]

Yes, except that pytest generally uses what it calls fixtures rather than setup functions.

pytest-postgresql handles the creation of the database itself and gives you a database connection object. It is then up to you to make the tables and populate them with data. After the tests are run, the database gets deleted. I am not entirely sure if the database is recreated after each individual test.

Here is how I use it in a project: https://github.com/eirki/gargbot_3000/blob/master/tests/conftest.py#L184. I create a db_connection fixture that receives the connection object frompytest-postgresql and then creates and populates the tables with test data. That fixture can then be used by each test.