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

On Oct 4, 2021, at 10:20 AM, Shaozhong SHI <shishaozhong@gmail.com> wrote:

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)