1
import pypyodbc as pyodbc
model_name = 'test'
model_name1 = Master_Cursor.execute("select col1,col2,col3 from tablename where col3 like '%s' order by col3" %(model_name)).fetchall()

above code returns one record matching model_name = test. How would I get back other records having model_name=123test123,abctestabc,ABCtestABC etc??

Basically, looking for

select col1,col2,col3 from tablename where col3 like '%test%'.
SCB
  • 4,721
  • 1
  • 29
  • 37
Sel_Python
  • 151
  • 1
  • 6
  • 15
  • 5
    The first thing is to _not_ use string formatting to create queries since that leaves you open to SQL injection. – roganjosh Feb 13 '18 at 23:00
  • Also if you *must* do string formatting, there are many more up-to-date options than `printf` style! The simple answer to your question is https://stackoverflow.com/q/10678229/3001761, but you really ought to use proper variable handling, [as documented](https://github.com/mkleehammer/pyodbc/wiki/Cursor). – jonrsharpe Feb 13 '18 at 23:01
  • At a guess: `model_name1 = Master_Cursor.execute("SELECT col1,col2,col3 FROM tablename WHERE col3 LIKE ? ORDER BY col3",(model_name,)).fetchall()` – roganjosh Feb 13 '18 at 23:03
  • @jonrsharpe I'm getting all sorts of different results back from searches for the correct parameterized query in pyodbc. Does the syntax depend on the underlying database? Some seem to suggest you can just use `LIKE`, and with `?` as placeholder. – roganjosh Feb 13 '18 at 23:06
  • 1
    @roganjosh yes, I'd expect to see `... LIKE ?`, then the variable would also have `%` in it for the wildcards. For example: https://stackoverflow.com/a/24377191/3001761. – jonrsharpe Feb 13 '18 at 23:07
  • @jonrsharpe makes sense. I also found some where they were using `%s` as placeholder and that was the root of the issue. My curiosity lies in if I tried to use `pyodbc` to connect to a MySQL db (which uses `%s` instead of `?`) whether the `pyodbc` query needed to use that instead. In other words, whether `?` was universal in `pyodbc` regardless of the database tech. I'll have a search :) – roganjosh Feb 13 '18 at 23:13
  • 2
    @roganjosh the Python DB API allows various parameter styles: https://www.python.org/dev/peps/pep-0249/#paramstyle. I don't know whether it would vary by underlying DB or not. – jonrsharpe Feb 13 '18 at 23:14

3 Answers3

4

Do not use string formatting to insert variables into your queries, it opens you up to the risk of SQL injection (see e.g. What is SQL injection?) Instead, and per the documentation, you should use ? to represent variables in the query, and let the library escape and insert them appropriately.

Next, you need to actually include wild cards along with model_name if you want looser matching. At the moment you're creating:

select col1,col2,col3 from tablename where col3 like 'test' order by col3

You need to surround model_name with the wildcards before passing it into the query, e.g. using '%%%s%%' % model_name (note that you need to repeat each % to escape it in printf-style formatting) or one of the more modern string formatting options.

In this case, for example (using str.format):

model_name1 = Master_Cursor.execute(
    "select col1, col2, col3 from tablename where col3 like ? order by col3",
    ("%{}%".format(model_name),)
).fetchall()
jonrsharpe
  • 99,167
  • 19
  • 183
  • 334
  • 2
    I think you need `...., ("%{}%".format(model_name),))` to create the tuple so it doesn't try to unpack the characters of the string. – roganjosh Feb 13 '18 at 23:37
  • 1
    @roganjosh I would hope that's handled appropriately, but there will be a fairly prompt error message about passing six parameters to one placeholder otherwise! – jonrsharpe Feb 13 '18 at 23:42
  • Just preempting another question of the same theme :P Anyway, +1, it's a shame the accepted answer uses string interpolation. – roganjosh Feb 13 '18 at 23:44
0

The following should work:

pattern = "%"+model_name+"%"
db.execute("select col1,col2,col3 from tablename where col3 like :s order by col3" ,{"s":pattern}).fetchall()
Jan Černý
  • 1,084
  • 2
  • 14
  • 26
Krishna Kumaran
  • 1
  • 3
-5
model_name1 = Master_Cursor.execute("select col1,col2,col3 from tablename where col3 like '%%%s%%' order by col3" %(model_name)).fetchall()

Use 2 percents to represent a real percent character

JoelFan
  • 34,383
  • 31
  • 123
  • 194
  • 2
    This will work, but *still has the problem commented on*. **Do not** use string interpolation to build queries. – jonrsharpe Feb 13 '18 at 23:32
  • 1
    2 downvotes for an accepted answer... and the only one that correctly answers the question as asked by the OP. Also... no other answer mentions the technique of using 2 percents as an escaped percent, which *does* have legitimate uses – JoelFan Feb 15 '18 at 00:49
  • *"the only one that correctly answers the question as asked"* - the actual question is *"How would I get back other records"*, not *"how do I escape percents in Python strong formatting"*, and I'd expect someone at your rep to be able to spot an XY problem even if it did ask that. *"no other answer mentions"* - mine *does* mention it. Did you actually read either the question or the others answer before commenting? *"does have legitimate uses"* - but you don't even *hint* that this might not be one of them. – jonrsharpe Feb 15 '18 at 07:45