"ValueError: Unsupported format character ' " ' (0x22) at..." in Python / String
First, your problem has nothing to do with SQL. Throw away all the SQL-related code and do this:
var = 'foo'
" OR tble_tble.var LIKE %'%s'%" % var
You'll get the same error. It's because you're trying to do %
-formatting with a string that has stray %
signs in it. So, it's trying to figure out what to do with %'
, and failing.
You can escape these stray %
signs like this:
" OR tble_tble.var LIKE %%'%s'%%" % var
However, that probably isn't what you want to do.
First, consider using {}
-formatting instead of %
-formatting, especially when you're trying to build formatted strings with %
characters all over them. It avoids the need for escaping them. So:
" OR tble_tble.var LIKE %'{}'%".format(var)
But, more importantly, you shouldn't be doing this formatting at all. Don't format the values into a SQL string, just pass them as SQL parameters. If you're using sqlite3, use ?
parameters markers; for MySQL, %s
; for a different database, read its docs. So:
" OR tble_tble.var LIKE %'?'%"
There's nothing that can go wrong here, and nothing that needs to be escaped. When you call execute
with the query string, pass [var]
as the args.
This is a lot simpler, and often faster, and neatly avoids a lot of silly bugs dealing with edge cases, and, most important of all, it protects against SQL injection attacks.
The sqlite3 docs explain this in more detail:
Usually your SQL operations will need to use values from Python variables. You shouldn’t assemble your query using Python’s string operations… Instead, use the DB-API’s parameter substitution. Put
?
as a placeholder wherever you want to use a value, and then provide a tuple of values as the second argument to the cursor’sexecute()
method. (Other database modules may use a different placeholder, such as %s or :1.) …
Finally, as others have pointed out in comments, with LIKE
conditions, you have to put the percent signs inside the quotes, not outside. So, no matter which way you solve this, you're going to have another problem to solve. But that one should be a lot easier. (And if not, you can always come back and ask another question.)
Related videos on Youtube
Comments
-
jdero over 1 year
I've seen a couple similar threads, but attempting to escape characters isn't working for me.
In short, I have a list of strings, which I am iterating through, such that I am aiming to build a query that incorporates however many strings are in the list, into a 'Select, Like' query.
Here is my code (Python)
def myfunc(self, cursor, var_list): query = "Select var FROM tble_tble WHERE" substring = [] length = len(var_list) iter = length for var in var_list: if (iter != length): substring.append(" OR tble_tble.var LIKE %'%s'%" % var) else: substring.append(" tble_tble.var LIKE %'%s'%" % var) iter = iter - 1 for str in substring: query = query + str ...
That should be enough. If it wasn't obvious from my previously stated claims, I am trying to build a query which runs the SQL 'LIKE' comparison across a list of relevant strings.
Thanks for your time, and feel free to ask any questions for clarification.
-
Barmar over 10 yearsI don't know Python well, but I'll bet
%%
in a format string expands to a%
. -
abarnert over 10 years@Barmar: Which is exactly what (I assume) the OP wants.
-
jdero over 10 years@abarnert Thank you. My knowledge of format strings is very ... amateur.
-
jdero over 10 years@abarnert What do you mean by passing them as SQL parameters? You seem to know your stuff.
-
abarnert over 10 years@jdero: This is explained near the very top of the
sqlite3
docs (and the DB-API 2.0 PEP, and similar places). See the two paragraphs and example starting with "Usually your SQL operations…" -
abarnert over 10 years@jdero: Also see this XKCD strip for an explanation of why you might want to use SQL parameter binding (or input sanitization, but parameter binding is a whole lot simpler and safer).
-
abarnert over 10 yearsIn standard SQL, and with most actual databases, the LIKE condition's pattern expression has to be a string value in quotes (or a bound parameter, of course).
-
jdero over 10 yearsI actually remember that from my dbm class :D just never put it into practice until this awful moment! Thanks for your help. Great links, and solid extension in your post.
-
zs2020 over 10 years@abarnert Thank you. Updated. I am a bit slow today and was not processing somehow...
-
jdero over 10 years@abarnert I know comments are not chat threads, but I just found it utterly hilarious, the intern next to me was like "Hey, I think I have an xkcd on my back, what does it say?" And it was that exact xkcd article. Coincidence? I think not :)
-
abarnert over 10 years@jdero: Sometimes you can learn more from interns' t-shirts than you can from StackOverflow or documentation. :)