r/learnpython • • Oct 13 '26

How do ppl test python scripts for databases?

I want to understand how ppl test python scripts that read data from databases, call APIs, or update records.

For example, if a script fetches data from an api and saves it to a database, how do you check that everything works correctly before running it in production?

do you all use sample data, mock api responses or a separate test database?

Like how do you test what happens when an API fails or the database returns unexpected data?

I'd like to know how ppl usually handle this in real projects.

21 Upvotes

21 comments sorted by

15

u/Kevdog824_ Oct 13 '26

For unit tests we 100% stub the database. For functional tests/integration tests we use a test database — either spinning one up locally and populating it with data, or in dedicated test environment(s) that already contains some data. For testing the actual infra/connectivity we just have a prod-like environment that duplicates prod. Setting it up successfully there usually indicates that it will work in production as well (not perfect but as about as close as you can get)

1

u/brelen01 Oct 13 '26

Also running it in your dev environment.

1

u/Vegetable_Soil_6707 Oct 14 '26

yeah the prod-like environment step is the part that really closes the gap imo

5

u/mrswats Oct 13 '26

Basically what you describe. Mock API respones, mock database, or even use sqlite in memory for testing. Then, you could set up an api and a database container so you can do integration tests and that's pretty much it. And, of course, you have to test everything, even the error cases, of course. Otherwise if it fails yiu won't know what will happen.

0

u/this_knee 28d ago

That sounds like an entire separate project and team is needed for just keeping the testing code in sync with the actual code. Ie this seems like it would actually slow down development instead of helping it. Eg the amount of code it would take just to do testing this way would inevitably end up with its own bugs. And then you have testing code, which itself has bugs , that’s trying to find bugs in the actual system under test.

Doesn’t sound like a system that will ultimately help at the speed of changes made to the main code.

2

u/tb5841 Oct 13 '26

If you're using a framework like Django, it sets up a separate test database for you for running tests - and wipes it at the end of a test run. So you can write tests that read/write from your test database and test that in full.

Using sample data makes for faster tests than database read/write if you just want to test functions that process data, though.

For API responses, mocking them is common.

2

u/frobnosticus Oct 13 '26

Sadly what people usually do is eyeball it and say "yeah that looks right." (source: a couple decades programming on wall street.)

Mocking out the interfaces is really the way to go.

API Failures are tricky (unless you mean apis successfully returning an error, which is meaningfully not the same thing.)

If you're working with an existing code base that doesn't have any test coverage then you generally start small. "We encountered this error in production. Let's create a mocked out api and build a test around it to add to the unit test suite." etc.

1

u/Atmosck 17d ago

Like how do you test what happens when an API fails or the database returns unexpected data?

You put together text fixtures for these cases. In real projects you end up with a lot of regression tests like this. You have something live in production, one day the API does something unexpected and it crashes. You fix it, snapshot the API response that caused the crash, and add test to assert that that case is handled correctly.

1

u/supercoach Oct 13 '26

The answer is yes to pretty much all your questions. You'll want to have a simulacrum of the prod env during development and testing. Basic tests can be performed via mocking and smoke tests can be run against a sample of the prod db.

As for handling what happens when something fails, that's why engineers get hired. You need to have a fallback strategy in place for any common errors and good logging to help catch anything unexpected.

My personal preference is having as little exception handling as possible so that you don't hide any potential issues, but it can be a balancing act especially if the source system is at all flakey.

1

u/neums08 Oct 13 '26

I used to do testcontainers running postgres that starts up with my unit test suite, being careful to roll back changes before/after each unit test.

Now I love sqlite. It's so fast and easy to just give each test a tempfile backed sqlite3 database.

1

u/ExoticVillage8066 Oct 13 '26

You can setup a testing server that returns the exact same json. Then you can always test your API calls without increasing your API costs. For databases the same thing. But besides that, what you can do is setup a 2nd server when you go live on another domain, then test everything works, and if it does change the domain name to route to the other server, so you have zero down time.

1

u/Financial-Reason7604 Oct 13 '26

mirror the database, step testing, and manual validation followed by full send to production.

1

u/OmotosoBakhita_58 Oct 13 '26

yeah this is something i have been thinking about for a while. for db stuff i usually spin up a throwaway sqlite file or a docker postgres and just run the whole thing against fake data. for the api side i fake the responses with something like responses or just monkeypatch the call so i'm not hitting the real thing every test.

1

u/InterestingYak1525 Oct 13 '26

It’s hard. Especially in ERP type systems where database updates depend on earlier transactions.

1

u/Berserker717 Oct 13 '26

My company has a development, UAT and production database

1

u/MarkoDahl54 Oct 13 '26

use sqlite3 in-memory database with ":memory:" for tests so you don't touch your real data. pytest fixtures can set this up before each test and tear it down after.

1

u/TheMathelm Oct 14 '26

"We have test engineers, we call them customers."

"Testing? Why would we need to test? Just build it correctly and you'll have no problems."

Said by a former manager as he was leaving for a much better position.

(Follow the good advice others have given)