Jak napisać DataFrame do tabeli postgres?

Istnieje metoda DataFrame.to_sql , ale działa ona tylko dla baz danych mysql, sqlite i oracle. Nie mogę przejść do tej metody połączenia postgres lub silnika sqlalchemy.

Począwszy od pandy 0.14 (wydanej pod koniec maja 2014), obsługiwany jest postgresql. sqlModuł używa teraz sqlalchemywspierać różne smaki bazy danych. Możesz przekazać silnik sqlalchemy dla bazy danych postgresql (zobacz dokumentację ). Na przykład:

from sqlalchemy import create_engine
engine = create_engine('postgresql://scott:tiger@localhost:5432/mydatabase')
df.to_sql('table_name', engine)

Masz rację, że w pandach do wersji 0.13.1 postgresql nie był obsługiwany. Jeśli musisz użyć starszej wersji pand, oto poprawiona wersja pandas.io.sql: https://gist.github.com/jorisvandenbossche/10841234 .
Napisałem to jakiś czas temu, więc nie mogę w pełni zagwarantować, że zawsze działa, ale podstawa powinna tam być). Jeśli umieścisz ten plik w katalogu roboczym i zaimportujesz go, powinieneś być w stanie to zrobić (gdzie conjest połączenie postgresql):

import sql  # the patched version (file is named sql.py)
sql.write_frame(df, 'table_name', con, flavor='postgresql')

Szybsza opcja:

Poniższy kod skopiuje twój Pandas DF do postgres DB znacznie szybciej niż metoda df.to_sql i nie będziesz potrzebować żadnego pośredniego pliku csv do przechowywania df.

Utwórz silnik na podstawie specyfikacji bazy danych.

Utwórz tabelę w swojej bazie danych postgres, która ma taką samą liczbę kolumn jak Dataframe (df).

Dane w DF zostaną wstawione do Twojej tabeli postgres.

from sqlalchemy import create_engine
import psycopg2 
import io

jeśli chcesz zamienić tabelę, możemy zastąpić ją normalną metodą to_sql, używając nagłówków z naszego df, a następnie załadować cały czasochłonny plik df do DB.

engine = create_engine('postgresql+psycopg2://username:password@host:port/database')

df.head(0).to_sql('table_name', engine, if_exists='replace',index=False) #truncates the table

conn = engine.raw_connection()
cur = conn.cursor()
output = io.StringIO()
df.to_csv(output, sep='\t', header=False, index=False)
output.seek(0)
contents = output.getvalue()
cur.copy_from(output, 'table_name', null="") # null values become ''
conn.commit()

Rozwiązanie Pandas 0.24.0+

W Pandas 0.24.0 wprowadzono nową funkcję zaprojektowaną specjalnie do szybkiego zapisu do Postgres. Możesz dowiedzieć się więcej na ten temat tutaj: https://pandas.pydata.org/pandas-docs/stable/user_guide/io.html#io-sql-method

import csv
from io import StringIO

from sqlalchemy import create_engine

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://myusername:mypassword@myhost:5432/mydatabase')
df.to_sql('table_name', engine, method=psql_insert_copy)

Tak to zrobiłem.

Może być szybszy, ponieważ używa execute_batch:

# df is the dataframe
if len(df) > 0:
    df_columns = list(df)
    # create (col1,col2,...)
    columns = ",".join(df_columns)

    # create VALUES('%s', '%s",...) one '%s' per column
    values = "VALUES({})".format(",".join(["%s" for _ in df_columns])) 

    #create INSERT INTO table (columns) VALUES('%s',...)
    insert_stmt = "INSERT INTO {} ({}) {}".format(table,columns,values)

    cur = conn.cursor()
    psycopg2.extras.execute_batch(cur, insert_stmt, df.values)
    conn.commit()
    cur.close()

Dla Pythona 2.7 i Pandas 0.24.2 oraz przy użyciu Psycopg2

Moduł połączeniowy Psycopg2

def dbConnect (db_parm, username_parm, host_parm, pw_parm):
    # Parse in connection information
    credentials = {'host': host_parm, 'database': db_parm, 'user': username_parm, 'password': pw_parm}
    conn = psycopg2.connect(**credentials)
    conn.autocommit = True  # auto-commit each entry to the database
    conn.cursor_factory = RealDictCursor
    cur = conn.cursor()
    print ("Connected Successfully to DB: " + str(db_parm) + "@" + str(host_parm))
    return conn, cur

Połącz się z bazą danych

conn, cur = dbConnect(databaseName, dbUser, dbHost, dbPwd)

Zakładając, że dataframe jest już obecna jako df

output = io.BytesIO() # For Python3 use StringIO
df.to_csv(output, sep='\t', header=True, index=False)
output.seek(0) # Required for rewinding the String object
copy_query = "COPY mem_info FROM STDOUT csv DELIMITER '\t' NULL ''  ESCAPE '\\' HEADER "  # Replace your table name in place of mem_info
cur.copy_expert(copy_query, output)
conn.commit()