BULK COPY

Builds a PostgreSQL COPY-based bulk insert using temporary files via SqlBulkCopy. This is significantly faster than individual INSERT statements when loading large amounts of data.

Basic Usage

bc = SQL.bulk_copy('films', 'code', 'title', 'did')
bc.write_row(code='UA502', title='Bananas', did=105)
bc.write_row(code='T_601', title='Yojimbo', did=106)
bc.execute(db)

This writes rows to a temporary file in PostgreSQL’s COPY text format, then uses psycopg2’s copy_from() to load them into the table.

NULL Values

Columns omitted from write_row() or passed as None are written as NULL:

bc = SQL.bulk_copy('films', 'code', 'title', 'did')
bc.write_row(code='UA502', title='Bananas')
# did is NULL

Escaping

Special characters (tabs, newlines, backslashes) and non-printable bytes are automatically escaped in the COPY format:

bc = SQL.bulk_copy('notes', 'id', 'body')
bc.write_row(id=1, body='line1\nline2')

Row Count

The count_rows attribute tracks how many rows have been written:

bc = SQL.bulk_copy('films', 'code', 'title')
bc.write_row(code='UA502', title='Bananas')
bc.write_row(code='T_601', title='Yojimbo')
assert bc.count_rows == 2