python, % and mysql statements

Question

I am trying to execute a mysql query, which needs to contain % characters... While building the query, I run into a problem of python using % and trying to stick it as a variable:

statmt="select id from %s WHERE `email` LIKE %blah%" % (tbl)
self.cursor.execute(statmt)

This naturally barfs with:

statmt="select id from %s WHERE `email` LIKE %blah%" % (tbl)
TypeError: not enough arguments for format string

How should I fix this so Python stops reading this as a variable, and takes it in as part of the string?

Thanks!

Greg Hewgill · Accepted Answer · 2012-08-22 20:29:49Z

7

When needing a literal % inside a Python formatting expression, use %%:

statmt="select id from %s WHERE `email` LIKE '%%blah%%'" % (tbl)

See the documentation section 5.6.2. String Formatting Operations for more information.

edited Aug 22, 2012 at 20:29

answered Aug 22, 2012 at 20:22

Greg Hewgill

1.0m192 gold badges1.2k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Cmag Over a year ago

Now, what if blah needed to be a "... ie.... SELECT id from tbl WHERE email LIKE %"%

Greg Hewgill Over a year ago

To escape a literal " inside a Python double-quoted string, use \". Or, you can use triple-quoted strings, """where you can "quote" things easily""".

Cmag Over a year ago

If I do that...: statmt="select id from %s WHERE email LIKE %%\"%%" % (tbl), I get an error:_mysql_exceptions.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 \'%"%\' at line 1')

Greg Hewgill Over a year ago

Well MySQL probably still requires single quotes around the string, LIKE '%%\"%%' and not just LIKE %%\"%% (I've updated my answer to reflect correct MySQL usage).

highlycaffeinated · Accepted Answer · 2012-08-22 20:22:31Z

2

You don't need to use string interpolation. The execute method handles it for you, so you can do this instead:

statmt="select id from %s WHERE `email` LIKE %blah%"
self.cursor.execute(statmt, tbl)

answered Aug 22, 2012 at 20:22

highlycaffeinated

19.9k9 gold badges62 silver badges92 bronze badges

Comments

dm03514 · Accepted Answer · 2012-08-22 20:29:19Z

2

you should escape your percent sign with %%

You should probably user parameterized queries though with ? and , instead of string interpolation.

edited Aug 22, 2012 at 20:29

answered Aug 22, 2012 at 20:22

dm03514

56.2k18 gold badges117 silver badges147 bronze badges

Comments

ecatmur · Accepted Answer · 2012-08-22 20:22:35Z

1

You can use str.format:

statmt="select id from {tbl} WHERE `email` LIKE %blah%".format(tbl=tbl)

Make sure you're not creating a SQL injection vulnerability.

answered Aug 22, 2012 at 20:22

ecatmur

158k28 gold badges311 silver badges387 bronze badges

Collectives™ on Stack Overflow

python, % and mysql statements

4 Answers 4

4 Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

4 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related