Pg8000 Copy From Csv

I'm using pg8000 on an App Engine flask application so that I can be able to process a CSV file and insert it into a PSQL instance (hosted on AZURE). Why am I using pg8000 and not

Solution 1:

Looking at the source code, there does not seem to be a way to directly import CSVs, nor does the code appear to have any built-in wrapper around INSERT queries, making it possible to

You do have the option of manually using a CSV reader and using executemany:

import csv
import pg8000

conn = pg8000.connect(user="postgres", password="C.P.Snow")
cursor = conn.cursor()

command = 'INSERT INTO book (title) VALUES (%s), (%s) RETURNING id, title'
with open('my-data.csv', 'rb') as fl:
    data = list(csv.reader(fl))
    conn.executemany(command, data)

As a word of caution, depending on the size of your data, it may be better to use islice:

withopen('my-data.csv', 'rb') as fl:
    reader = csv.reader(fl)
    slice = itertool.islice(reader, 100)
    whileslice:
        conn.executemany(command, slice)
        slice = itertool.islice(reader, 100)

Solution 2:

As suggested in another question here, you could use the next method before applying the logic on the csv files and before using the csv read method.

Sorry in advance for not inserting as a complement to the previous answer, but I don't have enough points to do so.

I'm having the same issue and I solved the problem using the below. Please notice that for me, the correct way of executing many is on cursor object, not on the conn.

conn = pg8000.connect(user='username', password='password', host='host', port=5432, database='database name')
cursor = conn.cursor()

command = "INSERT INTO public.salesforce_accounts (field1, field2, field3, field4, field5, field6) VALUES (%s, %s, %s, %s, %s, %s)"
with open('test.csv', 'r') as file:
    next(file)
    data = list(csv.reader(file))
    cursor.executemany(command, data)