I created a table with a structure similar to the following:
create table some_table (
id serial,
numbers int []
);
I want to copy a pandas dataframe in an efficient way, so I don't want to use the slow to_sql method, so, following https://stackoverflow.com/a/41876462/754176 and https://stackoverflow.com/a/29125940/754176 I tried the following:
import pandas as pd
import psycopg2
# Create the connection, and the cursor (ommited)
# Function from the second link
def lst2pgarr(alist):
return '{' + ','.join(alist) + '}'
df = pd.DataFrame({'numbers': [[1,2,3], [4,5,6], [7,8,9]]})
df['numbers'] = df.numbers.apply(lambda x: lst2pgarr([str(y) for y in x]))
import io
f = io.StringIO()
df.to_csv(f, index=False, header=False, sep="|")
f.seek(0)
cursor.copy_from(f, 'some_table', columns=["numbers"], sep='|')
cursor.close()
This code doesn't throw an error, but It doesn't write anything to the table.
So, I modified the code to
import csv
df = pd.DataFrame({'numbers': [[1,2,3], [4,5,6], [7,8,9]]})
df['numbers'] = df.numbers.apply(lambda x: lst2pgarr([str(y) for y in x]))
f = io.StringIO()
df.to_csv(f, index=False, header=False, sep="|", quoting=csv.QUOTE_ALL, quotechar="'"))
f.seek(0)
cursor.copy_from(f, 'some_table', columns=["numbers"], sep='|')
cursor.close()
This code throws the following error:
---------------------------------------------------------------------------
DataError Traceback (most recent call last)
<ipython-input-40-3c58c4a64abc> in <module>
----> 1 cursor.copy_from(f, 'some_table', columns=["numbers"], sep='|')
DataError: malformed array literal: "'{1,2,3}'"
DETAIL: Array value must start with "{" or dimension information.
CONTEXT: COPY some_table, line 1, column numbers: "'{1,2,3}'"
What should I do ?
Also, It will be interesting to know, why the first code doesn't throw an error.
