ProgrammingError:无法适应类型'set' [英] ProgrammingError: can't adapt type 'set'
问题描述
我正在使用Python将Excel数据导入到postgreSQL中,并遇到编程错误.我确实研究了这个问题,发现它与postgreSQL有关.有人可以提供帮助吗?
I am importing Excel data into postgreSQL using Python and running into an Programming Error. I did research the issue and found out it has something to do with postgreSQL. Can someone please provide assistance.
import psycopg2
import xlrd
book = xlrd.open_workbook("T:\data.xlsx")
sheet = book.sheet_by_name("HCSData")
database = psycopg2.connect (database = "", user="")
cursor = database.cursor()
delete = """Drop table if exists "Python".hcsdata"""
print (delete)
mydata = cursor.execute(delete)
cursor.execute('''CREATE TABLE "Python".hcsdata
(DCAD_Prop_ID varchar(55),
Address VARCHAR(50),
Addition VARCHAR(100),
Block text,
Lot integer,
Permit_Num varchar(55),
Permit_Date date,
Foundation_Date date,
Frame_Date date,
Final_Date date,
HCS varchar(55),
Project_ID integer
);''')
print "Table created successfully"
query = """INSERT INTO "Python".hcsdata (DCAD_Prop_ID,Address,Addition, Block, Lot,
Permit_Num,Permit_Date, Foundation_Date, Frame_Date, Final_Date, HCS,Project_ID)
VALUES (%s, %s, %s, %s, %s, %s, %s,%s, %s, %s, %s, %s)"""
for r in range(1, sheet.nrows):
DCAD_Prop_ID = sheet.cell(r,0).value
Address = sheet.cell(r,1).value
Addition = sheet.cell(r,2).value
Block = sheet.cell(r,3).value
Lot = sheet.cell(r,4).value
Permit_Num = sheet.cell(r,5).value
Permit_Date = None if not sheet.cell(r,6).value else xlrd.xldate.xldate_as_datetime(sheet.cell(r,6).value,book.datemode)
Foundation_Date = None if not sheet.cell(r,7).value else xlrd.xldate.xldate_as_datetime(sheet.cell(r,7).value,book.datemode)
Frame_Date = None if not sheet.cell(r,8).value else xlrd.xldate.xldate_as_datetime(sheet.cell(r,8).value,book.datemode)
Final_Date = None if not sheet.cell(r,9).value else xlrd.xldate.xldate_as_datetime(sheet.cell(r,9).value,book.datemode)
HCS = sheet.cell(r,10).value
Project_ID =sheet.cell(r,11).value
values = (DCAD_Prop_ID,set(Address),Addition, Block, Lot,
Permit_Num,Permit_Date, Foundation_Date, Frame_Date, Final_Date, HCS,Project_ID)
cursor.execute(query, values)
cursor.close()
database.commit()
database.close()
print ""
print "All Done! Bye, for now."
print ""
columns = str(sheet.ncols)
rows = str(sheet.nrows)
print "Done"
Python显示的错误是:
And the error Python shows is:
Drop table if exists "Python".hcsdata
Table created successfully
Traceback (most recent call last):
File "C:\Users\Programming\PythonSQLNew\HCSData.py", line 53, in <module>
cursor.execute(query, values)
ProgrammingError: can't adapt type 'set'
推荐答案
您的查询似乎期望所有字符串:
your query seem to expect all strings:
query = """INSERT INTO "Python".hcsdata (DCAD_Prop_ID,Address,Addition, Block, Lot,
Permit_Num,Permit_Date, Foundation_Date, Frame_Date, Final_Date, HCS,Project_ID)
VALUES (%s, %s, %s, %s, %s, %s, %s,%s, %s, %s, %s, %s)"""
在您的值中,几乎只有字符串,但外星人脱颖而出:
In your values there are almost only strings but an alien stands out:
values = (DCAD_Prop_ID,set(Address),Addition, Block, Lot,
Permit_Num,Permit_Date, Foundation_Date, Frame_Date, Final_Date, HCS,Project_ID)
我承认消息是神秘的,但是逻辑告诉我们要做的只是:
I admit the message is cryptic, but the logic tells us to do just:
values = (DCAD_Prop_ID,Address,Addition, Block, Lot,
Permit_Num,Permit_Date, Foundation_Date, Frame_Date, Final_Date, HCS,Project_ID)
(由于无需将 Address
放入 set
中,因此它看起来像是与其他字段一样的字符串)
(since there's no need to put Address
in a set
, it seems to be a string like the other fields)
这篇关于ProgrammingError:无法适应类型'set'的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!