Python MySQL Connector not inserting

Question

So here is my code. I don't know why insert isn't working. A select statement works. It doesn't fail the try catch either leading me to believe the query is executing. Also entering the insert query manually into MySQL Workbench seems to work fine.

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)

A sample of what an insert query looks like

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

Captain Jack Sparrow · Accepted Answer · 2023-05-29 14:32:04Z

5

This is a great question! I spent hours trying to figure this out a few weeks ago. It's tricky because after executing the query, you have to call

conn.commit()

to actually update the data. So change your runQuery function like this:

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}")

See this doc page for more info.

edited May 29, 2023 at 14:32

answered Apr 10, 2020 at 0:22

Captain Jack Sparrow

1,1181 gold badge16 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Python MySQL Connector not inserting

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related