python读mysql数据库二张表按日历生成表格

满天星

背景：新手上路，第一次用基于python3的flask做web，然后数据库用的mysql

目的：有一张人工维护的excel表，横轴是每个月的1-31天，纵轴是参与者名字及月初定的目标，然后每个人的数据，按天记录在后面，每个人的记录在每个月内一般只有5-15条，有少的可能一个月内只有1-5条



如下：

姓名      |      目标     |    1     |    2    |    3      |     4  |  ......    | 31 |

张三           1000          300                            100              200         

李四            2000                     100                200              100

王五           3000          100       100      100                          200



将以上表的数据转换为mysql的数据库

姓名和目标在一个goals表

姓名和reocrd数据在records表



这些都ok了，flask实现了goals的添加/修改/显示，records的添加/修改/显示

现在对以上表格的web化，不会弄了


1. 取出人数列循环
2. 以人数列的人名为参数（第一次for循环），循环X遍来读取数据库，遍历出每个人的所有record，有多少人，就得读取执行多少次sql查询
3. 最后将按人名读出来的record（第二次for循环），再按天来循环一次排列（第三次for循环），如果record中的天数和日历数值匹配了，就显示record值，如果不匹配，就显示空白


flask的代码部分：

1. 
   
2. 
   
3.     cursor.execute('SELECT name, distances from goals where DATE_FORMAT(date, "%Y%m") = DATE_FORMAT(CURDATE(), "%Y%m") ')
   
4.     goals = [dict(name=row[0], distances=row[1]) for row in cursor.fetchall()]
   
5.    
   
6.     monthly = []
   
7.     for goal in goals:
   
8.         # get month records with name
   
9.         sql = 'SELECT name,DATE_FORMAT(date, "%e"),distance FROM records WHERE DATE_FORMAT(date, "%Y%m") = DATE_FORMAT(CURDATE(), "%Y%m") and name = %s order by date'
   
10.         cursor.execute(sql, (goal["name"], ))
    
11.         records = [dict(name=row[0], day=row[1], distance=row[3]) for row in cursor.fetchall()]
    
12.         monthly.append(dict(goal, counts=len(records), records=records))
    
13.     #close db
    
14.     close_db()
    
15.     return render_template('index.html', monthly=monthly)
    
16. 
    
17. 
    

复制代码

实际的jinjia2 index.html模板：

1. 
   
2. 
   
3.   <table class="hover">
   
4.     <thead>
   
5.     <tr>
   
6.       <th width="150">ID</th>
   
7.       <th width="150">Name</th>
   
8.       <th width="150">Distances</th>
   
9.       <th width="150">Counts</th>
   
10.       
    
11.       {% for m in range(1,32) %}
    
12.          <th>{{ m }}-Mar</th>
    
13.       {% endfor %}
    
14. 
    
15.     </tr>
    
16.     </thead>
    
17.     <tbody>
    
18.   
    
19.   {% for month in monthly %}
    
20.   
    
21.       <tr>
    
22.       <td>{{ loop.index }}</td>
    
23.       <td>{{ month.name }}</td>
    
24.       <td>{{ month.distances }}KM</td>
    
25.       <td>{{ month.counts }}</td>
    
26. 
    
27.       {% for m in range(1,32) %}
    
28.           {% for record in month.records %}
    
29.             {% if record.day == m %}
    
30.               <td>{{ record.distance }}</td>
    
31.             {% endif %}
    
32.             <td>{{ record.day }}</td>
    
33.           {% endfor %}
    
34.       {% endfor %}
    
35. 
    
36.     </tr>
    
37.   {% else %}
    
38.     <tr><td>No record</td></tr>
    
39.   {% endfor %}
    
40. 
    
41.     </tbody>
    
42.   </table>
    
43. 
    

复制代码

先不考虑数据库多次查询的问题，如何将二张表里的数据，以日期顺序显示在web上，主要是三次for循环出问题了
另外，还有个特别笨的办法就是在数据库records里面按日期添加31个字段，然后写入的时候按天数写入，这个就太XX了，还是希望有个更先进的解决办法。

每个人的数据不是整月31天每天都有，只会有几天才会有，例如一个人只会有5条，打个比方，3号，8号，10号，15号，20号，就没有了，是没规律的

另外，也尝试直接把数据先读到python里面，生成符合这个table的数据格式，再用jinjia2的html模板生成，也一样面临这3个for循环嵌套的问题，
修改为2次sql查询的python flask代码如下：

1.     # get user goal from db
   
2.     sql = 'SELECT name, distances from goals where DATE_FORMAT(date, "%Y%m") = DATE_FORMAT(CURDATE(), "%Y%m") '
   
3.     cursor.execute(sql)
   
4.     goals = [dict(name=row[0], distances=row[1]) for row in cursor.fetchall()]
   
5.     app.logger.debug('index debug goals: %s', (goals))
   
6. 
   
7.     # get current month record from db
   
8.     sql = 'SELECT goals.name, goals.distances, DATE_FORMAT(records.date, "%e"), records.distance, records.pace, records.type \
   
9.             FROM goals LEFT JOIN records ON goals.name = records.name \
   
10.             where DATE_FORMAT(records.date, "%Y%m") = DATE_FORMAT(CURDATE(), "%Y%m") \
    
11.             AND DATE_FORMAT(goals.date, "%Y%m") = DATE_FORMAT(CURDATE(), "%Y%m") \
    
12.             ORDER by goals.name, records.date'
    
13.     cursor.execute(sql)
    
14.     records = [dict(name=row[0], distances=row[1], day=row[2], distance=row[3], pace=row[4], type=row[5]) for row in cursor.fetchall()]
    
15.     app.logger.debug('index debug records: %s', (records))
    
16.    
    
17.     monthly = []
    
18.     for goal in goals:
    
19.         for d in range(1,32):
    
20.             for record in records:
    
21.                 if record.date == d:
    
22.                     pass
    
23. 
    
24.     #close db
    
25.     close_db()
    
26.     return render_template('index.html', monthly=monthly)

复制代码

求大神指点，谢谢！