Search code examples
postgresqlpython-2.7postgresql-copy

Python PostgreSQL using copy_from to COPY list of objects to table


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


Solution

  • 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.