A Python library for upserting data into postgres.
Project description
pg_upsert
pg_upsert is a Python package that provides a method to interactively update and insert (upsert) rows of a base table or base tables from the staging table(s) of the same name. The package is designed to work exclusively with PostgreSQL databases.
The program will perform initial table checks in the form of not-null, primary key, foreign key, and check constraint checks. If any of these checks fail, the program will exit with an error message. If all checks pass, the program will display the number of rows to be inserted and updated, and ask for confirmation before proceeding. If the user confirms, the program will perform the upserts and display the number of rows inserted and updated. If the user does not confirm, the program will exit without performing any upserts.
Credits
This project was created using inspiration from ExecSQL and the example script pg_upsert.sql
. The goal of this project is to provide a Python implementation of pg_upsert.sql
without the need for ExecSQL.
Usage
CLI
usage: pg_upsert.py [-h] [-q] [-d] [-l LOGFILE] [-e EXCLUDE_COLUMNS] [-n NULL_COLUMNS] [-c] [-i] [-m METHOD] HOST DATABASE USER STAGING_SCHEMA BASE_SCHEMA TABLE [TABLE ...]
Update and insert (upsert) data from staging tables to base tables.
positional arguments:
HOST database host
DATABASE database name
USER database user
STAGING_SCHEMA staging schema name
BASE_SCHEMA base schema name
TABLE table name(s)
options:
-h, --help show this help message and exit
-q, --quiet suppress all console output
-d, --debug display debug output
-l LOGFILE, --log LOGFILE
write log to LOGFILE
-e EXCLUDE_COLUMNS, --exclude EXCLUDE_COLUMNS
comma-separated list of columns to exclude from null checks
-n NULL_COLUMNS, --null NULL_COLUMNS
comma-separated list of columns to exclude from null checks
-c, --commit commit changes to database
-i, --interactive display interactive GUI of important table information
-m METHOD, --method METHOD
method to use for upsert
Docker
docker pull ghcr.io/geocoug/pg_upsert:latest
Contributing
- Fork the repository
- Create a new branch (
git checkout -b feature-branch
) - Create a Python virtual environment (
python -m venv .venv
) - Activate the virtual environment (
source .venv/bin/activate
) - Install dependencies (
pip install -r requirements.txt
) - Install pre-commit hooks (
python -m pre-commit install --install-hooks
) - Make your changes and run tests (
make test
) - Push your changes to the branch (
git push origin feature-branch
) - Create a pull request
Running Tests Locally
Running tests locally requires a PostgreSQL database. The easiest way to set up a PostgreSQL database is to use Docker. The following command will create a PostgreSQL database called dev
with the user docker
and password docker
.
docker run --name postgres -e POSTGRES_USER=docker -e POSTGRES_PASSWORD=<passwd> -e POSTGRES_DB=dev -p 5432:5432 -d postgres
Once initialized, import the test data by running the following command.
docker exec -i postgres psql -U docker -d dev < tests/data.sql
Create a .env
file in the root directory with the following content, modifying the values as needed.
POSTGRES_HOST=
POSTGRES_PORT=5432
POSTGRES_DB=dev
POSTGRES_USER=docker
POSTGRES_PASSWORD=
Now you can run the tests using make test
.
Project details
Release history Release notifications | RSS feed
Download files
Download the file for your platform. If you're not sure which to choose, learn more about installing packages.
Source Distribution
Built Distribution
Hashes for pg_upsert-1.2.0-py3-none-any.whl
Algorithm | Hash digest | |
---|---|---|
SHA256 | 3d5f6ebe8555a49c28277eb6383046179238e2793803ef03c978bfe7a302b07b |
|
MD5 | 9aabdcaf7c4d3447dde95c61fe4557cc |
|
BLAKE2b-256 | baeb64ef3dcdf7a2abf8896f075f91e03ab58c7eee8d4f2ca61a400a07bb7c94 |