Using prepared statements with mysql in python

Question

I am trying to use SQL with prepared statements in Python. Python does not have its own mechanism for this, so I'm trying 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 happening?

Answer 1

The use of prepared statements with MySQL in Python is explained, for example, at http://zetcode.com/db/mysqlpython/ - look at this page for Prepared statements.

In your case, it will be, for example:

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

and then, in a loop, as you put it:

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

- MySQLdb ( , .. ..).

, "", , , .execute_many ( ) ( ).

: mysql own Connector/Python prepare=True .cursor() factory - . http://dev.mysql.com/doc/connector-python/en/connector-python-api-mysqlcursorprepared.html. , ( " , PREPARE EXECUTE" , mysql.com), , ; " , " " " (import this , ). MySQLdb ( , ) , Connector/Python .

Answer 2

Python :

sql = "INSERT INTO {} (date, time, tag, power) VALUES (%s, %s, %s, %s);"
sql = sql.format(self.db_scan_table)
self.cursor.execute(sql, (d, t, tag, power))

( , self.db_scan_table SQL-)

, paramstyle - 'format', MySQL.

Answer 3

        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()

CHOOSE 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