13

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?

Improve this question
10
  • You are just formatting the query yourself, and doing it wrong. Mysql complains about two adjacent single quotes. Commented Dec 25, 2014 at 18:45
  • @newtover - Yes, I am formatting the query myself. Which part am I doing wrong? Commented Dec 25, 2014 at 18:46
  • The method execute expects a formatting string and parameters. Commented Dec 25, 2014 at 18:48
  • An interesting question is, why you want to use prepared statements in the first place. Commented Dec 25, 2014 at 18:54
  • @newtover - I am under the impression that using prepared statements gives you a more efficient binary connection to the mysql server, in contrast to a regular 'insert' done over and over again in a loop. Commented Dec 25, 2014 at 18:55

3 Answers 3

Reset to default
13

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.

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

6 Comments

The loop is needed because the data is continually generated from an external source.
@horsehair, depending on circumstances it may still be possible to chunk up the data coming from the external source and do an executemany on a chunk at a time. However this optimization is minor unless your latency to the DB is high, so execute is probably going to be OK anyway.
If every insert is stand-alone (i.e, you don't need atomic behavior whereby either all or none of your inserts occur), keeping transactions short is preferable. Unless multi-statement atomic transactions are needed somewhere, connecting with autocommit=True from the start may in fact be better (then, of course, you don't bother calling commit at all:-).
The linked zetcode tutorial does not show an example of Prepared Statements. The tutorial shows an example of a Parameterized Query but calls it a Prepared Statement.
It's prepared=True.
|
4 
        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
Improve this answer

Comments

1

Python does support prepared statements:

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

(You should ensure self.db_scan_table is not vulnerable to SQL injection)

This assumes your paramstyle is 'format', which it should be for MySQL.

Improve this answer

5 Comments

This is not a prapared statement.
Yes it is -- again, as per my answer, see zetcode.com/db/mysqlpython at Prepared statements. The prepared-statement object is handled internally by MySQLdb (in a cache keyed by the textual SQL thus prepared).
@AlexMartelli, I agree, that mysql-python does most of the things for which prepared statements are usually used. But they are still not the same.
Right -- you might use Connector/Python if you need to make sure prepared statements are being used "behind the curtains", see dev.mysql.com/doc/connector-python/en/… ; with good old mysqldb, you can't be quite sure what's happening behind said curtains.
Looking at mysqldb's source code 1.2.4b4 reveals, that it doesn't use prepared statements, just string formatting.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.