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

Question

I have simple postgresql db with oid. The number of columns and their names are similar to the headers in the csv file. I'm trying to use copy 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 FROM STDIN WITH DELIMITER AS '|' CSV HEADER"""

    print ("OK")
    conn = psycopg2.connect(database=dbname,user=user,password=password,host=host,port=port)
    cursor = conn.cursor()

    for line in csv_file:
        print(line)


    #cursor.copy_expert(sql = SQL_STATEMENT % table, file = csv_file)
    cursor.copy_expert("COPY images_original FROM STDIN WITH DELIMITER AS '|' CSV HEADER", file = csv_file)
    cursor.execute("COMMIT;")

I got a list of lines as output and then my process becomes finished. But there are no changes in database, and no errors. How can I debug that? Here is my --trace:

manager_db.py(63):         cursor.copy_expert("COPY images_original FROM STDOUT WITH DELIMITER AS '|' 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 is my 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
)
WITH (
  OIDS=TRUE
);
ALTER TABLE images_original
  OWNER TO on;

and fist rows in my 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

FROM STDOUT is weird. Normally it's FROM STDIN or TO STDOUT. See the doc — Daniel Vérité
– Daniel Vérité, Commented Oct 28, 2014 at 15:40
can you provide the first dozen or so lines from the images_original file and edit into your question? Also, the sql to create the table you are copying in to would be handy to figure out why your copy in command doesn't work. — Greg
– Greg, Commented Oct 28, 2014 at 21:18
@DanielVérité actually I tried both and forgot to edit - fixed. — user1835337
– user1835337, Commented Oct 29, 2014 at 5:26

Greg · Accepted Answer · 2014-10-29 13:10:36Z

1

Thanks for updating the question. It is a bit unusual to have spaces in column names, not impossible though. I tried entering your copy line directly from psql command line. It worked. Then it jumped out at me:

for line in csv_file:
    print(line)

csv_file.seek(0)

When you print the data from the file you move the file pointer. seek(0) resets it to the beginning. This command should work if you add the seek, or, simply eliminate the loop that prints the files contents.

-g

answered Oct 29, 2014 at 13:10

Greg

6,7892 gold badges29 silver badges43 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user1835337 Over a year ago

I tried to do this copy command without this loop, I added it only to check that my STDIN isn't empty. I changed my script to cursor.execute("COPY.."), that origionally requires superuser rights. It works, but with some limitation, that aren't so necessary for me. Thanks for your answer.

Greg Over a year ago

Interesting. I ran your script above, just removing the self references. It populated the table just like you had it.

Collectives™ on Stack Overflow

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

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related