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)