[python] Postgres 테이블에 DataFrame을 작성하는 방법은 무엇입니까?

Program / 글쓴이

이 DataFrame.to_sql의 방법은, 그러나 그것은 단지 MySQL은, SQLite는 오라클 데이터베이스에 적용됩니다. 이 메서드 postgres 연결 또는 sqlalchemy 엔진에 전달할 수 없습니다.

답변

Pandas 0.14 (2014 년 5 월 말 출시)부터 postgresql이 지원됩니다. sql모듈은 현재 사용하는 sqlalchemy다른 데이터베이스의 맛을 지원합니다. postgresql 데이터베이스에 대한 sqlalchemy 엔진을 전달할 수 있습니다 ( docs 참조 ). 예 :

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

판다에서 버전 0.13.1까지 postgresql이 지원되지 않았다는 것이 맞습니다. 당신이 팬더의 이전 버전을 사용해야하는 경우, 여기의 패치 버전 pandas.io.sql: https://gist.github.com/jorisvandenbossche/10841234은 .
나는 이것을 오래 전에 썼기 때문에 그것이 항상 작동한다는 것을 완전히 보장 할 수는 없지만 기초가 있어야합니다.) 해당 파일을 작업 디렉토리에 넣고 가져 오면 다음 작업을 수행 할 수 있습니다 ( conpostgresql 연결 위치).

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

답변

더 빠른 옵션 :

다음 코드는 Pandas DF를 df.to_sql 메서드보다 훨씬 빠르게 postgres DB에 복사하며 df를 저장하는 데 중간 csv 파일이 필요하지 않습니다.

DB 사양에 따라 엔진을 만듭니다.

데이터 프레임 (df)과 동일한 수의 열이있는 postgres DB에 테이블을 만듭니다.

DF의 데이터는 postgres 테이블에 삽입 됩니다.

from sqlalchemy import create_engine
import psycopg2 
import io

테이블을 교체하려면 df의 헤더를 사용하여 일반 to_sql 메소드로 교체 한 다음 큰 시간이 소요되는 df 전체를 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()

답변

Pandas 0.24.0+ 솔루션

Pandas 0.24.0에서는 Postgres에 대한 빠른 쓰기를 위해 특별히 설계된 새로운 기능이 도입되었습니다. 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)

답변

이것이 내가 한 방법입니다.

다음을 사용하기 때문에 더 빠를 수 있습니다 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()

답변

Python 2.7 및 Pandas 0.24.2 및 Psycopg2 사용

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

데이터베이스에 연결

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

데이터 프레임이 이미 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()

답변