How to use wild card % in python string for sql statement

Question

I'm having this situation to check whether an account number exists in a row in database. The column can contain a number of account numbers each separated by a comma eg. 00001,00002,00003. eg case:SELECT * FROM finance_collection.member WHERE account_ids LIKE 00001,% OR %,00001 OR %,00001,% OR 00001 For this I wrote an SQL statement as:

account_exists = " SELECT * FROM finance_collection.member WHERE account_ids LIKE %s,% OR %,%s OR %,%s,% OR %s "

Here %s is the incoming account number , is a comma and % is a multicharacter wildcard.

    try:
    with connection.cursor() as cursor:

        sql = sqls.account_exists
        cursor.execute(sql, (account, account, account, account))
        return True

    except pymysql.Error as error:
    print(error)
    return False

I'm using pymysql to connect and it seems the query isn't working. I tried various combinations like surrounding %s,% with ' ' and others but none of them worked. Can you show me a light here?

"the query isn't working." can you be more specific? error? wrong results? what? remove the try/except to find out the whole traceback. — kindall
– kindall, Commented May 1, 2018 at 5:30
@AndrewPaxson that won't work. It'll raise TypeError: not enough arguments for format string — Abid Hasan
– Abid Hasan, Commented May 1, 2018 at 5:40

Abid Hasan · Accepted Answer · 2018-05-01 05:39:16Z

1

Escape the % with another % (yes this doesn't make much sense but it is what it is):

x = "a=%sx%%2C" % ('text')
x
>>> 'a=textx%2C'

answered May 1, 2018 at 5:39

Abid Hasan

6684 silver badges10 bronze badges

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

Collectives™ on Stack Overflow

How to use wild card % in python string for sql statement

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related