how to create a dynamic sql statement w/ python and mysqldb

Question

I have the following code:

    def sql_exec(self, sql_stmt, args = tuple()):
        """
        Executes an SQL statement and returns a cursor.

        An SQL exception might be raised on error

        @return: SQL cursor object
        """
        cursor = self.conn.cursor()
        if self.__debug_sql:
            try:
                print "sql_exec: " % (sql_stmt % args)
            except:
                print "sql_exec: " % sql_stmt

        cursor.execute(sql_stmt, args)
        return cursor

    def test(self, limit = 0):
        result = sql_exec("""
            SELECT
                *
            FROM
                table
            """ + ("LIMIT %s" if limit else ""), (limit, ))
        while True:
            row = result.fetchone()
            if not row:
                break
            print row
        result.close()

How can I nicely write test() so it works with or without 'limit' without having to write two queries?

Answer 1

First, don't.

Do not build SQL "on the fly". It's a security nightmare. It will cause more problems than it appears to solve.

Second, read the MySQL page on LIMIT. They suggest using a large number.

SELECT * FROM tbl LIMIT 18446744073709551615;

Switch your default from 0 to 18446744073709551615.

If you don't like that, then use an if statement and write two versions of the SELECT. It's better in the long run to have two similar SELECT statements with no security hole.

Third, don't test this way.

Use unittest. http://docs.python.org/library/unittest.html