Give a list of strings to a sql-query [Python]

Question

I use Python2.7 on Windows 7 and a mysql server, connection by pymssql.

My Problem: I have a very big Database and I like to select the ID's of objects matching one of several words(string) from a list, I give to my program. In this query there must be a LIKE %...% expression for these words of my list, too.

So far I connected my Python-Script to my Database and defined a cursor. Then I made a small list with the words, I am searching for and I created some placeholders for my query later:

wortliste = ['Bruch', 'Verwerfung']
placeholders = ','.join(['%s'] * len(wortliste))

Here is my Query:

query = """ SELECT BO_INDEX FROM GeoTest.dbo.Tabelle_Bohrung
        WHERE BO_BEMERKUNG IN ({})""".format(placeholders)

When I am searching for a single word, here for example for the word 'Bruch', my query would look like this:

query = """ SELECT BO_INDEX FROM GeoTest.dbo.Tabelle_Bohrung
        WHERE BO_BEMERKUNG LIKE '%Bruch%'"""

This query for a single word matches the right Id's (=BO_INDEX). The query with the placeholders doesn't crash, but it didn't match anything :(

But I like to loop my database for a couple of words and append the matching ID's for every word (string) in my list(=wortliste) and append it to a new list.

I really dont't know how to solve this problem!

I am grateful for every new way to solve this challenge! Thanks!

Bahrom · Accepted Answer · 2016-03-23T13:51:37.060

0

EDIT 2:

If you want to loop over your list and append to the output (using your example):

words = ['ab', 'cd', 'ef']
abfrage_list = []
for w in words:
    # Generate a query
    query = """ SELECT BO_INDEX FROM GeoTest.dbo.Tabelle_Bohrung
        WHERE BO_BEMERKUNG LIKE '%%%s%%' """ % w
    # Execute that query and get results
    cur.execute(query)
    result_all = cur.fetchall()
    # Add those results to your final list
    for i in result_all:
        abfrage_list.append(i)

EDIT:

For your example with multiple likes:

query = """ SELECT BO_INDEX FROM GeoTest.dbo.Tabelle_Bohrung
    WHERE BO_BEMERKUNG LIKE '%ab%'
     OR O_BEMERKUNG LIKE '%cd%' 
     OR O_BEMERKUNG LIKE '%ef%' """

query = """ SELECT BO_INDEX FROM GeoTest.dbo.Tabelle_Bohrung
    WHERE {params}""".format(
        params=" OR ".join("BO_BEMERKUNG LIKE '%%%s%%' \n" % w for w in wortliste)
    )
print(query)

Prints:

 SELECT BO_INDEX FROM GeoTest.dbo.Tabelle_Bohrung
    WHERE BO_BEMERKUNG LIKE '%Bruch%' 
 OR BO_BEMERKUNG LIKE '%Verwerfung%'

Your placeholders doesn't contain any of the items from your word list, use:

placeholders = ','.join("'%s'" % w for w in wortliste)

For example:

wortliste = ['Bruch', 'Verwerfung']
print(','.join(['%s'] * len(wortliste)))
print(','.join("'%s'" % w for w in wortliste))

Prints:

%s,%s
'Bruch','Verwerfung'

edited Mar 23 '16 at 13:51

answered Mar 23 '16 at 12:38

Bahrom

4,752
32
41

But my query still doesn't match anything, i have to include the LIKE '%...%' - expression, but I don't know how for this example. – Hardinger Mar 23 '16 at 12:48
Do you know what query you want to build, can you add that to the question? Maybe you would like to use this: http://stackoverflow.com/questions/12957993/how-to-use-sql-like-condition-with-multiple-values-in-postgresql – Bahrom Mar 23 '16 at 12:50
Well, this is the first step of my program. The contents of my list will chance with every new query and because of that, i want to outsource this list in a next step for a user, who only types the words he is searching for in this outsourced list and runs the program! :) – Hardinger Mar 23 '16 at 12:57
Yes, so suppose you have a list `['Bruch', 'Verwerfung']`, what would you want a sql query to look like for this list? – Bahrom Mar 23 '16 at 13:00
query = """ SELECT BO_INDEX FROM GeoTest.dbo.Tabelle_Bohrung WHERE BO_BEMERKUNG LIKE '%Bruch%'""" cur.execute(query) result_all=cur.fetchall() abfrage_list = [] for i in result_all: abfrage_list.append(i) and this procedure for any other word in my list! – Hardinger Mar 23 '16 at 13:02
But that only contains one item right? So would you want multiple queries for each item in the list? What about Verwerfung? – Bahrom Mar 23 '16 at 13:03
My list i give to the program could contain hundreds of strings ;-) – Hardinger Mar 23 '16 at 13:11
I understand, give me a sample query that you want your program to generate based on a list of two entries, for example for `['ab', 'cd']` – Bahrom Mar 23 '16 at 13:14
This looks good, but is it possible to frame this query for only one word of my list and append the matched ID's to a new list? And than loop through my list, do the query for every word in my list and append the results to the new list? – Hardinger Mar 23 '16 at 13:45
This task takes me so long, a long way to go :) – Hardinger Mar 23 '16 at 13:54
We've all been there :) – Bahrom Mar 23 '16 at 13:55
I really hope so! :-D – Hardinger Mar 23 '16 at 13:56

score 0 · Answer 2 · answered Mar 23 '16 at 13:22

for the Example of the following list = ['ab','cd','ef']

query = """ SELECT BO_INDEX FROM GeoTest.dbo.Tabelle_Bohrung
    WHERE BO_BEMERKUNG LIKE '%ab%'
     OR O_BEMERKUNG LIKE '%cd%' 
     OR O_BEMERKUNG LIKE '%ef%' """

cur.execute(query)
result_all = cur.fetchall()

abfrage_list = []
for i in result_all:
    abfrage_list.append(i)

But I need this procedure for possibly hundreds of strings in this list. i need to loop over this list and i need the LIKE expression in the query, otherwise it won't catch anything.

Give a list of strings to a sql-query [Python]

2 Answers2