I have a table in an sqlite database called 'General'. It has four columns 'One', 'Two' and three
One has a list of names:
One = ['James', 'Ben', 'John', 'Peter']
Two, a list of country's:
Two = ['Uk', 'USA', 'Germany', 'UK']
three, a score:
Three = [3,6,5,9]
In python I can access this easily enough using pd.read_sql_query and adding .format to get the relevant data.
name = 'John'
country = 'Germany'
conn = sqlite3.connect('score_history.db')
df = pd.read_sql_query('''SELECT * FROM General WHERE (name='{}') & (country='{}')'''.format(name, country), conn)
conn.close()
However, sometimes I just want to search the country (for instance UK) and leave name blank. How would I adapt the above so that the same code can be used to either just search by country / name / score, but also be able to search multiple columns as above for country and name, or country and score or name and score?
Thanks
You could explicitly handle the empty string in the comparisons, like so:
df = pd.read_sql_query('''
SELECT *
FROM General
WHERE ('{}' = '' OR name='{}') AND ('{}' = '' OR country='{}')
'''.format(name, name, country, country), conn)
You can also use IN to avoid repeating the parameter:
df = pd.read_sql_query('''
SELECT *
FROM General
WHERE '{}' IN ('', name) AND '{}' IN ('', country)
'''.format(name, country), conn)
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments