Re: Testing of a fast method to bulk insert a Pandas DataFrame into Postgres

Hello, Adrian Klaver,

Pandas version is 0.23.0.

I used the following code:

def psql_insert_copy(table, conn, keys, data_iter):
# gets a DBAPI connection that can provide a cursor
dbapi_conn = conn.connection
with dbapi_conn.cursor() as cur:
s_buf = StringIO()
writer = csv.writer(s_buf)
writer.writerows(data_iter)
s_buf.seek(0)

columns = ', '.join('"{}"'.format(k) for k in keys)
if table.schema:
table_name = '{}.{}'.format(table.schema, table.name)
else:
table_name = table.name

sql = 'COPY {} ({}) FROM STDIN WITH CSV'.format(
table_name, columns)
cur.copy_expert(sql=sql, file=s_buf)

engine = create_engine('postgresql+psycopg2://:5432/postgres')
try:
df.to_sql('test1', engine, schema='public', if_exists='append',
index=False, method=psql_insert_copy)

I could not find obvious reasons.

Regards,

David

On Mon, 4 Oct 2021 at 17:06, Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>
wrote:

> On 10/4/21 8:44 AM, Shaozhong SHI wrote:
> >
> > Has anyone tested this one?
> > A Fast Method to Bulk Insert a Pandas DataFrame into Postgres · Ellis
> > Valentiner
> > <
> https://ellisvalentiner.com/post/a-fast-method-to-insert-a-pandas-dataframe-into-postgres/
> >
> >
> > I tried psql_insert_copy method, but I got the following error message.
> >
> > to_sql() got an unexpected keyword argument 'method'
> >
> >
> > Can anyone shed light on this?
>
> method is a legitimate keyword:
>
>
> https://pandas.pydata.org/docs/reference/api/pandas.DataFrame.to_sql.html?highlight=to_sql#pandas.DataFrame.to_sql
>
> So there must be something about how you used it.
>
> Pandas version?
>
> The actual code you used when the error occurred?
>
>
> >
> >
> > Regards,
> >
> >
> > David
> >
>
>
> --
> Adrian Klaver
> adrian(dot)klaver(at)aklaver(dot)com
>