[ I saw you next email, but I answer to this one, because errors are here ]
Le Tue, 15 Apr 2008 11:03:03 -0700 (PDT), AJ <[EMAIL PROTECTED]> a écrit :
> kw = 'test'
>
> sql = """
> SELECT MAX(`table`.`id`)
> FROM `table`
> WHERE `table`.`name` LIKE %s
> GROUP BY `table`.`original_id`;"""
>
> cursor.execute(sql, [kw])
>
> and I get the error "Not all arguments converted during string
> formatting"
This _should_ work, since "execute" expect an iterable as its second argument
(I guess).
Maybe you can show us the traceback ?
Be aware that you cannot use variables interpolation in the declaration of the
SQL variable.
I mean, you cannot do this :
sql = "SELECT MAX(id) FROM %s WHERE name LIKE %s" % 'table'
since Python expects to find two values, and it raises the kind of error you
told us.
However, you can do something like this :
sql = "SELECT MAX(id) FROM %s WHERE name LIKE %%s" % 'table'
(notice the double "%").
[...]
> cursor.execute("SELECT MAX(`table`.`id`) FROM `table` WHERE
> `table`.`name` LIKE '%%%s%%' GROUP BY `table`.`original_id`;", [kw])
>
> And I get a sql error, it looks like there are quotes going on the
> inside of the %'s:
>
> 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 '%'test'% GROUP BY...
You already find the correct answer, but let's explain what's happening there.
You _must_ not quote your variables into your SQL query : this is the database
backend's job, and
by passing to "cursor.execute" an iterable as its second argument, you tell it
to handle
automatically the quotation of values.
In your previous example, you already put quotation marks around the "%%%s%%".
However, the backend
doesn't know about it, so it automatically add quotations marks around "%s".
So, the following instruction :
cursor.execute("SELECT MAX(id) FROM table WHERE name LIKE '%%%s%%'",
['myvalue'])
is translated into the following SQL query :
SELECT MAX(id) FROM table WHERE name LIKE '%%'myvalue'%%'
... and obviously, it doesn't work.
So, let do the quotation's job to the database backend.
And so, if you want to add wildcards character into your value, you must add it
to your value, not
in the SQL query (but you already proposed a solution in your last email ;) ).
I hope it clarifies some obscur points ...
- Jonathan
signature.asc
Description: PGP signature
