MySQLdb Python write/read methods

Question

I worked a little with MySQLDB in Python and thought I understood how it works. But now I want to define my own method to be more flexible. Read the code....

import MySQLdb as mydb

class DB(object):
    def read(self, slct, frm):
        connection = mydb.connect("123.12.34.56", "user", "pass", "foo")
        cursor = connection.cursor()
        cursor.execute("SELECT %s FROM %s", (slct, frm))
        print cursor.fetchall()

        connection.close()


if __name__ == '__main__':
    db = DB()
    db.read("*", "bar")

This throws me a SQL Syntax Error. Why can't I use it this way?

ProgrammingError: (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''bar'' at line 1")

yes I am sure. ``cursor.execute("SELECT * FROM bar")`` works... — uloco, Dec 05 '13 at 08:46

score 1 · Answer 1 · answered Dec 05 '13 at 08:50

I'm not exactly sure about mysqldb, but usually (atleast in pyodbc and sqlite) you cannot parametrize columns or the table name, only values. You can use Python's string formatting for the * and table name part, and use parameters for the rest. For example:

cursor.execute('SELECT {col} FROM {table} WHERE foo=%s'.format(col='*', table='bar'), ('my value',))

score 0 · Answer 2 · answered Dec 05 '13 at 09:02

0

There is a typo -

cursor.execute("SELECT %s FROM %s"%(slct, frm))

answered Dec 05 '13 at 09:02

Arovit

3,277
5
19
23

MySQLdb Python write/read methods

2 Answers2