The cursor of pymysql fail to exercute SQL language

41
October 25, 2019, at 3:10 PM

I would like to use pymysql to update the data in mysql database. But I don't know what's going wrong with my code. IDEļ¼š pycharm

name1=input("enter the new one")
stu_ID=int(input("enter student ID"))
sql=f."UPDATE student SET name={name1} WHERE ID={stu_ID}"
cursor. execute(sql)
Answer 1

Since in your post is missing the error, i'm going to suppose that code is working fine but data are not updated at the end of the execution. If this assumption is not true, please add the error.

In your code is not present the declaration of database connection and from my point of viewm the problem could be there. In fact, for the update statement you should add parameter autocommit=True.

Check the following example:

database_connection=pymysql.connect(host=my_host, 
                                   user=my_user, 
                                   password=my_psw, 
                                   db=my_db_name, 
                                   charset=my_db_charset, 
                                   cursorclass=my_cursor_type, 
                                   autocommit=True)

This because pymysql starts an implicit transaction in case of update.
So in your case (without autocommit), at the end of the query execution, data are rollbacked.

Another different option, if you don't want add the autocommit in the database connection declaration is add an esplicit commit after the query like this:

database_connection.commit()
READ ALSO
escape ticks in golang sql statement [duplicate]

escape ticks in golang sql statement [duplicate]

This question already has an answer here:

24
Error when foreign referencing in mySQL (Error 3780)

Error when foreign referencing in mySQL (Error 3780)

I am making two tables and I want the personOne column from table b to reference the person column on table a, but for some reason it doesn't work

33
why is mysql refusing local connections

why is mysql refusing local connections

I'm trying to use the (slightly older version of) MySQL C++ connector (see here) to open up a connection to a database and write stuff to itMy code works on my local Ubuntu 18

36
SQL - Check if a column auto increments

SQL - Check if a column auto increments

I am trying to run a query to check if a column auto incrementsI can check type, default value, if it's nullable or not, etc

30