Like statement in DataBase with Python

Question

I'm still learning Python as great simple programming language, the problem came with DataBase , I'm using MySQL to build it then I want to Select some tuples from Python code as I did write the query as following:

Q = "INSERT INTO contacts (FirstName,LastName,phone,mobile,email,address) VALUES('%s','%s','%s','%s','%s','%s') "% \
            (FN,LN,phone,Mobile,email,address)

becuase of using variables and it's OK.
but If I want to use (LIKE'') statement in query I get into quotations troubles ! as following:

    Q = "SELECT LastName FROM contacts WHERE phone LIKE '_'%s'%'" %\
            (phone)

What can I do to solve this problem, any hints ?

Python has serval impressive ORMs one of these is [sqlalchemy](http://www.sqlalchemy.org/) maybe you should take a look at it! — dav1d, Jun 20 '12 at 10:31

score 5 · Accepted Answer · edited May 23 '17 at 12:20

5

Use prepared statements and avoid the troubles of string formatting:

pattern = "_" + phone + "%"
cursor.execute("SELECT LastName FROM contacts WHERE phone LIKE %s", (pattern,))

If you don't want to use prepared statements, or if you stumble upon this problem in other cases, switch to using str.format (but read about SQL injections before doing this):

Q = "SELECT LastName FROM contacts WHERE phone LIKE '_{0}%'".format(phone)

Or combine the two:

pattern = "_{0}%".format(phone)
cursor.execute("SELECT LastName FROM contacts WHERE phone LIKE %s", (pattern,))

edited May 23 '17 at 12:20

Community

1
1

answered Jun 20 '12 at 10:27

Emil Vikström

90,431
16
141
175

Please also add an example of a prepared statement, beginners will take your example 1:1 which is a real risk! – dav1d Jun 20 '12 at 10:31
dav1d, you are of course correct. I've added it and restructured my answer to use prepared statements first. – Emil Vikström Jun 20 '12 at 10:40
That is not a prepared statement what you use. These always use `?` notation. Using the `%s` notation, MySQLdb internally assembles the queries in a safe way (`mysql_real_string_escape()`), but doesn't use prep statements. – glglgl Jun 20 '12 at 10:44
glglgl, that's interesting, I didn't know that. I thought prepared statements was the only way to parameterize a query. – Emil Vikström Jun 20 '12 at 10:47

Like statement in DataBase with Python

1 Answers1