今回はCSVデータをpostgreSQLに登録する内容を紹介します。
サンプルコード
#!/usr/bin/python
# -*- coding: utf-8 -*-
import codecs
import csv
import psycopg2
#DB接続
db_info = “host={0} port={1} user={2} password={3} dbname={4}”.format(“”,””,””,””,””)
connection = psycopg2.connect(db_info)
#トランザクション機能OFF
connection.autocommit = True
cur = connection.cursor()
#SQL準備
sql = “insert into table(aa,bb) values(‘{0[1]}’,'{0[3]}’);”
insertSQL = []
with codecs.open(“test.csv”, “r”, “utf-8”, “ignore”) as f:
reader = csv.reader(f, delimiter=’\t’, quotechar=”‘”, skipinitialspace=True, lineterminator=”\n”, escapechar=””)
for index, lines in enumerate(reader, 1):
#SQL実行
try:
cur.execute(sql.format(lines))
except Exception as e:
print(str(index) + “行目:” + e.args[0])
connection.close()
