0

using python 3 trying to a mysql query in flask 

SELECT title FROM Book WHERE title LIKE '%booktitlehere%';

From the input of my search form this is attempted flask implementation

search_string = search.data['search']
cur = mysql.connection.cursor()
likeString = "'%" + search_string + "%'"
cur.execute('''SELECT title FROM Book WHERE title LIKE %s;''',likeString)

Its not working tho getting this error when go to the page that holds this form

_mysql_exceptions.ProgrammingError: not all arguments converted during string formatting

I have tried adding the %% to escape the literal %

likeString = "'%%" + search_string + "%%'"
# print(like)
cur.execute("""SELECT title FROM Book WHERE title LIKE %s;""",likeString)
rv = cur.fetchall()

but still getting error 

_mysql_exceptions.ProgrammingError: not all arguments converted during string formatting

also tried with double quotes around select

cur.execute("SELECT title FROM Book WHERE title LIKE %s;",likeString)
gtx
  • 113
  • 2
  • 10
  • You didn't ask a question. Are you just looking for confirmation? Or is there something not working how you expect it to? – Bill Karwin Apr 13 '18 at 19:36
  • oh sorry added the error – gtx Apr 13 '18 at 19:43
  • 1
    Remove extra quotes in `cur.execute('''SELECT title FROM Book WHERE title LIKE %s;''',likeString)` – DEarTh Apr 13 '18 at 19:56
  • 1
    Remove extra quotes in *likeString* as `"%" + search_string + "%"` – Parfait Apr 13 '18 at 20:02
  • they are not extra quotes, i've seen mysql use LIKE with LIKE 'argument' – gtx Apr 13 '18 at 20:19
  • They are extra quotes. Parameterized queries are *not* using string formatting under the hood (at least if the client library is any good at all). They're passing the query and the parameters separately to the DB. Then the DB parses the query *without* any specific parameters and inserts the parameter values into the already parsed version of the query. By doing so, the possibility of a malicious user crafting their input to invoke a command is removed. So adding in the quotes yourself is unnecessary, since the DB never needs them. – jpmc26 Apr 14 '18 at 01:50
  • 2
    Duplicate of [Python MySQLdb TypeError: not all arguments converted during string formatting](https://stackoverflow.com/questions/21740359/python-mysqldb-typeerror-not-all-arguments-converted-during-string-formatting). The problem is that you're not wrapping your argument in a list, so Python is trying to unpack the string as a list of characters, treating each one as a separate parameter when you only have one declared in your query. Pasting the error into Google turned up the duplicate target immediately. – jpmc26 Apr 14 '18 at 02:01

4 Answers4

1

example :

cari = "%" + request.form['cari'] +"%"

conn.execute("SELECT nama, harga FROM barang where nama LIKE %s OR harga LIKE %s", (cari, cari))
robsiemb
  • 6,157
  • 7
  • 32
  • 46
Si Techno
  • 11
  • 1
0

Double it:

likeString = "'%%" + search_string + "%%'"

cur.execute("""SELECT title FROM Book WHERE title LIKE %s;""", (likeString))
hestellezg
  • 3,309
  • 3
  • 33
  • 37
  • good job on finding the duplicate post, but just a suggestion: maybe don't post an answer if you raised the proper dup. – MooingRawr Apr 13 '18 at 20:03
  • ok did but still getting error . ``` raise errorclass(errorvalue) _mysql_exceptions.ProgrammingError: not all arguments converted during string formattin``` – gtx Apr 13 '18 at 20:15
0

this work for me, i use %% and there should be "" after like

cur.execute('select * from chiase where name like "%%%s%%"', [firstName])

tng
  • 1
-1

Before use searching string in sql command

search_string= f"%{search_string}%"

and then apply it in sql command

cur.execute("SELECT * FROM books WHERE title LIKE :searching_string ORDER BY id ",{"searching_string":search_string})

Additionally can do it lower
search_string= f"%{search_string}%".lower() 

cur.execute("SELECT * FROM books WHERE LOWER(title) LIKE :searching_string ORDER BY id ", {"searching_string":search_string})
Gokhan Cacan
  • 1
  • 2