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, Jan 06 '14 at 00:42

score 5 · Accepted Answer · edited May 23 '17 at 10:29

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 '17 at 10:29

Community

1
1

answered Jan 06 '14 at 01:14

Emilio Silva

1,942
14
17

There is no REGEX operator, and the linked answers say that REGEXP is not available by default. – CL. Jan 06 '14 at 08:58
Sorry about the typo. However, the linked answers give enough information to install and use the `REGEXP` operator. For example, [this answer](http://stackoverflow.com/questions/5365451) installs the operator in 5 lines of Python. – Emilio Silva Jan 08 '14 at 00:50

score 3 · Answer 2 · answered Jan 06 '14 at 00:31

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%'

Python SQLite SELECT LIKE IN [list]

2 Answers2

Linked