Replace

When possible, faster insertion may be realized by inserting into an empty table with no indices or constraints. In a case where the entire contents of the table can be reinserted, the pgcopy.Replace context manager automates the process.

class pgcopy.Replace(connection, table)

Context manager for fast updates on table involving most rows in the table. Instead of executemany(“UPDATE …”), create and populate a new table (which can be done using COPY), then rename. This is possible only if no other tables in the db depend on the table.

Parameters
  • connection (psycopg2 connection) – database connection

  • table (str) – the table name. Schema may be specified using dot notation: schema.table.

On entry, it creates a new table like the original, with a temporary name. Default column values are included.

On exit, it recreates the constraints, indices, triggers, and views on the new table, then replaces the old table with the new:

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

New db objects are named like the old, where possible. Names of foreign key and check constraints will be mangled.

Note

on PostgreSQL 9.1 and earlier, concurrent queries on the table will fail once the table is dropped.

As of v0.6 there is also pgcopy.util.RenameReplace, which instead of dropping the original objects renames them using a transformation function.

class pgcopy.util.RenameReplace(connection, table, xform)

Subclass of Replace whic renaming original table instead of dropping it.

Parameters
  • connection (psycopg2 connection) – database connection

  • table (str) – the table name. Schema may be specified using dot notation: schema.table.

  • xform (function) – a function translating original table name to new name, used for table and pk constraint names

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)