Update fsij_list remove 2006 list, adding fsij-meeting.
[fsij-server-admin.git] / get_list_by_sql.py
1 import sqlite3
2
3 DB='../fsij-data/fsij.db'
4 conn = sqlite3.connect(DB)
5 c = conn.cursor()
6
7 # TABLE: auth_user
8 # id, username, email
9
10 # TABLE: auth_user_groups
11 # id, user_id, group_id
12
13 # TABLE: auth_group
14 # id, name
15
16 # TODO: should care about multiple forward destinations
17
18 c.execute('''select username, auth_user.email from auth_user, auth_user_groups,
19   auth_group, membership_member
20   where auth_user_groups.group_id = auth_group.id
21         and auth_user.id = auth_user_groups.user_id
22         and auth_group.name = \'fsijMember\'
23         and auth_user.id = membership_member.user_id
24         and membership_member.member_type = \'M'
25 ''')
26
27 individual_member_list = []
28 for row in c:
29     individual_member_list.append(( row[0], [ row[1] ] ))
30
31 c.execute('''select email from auth_user, auth_user_groups, auth_group
32   where auth_user_groups.group_id = auth_group.id
33         and auth_user.id = auth_user_groups.user_id
34         and auth_group.name = \'boardMember\'''')
35 board_member_list = []
36 for row in c:
37    board_member_list.append(row[0])
38
39 c.execute('''select email from auth_user, auth_user_groups, auth_group
40   where auth_user_groups.group_id = auth_group.id
41         and auth_user.id = auth_user_groups.user_id
42         and auth_group.name = \'fsijMember\'''')
43 fsij_member_list = []
44 for row in c:
45    fsij_member_list.append(row[0])
46
47 c.close()
48 conn.close()
49
50 if __name__ == "__main__":
51     print("++++++++++++++++++++++++++")
52     for row in individual_member_list:
53         print row
54
55     print("++++++++++++++++++++++++++")
56     for row in board_member_list:
57         print row
58
59     print("++++++++++++++++++++++++++")
60     for row in fsij_member_list:
61         print row