Fail to insert data into mysql table using python script

Question

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

hi, spliced variable contain a license plate characters ( string) — mandebo
– mandebo, Commented Aug 25, 2022 at 5:11
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
– Tim Roberts, Commented Aug 25, 2022 at 5:11
@TimRoberts the id is an auto increment attribute in the mysql table, do i need to include that in the INSERT statement? . — mandebo
– mandebo, Commented Aug 25, 2022 at 5:15
No, because you don't have a value for it. The answer below is correct. — Tim Roberts
– Tim Roberts, Commented Aug 25, 2022 at 5:24

t_krill · Accepted Answer · 2022-08-25 05:22:31Z

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.

answered Aug 25, 2022 at 5:22

t_krill

4001 silver badge7 bronze badges

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

1 Comment

t_krill Over a year ago

@ERENYEETGAR of course!

Collectives™ on Stack Overflow

Fail to insert data into mysql table using python script

1 Answer 1

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Linked

Related