Drop Table w/ MySQLdb?

[Mark Lawrence]

On 06/06/2010 21:20, Dennis Lee Bieber wrote:
> On Sun, 6 Jun 2010 11:07:25 -0400, Victor Subervi
> <victorsubervi at gmail.com>  declaimed the following in
> gmane.comp.python.general:
>
>> Hi;
>> I tried this:
>>
>>      cursor.execute('drop table tmp%s', tmpTable)
>>
> 	ONCE AGAIN...
>
> 	Database SCHEMA entities must be formatted using the Python %
> operator. AND they should never be obtained as input from a user.
>
> 	DATA ITEMS obtained from anywhere need to use parameterized query
> passing so that the DB-API can safely escape them, and in the case of
> MySQLdb, put quote marks around them.
>
> 	SCHEMA entities are: DATABASE name, TABLE name, COLUMN name (and if
> you have them, TRIGGER, PROCEDURE, and VIEW names).
>
> 	The statement you have is SCHEMA manipulation.
>
> 	Print this out and tape it to your monitor. The next time you get
> one of these "inexplicable" error messages, review the above statements
> and compare to your query. I'm sure the answer will be obvious.
>
>
> 	Do you have edit access to the MySQLdb files? If so, open
> cursors.py, find the execute method, and put in a print statement. This
> does also assume you are running locally (not via a web client) so that
> the output can be seen on screen.
>
> (Mine is located in E:\Python25\Lib\site-packages\MySQLdb\cursors.py)
>
> Look for:
>
> -=-=-=-=-
>      def execute(self, query, args=None):
>
>          """Execute a query.
>
>          query -- string, query to execute on server
>          args -- optional sequence or mapping, parameters to use with
> query.
>
>          Note: If args is a sequence, then %s must be used as the
>          parameter placeholder in the query. If a mapping is used,
>          %(key)s must be used as the placeholder.
>
>          Returns long integer rows affected, if any
>
>          """
>          from types import ListType, TupleType
>          from sys import exc_info
>          del self.messages[:]
>          db = self._get_db()
>          charset = db.character_set_name()
>          if isinstance(query, unicode):
>              query = query.encode(charset)
>          if args is not None:
>              query = query % db.literal(args)
>          try:
>              r = self._query(query)
>          except TypeError, m:
>              if m.args[0] in ("not enough arguments for format string",
>   -=-=-=-=-
> and change
>
>          if args is not None:
>              query = query % db.literal(args)
>
> into
>          if args is not None:
>              query = query % db.literal(args)
>              print query
>
> 	Notice how your use of % for testing does NOT generate the same code
> -- MySQLdb invokes db.literal() to escape the arguments, but you are
> doing just
> 	query = query % (args)
>
>
>>      cursor.execute('drop table tmp%s', tmpTable)
>
> produces
> 	drop table tmp'xyz'
> NOT
> 	drop table tmpxyz
>

Well put Sir.  Can I suggest that the OPs continual requests for 
assistance are simply not cricket? :)  Yeah, I'm a Brit, and yeah, I'm 
fed up with getting thrashed by the Aussies.

Kindest regards.

Mark Lawrence.