Count specific information in .db file

I wish to count the number of males and females in a specific city that the user chose. Here’s a gist of the .db file:

example_table

CODE     AGE     SEX     CITY
----     ---     ---     ----
E101      25      M      New York
E102      42      F      New York
E103      31      M      Chicago
E104      67      F      Chicago

This is what I’ve coded so far based on the references I’ve read:

city=input("Input city: ")

import sqlite3

db = sqlite3.connect('covid.db')
cursor = db.cursor()

sql = 'SELECT Sex, COUNT(Code) FROM example_table GROUP BY Sex'
data = cursor.execute(sql).fetchall()
for sex, cases in data:
    print(sex, ':', cases)

cursor.close()

So far, that prints the overall number of males and females in the data. I’d like to ask how could I print the exact number of males and females in a city? Say I input “New York”, the result would be:

M : 1
F : 1

Answer

Add a WHERE clause restricting to a certain city, e.g.

sql = 'SELECT Sex, COUNT(Code) FROM example_table WHERE CITY = ? GROUP BY Sex'
params = ('New York',)
data = cursor.execute(sql, params).fetchall()
for sex, cases in data:
    print(sex, ':', cases)

Advertisement

Answer