Python SQLite SELECT LIKE IN [list]

Question

How to write SQL query in Python to select elements LIKE IN Python list?

For example, I have Python list of strings

Names=['name_1','name_2',..., 'name_n']

and SQLite_table.

My task is to find shortest way to

SELECT elements FROM SQLite_table WHERE element_name LIKE '%name_1%'
SELECT elements FROM SQLite_table WHERE element_name LIKE '%name_2%'
...
SELECT elements FROM SQLite_table WHERE element_name LIKE '%name_n%'

SELECT elements FROM SQLite_table WHERE element_name LIKE '%name_1%' OR element_name LIKE '%name_2%' OR element_name LIKE '%name_n%' ?? — PostMan
– PostMan, Commented Jan 6, 2014 at 0:42

Community · Accepted Answer · 2017-05-23 10:29:01Z

5

You would need a regular expression of the form name_1|name_2|name_3|…, which you can generate using '|'.join(Names):

SELECT elements FROM SQLite_table WHERE element_name REGEXP 'name_1|name_2|name_3|…|name_n'

Check How do I use regex in a SQLite query? for instructions on how to use regular expressions in SQLite.

edited May 23, 2017 at 10:29

CommunityBot

11 silver badge

answered Jan 6, 2014 at 1:14

Emilio Silva

2,15016 silver badges22 bronze badges

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

2 Comments

CL. Over a year ago

There is no REGEX operator, and the linked answers say that REGEXP is not available by default.

Emilio Silva Over a year ago

Sorry about the typo. However, the linked answers give enough information to install and use the REGEXP operator. For example, this answer installs the operator in 5 lines of Python.

maurelio79 · Accepted Answer · 2014-01-06 00:31:23Z

3

Mmhh

Names=['name_1','name_2','name_n']
for i in Names:
    sql = "SELECT elements FROM SQLite_table WHERE element_name LIKE '%" + i + "%'" 
    print sql

SELECT elements FROM SQLite_table WHERE element_name LIKE '%name_1%'
SELECT elements FROM SQLite_table WHERE element_name LIKE '%name_2%'
SELECT elements FROM SQLite_table WHERE element_name LIKE '%name_n%'

answered Jan 6, 2014 at 0:31

maurelio79

2921 silver badge9 bronze badges

Collectives™ on Stack Overflow

Python SQLite SELECT LIKE IN [list]

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