how to using MySQL pattern matching and binding parameters in sql query in python?

Question

I know how to use MySQL pattern matching, for example:

SELECT * FROM table WHERE col LIKE '%mid%'

I also know how to bind parameters into a sql query in python, for example:

import MySQLdb
s = 'something'
db = MySQLdb.connect(host=blablabla...)
cur = db.cursor()
sql = "SELECT * FROM table WHERE col = %s"
cur.execute(sql, s)
data = cur.fetchall()
db.close()

But I can't find a method to combine these together in one query, like

sql = "SELECT * FROM table WHERE col LIKE '%%s%'"
cur.execute(sql, s)

where the first and the third '%' are pattern character and the middle '%s' is used to bind parameter s. Anyone have an idea?

sql = "SELECT * FROM table WHERE col = %s" %some_words before call.. — dsgdfg
– dsgdfg, Commented Mar 11, 2016 at 7:13
@XiangZhang: But don't use that because it's vulnerable to SQL injection! — Eric
– Eric, Commented Mar 11, 2016 at 7:26

Xiang Zhang · Accepted Answer · 2016-03-11 07:26:20Z

2

Alright, I will answer myself.. @dsgdfg inspired me and here is my code:

sql = "SELECT * FROM table WHERE col LIKE %s"
cur.execute(sql, "%"+s+"%")

answered Mar 11, 2016 at 7:26

Xiang Zhang

2411 silver badge7 bronze badges

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

3 Comments

Eric Over a year ago

This is better than @dsgdfg's answer because you're leaving the interpolation to the database driver, which should protect you from injection

Xiang Zhang Over a year ago

Yes, but still looking for better solution. I think this method is not that beautiful

dsgdfg Over a year ago

Thnx for warning. deleted answer @Eric

Rick James · Accepted Answer · 2016-03-11 20:12:59Z

0

sql = "SELECT * FROM table WHERE col LIKE CONCAT('%', %s, '%')"
cur.execute(sql, s)

(I am assuming that execute deals with escaping, thereby preventing SQL injection.)

answered Mar 11, 2016 at 20:12

Rick James

144k15 gold badges144 silver badges254 bronze badges

Comments

Suraj Rao · Accepted Answer · 2020-11-30 14:14:05Z

0

for the people using py charm you can do this

qq=input("enter the author name:")
print(pd.read_sql_query("select Book_Id,Book_Name,book_author from bookd where book_author like '%s'" %("%"+qq+"%",), conn2))

edited Nov 30, 2020 at 14:14

Suraj Rao

29.7k11 gold badges96 silver badges104 bronze badges

answered Nov 30, 2020 at 14:04

sneha

1

Collectives™ on Stack Overflow

how to using MySQL pattern matching and binding parameters in sql query in python?

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related