MySQL UPDATE statement Python

Question

I'm having difficulty with an update statement for MySQL in Python I've created a class for executing queries, all my other queries work but an update I could use a fresh pair of eyes.

database_handler.sql_post_query_with_args_performed_successfully("UPDATE members SET email ='test@test.ie' WHERE id= '%s'",id)

The function sql_post_query_with_args_performed_successfully returns true when it performs the query. As it stands this code is returning true but not updating the field of the table code for that is here: def sql_post_query_with_args_performed_successfully(self, query, args): with UseDatabase(self.MYSQL_DETAILS) as cursor: try: cursor.execute(query, args) return True except mysql.connector.errors.IntegrityError: return False

this function makes use of "UseDatabase" another class whichs handles the connection and execution of the queries, here id the code for that:

import mysql.connector

class UseDatabase:
    def __init__(self, configuration:dict):
        self.config = configuration

    def __enter__(self) -> 'cursor':
        """Connect to database and create a DB cursor. 

        Return the database cursor to the context manager.
        """        
        self.conn = mysql.connector.connect(**self.config)
        self.cursor = self.conn.cursor()
        return self.cursor

    def __exit__(self, exc_type, exc_value, exc_traceback):
        self.cursor.close()
        self.conn.commit()
        self.conn.close()

`UPDATE members SET email ='test@test.ie' WHERE id= %s` Try to remove `'` — Lukasz Szozda, Nov 21 '15 at 15:22
when I remove the ' I get the following error: mysql.connector.errors.ProgrammingError: 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '%s' at line 1 — David_D, Nov 21 '15 at 15:27
@Drew This is the code for the function def sql_post_query_with_args_performed_successfully(self, query, args): with UseDatabase(self.MYSQL_DETAILS) as cursor: try: cursor.execute(query, args) return True except mysql.connector.errors.IntegrityError: return False — David_D, Nov 21 '15 at 15:30
UseDatabase does my commit for me. I know its not that because Ive done insertions with it without any issues — David_D, Nov 21 '15 at 15:34
@Drew thanks for helping out....as you can see from the edit to the post ive included the code for the UseDatabase class which handles the commit — David_D, Nov 21 '15 at 15:54
**Definitely something to do with the '%s' because i substituted that for '123' and it updated the table** — David_D, Nov 21 '15 at 16:10

score 0 · Answer 1 · answered Nov 26 '15 at 10:43

After more digging I realised my sql statement needed to be: "UPDATE members SET verified = 1 WHERE id = %s"

My function call required the id to be passed within a tuple like so: sql_post_query_with_args_performed_successfully("UPDATE members SET email ='test@test.ie' WHERE id= %s",(id,))

score -1 · Answer 2 · edited May 23 '17 at 10:27

-1

After much trial and error I found the answer here: Python mysql.connector.errors. %s passed to SQL query with quotes

the statement being: "UPDATE members SET email='test@test.ie' WHERE id='{0}'".format(id))

edited May 23 '17 at 10:27

Community

1
1

answered Nov 21 '15 at 16:31

David_D

33
1
7

This can not be a correct answer. This piece of code is subject to sql injections and happens to work only because id is an integer. As @lad2025 mentioned, you should use "WHERE id=%s" and it is the connectors responsibility to correctly quote the literal based on its type. See http://www.mysqltutorial.org/python-mysql-update/ and http://dev.mysql.com/doc/connector-python/en/connector-python-example-cursor-select.html for an example. – newtover Nov 21 '15 at 21:23
@newtover Apologies for the delay, Ive been working away on this after reading your comment and figured it out, my database query executer function was looking for a tuple with the id as you can see from my answer – David_D Nov 26 '15 at 10:40
I am glad, you managed to find a solution. It is better to edit the old incorrect answer than to add a new one. If the new answer is correct, people who downvoted the old answer, can take their minuses back. – newtover Nov 26 '15 at 15:12
Thanks Ill do that now so – David_D Nov 26 '15 at 20:44

MySQL UPDATE statement Python

2 Answers2