All jobs

Migrate a Flask App From psycopg2 to Psycopg 3

Yes. An agent ports the pool, cursors and error handling from psycopg2 to psycopg 3, runs the suite on PostgreSQL, and opens a pull request.

Run this on RushOpens the console with this prompt ready to send, in a Project that has your repository.

What the agent does

The agent maps every place the driver is visible. In an app like offenesdresden/ParkAPI that is a short list. park_api/db.py builds a ThreadedConnectionPool(1, 20, ...) from a parsed database_uri and hands out cursors made with cursor_factory=psycopg2.extras.RealDictCursor, so every caller reads rows as dictionaries. park_api/app.py catches psycopg2.OperationalError and psycopg2.ProgrammingError. requirements.txt pins psycopg2==2.8.6, the source package, which is why the Dockerfile installs postgresql-dev build-base before pip install -e .. The queries in app.py, scraper.py, timespan.py and util.py use %s and %(name)s placeholders and pass timestamps as strings.

It then gets the suite running as CI does. That takes a C compiler and libpq-dev to build 2.8.6, a PostgreSQL server with the park_api role from config_ci.ini, python bin/parkapi-setupdb for the schema, and python -m unittest discover tests, which runs 29 tests. With that baseline it rewrites db.py: a psycopg_pool.ConnectionPool with min_size=1, max_size=20 built from the same URI, row_factory=dict_row in place of RealDictCursor, and with pool.connection() in place of getconn and putconn. The two exception names in app.py exist unchanged under psycopg. The 29 tests pass again. Rows are plain dictionaries, the JSON column still arrives parsed and the timestamps are still datetime objects.

The part that reading the code does not show is the migration tool. requirements.txt also pins yoyo-migrations==7.3.1, and park_api/setupdb.py gives it the same database_uri. Once psycopg2 is uninstalled, bin/parkapi-setupdb fails with ModuleNotFoundError: No module named 'psycopg2', because that yoyo release imports it for any postgresql:// URI. yoyo 9.0.0 can use psycopg 3 but only under the scheme postgresql+psycopg://, and the pool does not accept that scheme: with it in config.ini, the two tests that reach the database wait out its timeout and error. So one setting now has to be read two ways. The agent upgrades yoyo and rewrites the scheme inside setupdb.py only, leaving every config file alone.

It also records what changes in behaviour. The old pool raised PoolError: connection pool exhausted at once on the twenty-first checkout; the new one waits up to its 30 second default and raises PoolTimeout. Psycopg 3 binds parameters on the server, so a tuple passed to IN %s is a syntax error there; the agent searches for that form and finds none here. And psycopg 3.2 needs Python 3.8, while the Dockerfile starts from python:3.7-alpine and the workflow in .github/workflows/tests.yml tests 3.7, 3.8 and 3.9. On 3.9 pip resolves psycopg 3.2.13, not the newest release, which asks for 3.10.

Start it with one API call

POST https://api.phoenix.so/o/{org}/sessions with this body:

{
  "agent": "claude",
  "input": "Migrate this application from psycopg2 to psycopg 3 with psycopg_pool. Read the requirement files, setup.py, the Dockerfile, the CI workflow, the module that owns the connection pool and every file that executes SQL or catches a psycopg2 exception. List each psycopg2 feature in use: pool class and sizes, cursor factories, extras, placeholders, adapters, exception classes, and any other package that imports psycopg2 itself, such as a migration tool. There is no Docker here: install PostgreSQL, a C compiler and libpq-dev with apt, create the role and database the CI config names, install the project the way CI does, create the schema and run the test suite. Keep that output as the baseline. Then port the pool and cursor code, keeping dictionary rows and the same pool limits, and replace the exception imports. Remove psycopg2 from the requirements, uninstall it, and run schema setup and the tests again; fix whatever still imports it. Search every query for forms psycopg 3 treats differently (IN with a tuple, multiple statements with parameters, COPY) and report each. Compare pool exhaustion behaviour before and after. Report the lowest Python the new requirements support against the Dockerfile and the CI matrix, and change those files only as far as needed. Report defects you find that are not caused by the migration without fixing them. Open a pull request with every command and its output.",
  "resources": [
    {
      "url": "https://github.com/your-org/your-app"
    }
  ]
}
  1. Get an API key. In the console, open Settings, then API keys, and create a key for your org. Send it in the Authorization header as a Bearer token.
  2. Send the request. POST the body in request.json to https://api.phoenix.so/o/{org}/sessions, with your repository as the resources URL. The run starts in its own cloud sandbox.
  3. Read what came back. Follow the run on its live stream or wait for the run.completed webhook, then read the result and its trajectory.

What comes back

A pull request that changes park_api/db.py, two lines of park_api/app.py, park_api/setupdb.py, requirements.txt, the Dockerfile and the test workflow. The description lists the psycopg2 features found and what replaced each, the 29 tests before and after, the yoyo failure with its fix, the pool comparison, and the Python floor with the lines that had to move. Because psycopg[binary] ships its own libpq, the build packages can leave the image, and the description says whether the agent removed them or left that for you.

A reviewer checks the pool first: a request that used to fail fast under load will now queue, which is usually better and should be a decision. Then the Python versions. Two findings sit in a separate section because they were there before. get_most_lots_from_known_data in util.py indexes a dictionary row with [0], which raises KeyError under either driver, and bin/parkapi-setupdb applies every migration each time it runs, so a second run fails with relation "parkapi" already exists.

Run it on every Monday morning

Treat the driver as one of the upgrades a weekly run is allowed to propose. In the console, add an automation to the Project with a schedule trigger, cron set to 0 9 * * 1 and your timezone, and a saved prompt that tells the agent to check whether the repository still depends on psycopg2 and, if it does, to do this migration and nothing else.

The schedule starts one run per slot in the Project's repository. Once the pull request is merged the same prompt finds nothing to do and the run ends with a short answer. To cover many services in one pass, send the body above with an API key once per repository.

The use case this job follows, with its automation and trigger: Upgrade dependencies weekly.

Why run it on Rush?

Almost everything this migration can break is outside the file you would edit. A migration tool that imports the old driver, a URI scheme two libraries read differently, a pool that changes from refusing to waiting, an interpreter one version too old: none of these is in db.py. They show up when the old package is really uninstalled and the schema script and tests really run against a server, which is what a run in its own sandbox does.

The record matters as much as the result. The baseline, the failure after uninstalling psycopg2 and the passing run are each kept as command output, and the pull request quotes them. A reviewer can see that the 29 tests ran against PostgreSQL both times and were not skipped for want of a database.

Frequently asked questions

Is it enough to change the import from psycopg2 to psycopg?

For exceptions and simple execute calls, nearly. Pools moved to the separate psycopg_pool package with a different interface, RealDictCursor became the dict_row row factory, and parameters are bound on the server, which changes a few SQL forms. The agent lists which of these your code touches before it edits.

Do I still need libpq-dev and a compiler in my image?

Not with psycopg[binary], which bundles libpq in its wheel. psycopg2 pinned as a source package needed both, which is the reason for the apk add line in this Dockerfile. If you prefer the system libpq, install psycopg[c] and keep the build packages.

What happens to tools that depend on psycopg2?

They keep importing it until they are upgraded or configured otherwise. Here yoyo-migrations 7.3.1 fails once psycopg2 is gone, and 9.0.0 needs the postgresql+psycopg:// scheme to pick the new driver. The agent finds these by uninstalling the old package and running every entry point.

Can psycopg2 and psycopg 3 be installed together during a transition?

Yes, they are different packages with different import names. That lets a large codebase move module by module. The request above removes the old one in the same change because the suite is small enough to prove the whole port at once.

More psycopg2 jobs

More jobs like this: Upgrade dependencies weekly

Keep reading