python - 如何通过csv文件将特定列复制到postgresql

Question

import psycopg2
import time


def read_database():
    conn = None
    try:
        conn = psycopg2.connect(database="capitadb", user="capita_user", password="capita_user",
                                host = "127.0.0.1", port = "5432")
        cur = conn.cursor()
        start_time = time.time()
        cur.execute("COPY stagging(Activity_ID,F_Qtr,Fiscal_Week_Num,Manager,MBadge) FROM '/home/vivek/Downloads/dell_data.csv'  DELIMITER',' CSV;;")

        print("--- %s seconds ---" % (time.time() - start_time))
        print("Operation done successfully")
        conn.commit()

    except Exception as e:
        print("Error: %s" % e)
    finally:
        conn.close()

if __name__ == '__main__':
    read_database()

这里我们在 csv 文件中有 15 列，但我们只想复制 4 列。我们如何在不提取任何文件中的数据的情况下实现这一目标？

Answer 1

您将需要使用COPY FROM STDIN 功能- http://initd.org/psycopg/docs/cursor.html#cursor.copy_from .您将能够为该函数提供类似文件的对象。您可以使用 itertools模块

from itertools import chain, islice
class some_magic_adaptor(object):
    def __init__(self, src):
        self.src = chain.from_iterable(src)
    def read(self, n):
        return "".join(islice(self.src, None, n))


def read_csv():
    for line in open(csv_filename):
        yield transform_line(line)

file_like_object_for_postgresql = some_magic_adaptor(read_csv())