将快速 pandas 数据框写入Postgres [英] Write fast pandas dataframe to postgres

问题描述

我想知道从pandas DataFrame到postges DB中表中写入数据的最快方法。

I wonder of the fastest way to write data from pandas DataFrame to table in postges DB.

1）我试过 pandas。 to_sql ，但是出于某种原因，复制数据需要实体，

1) I've tried pandas.to_sql, but for some reason it takes entity to copy data,

2）除了我尝试过的操作之外：

2) besides I've tried following:

import io
f = io.StringIO()
pd.DataFrame({'a':[1,2], 'b':[3,4]}).to_csv(f)
cursor = conn.cursor()
cursor.execute('create table bbbb (a int, b int);COMMIT; ')
cursor.copy_from(f, 'bbbb', columns=('a', 'b'), sep=',')
cursor.execute("select * from bbbb;")
a = cursor.fetchall()
print(a)
cursor.close()

但它返回空列表 [] 。

所以我有两个问题：最快的是什么将数据从python代码（数据框）复制到Postgres DB的方法？以及我尝试过的第二种方法有什么不正确？

So I have two questions: what is the fastest way to copy data from python code (dataframe) to postgres DB? and what was incorrect in the second approach that I've tried?

推荐答案

您的第二种方法应该很快。

Your second approach should be very fast.

您的代码有两个问题：

1. 将csv写入 f 您位于文件的末尾。


2. 编写CSV时，您需要省略标题和索引

1. After writing the csv to f you are positioned at the end of the file. You need to put your position back to the beginning before starting to read.
2. When writing a csv, you need to omit the header and index

这是您的最终代码：

import io
f = io.StringIO()
pd.DataFrame({'a':[1,2], 'b':[3,4]}).to_csv(f, index=False, header=False)  # removed header
f.seek(0)  # move position to beginning of file before reading
cursor = conn.cursor()
cursor.execute('create table bbbb (a int, b int);COMMIT; ')
cursor.copy_from(f, 'bbbb', columns=('a', 'b'), sep=',')
cursor.execute("select * from bbbb;")
a = cursor.fetchall()
print(a)
cursor.close()

这篇关于将快速 pandas 数据框写入Postgres的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！