时间戳

首页 > 解决方案 > 有没有办法我可以创建一个 for 循环，遍历不同的值以在 sqlite3 查询中搜索

python - 有没有办法我可以创建一个 for 循环，遍历不同的值以在 sqlite3 查询中搜索

问题描述

我有一个函数可以每年获取导演的所有数据，但我必须为每年创建一个新函数以将其更改year_granted为下一年或上一年。有没有一种方法可以制作一个只使用一个函数并将其更改year_granted为下一年的循环。

def getDirectorsInfo2019(self):
   c.execute('SELECT first_name, last_name, year_granted, app_units_granted, 
   full_value_units_granted 
   FROM Directors INNER JOIN DirectorsUnits ON DirectorsUnits.id_number_unit = 
   Directors.id_number_dir 
   WHERE id_number_dir BETWEEN 1 AND 50 AND year_granted=2019')
   datas = c.fetchall()
   for people in data:
       people = [datas[0]]
   for people2 in [datas[0]]:
       peopl02 = list(pepl2)
   self.firstNAme = people2[0]
   self.year2019 = people2[2]
   self.lastNAme = people2[1]   
   self.aUnits2019 = people2[3]
   self.fUnits2019 = people2[4]

标签： pythonsqlite

解决方案

是的，这很简单。基本思想是循环一个范围并使用 DB-API 的“参数替换”方法填写 sql 查询。它看起来像这样：

query = """
SELECT first_name, last_name, year_granted, app_units_granted, full_value_units_granted FROM Directors 
INNER JOIN DirectorsUnits ON DirectorsUnits.id_number_unit = Directors.id_number_dir 
WHERE id_number_dir BETWEEN 1 AND 50 AND year_granted=?
"""
# I used a triple-quote string here so that the line breaks are ignored
# Note that this loop would fetch data for 1998, 1999, 2000, and 2001, but not 2002
for year in range(1998, 2002):
    # Parameter substitution takes a list or tuple of values, so the value must be in a list even though there's only one
    rows = c.execute(query, [str(year),]).fetchall()
    for row in rows:
       #strictly speaking, you don't need these variable assignments, but it helps to show what's going on
       first_name = row[0]
       last_name = row[1]
       year = row[3]
       a_units = row[4]
       f_units = row[5]
       # do stuff with row data here, append to a list, etc.

我希望这有帮助！

推荐阅读