psycodict: dictionary-based python interface to PostgreSQL databases

This project was split off from the L-functions and modular forms database so that other projects could use the SQL interface that we created for that project.

Built upon psycopg2, the core of the interface is the ability to create SELECT queries using a dictionary. In addition, the package provides a number of other features that were useful for the LMFDB:

  • Data management tools wrapping PostgreSQL’s mechanisms for loading from and saving to files (see DataManagement.md)

  • Statistics tables for storing statistics and counts (this is particularly useful in the LMFDB’s context since the data changes rarely)

The query language is specified in QueryLanguage.md and the read API (search, lucky, count, random, …) in Searching.md. What the version number promises — the public API, database metadata compatibility, and the deprecation policy — is laid out in Versioning.md.

Install

pip3 install -U "psycodict[pgbinary] @ git+https://github.com/roed314/psycodict.git"

or

pip3 install -U "psycodict[pgsource] @ git+https://github.com/roed314/psycodict.git"

Getting started

You will first need to install postgres and create a user and a database. For example, you might execute the following commands in psql:

CREATE DATABASE database_name;
CREATE USER username;
ALTER USER psetpartners WITH password 'good password';
GRANT ALL PRIVILEGES ON DATABASE database_name TO username;

Running the tests

Install the test dependencies and point the standard PostgreSQL environment variables at a database you do not mind being written to:

pip3 install -e ".[pgbinary,test]"
createdb psycodict_test
PGDATABASE=psycodict_test pytest

The connection is configured through PGHOST, PGPORT, PGUSER, PGPASSWORD and PGDATABASE, defaulting to postgres@localhost:5432 and a database named psycodict_test. The database only needs to be empty: the meta tables are bootstrapped on first connection, and every test creates its own randomly named tables and drops them afterwards.

If no server is reachable the tests that need one are skipped, so pytest tests/test_encoding.py tests/test_utils.py tests/test_config.py works with no database at all.

Two further sets of tests are opt-in:

PSYCODICT_TEST_DEVMIRROR=1 pytest tests/test_devmirror.py

runs read-only queries against LMFDB’s public mirror at devmirror.lmfdb.xyz, which checks psycodict against a real 190-table schema, and

PSYCODICT_TEST_DB_REQUIRED=1 pytest

turns “no database, skip” into a hard failure — which is what continuous integration wants, so that a misconfigured job cannot report success by quietly skipping everything.