An Example of MySQL update query using python 3.4

Question

How to write an MySQL update query using python 3.4 ? I'm using pymysql as a connector and I want to update a table in a database which is hosted on a localhost.

I have written one small piece of code but it is showing an error. probably because it is not compatible with python 3.4.

sql1 = ("""
          UPDATE field_data_comment_body SET  Sentiments=%s
            WHERE comment_body_value=%s """, (para3,res))
    cursor.execute(sql1)

while executing this it is showing an error

Traceback (most recent call last):
  File "F:\The Script\new.py", line 44, in <module>
    cursor.execute(sql1)
  File "C:\Python34\lib\site-packages\pymysql-0.6.6-py3.4.egg\pymysql\cursors.py", line 134, in execute
    result = self._query(query)
  File "C:\Python34\lib\site-packages\pymysql-0.6.6-py3.4.egg\pymysql\cursors.py", line 282, in _query
    conn.query(q)
  File "C:\Python34\lib\site-packages\pymysql-0.6.6-py3.4.egg\pymysql\connections.py", line 767, in query
    self._execute_command(COMMAND.COM_QUERY, sql)
  File "C:\Python34\lib\site-packages\pymysql-0.6.6-py3.4.egg\pymysql\connections.py", line 957, in _execute_command
    self._write_bytes(prelude + sql[:chunk_size-1])
TypeError: can't concat bytes to tuple

TypeError: can't concat bytes to tuple

so please help me find out the error !!

Stuart Robertson · Accepted Answer · 2015-03-30 15:32:16Z

3

Vaultah's answer is perfectly fine, but I personally prefer to separate the query string from the data. It's just seems clearer to what is going on to me.

sql1 = "UPDATE field_data_comment_body SET Sentiments=%s WHERE comment_body_value=%s"
data = (para3, res)
cursor.execute(sql1, data)

answered Mar 30, 2015 at 15:32

Stuart Robertson

7937 silver badges23 bronze badges

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

2 Comments

Pratik Dhole Over a year ago

Thank you it worked !! but only the problem is Values are not getting stored in the databases. I'll try searching it on web. but in case you find something wrong then plz help me....anyways thank you so much for your help :-)

Pratik Dhole Over a year ago

Got the Answer !! :-D i did not use db.commit() that was the only error for the value not getting stored !! :)

shybovycha · Accepted Answer · 2016-03-21 11:50:02Z

-1

Short answer

cursor.execute("""
    UPDATE field_data_comment_body 
    SET  Sentiments = ? 
    WHERE comment_body_value = ?
    """, (para3, res))

Explained version

First: you got a few errors in your code.

If you want a Python string for SQL, you may want to use either string.format(format_string, params) or string interpolation - format_string % (params) in case of many params or format_string % param in case of a single param.

I assume you've chosen the second option. And here is how it should look like:

sql1 = ("""UPDATE field_data_comment_body SET Sentiments=%s
        WHERE comment_body_value=%s""" % (para3,res))

Second: you got an error in your SQL query. If you try to match the string value in your WHERE condition - you must enclose that value by the quotes. Same thing with SET value.

Try this:

sql1 = """UPDATE field_data_comment_body SET Sentiments = '%s'
        WHERE comment_body_value = '%s'""" % (para3,res)

This should not cause neither Python, nor SQL errors.

But beware: if your parameters, `para3` and `res` may be somehow replaced by the end user, you may face SQL injections issue!

And, finally, third: do not use raw SQL queries - you may pass your parameters (para3 and res) to the cursor.execute() method to let him safely wrap your parameters - no more need to think where you need or not to use quotes.

So just try this one:

cursor.execute("""
    UPDATE field_data_comment_body 
    SET  Sentiments = ? 
    WHERE comment_body_value = ?
    """, (para3, res))

That's the best option, I believe.

edited Mar 21, 2016 at 11:50

answered Mar 30, 2015 at 15:18

shybovycha

12.4k6 gold badges54 silver badges85 bronze badges

5 Comments

vaultah Over a year ago

First: you're basically suggesting to allow SQL injections. Second: the quotes do not matter here.

shybovycha Over a year ago

@vaultah yeah, you're right. Changed that to use more prepare-statement-style =)

shybovycha Over a year ago

@AnttiHaapala i've already fixed that - just mentioned how string interpolation should be done

Antti Haapala Over a year ago

Please add strikethrough over the incorrect code (<s></s>) or sth, it is not obvious for fast reader; and reformat the last codeblock so that very important parts are visible without scrolling.

shybovycha Over a year ago

@AnttiHaapala code is not incorrect - it just may be unsafe. But i'll do my best =)

Collectives™ on Stack Overflow

An Example of MySQL update query using python 3.4

2 Answers 2

2 Comments

Short answer

Explained version

But beware: if your parameters, `para3` and `res` may be somehow replaced by the end user, you may face SQL injections issue!

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Short answer

Explained version

But beware: if your parameters, para3 and res may be somehow replaced by the end user, you may face SQL injections issue!

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related

But beware: if your parameters, `para3` and `res` may be somehow replaced by the end user, you may face SQL injections issue!