使用Python将CSV数据导入PostgreSQL [英] CSV data into postgreSQL using Python
问题描述
我正在尝试使用Python将CSV数据导入到postgreSQL中。我试图在Stack Overflow上搜索此问题,但找不到适合我的情况的结果。我的CSV文件中有空列,运行代码时,它会为没有信息的空白列抛出错误。我希望能够告诉Python忽略空白列,然后继续进行下一列。并非所有列中都有数据,因此,我希望您能提供帮助,以实现一个解决方案,我可以在脚本中针对其中没有数据的列进行编辑。我是编程的新手,请原谅我的愚蠢。
I am trying to import CSV data into postgreSQL using Python. I tried to search on Stack Overflow for this issue but couldn't find anything fruition for my situation. I have empty columns in my CSV file, when I run the code it throws out an error for the blank column for which there's no information. I want to be able to tell Python to ignore the blank column and continue to the next column. Not all of the columns have data in them so I want your kind assistance to implement a solution which I can have edited in the script for the columns which have no data in them. I am new to Programming so please pardon my stupidity.
import psycopg2
import csv
csv_data = csv.reader(file('SampleData1.csv'))
database = psycopg2.connect (database = "**", user="**", password="***", host="**", port="**")
cursor = database.cursor()
delete = """Drop table if exists "Real".SampleDataOne"""
print (delete)
mydata = cursor.execute(delete)
cursor.execute("""Create Table "Real".SampleDataOne
(Username varchar(55),
TimeStamp timestamp,
Week date,
Actual_Sale_Date date,
Estimated_Closing_Date date,
Last_Name_First_Name varchar(55),
Ages varchar(55)
);""")
print "Table created successfully"
next(csv_data)
for row in csv_data:
cursor.execute("""Insert into "Real".SampleDataOne(Username,TimeStamp, Week, Actual_Sale_Date, Estimated_Closing_Date, \
Last_Name_First_Name, Ages)"""\
"""VALUES (%s,%s,%s,%s,%s,%s,%s)""",
row)
cursor.close()
database.commit()
database.close()
print "CSV imported"
错误如下:'后的列旁有一个向上的点(^)。假'。
The error is as follows: It has a point upwards (^) next to the column after the 'False'.
Drop table if exists "Real".SampleDataOne
Table created successfully
Traceback (most recent call last):
File "C:/Users/Desktop/Programming/SampleData1Python.py", line 61, in <module>
row)
DataError: invalid input syntax for type date: ""
LINE 1: ...****@jcp.com','****@comcast.net','No','FALSE','','','')
推荐答案
http://initd.org/psycopg/docs /cursor.html#cursor.copy_from
f = open('SampleData1.csv')
cursor.copy_from(f, '"Real".sampledataone', sep=',', null='')
如果您具有简单的csv数据,但不使用引号或逗号,则应该可以工作。
Should work provided you have simple csv data without quoting or commas in the data.
这篇关于使用Python将CSV数据导入PostgreSQL的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!