psycopg: can't copy from csv to postgresql with python, no results -

psycopg: can't copy from csv to postgresql with python, no results -

i have simple postgresql db oid. number of columns , names similar headers in csv file. i'm trying utilize re-create command:

def db_copy_images(self, file): my_file = open(file) try: self.process_file('images_original', my_file) finally: pass def process_file(self, table, csv_file): dbname = 'v' user = 'on' password = 'on' host = 'ip' port = 5432 sql_statement = """copy %s stdin delimiter '|' csv header""" print ("ok") conn = psycopg2.connect(database=dbname,user=user,password=password,host=host,port=port) cursor = conn.cursor() line in csv_file: print(line) #cursor.copy_expert(sql = sql_statement % table, file = csv_file) cursor.copy_expert("copy images_original stdin delimiter '|' csv header", file = csv_file) cursor.execute("commit;")

i got list of lines output , process becomes finished. there no changes in database, , no errors. how can debug that? here --trace:

manager_db.py(63): cursor.copy_expert("copy images_original stdout delimiter '|' csv header", file = csv_file) manager_db.py(66): cursor.execute("commit;") manager_db.py(68): conn.commit manager_db.py(69): cursor.close manager_db.py(70): conn.close manager_db.py(36): pass --- modulename: trace, funcname: _unsettrace trace.py(80): sys.settrace(none)

here db create:

create table images_original ( "image id" integer not null, "file url" character varying not null, "file format id" integer, "file format" character varying, "file width" integer, "file height" integer, "file quality factor" integer, "file bit depth" integer, "file margin" integer, "file bg color" integer, "file size" integer, "scaling factor" character varying, delta character varying ) ( oids=true ); alter table images_original owner on;

and fist rows in test file:

image id|file url|file format id|file format|file width|file height|file quality factor|file bit depth|file margin|file bg color|file size|scaling factor|delta 172317239|http://cps-static.r.com/2/open/nbc/the%20office/steve%20carell%204.jpg|0||2336|3504|||||949406||ins 172317239|http://cps-static.r.com/2/open/nbc/the%20office/_derived_jpg_q90_0x200_m0/steve%20carell%204.jpg|1391886|jpg|133|200|90|24|0|24|6624|5.69|ins 172317239|http://cps-static.r.com/2/open/nbc/the%20office/_derived_jpg_q90_155x235_m0/steve%20carell%204.jpg|1391887|jpg|155|232|90|24|0|24|8092|6.64|ins

thanks updating question. bit unusual have spaces in column names, not impossible though. tried entering re-create line straight psql command line. worked. jumped out @ me:

for line in csv_file: print(line) csv_file.seek(0)

when print info file move file pointer. seek(0) resets beginning. command should work if add together seek, or, eliminate loop prints files contents.

-g

python postgresql csv psycopg2