CODEWITHSUNDEEP
pythonmysqlmysql-python
TLSK
TLSK

Reputation: 273

Mysqldb Update error with set %s

I have created a database with MySQLdb.
In database I have a table with name student with columns: 

id(is int),
id_user(is int),
f_name(is str),
l_name(is str)

I want to update a row.
My code is below: 

db=mdb.connect(host="localhost", use_unicode="True", charset="utf8", 
               user="", passwd="", db="test")                          
# prepare a cursor object using cursor() method
cursor = db.cursor()

sql="""SELECT id_user FROM student"""

try:
   # Execute the SQL command
   cursor.execute(sql)
   # Commit your changes in the database
   db.commit()
except:
   # Rollback in case there is any error
   db.rollback()

rows = cursor.fetchall()

the=int(7)
se=str('ok')
for row in rows:
    r=int(row[0])
    if r==the:          
        sql2 = """UPDATE student
                SET f_name=%s
                WHERE id_user = %s"""% (se,the)

               # Execute the SQL command
        cursor.execute(sql2)
        # Commit your changes in the database
        db.commit()

        db.rollback()
# disconnect from server
db.close()

When I run it I take the error there is column with name ok why?
Can anyone help me find what I am doing wrong please?

Upvotes: 0

Views: 1822

Answers (3)

user945967
user945967

Reputation:

You should always enclose your data with quotes.

Instead of %s solely use '%s' the only types you dont need it are numeric ones, but even there i would enclose %d with '%d' cos it is more save.

And you should use at least db.escape_string(your_data) before inserting or updating same values into your database.

Or have a look at the pdo-using style of mysqldb:

http://mysql-python.sourceforge.net/MySQLdb.html#some-examples

c=db.cursor()
max_price=5
c.execute("""SELECT spam, eggs, sausage FROM breakfast
      WHERE price < %s""", (max_price,))

Upvotes: 0

Luk&#225;š Lalinsk&#253;
Luk&#225;š Lalinsk&#253;

Reputation: 41316

You should run the query like this:

sql2 = """UPDATE student
          SET f_name = %s
          WHERE id_user = %s"""
cursor.execute(sql2, (se, the))

Don't use string interpolation, let the database driver handle passing the parameters for you. Otherwise you have to deal with syntax errors like this, or worse, SQL injection.

More details here.

Upvotes: 2

ruakh
ruakh

Reputation: 183504

str doesn't wrap its argument in quotation marks, so your statement is this:

UPDATE student SET f_name=ok WHERE id_user = 7

when it needs to be this:

UPDATE student SET f_name='ok' WHERE id_user = 7

So, either change this line:

                SET f_name=%s

to this:

                SET f_name='%s'

or else change this line:

se=str('ok')

to this:

se="'" + str('ok') + "'"

Though I recommend reading about SQL injection, which will become a concern as soon as you start using user-supplied data instead of hard-coded values.

Upvotes: 3

Related Questions