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??

Answer 1

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.