python函数中的sql

Question

我有如下表格：

table1: name born country id
        A    1943  UK      3
        B    1966  USA     2...
table2: artist_id  artist_name formation
        3           The Beatles  1960
        2           The Door     USA

所以我正在努力写下面的函数，有人能给我一个开始的想法吗？你知道吗

def band_by_country(sql_cursor,country):
'''(sqlite3.Cursor,str)->list of[]
return a list of bands representing all the bands who have at least one member who was born in that country.
>>>band_by_country(sql_cursor, UK)
['The Beatles']
>>>band_by_country(sql_cursor,USA)
['The Doors']
'''

Answer 1

假设您的连接已经设置好了，下面应该会给出表中的“name”参数。你知道吗

def band_by_country(sql_cursor,country):
    bands = []
    for row in sql_cursor.execute('SELECT name FROM table1 WHERE country=?', (country,)):
        bands.append( row[0] )
    return bands

如果要将表1与表2连接起来，则如下所示：

def band_by_country(sql_cursor,country):
    bands = []
    for row in sql_cursor.execute(
        "SELECT table2.artist_name FROM table1 JOIN table2 ON "
        "table1.id=table2.artist_id WHERE table1.country=?", (country,)):
        bands.append( row[0] )
    return bands