python - 如何创建自动更新的 sqlite 表？

Question

我正在使用 python 脚本为我观看的动漫节目创建和维护 sqlite 数据库，以帮助我更好地跟踪它们以及我拥有和需要获取的剧集。

我使用脚本为每个系列创建一个表，例如《Bleach》、《Black Lagoon》...，并且在每个表中存储以下信息:

系列表:

Season_Num         # Unique season number
I_Have_Season      # yes or no to say I have a directory for that season
Season_Episodes    # Amount of episodes according to the TVDB that are in that season
Episodes_I_Have    # The numer of episodes I have for that season

为我拥有的每个系列创建同一个表，并为该系列中的每个季节创建一行，看起来效果很好。

现在我想做的是创建一个汇总表，该表从每个系列的表中获取信息，并仅创建 1 个包含我需要的所有信息的表，它包含以下信息:

汇总表:

Series          # Unique Series name
Alt_Name        # Alternate name (The series name in english)
Special_Eps     # The amount of Special episodes (Season 0 in the series table)
Special_Eps_Me  # The number of Special Episodes I have
Tot_Ses         # The total count of the Seasons (excluding season 0)
Tot_Ses_Me      # The total count of Seasons that have yes in I_Have_Season column
Tot_Episodes    # Total Episodes excluding season 0 episodes
Tot_Eps_Me      # Total Episodes I have excluding season 0 episodes

我认为我想做的事情可以使用触发器来完成，但我不确定如何实现它们，以便汇总表会自动更新，例如，如果将新赛季添加到系列表或系列表的值中已更改。

更新:

经过更多的思考和研究，Fabian 的 View 而不是表格的想法听起来像是我想要的，但如果可能的话，我希望将每个系列单独保存在自己的表格中进行更新，而不是只有一张表格每个系列和每个季节都混合在一起。

更新2:

我已经继续放入 INSERT、UPDATE 和 DELETE 的触发器，我将它们添加到脚本的初始创建循环中，使用变量作为表名称，并且汇总表似乎更新得很好(在修复了某些问题之后)其中的值已计算)。我将进一步测试它，希望它能继续工作。现在我只需要让我的脚本为新系列和我删除的系列添加和删除表。

Answer 1

这可以使用触发器来实现。但这种事情通常最好使用 View 以声明方式完成:

例如，

create table series (
  series_name,
  alt_name,
  special_eps,
  special_eps_me,
  primary key(series_name)
);

create table seasons (
  series_name,
  season_num,         
  i_have_season, 
  episodes, 
  episodes_i_have,
  primary key (series_name,season_num),
  foreign key (series_name) references series (series_name),
  check (i_have_season in ('F','T'))
);

create view everything_with_counts as 
select series_name, 
       alt_name, 
       special_eps, 
       special_eps_me, 
       (select count(*) from seasons  where seasons.series_name = series.series_name) as tot_ses,
       (select count(*) from seasons  where seasons.series_name = series.series_name and i_have_season = 'T') as tot_ses_me,
       (select sum(episodes) from seasons where seasons.series_name = series.series_name) as tot_epsiodes,
       (select sum(episodes_i_have) from seasons where seasons.series_name = series.series_name and i_have_season = 'T') as tot_epsiodes_me
from series;

编辑

由于您想坚持触发器设计:假设您有这样的系列表:

create table series_a (
  season_num,         
  i_have_season, 
  episodes, 
  episodes_i_have
);

create table series_b (
  season_num,         
  i_have_season, 
  episodes, 
  episodes_i_have
);

依此类推，您的汇总表如下所示:

create table summary (
  series_name,
  alt_name,
  special_eps,
  special_eps_me,
  tot_ses,
  tot_ses_me,
  tot_episodes,
  tot_episodes_me,
  primary key(series_name));

您必须为每个系列表创建三个触发器(插入、更新、删除)，例如:

  create trigger series_a_ins after insert on series_a 
  begin
    update summary set tot_ses = (select count(*) from series_a ),
                       tot_ses_me = (select count(*) from series_a  where  i_have_season = 'T'),
                       tot_episodes = (select sum(episodes) from series_a ),
                       tot_episodes_me = (select sum(episodes_i_have) from series_a where  i_have_season = 'T') 
    where series_name = 'a';
  end;

/* create trigger series_a_upd after update on series_a ... */
/* create trigger series_a_del after delete on series_a ... */

使用此版本，您必须在汇总表中手动添加汇总条目，并且当您修改 series_... 表时，计数器会自动更新。

您还可以使用 INSERT OR REPLACE ( see documentation ) 按需创建摘要条目。