[Tutor] help with postgreSQL and .csv

[Peter Otten]

Ismar Sehic wrote:

> hello.

Ismar, please post in plain text. The markup appears as funny stars over 
here.

> i wrote the following code, to insert some values from a csv file to my
> postgres table :
> 
> *    ******
> *import psycopg2*
> *conn = psycopg2.connect("host = ***.***.***.*** user=******* dbname =
> ****** ")*
> *cur = conn.cursor()*
> *import csv*
> *with open('HotelImages.csv', 'rb') as f:           *
> *    mycsv = csv.reader(f, delimiter = '|')*
> *    for row in mycsv:*
> *        hotel_code = row[0]*
> *        hotel_url = row[-1]*
> *        sql  = " UPDATE hotel SET path_picture = "+"';"+hotel_url+"'
>  WHERE code LIKE '"+"%"+hotel_code+"'"*
> *        print '--->'+sql*
> *        cur.execute(sql)*
> *        conn.commit()*
> *c.close()*
> *print '----->Complete'*
> *    *******
> 
> 
> the for loop iterates through the table, comparing the values from the csv
> line by line with the table column 'code'.
> example of csv lines:
> *    ******
> *94176|HAB|7|2|09/094176/094176a_hb_w_007.jpg*
> *94176|HAB|8|3|09/094176/094176a_hb_w_008.jpg*
> *94176|BAR|6|7|09/094176/094176a_hb_ba_006.jpg*
> *94176|RES|5|6|09/094176/094176a_hb_r_005.jpg*
> *94176|HAB|1|1|09/094176/094176a_hb_w_001.jpg*
> *94176|CON|4|8|09/094176/094176a_hb_k_004.jpg*
> *94176|COM|2|4|09/094176/094176a_hb_l_002.jpg*
> *94176|RES|3|5|09/094176/094176a_hb_r_003.jpg*
> *    ******
> example of the code column value : *GEN94176, XLK94176,KJK94176*....
> the number before the first ' | ' gets just one hit in the database table
> column, inserts some random picture once.also, if the same numbers in some
> other 'code' column row are appearing, but in different order, it inserts
> the same picture.
> my goal is to make it write all the picture url values separated by a ';'
> in just one field and to input the data correctly.
> i'm new to python, as a matter of fact, just started to learn programming.
> i would really like to know where is my mistake.advice and help
> appreciated!

In sqlite3

    cur.execute("update hotel "
                "set path_picture = coalesce(path_picture || ';' || ?, ?) "
                "where code like ?;",
                (path_picture, path_picture, "%" + code))

would work. Alternatively you could collect pictures with the same code in 
Python:

from collections import defaultdict

code_to_pic = defaultdict(list)
for row in mycsv:
    code_to_pic[row[0]].append(row[-1])

cur.executemany("update hotel set path_picture = ? where code like ?",
                [(";".join(urls), "%" + code) for code, urls in 
code_to_pic.items()])

Warning: This would overwrite any urls already in the database. It also 
assumes that you have only one match per row of the where-expression. So 
don't do it that way unless you are absolutely sure you understand these 
limitations.

By the way, the right way to do this is to add another table to the db. That 
table should have code and url columns and one row per url/code pair.