Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1mXQmK-0007lA-6f for pgsql-general@arkaria.postgresql.org; Mon, 04 Oct 2021 16:25:24 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1mXQmJ-000724-1w for pgsql-general@arkaria.postgresql.org; Mon, 04 Oct 2021 16:25:23 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1mXQmI-00071v-PI for pgsql-general@lists.postgresql.org; Mon, 04 Oct 2021 16:25:22 +0000 Received: from mail-il1-x130.google.com ([2607:f8b0:4864:20::130]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1mXQmC-0000js-0c for pgsql-general@lists.postgresql.org; Mon, 04 Oct 2021 16:25:22 +0000 Received: by mail-il1-x130.google.com with SMTP id y17so10581629ilb.9 for ; Mon, 04 Oct 2021 09:25:15 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20210112; h=from:message-id:mime-version:subject:date:in-reply-to:cc:to :references; bh=1bHoUTu7x3C2x3Ghfq9uGbk2bKO+0dNtHyZ3YXbPqWU=; b=hgnnfKNvAvHJzo54nEgPUlfAfFWxCKTYMkMLwUQ3oXdDUgp8h60TOG+KJu3DD5qWCj 1XyzpEgxEmKAFxeNy7YWohEP6d6wnembc0Ko8HicUbS9dERCPcR1V5CM0+aisF0DczUc 7mcdJFe8ZLv2me3G7zjSrTuu4FWEI/yaqHZYOb8+14etPBb8ECx5EmxuibY+qJ/K/6mo zN7Coth94DoHScgf/2ax9AYuM5Iht049DIz8DtIpi3393PK57i+CFkStf1g64BMcGlNg 9Ty3HUaFBI3UEdxpdiqD89C2zJY48CngH55I8PBKHHcsjom5X3sjDrVgqLj7aCvejuZq lydA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20210112; h=x-gm-message-state:from:message-id:mime-version:subject:date :in-reply-to:cc:to:references; bh=1bHoUTu7x3C2x3Ghfq9uGbk2bKO+0dNtHyZ3YXbPqWU=; b=geDOlERtnxDoB0ScUjWyNp/2ubHEkF9XZdVQrHf/9wfO0eAfwGniyQpaUNlyG6pmp9 pSC5vphBVGYNDwrXSL2TCymVFxI+2dGImX/WVoD4l5xrI5GxzTYNyBBIyCrm8YN/AjGl Ne2egwro0X5bML5gFPQxXlB+4MXUspX31W9dx1E4brH/LwpAiqXHm7ar7L11pmCHFhdD OveDRb1E0yRn48ev9OnO8Gx+t+rUVdTOGn34I9RfINPuzU7xScliVUeq4Ljv3SuLPMvO 7D5+p73goazf3ctx56L/Ag1qvH8+gHadONEjplrU+8dyEARd9lW+NJKLF3ws0fyUszHK GJHg== X-Gm-Message-State: AOAM5318KAg9rYjKC6RVqICJcHin6D+8C3YTqrCNTB970jleguVmMODG UNJpEcwdaO+FTbZn119Z3yg= X-Google-Smtp-Source: ABdhPJwl33HRuF9DkSZwyUOCGXHrI2ICNxr7h4Bxv2SB8nmn6t8qQvKB6nCbSsOft/ANJb/D/LWdbQ== X-Received: by 2002:a05:6e02:188d:: with SMTP id o13mr10880949ilu.320.1633364714615; Mon, 04 Oct 2021 09:25:14 -0700 (PDT) Received: from smtpclient.apple ([2601:681:5500:dde0:ed94:cc53:bb45:5a54]) by smtp.gmail.com with ESMTPSA id o5sm9668447ilk.88.2021.10.04.09.25.13 (version=TLS1_2 cipher=ECDHE-ECDSA-AES128-GCM-SHA256 bits=128/128); Mon, 04 Oct 2021 09:25:14 -0700 (PDT) From: Rob Sargent Message-Id: <3D84E92F-28DF-460C-90C7-160F41BE8B11@gmail.com> Content-Type: multipart/alternative; boundary="Apple-Mail=_A87AA26F-A84E-45B5-8792-A94D7C5D260B" Mime-Version: 1.0 (Mac OS X Mail 14.0 \(3654.120.0.1.13\)) Subject: Re: Testing of a fast method to bulk insert a Pandas DataFrame into Postgres Date: Mon, 4 Oct 2021 10:25:12 -0600 In-Reply-To: Cc: Adrian Klaver , pgsql-general To: Shaozhong SHI References: X-Mailer: Apple Mail (2.3654.120.0.1.13) List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --Apple-Mail=_A87AA26F-A84E-45B5-8792-A94D7C5D260B Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=utf-8 > On Oct 4, 2021, at 10:20 AM, Shaozhong SHI = wrote: >=20 > Hello, Adrian Klaver, >=20 > Pandas version is 0.23.0. >=20 > I used the following code: >=20 > def psql_insert_copy(table, conn, keys, data_iter): > # gets a DBAPI connection that can provide a cursor > dbapi_conn =3D conn.connection > with dbapi_conn.cursor() as cur: > s_buf =3D StringIO() > writer =3D csv.writer(s_buf) > writer.writerows(data_iter) > s_buf.seek(0) >=20 > columns =3D ', '.join('"{}"'.format(k) for k in keys) > if table.schema: > table_name =3D '{}.{}'.format(table.schema, table.name = ) > else: > table_name =3D table.name >=20 > sql =3D 'COPY {} ({}) FROM STDIN WITH CSV'.format( > table_name, columns) > cur.copy_expert(sql=3Dsql, file=3Ds_buf) > =20 > engine =3D create_engine('postgresql+psycopg2://:5432/postgres') > try: > df.to_sql('test1', engine, schema=3D'public', if_exists=3D'append', = index=3DFalse, method=3Dpsql_insert_copy)=20 you need to quote =E2=80=98psql_insert_copy' --Apple-Mail=_A87AA26F-A84E-45B5-8792-A94D7C5D260B Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset=utf-8

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 = =3D conn.connection
    with = dbapi_conn.cursor() as cur:
      =   s_buf =3D StringIO()
      =   writer =3D csv.writer(s_buf)
    =     writer.writerows(data_iter)
  =       s_buf.seek(0)

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

      =   sql =3D 'COPY {} ({}) FROM STDIN WITH CSV'.format(
            table_name, = columns)
        = cur.copy_expert(sql=3Dsql, file=3Ds_buf)
  =       
engine =3D = create_engine('postgresql+psycopg2://:5432/postgres')
try:
    df.to_sql('test1', = engine, schema=3D'public', if_exists=3D'append', index=3DFalse, = method=3Dpsql_insert_copy) 

you need to quote = =E2=80=98psql_insert_copy'

= --Apple-Mail=_A87AA26F-A84E-45B5-8792-A94D7C5D260B--