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-23 13:51:37Z

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, 2016 at 13:51

answered Mar 23, 2016 at 12:38

Bahrom

4,87235 silver badges44 bronze badges

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

12 Comments

Hardinger Over a year ago

But my query still doesn't match anything, i have to include the LIKE '%...%' - expression, but I don't know how for this example.

Bahrom Over a year ago

Do you know what query you want to build, can you add that to the question? Maybe you would like to use this: stackoverflow.com/questions/12957993/…

Hardinger Over a year ago

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! :)

Bahrom Over a year ago

Yes, so suppose you have a list ['Bruch', 'Verwerfung'], what would you want a sql query to look like for this list?

Hardinger Over a year ago

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 · Accepted Answer · 2016-03-23 13:22:43Z

0

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.

answered Mar 23, 2016 at 13:22

Hardinger

258 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

12 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

12 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related