PostgreSQL COPY from file results in empty table

Question

I am using the following code to copy data from a .csv file into a table:

csv file (comma separated) with header:

uid,name,val1,val2,file
SKU001,glasses,blue,metal,block_0
...

create table:

ih_sql = 'CREATE TABLE "sku_data" (uid VARCHAR, name VARCHAR, val1 VARCHAR, val2 VARCHAR, file VARCHAR, PRIMARY KEY (uid));'
engine.execute(ih_sql)
#<sqlalchemy.engine.result.ResultProxy at 0x7fee9b8ff550>

copy data:

ih_file = os.path.abspath(f'{PATH}data.csv')
copy_sql = f"COPY sku_data FROM '{ih_file}' WITH CSV HEADER DELIMITER ','"
engine.execute(copy_sql)
#<sqlalchemy.engine.result.ResultProxy at 0x7fee9b8ff710>

However the resultant table is empty, zero rows were created. How can I work out why no data was copied from the csv file?

Related: https://stackoverflow.com/questions/13125236/sqlalchemy-psycopg2-and-postgresql-copy — Ilja Everilä, Oct 13 '18 at 12:23

score 2 · Accepted Answer · answered Oct 13 '18 at 09:48

You have to explicitly commit the transaction, e.g.:

copy_sql = f"COPY sku_data FROM '{ih_file}' WITH CSV HEADER DELIMITER ','"
connection = engine.connect()
with connection.begin():
    connection.execute(copy_sql)

PostgreSQL COPY from file results in empty table

1 Answers1