pgcopy

https://pgcopy.readthedocs.io/en/latest/
Admin
pgcopy is a Python library for fast data loading into PostgreSQL using binary copy with support for...
Tokens:4,631
Snippets:89
Trust Score:9.6
Update:5 months ago
Show doc for...
Tokens:
Raw
### Install pgcopy using pip

Source: https://pgcopy.readthedocs.io/en/latest/_sources/installation.rst.txt

Use this command to install the pgcopy library. Ensure you have Python 3 and pip installed.

```bash
$ pip install pgcopy
```

--------------------------------

### Install pgcopy with pip

Source: https://pgcopy.readthedocs.io/en/latest/installation.html

Use pip to install the pgcopy package. Ensure you have Python 3 and pip installed.

```bash
$ pip install pgcopy

```

--------------------------------

### Run Quick Tests with Pytest

Source: https://pgcopy.readthedocs.io/en/latest/testing.html

Use this command for a fast test run with the available Python installation. Ensure pytest is installed.

```bash
$ pytest tests

```

--------------------------------

### Run Thorough Tests with Tox and Docker

Source: https://pgcopy.readthedocs.io/en/latest/testing.html

Execute this command for comprehensive testing across multiple Python and PostgreSQL versions. Requires tox and tox-docker to be installed.

```bash
$ tox

```

--------------------------------

### Run tox tests

Source: https://pgcopy.readthedocs.io/en/latest/_sources/testing.rst.txt

Perform thorough testing across multiple Python and PostgreSQL versions using tox and tox-docker. Docker must be installed.

```bash
$ tox
```

--------------------------------

### Run pgcopy tests with Docker Compose

Source: https://pgcopy.readthedocs.io/en/latest/_sources/testing.rst.txt

Execute the pgcopy test suite using docker-compose. Requires Docker to be installed.

```bash
$ docker-compose up pgcopy
```

--------------------------------

### Run pytest tests

Source: https://pgcopy.readthedocs.io/en/latest/_sources/testing.rst.txt

Execute the test suite using pytest. Ensure pytest is installed.

```bash
$ pytest tests
```

--------------------------------

### Run pgcopy Benchmarks

Source: https://pgcopy.readthedocs.io/en/latest/benchmarks.html

Execute the benchmark script to compare pgcopy performance. This command shows the execution time for ExecuteManyBenchmark versus PGCopyBenchmark.

```bash
$ python -m tests.benchmark
          ExecuteManyBenchmark:   7.75s
               PGCopyBenchmark:   0.54s

```

--------------------------------

### Run pgcopy benchmarks

Source: https://pgcopy.readthedocs.io/en/latest/_sources/benchmarks.rst.txt

Execute the benchmark script to see performance comparisons. This provides a general idea of the performance improvement possible with pgcopy.

```bash
$ python -m tests.benchmark
          ExecuteManyBenchmark:   7.75s
               PGCopyBenchmark:   0.54s
```

--------------------------------

### Copy Data with CopyManager

Source: https://pgcopy.readthedocs.io/en/latest/use.html

Instantiate a CopyManager and use its `copy()` method to load records into a specified table. Remember to commit the transaction after copying.

```python
from datetime import datetime
from pgcopy import CopyManager
import psycopg
cols = ('id', 'timestamp', 'location', 'temperature')
now = datetime.now()
records = [
        (0, now, 'Jerusalem', 72.2),
        (1, now, 'New York', 75.6),
        (2, now, 'Moscow', 54.3),
    ]
conn = psycopg.connect(database='weather_db')
mgr = CopyManager(conn, 'measurements_table', cols)
mgr.copy(records)

# don't forget to commit!
conn.commit()
```

--------------------------------

### CopyManager.copy()

Source: https://pgcopy.readthedocs.io/en/latest/use.html

Copies data into the database using a temporary file. Data is serialized first in its entirety and then sent to the database. By default, a temporary file on disk is used. For very large datasets, serialization can be done directly to the database connection using `threading_copy()`.

```APIDOC
## CopyManager.copy()

### Description
Copies data into the database using a temporary file.

### Parameters
#### Path Parameters
None

#### Query Parameters
None

#### Request Body
* **data** (iterable of iterables) - Required - The data to be inserted.
* **fobject_factory** (function) - Optional - A tempfile factory. Example: `BytesIO` for in-memory storage.

### Request Example
```python
from io import BytesIO
mgr.copy(records, BytesIO)
```

### Response
#### Success Response (200)
None (operation is performed)

#### Response Example
None
```

--------------------------------

### Using Replace for Fast Table Updates

Source: https://pgcopy.readthedocs.io/en/latest/replace.html

Use the Replace context manager for fast updates on tables involving most rows. It automates the process of creating a new table, populating it, and then replacing the old one. This is only possible if no other tables depend on the target table.

```python
from pgcopy import CopyManager, Replace
with Replace(conn, 'mytable') as temp_name:
    mgr = CopyManager(conn, temp_name, cols)
    mgr.copy(records)
```

--------------------------------

### Copy Data to BytesIO with CopyManager

Source: https://pgcopy.readthedocs.io/en/latest/use.html

Use the `copy()` method with `BytesIO` as the `fobject_factory` for in-memory data serialization, which can offer a slight performance benefit for datasets that fit in memory.

```python
from io import BytesIO
mgr.copy(records, BytesIO)
```

--------------------------------

### pgcopy.Replace

Source: https://pgcopy.readthedocs.io/en/latest/_sources/replace.rst.txt

The `pgcopy.Replace` context manager automates the process of replacing the entire contents of a table. This is typically faster when inserting into an empty table with no indices or constraints.

```APIDOC
## Class: pgcopy.Replace

### Description
Provides a context manager to efficiently replace the contents of a table. It is designed to be used when the entire table can be reinserted, often by first dropping indices and constraints, performing the insertion, and then recreating them.

### Usage
```python
from pgcopy import Replace

with Replace(conn, 'my_table') as replace:
    replace.write(data)
```

### Parameters
* **conn**: Database connection object.
* **table_name**: The name of the table to replace.

### Methods
* **write(data)**: Writes the provided data to the table.
```

--------------------------------

### Load Data into PostgreSQL Table

Source: https://pgcopy.readthedocs.io/en/latest/index.html

Use this snippet to load records into a PostgreSQL table using pgcopy. Ensure you have a psycopg connection and define the table columns and records beforehand. Remember to commit the transaction after copying.

```python
from datetime import datetime
from pgcopy import CopyManager
import psycopg
cols = ('id', 'timestamp', 'location', 'temperature')
now = datetime.now()
records = [
        (0, now, 'Jerusalem', 72.2),
        (1, now, 'New York', 75.6),
        (2, now, 'Moscow', 54.3),
    ]
conn = psycopg.connect(database='weather_db')
mgr = CopyManager(conn, 'measurements_table', cols)
mgr.copy(records)

# don't forget to commit!
conn.commit()

```

--------------------------------

### CopyManager.threading_copy()

Source: https://pgcopy.readthedocs.io/en/latest/use.html

Copies data by serializing directly to the database. This method is suitable for very large datasets where direct serialization to the database connection is preferred over using temporary files.

```APIDOC
## CopyManager.threading_copy()

### Description
Copy data, serializing directly to the database.

### Parameters
#### Path Parameters
None

#### Query Parameters
None

#### Request Body
* **data** (iterable of iterables) - Required - The data to be inserted.

### Request Example
```python
mgr.threading_copy(large_dataset)
```

### Response
#### Success Response (200)
None (operation is performed)

#### Response Example
None
```

--------------------------------

### CopyManager Class

Source: https://pgcopy.readthedocs.io/en/latest/_sources/use.rst.txt

The CopyManager class is used to manage the copying of data to a table. Instantiate it for your table and then pass data to its methods.

```APIDOC
## Class: pgcopy.CopyManager

### Description
Manages the process of copying data to a database table.

### Methods

#### copy(data[, fobject_factory])

Copies the provided data to the table managed by this CopyManager.

- **data**: The data to be copied.
- **fobject_factory** (optional): A factory function for creating file objects.

#### threading_copy(data[, fobject_factory])

Copies the provided data to the table using threading. This can be useful for larger datasets.

- **data**: The data to be copied.
- **fobject_factory** (optional): A factory function for creating file objects.
```

--------------------------------

### pgcopy.util.RenameReplace

Source: https://pgcopy.readthedocs.io/en/latest/_sources/replace.rst.txt

As of v0.6, `pgcopy.util.RenameReplace` offers an alternative to `pgcopy.Replace`. Instead of dropping and recreating table objects, it renames them using a transformation function, which can be useful in certain scenarios.

```APIDOC
## Class: pgcopy.util.RenameReplace

### Description
An alternative to `pgcopy.Replace` that renames existing table objects instead of dropping and recreating them. This is useful when direct dropping of objects is not feasible or desired.

### Usage
```python
from pgcopy.util import RenameReplace

with RenameReplace(conn, 'my_table', transform_func) as rr:
    rr.write(data)
```

### Parameters
* **conn**: Database connection object.
* **table_name**: The name of the table to replace.
* **transform_func**: A function to transform object names.

### Methods
* **write(data)**: Writes the provided data to the table.
```

--------------------------------

### Using RenameReplace for Table Updates

Source: https://pgcopy.readthedocs.io/en/latest/replace.html

The RenameReplace context manager, a subclass of Replace, renames the original table instead of dropping it. This is useful for preserving the original table data or for specific database environments. A transformation function is required to define the new table name.

```python
from pgcopy import CopyManager
from pgcopy.util import RenameReplace
xform = lambda s: s + '_old'
with RenameReplace(conn, 'mytable', xform) as temp_name:
    mgr = CopyManager(conn, temp_name, cols)
    mgr.copy(records)
```
Tokens:
Raw