使用psycopg2快速将pandas DataFrame插入Postgres数据库

Question

我正在尝试将一个pandas DataFrame以最高效的方式（使用Python 2.7）插入到Postgresql DB（9.1）中。
使用"cursor.execute_many"非常缓慢，"DataFrame.to_csv(buffer,...)"与"copy_from"一起使用也是如此。
我在网上找到了一个已经快得多的解决方案(http://eatthedots.blogspot.de/2008/08/faking-read-support-for-psycopgs.html)，并且我将其修改为适用于pandas。
我的代码如下所示。
我的问题是，这个相关问题的方法（使用"copy from stdin with binary"）是否可以轻松转换为适用于DataFrames，并且是否更快。
使用二进制COPY表从psycopg2
不幸的是，我的Python技能还不足以理解这种方法的实现。
这是我的方法：

import psycopg2
import connectDB # this is simply a module that returns a connection to the db
from datetime import datetime

class ReadFaker:
    """
    This could be extended to include the index column optionally. Right now the index
    is not inserted
    """
    def __init__(self, data):
        self.iter = data.itertuples()

    def readline(self, size=None):
        try:
            line = self.iter.next()[1:]  # element 0 is the index
            row = '\t'.join(x.encode('utf8') if isinstance(x, unicode) else str(x) for x in line) + '\n'
        # in my case all strings in line are unicode objects.
        except StopIteration:
            return ''
        else:
            return row

    read = readline

def insert(df, table, con=None, columns = None):

    time1 = datetime.now()
    close_con = False
    if not con:
        try:
            con = connectDB.getCon()   ###dbLoader returns a connection with my settings
            close_con = True
        except psycopg2.Error, e:
            print e.pgerror
            print e.pgcode
            return "failed"
    inserted_rows = df.shape[0]
    data = ReadFaker(df)

    try:
        curs = con.cursor()
        print 'inserting %s entries into %s ...' % (inserted_rows, table)
        if columns is not None:
            curs.copy_from(data, table, null='nan', columns=[col for col in columns])
        else:
            curs.copy_from(data, table, null='nan')
        con.commit()
        curs.close()
        if close_con:
            con.close()
    except psycopg2.Error, e:
        print e.pgerror
        print e.pgcode
        con.rollback()
        if close_con:
            con.close()
        return "failed"

    time2 = datetime.now()
    print time2 - time1
    return inserted_rows

- Arthur G

---

哪个答案提供了最佳性能更新？ - Cilvic

没有一个答案真正回答了我的问题。 - Arthur G

请查看此问题：https://dev59.com/Z57ha4cB1Zd3GeqPlZJJ。有一个相当完整的答案。 - Marcel Mars

Answer 1

Pandas 数据框现在有一个 .to_sql 方法。目前还不支持PostgreSQL，但是有一个补丁看起来可以使用。请参见 此处 和 此处 中的问题。

- foobarbecue

---

由于它仅使用一种标准方法，因此这并没有帮助，因为该方法明显较慢。 - Arthur G

.to_sql 也很慢。我正在使用 Python 2.7。 - Ram Dwivedi

Answer 2

我还没有测试过性能，但也许你可以使用类似以下的方法：

1. 遍历DataFrame的行，产生一个表示该行的字符串（见下文）
2. 将此可迭代对象转换为流，例如使用Python：如何将可迭代对象转换为流？
3. 最后在此流上使用psycopg的copy_from。

要高效地产生DataFrame的行，请使用类似以下的方法：

    def r(df):
            for idx, row in df.iterrows():
                    yield ','.join(map(str, row))

- lbolla

---

这也会流式传输CSV，这就是我上面的类正在做的事情。我的问题是，在创建流时，二进制表示是否会产生更快的结果。 - Arthur G