Как написать DataFrame в таблицу postgres?
Существует метод DataFrame.to_sql, но он работает только для баз данных mysql, sqlite и oracle. Я не могу перейти к этому методу postgres connection или sqlalchemy engine.
Ответы
Ответ 1
Начиная с pandas 0,14 (выпущенный в конце мая 2014 года) поддерживается postgresql. Модуль sql
теперь использует sqlalchemy
для поддержки различных вариантов базы данных. Вы можете передать движок sqlalchemy для базы данных postgresql (см. docs). Например:.
from sqlalchemy import create_engine
engine = create_engine('postgresql://scott:[email protected]:5432/mydatabase')
df.to_sql('table_name', engine)
Вы правы, что в pandas до версии 0.13.1 postgresql не поддерживался. Если вам нужно использовать более старую версию pandas, вот исправленная версия pandas.io.sql
: https://gist.github.com/jorisvandenbossche/10841234.
Я написал это некоторое время назад, поэтому не могу полностью гарантировать, что он всегда работает, но база должна быть там). Если вы поместите этот файл в свой рабочий каталог и импортируете его, тогда вы сможете это сделать (где con
- это соединение postgresql):
import sql # the patched version (file is named sql.py)
sql.write_frame(df, 'table_name', con, flavor='postgresql')
Ответ 2
Более быстрый вариант:
Следующий код скопирует ваш DF Pandas в postgres DB гораздо быстрее, чем метод df.to_sql, и вам не понадобится промежуточный файл csv для хранения df.
Создайте движок на основе ваших спецификаций БД.
Создайте таблицу в своей базе данных postgres, которая будет иметь такое же количество столбцов, что и кадр данных (df).
Данные в DF будут вставлены в вашу таблицу postgres.
from sqlalchemy import create_engine
import psycopg2
import io
если вы хотите заменить таблицу, мы можем заменить ее обычным методом to_sql, используя заголовки из нашего df, а затем загрузить всю большую часть времени, требующую df, в базу данных.
engine = create_engine('postgresql+psycopg2://username:[email protected]: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()
Ответ 3
Вот как я это делаю, я могу быть быстрее, потому что он использует 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()
cur = db_conn.cursor()
psycopg2.extras.execute_batch(cur, insert_stmt, df.values)
conn.commit()
cur.close()
Ответ 4
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:[email protected]:5432/mydatabase')
df.to_sql('table_name', engine, method=psql_insert_copy)
Ответ 5
@mgoldwasser: я пытаюсь добавить данные в существующую таблицу, используя ваше решение, но получаю сообщение об ошибке, что таблица уже существует и не может добавить данные. Можете ли вы помочь?