>>> from env_helper import info; info()
页面更新时间： 2024-04-07 15:54:15
运行环境：
Linux发行版本: Debian GNU/Linux 12 (bookworm)
操作系统内核: Linux-6.1.0-18-amd64-x86_64-with-glibc2.36
Python版本: 3.11.2
2.6. 实例：将数据库信息导入到电子表格¶
数据都在数据库中不方便对数据的查看及解析。这就需要将数据导出来进行解析或其他操作。而数据导出的格式中以excel工作表格形式更加方便查看与操作。Python中对表格操作的库有很多，下面实例中用的是openpyxl库。openpyxl模块是一个读写Excel 2010文档的Python库，如果要处理更早格式的Excel文档，需要用到额外的库，openpyxl是一个比较综合的工具，能够同时读取和修改Excel文档。其他很多的与Excel相关的项目基本只支持读或者写Excel一种功能。
2.6.1. 读取数据库数据¶
连接earth数据库，并开始读取数据库中earthquake表中全部数据。
>>> from peewee import *
>>> db = SqliteDatabase('earth.db')
>>> db.connect()
>>>
>>> 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
>>>
>>> eq = earthquake
>>> data = eq.select()
2.6.2. 写到excel¶
上面已经将数据都存入data变量中，然后将data信息保存在excel内。 用openpyxl模块新建excel表格，将所得数据一一列入到表格中。 对表格第一行写下字段属性，方便对数据查看。
>>> from openpyxl import Workbook
>>> wb = Workbook()
>>> ws = wb.create_sheet()
>>> ws.cell(row=1,column=1).value='id'
>>> ws.cell(row=1,column=2).value='time'
>>> ws.cell(row=1,column=3).value='m'
>>> ws.cell(row=1,column=4).value='lat'
>>> ws.cell(row=1,column=5).value='lon'
>>> ws.cell(row=1,column=6).value='depth'
>>> ws.cell(row=1,column=7).value='location'
>>> num=1
>>> for i in data:
>>> num+=1
>>> ws.cell(row=num,column=1).value=i.id
>>> ws.cell(row=num,column=2).value=i.o_time
>>> ws.cell(row=num,column=3).value=i.m
>>> ws.cell(row=num,column=4).value=i.epi_lat
>>> ws.cell(row=num,column=5).value=i.epi_lon
>>> ws.cell(row=num,column=6).value=i.epi_depth
>>> ws.cell(row=num,column=7).value=i.location_c
---------------------------------------------------------------------------
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 [13], line 12
10 ws.cell(row=1,column=7).value='location'
11 num=1
---> 12 for i in data:
13 num+=1
14 ws.cell(row=num,column=1).value=i.id
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
将表格保存，关闭数据库连接，释放资源。
>>> wb.save("data.xlsx")
>>> db.close()
True
结果如下：
