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
. Mixed-case names are not supported.
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)