Using prepared statements with mysql in python

Question

I am trying to use SQL with prepared statements in Python. Python doesn't have its own mechanism for this so I try to use SQL directly:

sql = "PREPARE stmt FROM ' INSERT INTO {} (date, time, tag, power) VALUES (?, ?, ?, ?)'".format(self.db_scan_table)
self.cursor.execute(sql)

Then later, in the loop:

sql = "EXECUTE stmt USING \'{}\', \'{}\', {}, {};".format(d, t, tag, power)
self.cursor.execute(sql)

And in the loop I get:

MySQL Error [1064]: 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 ''2014-12-25', '12:31:46', 88000000, -6.64' at line 1

What's going on?

Answer 1

Using prepared statements with MySQL in Python is explained e.g at http://zetcode.com/db/mysqlpython/ -- look within that page for Prepared statements.

In your case, that would be, e.g:

sql = ('INSERT INTO {} (date, time, tag, power) VALUES '
       '(%s, %s, %s, %s)'.format(self.db_scan_table))

and later, "in the loop" as you put it:

self.cursor.execute(sql, (d, t, tag, power))

with no further string formatting -- the MySQLdb module does the prepare and execute parts on your behalf (and may cache things to avoid repeating work needlessly, etc, etc).

Do consider, depending on the nature of "the loop" you mention, that it's possible that a single call to .execute_many (with a sequence of tuples as the second argument) could take the place of the whole loop (unless you need more processing within that loop beyond just the insertion of data into the DB).

Added: a better alternative nowadays may be to use mysql's own Connector/Python and the explicit prepare=True option in the .cursor() factory -- see http://dev.mysql.com/doc/connector-python/en/connector-python-api-mysqlcursorprepared.html . This lets you have a specific cursor on which statements are prepared (with the "more efficient than using PREPARE and EXECUTE" binary protocol, according to that mysql.com page) and another one for statements that are better not prepared; "explicit is better than implicit" is after all one of the principles in "The Zen of Python" (import this from an interactive prompt to read all those principles). mysqldb doing things implicitly (and it seems the current open-source version doesn't use prepared statements) can't be as good an architecture as Connector/Python's more explicit one.

Answer 2

        import mysql.connector
        db_con=mysql.connector.connect(host='',
            database='',
            user='',
            password='')

        cursor = db_con.cursor(prepared=True,)
        #cursor = db_con.cursor(prepared=True)#IT MAY HAVE PROBLEM

        sql = """INSERT INTO table (xy,zy) VALUES (%s, %s)"""
        input=(1,2)
        cursor.execute(sql , input)
        db_con.commit()

SELECT STMT

        sql = """SELECT * FROM TABLE WHERE XY=%s ORDER BY id DESC LIMIT 1 """
        ID=1

        input=(ID,)
        #input=(ID)# IT MAY HAS PROBLEM
        cursor.execute(sql, input)
        data = cursor.fetchall()
        rowsNumber=cursor.rowcount