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
– Maurice Meyer, Commented May 14, 2017 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 wasn't helpful, but I appreciate it @MauriceMeyer — Stack Johan
– Stack Johan, Commented May 14, 2017 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
– Maurice Meyer, Commented May 14, 2017 at 21:49
@MauriceMeyer I'm using SQLite. I'll add some question edits shortly. Another of my questions 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
– Stack Johan, Commented May 14, 2017 at 21:54

Maurice Meyer · Accepted Answer · 2017-05-14 22:03:51Z

-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, 2017 at 22:03

Maurice Meyer

18.2k4 gold badges35 silver badges54 bronze badges

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

2 Comments

Stack Johan Over a year ago

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 Over a year ago

I decided to go a different route and scrap that substitution method. Instead I added the strings together.

Stack Johan · Accepted Answer · 2017-05-14 22:43:03Z

-1

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

answered May 14, 2017 at 22:43

Stack Johan

3791 gold badge6 silver badges24 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related