>>> from env_helper import info; info()
页面更新时间： 2024-04-07 23:41:56
运行环境：
Linux发行版本: Debian GNU/Linux 12 (bookworm)
操作系统内核: Linux-6.1.0-18-amd64-x86_64-with-glibc2.36
Python版本: 3.11.2
2.5. 实例：报表自动化生成¶
有时候我们需要按照某种规则生成一种固定模板的word报告，python能够很好的完成这项工作。 例如将sqlite的信息读取后用于word文档内。 需要安装word库python-docx。
2.5.1. 创建模板¶
首先我们需要有一个word报告模板，模板中内置了一些需要修改的关键字。留出空白也可以。前面的章节已经使用替换了关键字内容，这次我们直接留出空白在表格内插入内容。
文档内容主要为两个表。为了存储中国地震台网的一个月内，全世界地震信息记录。
word内的表和excel的处理起来很相似，word内有多个表的时候，我们可以遍历他，第一个表就是0第二个为1，在excel内可以指定sheet或者也可以进行遍历获取到第几张表。
2.5.2. 生成报表¶
>>> from docx import Document
>>> from peewee import *
>>> import os
创建一个文档对象，加载我们的word模板。
>>> word = Document('./word/eg.docx')
创建数据库对象，加载数据库的数据。
>>> db_file = SqliteDatabase('earth.db')
>>> class earthquake(Model):
>>> id= CharField(null=False,primary_key=True,unique=True)
>>> did = CharField(null=False,max_length=50)
>>> o_time = CharField(null=False,max_length=100)
>>> m = CharField(null=False,max_length=20)
>>> epi_lat = CharField(null=False,max_length=30)
>>> epi_lon = CharField(null=False,max_length=30)
>>> epi_depth = CharField(null=False,max_length=30)
>>> class Meta:
>>> database = db_file
>>> db_file.connect()
True
读取数据库的数据，准备存入word内。
查询震级高于4级的信息,保存进表一内。
>>> data=earthquake.select().where(earthquake.m>4)
>>> num=1
>>> table0=word.tables[0]
>>> for i in data:
>>> table0.cell(num,0).text=i.m
>>> table0.cell(num,1).text=i.o_time
>>> table0.cell(num,2).text=i.did
>>> table0.cell(num,3).text=i.epi_lat
>>> table0.cell(num,4).text=i.epi_lon
>>> table0.cell(num,5).text=i.epi_depth
>>> num+=1
---------------------------------------------------------------------------
OperationalError Traceback (most recent call last)
File /usr/lib/python3/dist-packages/peewee.py:3177, in Database.execute_sql(self, sql, params, commit)
3176 try:
-> 3177 cursor.execute(sql, params or ())
3178 except Exception:
OperationalError: no such table: earthquake
During handling of the above exception, another exception occurred:
OperationalError Traceback (most recent call last)
Cell In [5], line 4
2 num=1
3 table0=word.tables[0]
----> 4 for i in data:
5 table0.cell(num,0).text=i.m
6 table0.cell(num,1).text=i.o_time
File /usr/lib/python3/dist-packages/peewee.py:6960, in BaseModelSelect.__iter__(self)
6958 def __iter__(self):
6959 if not self._cursor_wrapper:
-> 6960 self.execute()
6961 return iter(self._cursor_wrapper)
File /usr/lib/python3/dist-packages/peewee.py:1918, in database_required.<locals>.inner(self, database, *args, **kwargs)
1915 if not database:
1916 raise InterfaceError('Query must be bound to a database in order '
1917 'to call "%s".' % method.__name__)
-> 1918 return method(self, database, *args, **kwargs)
File /usr/lib/python3/dist-packages/peewee.py:1989, in BaseQuery.execute(self, database)
1987 @database_required
1988 def execute(self, database):
-> 1989 return self._execute(database)
File /usr/lib/python3/dist-packages/peewee.py:2162, in SelectBase._execute(self, database)
2160 def _execute(self, database):
2161 if self._cursor_wrapper is None:
-> 2162 cursor = database.execute(self)
2163 self._cursor_wrapper = self._get_cursor_wrapper(cursor)
2164 return self._cursor_wrapper
File /usr/lib/python3/dist-packages/peewee.py:3190, in Database.execute(self, query, commit, **context_options)
3188 ctx = self.get_sql_context(**context_options)
3189 sql, params = ctx.sql(query).query()
-> 3190 return self.execute_sql(sql, params, commit=commit)
File /usr/lib/python3/dist-packages/peewee.py:3174, in Database.execute_sql(self, sql, params, commit)
3171 else:
3172 commit = not sql[:6].lower().startswith('select')
-> 3174 with __exception_wrapper__:
3175 cursor = self.cursor(commit)
3176 try:
File /usr/lib/python3/dist-packages/peewee.py:2950, in ExceptionWrapper.__exit__(self, exc_type, exc_value, traceback)
2948 new_type = self.exceptions[exc_type.__name__]
2949 exc_args = exc_value.args
-> 2950 reraise(new_type, new_type(exc_value, *exc_args), traceback)
File /usr/lib/python3/dist-packages/peewee.py:191, in reraise(tp, value, tb)
189 def reraise(tp, value, tb=None):
190 if value.__traceback__ is not tb:
--> 191 raise value.with_traceback(tb)
192 raise value
File /usr/lib/python3/dist-packages/peewee.py:3177, in Database.execute_sql(self, sql, params, commit)
3175 cursor = self.cursor(commit)
3176 try:
-> 3177 cursor.execute(sql, params or ())
3178 except Exception:
3179 if self.autorollback and not self.in_transaction():
OperationalError: no such table: earthquake
将震级小于等于4级的数据，降序保存到表二：
>>> data=earthquake.select().where(earthquake.m<=4)
>>> num1=1
>>> table1=word.tables[1]
>>> for i in data:
>>> table1.cell(num1,0).text=i.m
>>> table1.cell(num1,1).text=i.o_time
>>> table1.cell(num1,2).text=i.did
>>> table1.cell(num1,3).text=i.epi_lat
>>> table1.cell(num1,4).text=i.epi_lon
>>> table1.cell(num1,5).text=i.epi_depth
>>> num1+=1
>>> word.save('abc.docx')
2.5.3. 查看结果¶
表一的内容为：
表二的内容为：