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 maybe you should take a look at it! — dav1d
– dav1d, Commented Jun 20, 2012 at 10:31

Community · Accepted Answer · 2017-05-23 12:20:04Z

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, 2017 at 12:20

CommunityBot

11 silver badge

answered Jun 20, 2012 at 10:27

Emil Vikström

92.3k17 gold badges144 silver badges178 bronze badges

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

4 Comments

dav1d Over a year ago

Please also add an example of a prepared statement, beginners will take your example 1:1 which is a real risk!

Emil Vikström Over a year ago

dav1d, you are of course correct. I've added it and restructured my answer to use prepared statements first.

glglgl Over a year ago

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.

Emil Vikström Over a year ago

glglgl, that's interesting, I didn't know that. I thought prepared statements was the only way to parameterize a query.

Collectives™ on Stack Overflow

Like statement in DataBase with Python

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related