将数据框写入Postgres数据库

本文介绍了将数据框写入Postgres数据库的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我想将pandas数据框写入postgres表。我以如下方式连接到db：

I want to write a pandas dataframe to a postgres table. I make a connection to db as follows:

import psycopg2 import pandas as pd import sqlalchemy def connect(user, password, db, host='localhost', port=5432): '''Returns a connection and a metadata object''' url = 'postgresql://{}:{}@{}:{}/{}' url = url.format(user, password, host, port, db) # The return value of create_engine() is our connection object con = sqlalchemy.create_engine(url, client_encoding='utf8') # We then bind the connection to MetaData() meta = sqlalchemy.MetaData(bind=con, reflect=True) return con, meta con, meta = connect('user_name', 'password', 'db_name', host='host_name')

当我从已经填充的表中读取时，它可以工作罚款：

When I read from a table that is already populated, it works fine:

df = pd.read_sql("SELECT * FROM db.table_name limit 10",con=con) print df

我会喜欢e能够将df写入表。为了对此进行测试，我有一个名为 test的临时表，其中有两个字段，名称和年龄。

I would like to be able to write df to a table. To test this, I have a temporary table called 'test' with two fields name and age.

# create a temp df table = [['name', 'age'], ['nameA' , 20], ['nameB', 30]] headers = table.pop(0) df = pd.DataFrame(table, columns=headers) # write to db df.to_sql('db.test', con, if_exists = 'replace', index=False)

然后检查是否填充了临时表：

I then check if the temp table is populated:

df = pd.read_sql("SELECT * FROM db.test limit 10",con=con) print df

我得到一个空的数据框！使用df.to_sql时没有出现任何错误，但是没有任何内容写入数据库（？）。我缺少什么以及如何解决这个问题？

I get an empty dataframe! I got no errors when I use df.to_sql but nothing is getting written to the database (?). What am I missing and how do I go about fixing this?

版本：

Pandas: 0.19.2 Sqlachemy: 1.1.10 Postgres: 9.4.9

推荐答案

我还没有弄清楚为什么 df.to_sql 没有写到表中。使用 pd.io.sql.SQLDatabase 写入表适用于我的测试用例：

I have not figured out why df.to_sql did not write to the table. Writing to table using pd.io.sql.SQLDatabase worked for my test case:

meta = sqlalchemy.MetaData(con, schema='db_name') meta.reflect() pdsql = pd.io.sql.SQLDatabase(con, meta=meta) pdsql.to_sql(df, 'test', if_exists='replace')

我不会考虑这个解决方案-我很乐意接受更好的解决方案或一个答案，以使df.to_sql（）不能按预期方式运行。

I would not consider this THE solution -- I'd be happy to accept better solution or an answer that brings a closure to why df.to_sql() does not behave as expected.