Python MySQL 连接器未插入

Question

这是我的代码。我不知道为什么插入不起作用。 select 语句有效。它不会使 try catch 失败，也不会导致我相信查询正在执行。另外，手动将插入查询输入 MySQL Workbench 似乎工作正常。

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');

Answer 1

这是一个很好的问题!几周前我花了几个小时试图解决这个问题。这很棘手，因为执行查询后，您必须调用

conn.commit()

实际更新数据。因此，请像这样更改您的 runQuery 函数:

def runQuery(query):
    try:
        conn = mysql.connector.connect(host='localhost',
                                       database='optionsdata',
                                       user='python',
                                       passwd='python')
        cursor = conn.cursor()
        cursor.execute(query)
        conn.commit()  # Added commit line
        conn.close()
        cursor.close()
        print(query)
    except Error as e:
        print(f"Error: {e}")

参见this doc page了解更多信息。