Python PostgreSQL using copy_from to COPY list of objects to table

Question

I'm using Python 2.7 and psycopg2 to connect to my DB server ( PostgreSQL 9.3 ) and I a list of objects of ( Product Class ) holds the items which i want to insert

products_list = []
products_list.append(product1)
products_list.append(product2)

And I want to use copy_from to insert this products list to the product table. I tried some tutorials and i had a problem with converting the products list to CSV format because the values contain single quote, new lines, tabs and double quotes. For example ( Product Description ) :

<div class="product_desc">
    Details :
    Product's Name : name
</div>

The escaping corrupted the HTML code by adding single quote before any single quote and it, So i need to use a save way to convert the list into CSV to COPY it? OR using any other way to insert the list without converting it to CSV format??

Use the Python csv module to do that, rather than doing it yourself. Write it to a StringIO object, then send that to the database using copy_from. — Craig Ringer
– Craig Ringer, Commented Jun 15, 2015 at 0:46
"corrupted" how? Details? Using what settings for the module? Using what COPY options? — Craig Ringer
– Craig Ringer, Commented Jun 15, 2015 at 19:02

Morad Edwar · Accepted Answer · 2015-06-17 12:56:55Z

I figured it out, First of all i created a function to convert my object to csv row

import csv

@staticmethod
def adding_product_to_csv(item, out):
writer = csv.writer(out, quoting=csv.QUOTE_MINIMAL,quotechar='"',delimiter=',',lineterminator="\r\n")
writer.writerow([item.name,item.description])

Then in my code i created a csv file using Python IO to store the data in it to COPY it and stored every object in the csv file using my previous function:

file_name = "/tmp/file.csv"
myfile = open(file_name, 'a')
for item in object_items:
    adding_product_to_csv(item, myfile)

Now I created the CSV file and it's ready to be copied using copy_from which exists in psycopg2 :

# For some reason it needs to be closed before copying it to the table
csv_file.close()
cursor.copy_expert("COPY products(name, description) from stdin with delimiter as ',' csv QUOTE '\"' ESCAPE '\"' NULL 'null' ",open(file_name))
conn.commit()
# Clearing the file
open(file_name, 'w').close()

And it's working now.

Collectives™ on Stack Overflow

Python PostgreSQL using copy_from to COPY list of objects to table

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related