I try to load csv data into postgres. The creating table part is fine. But when I try to load data from csv, it got error. My code and error are attached below. Is %s wrong?
import psycopg2
import csv
conn = psycopg2.connect(host="127.0.0.1", port="5432", database="postgres", user="postgres", password="*******")
print "Opened database successfully"
cur = conn.cursor()
cur.execute('''create table calls_aapl("Ask" float,"Bid" float,"Change" float,"ContractSymbol" varchar(50),"ImpliedVolatility" float,"LastPrice" float,
"LastTradeDate" date,"OpenInterest" int,"PercentChange" float,"Strike" float,"Volume" int);''')
print "Table created successfully"
reader = csv.reader(open('D:/python/Anaconda/AAPL_Data/Calls.csv', 'r'))
for i, row in enumerate(reader):
print(i, row)
if i == 0: continue
cur.execute('''
INSERT INTO "calls_aapl"(
"Ask", "Bid", "Change", "ContractSymbol", "ImpliedVolatility", "LastPrice", "LastTradeDate", "OpenInterest", "PercentChange", "Strike", "Volume"
) values (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)''', row
)
conn.commit()
cur.close()
Error:
(0, ['Ask', 'Bid', 'Change', 'ContractSymbol', 'LastPrice', 'LastTradeDate', 'OpenInterest', 'PercentChange', 'PercentImpliedVolatility', 'Strike', 'Volume'])
(1, ['41.7', '39.75', '1.15', 'AAPL180803C00150000', '41.05', '7/31/2018', '52', '2.88', '154.59', '150', '6'])
DataError: invalid input syntax for type double precision: "7/31/2018"
LINE 4: ...1.7','39.75','1.15','AAPL180803C00150000','41.05','7/31/2018...
^
Using %s is ok because PostgreSQL can cast strings to numbers in an INSERT.
Your problem is a different one. Your INSERT statement specifies a column "ImpliedVolatility" (too late for a warning against mixed case identifiers) which is not in the data.
This causes the fifth column (labeled LastPrice to be inserted into "ImpliedVolatility" and the next column (labeled LastTradeDate) to be inserted into "LastPrice".
The former of these is wrong but works, because both "LastPrice" and "ImpliedVolatility" are float^H^H^H^H^Hdouble precision, but the latter fails because it tries to insert a date string into a double precision column.
Omit the column "ImpliedVolatility" from the INSERT statement.
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments