CODEWITHSUNDEEP
pythonmysqlregex
Xiang Zhang
Xiang Zhang

Reputation: 241

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

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?

Upvotes: 0

Views: 971

Answers (3)

sneha
sneha

Reputation: 1

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))

enter image description here

Upvotes: 0

Rick James
Rick James

Reputation: 142296

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.)

Upvotes: 0

Xiang Zhang
Xiang Zhang

Reputation: 241

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+"%")

Upvotes: 2

Related Questions