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

Pingdom Pingdom Monitoring Google Google Object Storage and Download Analytics Sentry Sentry Error logging AWS AWS Cloud computing DataDog DataDog Monitoring Fastly Fastly CDN DigiCert DigiCert EV certificate StatusPage StatusPage Status page