How do I use a Python variable inside an SQL LIKE statement?

Question

I'm struggling to find an answer to how I can execute a Python variable:%s or ({url}) inside a LIKE %var% startment.

The percent signs cause me problems...syntax error

Here's a sample dump of the SQLite database I'm trying query from:

PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE IF NOT EXISTS "user_cats" (
    `category`  TEXT,
    `uploader`  TEXT,
    `folder`    TEXT,
    `playlists` TEXT,
    `playlist_image`    TEXT,
    `save_directory`    TEXT
);
INSERT INTO user_cats VALUES('Comics','ComicsExplained','DC Rebirth','[''New to DC Comics? Start here!'']',NULL,'%USERPROFILE%\Videos\Online Videos\Comics\ComicsExplained\');
INSERT INTO user_cats VALUES('Comics','Comicstorian',NULL,NULL,NULL,NULL);
INSERT INTO user_cats VALUES('Video Games','IGN','Daily Fix','[''Daily Fix'']',NULL,'%USERPROFILE%\Videos\Online Videos\Video Games\IGN\Daily Fix\');
INSERT INTO user_cats VALUES('Comics','Marvel Entertainment','Marvel Top 10','[''Marvel Top 10'']',NULL,'%USERPROFILE%\Videos\Online Videos\Comics\Marvel Entertainment\');
INSERT INTO user_cats VALUES('','ScrewAttack!',NULL,NULL,NULL,NULL);
COMMIT;

What I'd like to do is have a formula (that I'll likely turn into an object method) that uses youtube-dl to retrieve the uploader and playlist title.

Then use the database to route files to different save locations, and - probably save other information. But, I'm having problems with the SELECT statement and percentage signs. The error depends how I edit the statement - various syntax or unexpected character etc. (It would probably not help to type every error I've gotten - it's a problem with the variable assignment - not the statement.

If I query SQLite (outside of Python) using simply...

SELECT save_directory FROM user_cats WHERE uploader='IGN' AND playlists LIKE '%ail%';

Bingo. Works.

def save_dir (uploader, playlist):
    query = "SELECT save_directory FROM user_cats WHERE uploader=({ul}) AND playlists LIKE '%%({pl})%%'.format(ul = uploader, pl = playlist)".format(ul=uploader, pl=playlist))

    c.execute(query)
    all_rows = c.fetchall()
    print('1):', all_rows)

You need to double percent signs. `select * from user_cats where playlists like '%%s' % 'your string'`. — Maurice Meyer, May 14 '17 at 21:22
Possible duplicate of [Python string percent sign escape](http://stackoverflow.com/questions/40230546/python-string-percent-sign-escape) — Maurice Meyer, May 14 '17 at 21:22
I'm still getting an error...if I use this where I use %% ... `query = 'SELECT save_directory FROM user_cats WHERE uploader="%%s" AND playlists LIKE "%%%s%"' % (uploader,playlist)` The problem is that SQL uses %var% for LIKE operations - and python uses %s for substitutions. So, the LIKE statement becomes: `%%%s%`. The [link](http://stackoverflow.com/questions/40230546/python-string-percent-sign-escape) wasn't helpful, but I appreciate it @MauriceMeyer — Stack Johan, May 14 '17 at 21:42
You may want to edit your question, how the original SQL query looks like. So we can see what you want to achieve, you talk about percent signs but i dont see any in your code. Btw. which database are you using ? — Maurice Meyer, May 14 '17 at 21:49
@MauriceMeyer I'm using SQLite. I'll add some question edits shortly. [Another of my questions](http://stackoverflow.com/questions/43959200/how-to-create-an-sqlite-trigger-that-updates-an-entry-depending-on-another-entry/43962652#43962652) has details about the database info I'm trying to retrieve. The formula should have the parameters (uploader,playlist) and match uploader and playlists columns — Stack Johan, May 14 '17 at 21:54
I can just help with formatting string, SQLite is nothing i ever used. — Maurice Meyer, May 14 '17 at 22:05
@MauriceMeyer I'll give your (below) answer a shot. I updated the question with some database info, the SQLite query that works, and my current attempt at the Python call. I found a [similar question](http://stackoverflow.com/questions/26536676/python-mysql-parameterized-query-conflicts-with-wildcard-in-like-statement) but wasn't able to get that working. — Stack Johan, May 14 '17 at 22:11
Possible duplicate of [how to safely generate a SQL LIKE statement using python db-api](https://stackoverflow.com/questions/2097475/how-to-safely-generate-a-sql-like-statement-using-python-db-api) — Ilja Everilä, Jan 07 '18 at 09:20

score -1 · Accepted Answer · answered May 14 '17 at 22:03

-1

You can format this way:

>>> uploader, playlist = 'someUploader', 'somePlaylist'
>>> query ='SELECT save_directory FROM user_cats WHERE uploader={0:s} AND playlists LIKE %{1:s}'
>>> query.format(uploader, playlist)
'SELECT save_directory FROM user_cats WHERE uploader=someUploader AND playlists LIKE %somePlaylist'

Or even this way:

>>> query ='SELECT save_directory FROM user_cats WHERE uploader=%(ul)s AND playlists LIKE %(pl)s'
>>> query % {'ul': 'someUploader', 'pl': '%somePlaylist%'}
'SELECT save_directory FROM user_cats WHERE uploader=someUploader AND playlists LIKE %somePlaylist%'

answered May 14 '17 at 22:03

Maurice Meyer

17,279
4
30
47

Hm, I got the same error with both of those formats: `sqlite3.OperationalError: near "%": syntax error` ` query =`SELECT save_directory FROM user_cats WHERE uploader=({0:s}) AND playlists LIKE %({1:s})%' query.format(uploader,playlist)` – Stack Johan May 14 '17 at 22:22
I decided to go a different route and scrap that substitution method. Instead I [added the strings together](http://stackoverflow.com/a/43969492/1947026). – Stack Johan May 14 '17 at 22:44

score -1 · Answer 2 · answered May 14 '17 at 22:43

It's not pretty, but I finally got the query to work by adding the string together, instead of trying to substitute values.

def save_dir (uploader, playlist):

query ="SELECT save_directory FROM user_cats WHERE\
uploader='"+uploader+"' AND playlists LIKE '%"+playlist+"%'"
c.execute(query)
all_rows = c.fetchall()
print('1):', all_rows)

save_dir('IGN','Daily Fix')

And the output...

1): [('%USERPROFILE%\\Videos\\Online Videos\\Video Games\\IGN\\Daily Fix',)]
[Finished in 0.116s]

Finally -___-

How do I use a Python variable inside an SQL LIKE statement?

2 Answers2