Search code examples
pythonsqlpython-3.xpymysql

SQL: SELECT where one of many columns contains 'x' and result is not "NULL"

I have a piece of code that I realized is probably quite inefficient, though I'm not sure how to improve it.

Basically, I have a database table like this:

Example DB table

Any or several of columns A-G might match my search query. If that is the case, I want to query VALUE from that row. I need VALUE not to equal NULL though, so if that's the case, it should keep looking. If my query were abc, I'd want to obtain correct.

Below is my current code, using a database named db with a table table.

cur=db.cursor()

data="123"
fields_to_check=["A","B","C","D","E","F","G"]

for field in fields_to_check:

    "SELECT Value FROM table WHERE {}='{}'".format(field,data)
    query=cur.fetchone()
    if query and query !="NULL":
        break

db.close()

I think that the fact that this performs 8 queries is likely very inefficient.

Solution 
  • cur=db.cursor()

data="123"
fields_to_check=["A","B","C","D","E","F","G"]
sub_query = ""

for field in fields_to_check:
    sub_query = sub_query + "or {}='{}' ".format(field,data)

if sub_query:
    query = "SELECT Value FROM table WHERE ("+ str(sub_query[2:]) +") and value IS NOT NULL;"

if query:
    cur.execute(query)
    rows = cur.fetchall()
    if rows:
      for row in rows:
        print(row)