Prev: map is useless!
Next: Importing modules
From: James Mills on 6 Jun 2010 11:29
On Mon, Jun 7, 2010 at 1:07 AM, Victor Subervi <victorsubervi(a)gmail.com> wrote:
> Hi;
> I tried this:
>
>     cursor.execute('drop table tmp%s', tmpTable)
>
> and got this error:
>
> Traceback (most recent call last):
>   File "/var/www/html/angrynates.com/cart/cart.py", line 196, in ?
>     cart()
>   File "/var/www/html/angrynates.com/cart/cart.py", line 189, in cart
>     cursor.execute('drop table tmp%s', tmpTable)
>   File "/usr/lib64/python2.4/site-packages/MySQLdb/cursors.py", line 163, in
> execute
>     self.errorhandler(self, exc, value)
>   File "/usr/lib64/python2.4/site-packages/MySQLdb/connections.py", line 35,
> in defaulterrorhandler
>     raise errorclass, errorvalue
> 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 ''127541158007'' at line 1")
>
> But when I print that statement out (exchanging the comma for a %) and
> manually enter it:
>
> mysql> drop table tmp127541158007;
> Query OK, 0 rows affected (0.00 sec)
>
> I am able to successfully drop the table. Why?

If I remember correctly, you should use '?', eg:

cursor.execute("DROP TABLE ?", my_table)

cheers
James
From: MRAB on 6 Jun 2010 11:40
Victor Subervi wrote:
> Hi;
> I tried this:
>
> cursor.execute('drop table tmp%s', tmpTable)
>
> and got this error:
>
> Traceback (most recent call last):
> File "/var/www/html/angrynates.com/cart/cart.py
> <http://angrynates.com/cart/cart.py>", line 196, in ?
> cart()
> File "/var/www/html/angrynates.com/cart/cart.py
> <http://angrynates.com/cart/cart.py>", line 189, in cart
> cursor.execute('drop table tmp%s', tmpTable)
> File "/usr/lib64/python2.4/site-packages/MySQLdb/cursors.py", line
> 163, in execute
> self.errorhandler(self, exc, value)
> File "/usr/lib64/python2.4/site-packages/MySQLdb/connections.py", line
> 35, in defaulterrorhandler
> raise errorclass, errorvalue
> 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 ''127541158007'' at line 1")
>
> But when I print that statement out (exchanging the comma for a %) and
> manually enter it:
>
> mysql> drop table tmp127541158007;
> Query OK, 0 rows affected (0.00 sec)
>
> I am able to successfully drop the table. Why?
>
As has been explained already, SQL might not (and here it clearly does
not) let you use placeholders for table or column names, only for
values.

Build the SQL statement with placeholders for the values (any values
which aren't constants) and then execute the SQL statement, passing the
values so that .execute performs the substitution itself.
From: James Mills on 6 Jun 2010 11:53
On Mon, Jun 7, 2010 at 1:40 AM, MRAB <python(a)mrabarnett.plus.com> wrote:
> As has been explained already, SQL might not (and here it clearly does
> not) let you use placeholders for table or column names, only for
> values.

Yes I should have stated that '?' place-holders are used only for
"values" :) *sigh*

--James
From: Thomas Jollans on 6 Jun 2010 15:38
On 06/06/2010 05:07 PM, Victor Subervi wrote:
> Hi;
> I tried this:
>
> cursor.execute('drop table tmp%s', tmpTable)

It looks like you're trying to %s-insert *part* of the table name. I
doubt any DB interface allows that. cursor.execute('drop table %s',
table_name) might work, otherwise, you'll have to write the query
yourself, in full.

>
> and got this error:
>
> Traceback (most recent call last):
> File "/var/www/html/angrynates.com/cart/cart.py
> <http://angrynates.com/cart/cart.py>", line 196, in ?
> cart()
> File "/var/www/html/angrynates.com/cart/cart.py
> <http://angrynates.com/cart/cart.py>", line 189, in cart
> cursor.execute('drop table tmp%s', tmpTable)
> File "/usr/lib64/python2.4/site-packages/MySQLdb/cursors.py", line
> 163, in execute
> self.errorhandler(self, exc, value)
> File "/usr/lib64/python2.4/site-packages/MySQLdb/connections.py",
> line 35, in defaulterrorhandler
> raise errorclass, errorvalue
> 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 ''127541158007'' at line 1")
>
> But when I print that statement out (exchanging the comma for a %) and
> manually enter it:
>
> mysql> drop table tmp127541158007;
> Query OK, 0 rows affected (0.00 sec)
>
> I am able to successfully drop the table. Why?
> TIA,
> beno

From: Mark Lawrence on 7 Jun 2010 10:05
On 06/06/2010 21:20, Dennis Lee Bieber wrote:
> On Sun, 6 Jun 2010 11:07:25 -0400, Victor Subervi
> <victorsubervi(a)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.

 |  Next  |  Last
Pages: 1 2
Prev: map is useless!
Next: Importing modules