这是我的代码。我不知道为什么insert不起作用。select语句有效。try-catch也没有失败,这让我相信查询正在执行。手动将insert查询输入MySQL工作台似乎也可以正常工作
def runQuery(query):
try:
conn = mysql.connector.connect(host='localhost',
database='optionsdata',
user='python',
passwd='python')
cursor = conn.cursor()
cursor.execute(query)
conn.close()
cursor.close()
print(query)
except Error as e:
print("Error", e)
def convertDate(date_str):
date_object = datetime.datetime.strptime(date_str, '%m/%d/%Y').date()
return date_object
ticker = "MSFT"
html = urlopen("https://api.nasdaq.com/api/quote/" + ticker + "/option-chain?assetclass=stocks&todate=2020-05-08&fromdate=2020-04-07&limit=0").read().decode('utf-8')
optionsData = json.loads(html)
rows = optionsData["data"]["optionChainList"]["rows"]
for row in rows:
call = row["call"]
expiryDate = convertDate(call["expiryDate"])
query = "INSERT INTO `optionsdata`.`call` (`ticker`, `symbol`, `last`, `change`, `bid`, `ask`, `volume`, `openinterest`, `strike`, `expiryDate`, `grabTime`) VALUES ('{0}', '{1}', '{2}', '{3}', '{4}', '{5}', '{6}', '{7}', '{8}', '{9}', '{10}');".format(ticker, call["symbol"], call["last"], call["change"], call["bid"], call["ask"], call["volume"], call["openinterest"], call["strike"], expiryDate, datetime.datetime.now())
runQuery(query)
插入查询的示例
INSERT INTO `optionsdata`.`call` (`ticker`, `symbol`, `last`, `change`, `bid`, `ask`, `volume`, `openinterest`, `strike`, `expiryDate`, `grabTime`) VALUES ('MSFT', '@MSFT 200508C00175000', '3.21', '-0.29', '2.80', '4.25', '54', '228', '175.00', '2020-05-08', '2020-04-09 19:39:22.554538');
我认为这是一个很好的问题!几周前,我花了几个小时试图弄明白这一点。这很棘手,因为在执行查询之后,您必须调用
实际更新数据。因此,如下更改
runQuery
函数:有关更多信息,请参见this doc page
相关问题 更多 >
编程相关推荐