如何将DataFrame写入postgres表? [英] How to write DataFrame to postgres table?

问题描述

有 DataFrame.to_sql 方法，但是它仅适用于mysql，sqlite和oracle数据库.我无法将这种方法传递给postgres连接或sqlalchemy引擎.

There is DataFrame.to_sql method, but it works only for mysql, sqlite and oracle databases. I cant pass to this method postgres connection or sqlalchemy engine.

推荐答案

从pandas 0.14(2014年5月发布)开始，支持postgresql. sql模块现在使用sqlalchemy来支持不同的数据库风格.您可以为PostgreSQL数据库传递sqlalchemy引擎(请参见 docs ).例如:

Starting from pandas 0.14 (released end of May 2014), postgresql is supported. The sql module now uses sqlalchemy to support different database flavors. You can pass a sqlalchemy engine for a postgresql database (see docs). E.g.:

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 .
我是在前一段时间写的，所以不能完全保证它始终有效，但是基础应该在那里).如果您将该文件放在工作目录中并导入，那么您应该能够做到(其中con是Postgresql连接):

---

You are correct that in pandas up to version 0.13.1 postgresql was not supported. If you need to use an older version of pandas, here is a patched version of pandas.io.sql: https://gist.github.com/jorisvandenbossche/10841234.
I wrote this a time ago, so cannot fully guarantee that it always works, buth the basis should be there). If you put that file in your working directory and import it, then you should be able to do (where con is a postgresql connection):

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

这篇关于如何将DataFrame写入postgres表?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！