0

i'm trying to insert data from my python script into my sql table. The script run with no error, however when i check in the table no data actually inserted. I have search around the internet and still couldn't figure out what is the problem. Any help would be very appreciated. Code below is what i've tried so far.

 ts = time.time()
    timestamp = datetime.datetime.fromtimestamp(ts).strftime('%Y-%m-%d %H:%M:%S')
    try:
        mydb = mysql.connector.connect(host="localhost", user="root", passwd="", database="dummy_monke")
        mycursor = mydb.cursor()  # to point at database table
        queries = "INSERT INTO monitoring(id,lp,time) values (%s, %s, %s)"
        mycursor.execute(queries,spliced, timestamp)
        mydb.commit()
        print(mycursor.rowcount, "record updated successfully")

    except:
        mydb.rollback()
        print("record fail to update")
mandebo
  • 21
  • 4
  • What is the content of `spliced`? – Tim Biegeleisen Aug 25 '22 at 05:08
  • hi, spliced variable contain a license plate characters ( string) – mandebo Aug 25 '22 at 05:11
  • 1
    Your `execute` parameters do not match your substitutions. You have three substitutions, so you need a tuple with 3 items to pass to execute. You would have seen this if you had not included your blanket `try/except`. You should NEVER include `try/except` until you have the code working. Where does the `id` come from? – Tim Roberts Aug 25 '22 at 05:11
  • @TimRoberts the id is an auto increment attribute in the mysql table, do i need to include that in the INSERT statement? . – mandebo Aug 25 '22 at 05:15
  • No, because you don't have a value for it. The answer below is correct. – Tim Roberts Aug 25 '22 at 05:24

1 Answers1

1

Based on this post, you can do

queries = "INSERT INTO monitoring(lp,time) values (%s, %s)"
mycursor.execute(queries, (spliced, timestamp))

to generate a tuple on the fly and have the auto-increment done for you.

Side note: it may make sense to rename queries to a singular query.

t_krill
  • 361
  • 1
  • 6