Skip to main content

RTB House apps pg_stream_copy

Project description

PG Stream Copy

Build Status codecov PyPI PyPI - Python Version

Fast COPY TO postgresql table directly from python by converting input data to bytes and stream to psycopg2 cursor using COPY <table> FROM STDIN BINARY

Benchmark:

The test with 1 mln rows of different column types on docker environment gave results:

  • ~21.5s for pg_stream_copy
  • ~54s for psycopg2.extras.execute_values

Usage:

from datetime import date
from psycopg2 import connect
from pg_stream_copy import Schema, WriterEncoder


conn = connect('postgresql://postgres@localhost')
cursor = conn.cursor()
table_name = 'public.example_table'

cursor.execute(f'''
    CREATE TABLE {table_name} (
        _smallint SMALLINT NULL,
        _integer INTEGER NULL,
        _bigint BIGINT NULL,
        _float DOUBLE PRECISION NULL,
        _numeric NUMERIC NULL,
        _character_varying CHARACTER VARYING NULL,
        _date DATE NULL
    );
''')
schema = Schema.load_from_table(cursor, table_name)

with WriterEncoder(cursor, table_name, schema) as writer_encoder:
    writer_encoder.append_tuple((2, 3, 4, 2.34, 'foo bar', date(2019, 2, 1)))
    writer_encoder.append_dict({
        '_smallint': 200,
        '_integer': 300,
        '_bigint': 400,
        '_float': 234,
        '_numeric': Decimal("-12.34")
        '_character_varying': 'bar baz',
        '_date': date(2019, 2, 3),
    })

conn.commit()
conn.close()

Supported PostgreSQL types:

  • boolean
  • smallint
  • integer
  • bigint
  • double precision
  • numeric
  • character varying
  • text
  • date
  • timestamp
  • timestamp with time zone
  • json
  • jsonb

Development:

# prepare env
docker-compose run py bash
python -m venv venv
pip install -e .[dev,e2e]
# run tests
pytest tests/

If you need to test different PostgreSQL and Python version, you can use env vars:

PYTHON_VERSION=3.7 PG_VERSION=10 docker-compose build
PYTHON_VERSION=3.7 PG_VERSION=10 docker-compose run py ...

or use CI script:

PG_VERSION=11 PYTHON_VERSION=3.7 ./bin/tests.sh

currently pg_stream_copy is supporting Python 3.7 and 3.8 and PostgreSQL v10, v11, v12

Changelog

1.2.0

  • Added new data type - numeric

1.1.0

  • Added new data types - text, json, jsonb

1.0.3

  • Added README to pypi.org
  • Added LICENCE, CHANGELOG

1.0.2

  • Updated pypi token

1.0.1

  • Added Travis CI

1.0.0

  • Initial version

Project details


Download files

Download the file for your platform. If you're not sure which to choose, learn more about installing packages.

Files for pg-stream-copy, version 1.5.4
Filename, size File type Python version Upload date Hashes
Filename, size pg_stream_copy-1.5.4-py3-none-any.whl (16.4 kB) File type Wheel Python version py3 Upload date Hashes View
Filename, size pg_stream_copy-1.5.4.tar.gz (12.2 kB) File type Source Python version None Upload date Hashes View

Supported by

AWS AWS Cloud computing Datadog Datadog Monitoring DigiCert DigiCert EV certificate Facebook / Instagram Facebook / Instagram PSF Sponsor Fastly Fastly CDN Google Google Object Storage and Download Analytics Pingdom Pingdom Monitoring Salesforce Salesforce PSF Sponsor Sentry Sentry Error logging StatusPage StatusPage Status page