update query throws exception in python

Question

My requirement is to change the name field to sun if id is 1.Below is my code.It throws exception and print error in update?.Is i am doing anything wrong on query or python code?

     import MySQLdb
     import datetime    
        a="sun"
        b=1
        sql="update selva set name=%s where selid=%d"
        try:
            cursor.execute(sql,[a,b])
            db.commit()
        except:
            print "error in update"
            db.rollback()

        db.close()

Any Help will be greatly appreciated!

Did you try reraising the exception so you can see what it is? — BrenBarn
– BrenBarn, Commented Sep 24, 2014 at 6:15
i am newbie to python,i found out the exception throws by using print in except block,even i don't know which type of exception throws in my code — Selva
– Selva, Commented Sep 24, 2014 at 6:17
Then don't catch it! Remove the try/except and let Python report the exception to you. — Daniel Roseman
– Daniel Roseman, Commented Sep 24, 2014 at 6:29

mhawke · Accepted Answer · 2014-09-24 06:34:48Z

3

The placeholders in the query should be %s, not %d. I have modified your exception handler to catch and print the exception, and to close the database connection in a finally clause to ensure that it will always be closed.

import MySQLdb
import datetime    
a="sun"
b=1
sql="update selva set name=%s where selid=%s"
try:
    cursor.execute(sql, [a, b])
    db.commit()
except Exception, exc:
    print "error in update"
    print "Exception : %s" % exc
    db.rollback()
finally:
    db.close()

answered Sep 24, 2014 at 6:34

mhawke

87.5k10 gold badges122 silver badges142 bronze badges

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

6 Comments

sundar nataraj Over a year ago

please use tuple rather list.

mhawke Over a year ago

@sundarnatarajСундар : tuple or list or any other sequence is fine.

sundar nataraj Over a year ago

i think for sql we use tuple since they are immutable . even query result are tuples.so it is good practice.please correct me if i am wrong:)

sundar nataraj Over a year ago

i am getting error near %s since there are no quotes.

mhawke Over a year ago

The data type of the query result has no bearing on the data type used in cursor.execute. Any sequence is permitted, although a tuple would seem to be the defacto standard.

|

Stephen Lin · Accepted Answer · 2014-09-24 06:40:27Z

0

Try this:

 import MySQLdb
 import datetime    
    a="sun"
    b=1
    sql="update selva set name = '%s' where selid = '%d'"
    cmd = sql % (a, b)
    try:
        cursor.execute(cmd)
        db.commit()
    except Exception, e:
        print "error in update"
        print repr(e)
        db.rollback()

    db.close()

Tell if there is error message printed out.

edited Sep 24, 2014 at 6:40

answered Sep 24, 2014 at 6:17

Stephen Lin

4,9021 gold badge16 silver badges26 bronze badges

6 Comments

Stephen Lin Over a year ago

@Madhesh What about adding some spaces beside equals. I've update my answer.

mhawke Over a year ago

You don't want to do it this way which forms the query via string interpolation. Instead you want to use parameterised queries as already present in the OP. The reason: string interpolation is vulnerable to SQL injection attacks whereas parameterised queries are not. Anyway, you need to put single quotes around the name.

Selva Over a year ago

error in update OperationalError(1054, "Unknown column 'sun' in 'field list'")

Stephen Lin Over a year ago

@Madhesh That means you don't have a column named sun, do you?

Selva Over a year ago

@mhawke,after putting single quotes in %s,my query works,thanks for help me.

|

xecgr · Accepted Answer · 2014-09-24 11:13:40Z

0

You can use execute invocation like this

cursor.execute ("update selva set name=%s where selid=%s", (a,b)) #passing a tuple with parameters

or

cursor.execute ("update selva set name='%s' where selid=%d" % (a,b)) #passing a raw sql command with interpoled data

edited Sep 24, 2014 at 11:13

answered Sep 24, 2014 at 6:22

xecgr

5,2033 gold badges22 silver badges28 bronze badges

Collectives™ on Stack Overflow

update query throws exception in python

3 Answers 3

6 Comments

6 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

6 Comments

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related