Search code examples
pythoncsvgoogle-app-enginepg8000

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 psycopg2? -> Because app engine doesn't support psycopg2.

So far, the docs of pg8000 don't state a function that will do this like the one psycopg2 has. I haven't found an example that achieves this on SO or any other place, including the docs.

Anyone knows if this is possible?

Solution

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

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