Record not INSERTing with MySQL connector in Python

Question

I have searched for this, and no matter what I try, I cannot get records to insert into a MySQL table in Python. I have done a commit, but still nothing is committed. Here is the code that I have now. Thanks for any insight you can offer.

commentauthor = comment.author
theauthor = commentauthor.name

try:
    mydb = mysql.connector.connect(
        host="localhost",
        user="user",
        passwd="pass",
        database="mydb"
)

    readcursor = mydb.cursor(buffered=True)
    SQL = "SELECT * FROM atable WHERE username = '%s'" % (theauthor)
    readcursor.execute(SQL)
    if readcursor.rowcount == 0:
        repcount = 1
    else:
        myresult = readcursor.fetchone()
        for row in myresult:
            repcount = row[1] + 1

    writecursor = mydb.cursor()
    if repcount == 1:
        SQL = "INSERT INTO atable (username, usercount) VALUES ('%s', %d)" % (theauthor, repcount)
    else:
        SQL = "UPDATE atable SET usercount = %d WHERE username = '%s'" % (repcount, theauthor)

    writecursor.execute(SQL)
    mydb.commit

    print(SQL)

    SQL = "SELECT * FROM atable WHERE username = '%s'" % (theauthor)
    readcursor.execute(SQL)

    print(SQL)

    acount = readcursor.fetchone()[1]
    print(acount)

    readcursor.close
    writecursor.close
    mydb.close
except:
    print(SQL)
    print("Error: unable to fetch data")

Immediately after the INSERT, the next SELECT that is done shows a '1' for the value (like it did insert the data), but no data is actually in the table. Using a MySQL admin GUI, the table is empty, and running the same code above a second time returns no records.

You missed to call commit. Add () to it. The same applies to other calls in your code. — Klaus D.
– Klaus D., Commented Sep 9, 2019 at 2:30
Doh! Thank you! I'm new to Python, and I missed that point. It works now. Appreciate your time! — M.C.
– M.C., Commented Sep 9, 2019 at 2:33

M.C. · Accepted Answer · 2019-09-09 02:35:30Z

1

Thanks to Klaus, it was due to a coding error. Works now that I did a proper call.

answered Sep 9, 2019 at 2:35

M.C.

136 bronze badges

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

Collectives™ on Stack Overflow

Record not INSERTing with MySQL connector in Python

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