内容来源于《Web接口开发与自动化测试——基于Python语言》虫师编著,如有涉及版权问题,归虫师本人所有。请大家支持虫师的著作:http://www.broadview.com.cn/book/4811
源码下载:https://github.com/defnngj/guest
目标:数据库表的设计很重要,工作量相当于Web系统的一半。
4.1 设计系统表Django提供了完善的模型(model)层来创建和存储数据,它包含你所存储数据的必要字段和行为。通常,每个模型对应数据库中唯一的一张表。所以,模型避免了我们直接对数据库操作。
Django 模型基础知识:
from django.db import models
# Create your models here.
# 发布会表
class Event(models.Model):
name = models.CharField(max_length=100) # 发布会标题
limit = models.IntegerField() # 参会人数
status = models.BooleanField() # 状态
address = models.CharField(max_length=200) # 地址
start_time = models.DateTimeField('event_time') # 发布会时间
create_time = models.DateTimeField(auto_now=True) # 创建时间(自动获取当前时间)
def __str__(self):
return self.name
# 嘉宾表
class Guest(models.Model):
event_id = models.ForeignKey(Event, on_delete=models.CASCADE,) # 关联发布会id
realname = models.CharField(max_length=64) # 姓名
phOne= models.CharField(max_length=16) # 手机号
email = models.EmailField() # 邮箱
sign = models.BooleanField # 签到状态
create_time = models.DateTimeField(auto_now=True) # 创建时间(自动获取当前时间)
class Meta:
unique_together = ("event_id", "phone")
def __str__(self):
return self.realname
发布会表(Event类)和嘉宾表(Guest类)的每一个字段在代码中已经做了注释。有些字段的设计需要作一个简单的说明。
首先,发布会表和嘉宾表中默认都会生成自增id,但在创建模型类时不需要声明该字段。
其次,发布会表中增加status字段用于表示该发布会的状态是否开启,从而控制发布会是否可用。
再次,嘉宾表中通过event字段(表中字段名为event_id)关联发布会id,一条嘉宾信息一定属于某一场发布会。ForeignKey()用来创建外键。
最后,对于一场发布会来说,因为手机号具有很强的唯一性,因此一般会选择手机号作为一位嘉宾的唯一验证信息。在嘉宾表中,除了嘉宾id为主键外,这里通过发布会id+手机号来作为联合主键。Meta是Django模型类的一个内部类,它用来定义一些Django模型类的行为特性。unique_together用来设置两个字段为联合主键。
注意:关联发布会id时,在django2.0后,定义外键和一对一关系的时候需要加on_delete选项,此参数为了避免两个表里的数据不一致问题,不然会报错。即on_delete=models.CASCADE。
接下来当模型创建好以后,执行数据库迁移。
(venv) liujindeMacBook-Pro:guest liujin$ python3 manage.py makemigrations sign
Migrations for 'sign':
sign/migrations/0001_initial.py
- Create model Event
- Create model Guest
- Alter unique_together for guest (1 constraint(s))
(venv) liujindeMacBook-Pro:guest liujin$ python3 manage.py migrate
Operations to perform:
Apply all migrations: admin, auth, contenttypes, sessions, sign
Running migrations:
Applying sign.0001_initial... OK
创建的发布会和嘉宾表可以通过Admin后台管理。
打开…/sign/admin.py文件。
from django.contrib import admin
from sign.models import Event, Guest
# Register your models here.
admin.site.register(Event)
admin.site.register(Guest)
这些代码通知Admin管理工具未这些模块逐一提供界面。
登录Admin后台:http://127.0.0.1:8000/admin/ (admin/admin123456)
Admin管理Events和Guests表.png
单击Event,“Add”一条发布会信息。
Event列表.png
如上图所示,显示的是一条发布会信息,默认只有发布会名称,这与创建model时设置的__ str__()有关,默认返回self.name,即发布会名称。
如何才能显示表中的更多字段呢?继续修改admin.py文件。
from django.contrib import admin
from sign.models import Event, Guest
# Register your models here.
class EventAdmin(admin.ModelAdmin):
list_display = ['id', 'name', 'status', 'address', 'start_time']
class GuestAdmin(admin.ModelAdmin):
list_display = ['realname', 'phone', 'email', 'sign', 'create_time', 'event']
admin.site.register(Event, EventAdmin)
admin.site.register(Guest, GuestAdmin)
刷新Events列表,如下图:
Event列表.png
添加一条嘉宾(Guest)信息,如下图:
Guests列表.png
除此之外,还可以快速地生成搜索栏和过滤器,重新打开admin.py文件,做如下修改:
from django.contrib import admin
from sign.models import Event, Guest
# Register your models here.
class EventAdmin(admin.ModelAdmin):
list_display = ['id', 'name', 'status', 'address', 'start_time']
search_fields = ['name'] # 搜索栏
list_filter = ['status'] # 过滤器
class GuestAdmin(admin.ModelAdmin):
list_display = ['realname', 'phone', 'email', 'sign', 'create_time', 'event']
search_fields = ['realname', 'phone'] # 搜索栏
list_filter = ['sign'] # 过滤器
admin.site.register(Event, EventAdmin)
admin.site.register(Guest, GuestAdmin)
search_fields用于创建表字段的搜索器,可以设置搜索关键字匹配多个表字段。
list_filter用于创建字段过滤器。
Events列表的搜索栏与过滤器.png
4.3 基本数据访问接下来练习数据库表的操作。
(venv) liujindeMacBook-Pro:guest liujin$ python3 manage.py shell
Python 3.7.0 (v3.7.0:1bf9cc5093, Jun 26 2018, 23:26:24)
[Clang 6.0 (clang-600.0.57)] on darwin
Type "help", "copyright", "credits" or "license" for more information.
(InteractiveConsole)
>>> from sign.models import Event, Guest
>>> Event.objects.all()
>>> Guest.objects.all()
>>>
>>> from datetime import datetime
>>> e1 = Event(id=2,name='魅族18发布会',limit=2000,status=True,address='珠海XXXX',start_time=datetime(2018,8,22,14,0,0))
>>> e1.save()
/Users/liujin/Documents/virtualenv3.7Demo/venv/lib/python3.7/site-packages/django/db/models/fields/__init__.py:1421: RuntimeWarning: DateTimeField Event.start_time received a naive datetime (2018-08-22 14:00:00) while time zone support is active.
RuntimeWarning)
警告和UTC有关,设置setting.py:USE_TZ=False即可消失。
如果你觉得2步麻烦,也可以合成一步:
>>> Event.objects.create(id=3,name='小米MAX10发布会', limit=2000,status=True,address='北京会展中心', start_time=datetime(2018,9,22,14,0,0))
>>> Guest.objects.create(realname='andy', phOne='13856785678', email='andy@email.com', sign=False, event_id=3)
通过发布会名字查询
>>> from sign.models import Event,Guest
>>> e1 = Event.objects.get(name='小米10发布会')
>>> e1
>>> e1.address
'北京市鸟巢'
>>> e1.start_time
datetime.datetime(2018, 8, 21, 2, 28, 57)
>>> Event.objects.get(name='小米10发布会').status
True
>>> Event.objects.get(name='小米10发布会').limit
200
>>> Event.objects.get(name='*发布会').address
Traceback (most recent call last):
File "
File "/Users/liujin/Documents/virtualenv3.7Demo/venv/lib/python3.7/site-packages/django/db/models/manager.py", line 82, in manager_method
return getattr(self.get_queryset(), name)(*args, **kwargs)
File "/Users/liujin/Documents/virtualenv3.7Demo/venv/lib/python3.7/site-packages/django/db/models/query.py", line 399, in get
self.model._meta.object_name
sign.models.Event.DoesNotExist: Event matching query does not exist.
# 模糊查询失败
>>> e2 = Event.objects.filter(name__cOntains='发布会')
>>> e2
>>>
# 模糊查询成功
# 这里的name和contains之间是双下划线
通过嘉宾查询
>>> g1 = Guest.objects.get(phOne='13856785678')
>>> g1.event
>>> g1.event.name
'小米MAX10发布会'
>>> g1.event.address
'北京会展中心'
>>>
查询到上面嘉宾信息,将它删除
# 第一种删除
>>> g2 = Guest.objects.get(phOne='13856785678')
>>> g2.delete()
(1, {'sign.Guest': 1})
# 第二种删除
>>> Guest.objects.get(phOne='15258800986').delete()
(1, {'sign.Guest': 1})
# 第一种更新
4.4 SQLite管理工具
>>> g3 = Guest.objects.get(phOne='15258800986')
>>> g3.realname='liujin2'
>>> g3.save()
>>> Guest.objects.all()
# 第二种更新
>>> Guest.objects.select_for_update().filter(phOne='15258800986').update(realname='liujin3')
1
>>> Guest.objects.all()
可视化的SQL工具可以方便地管理数据库。下面来介绍两款常用的管理工具:
这里介绍的是一款基于Firefox浏览器的插件。
插件地址:https://addons.mozilla.org/en-US/firefox/addon/sqlite-manager/
安装方式很简单,打开Firefox浏览器,单击菜单栏“工具”→“添加组件”,搜索“SQLiteManager”安装,并重新启动Firefox浏览器。在菜单栏“工具”下拉菜单中将会出现“SQLiteManager”选项。
打开如下图所示:
(目前组件文件损坏,无法添加)
这是一个跨平台的SQLite数据库的管理工具,采用Tcl语音开发,而且支持中文,是管理SQLite数据库的常用软件之一。
下载地址:http://sqlitestudio.pl/
找到guest项目下的数据库文件db.split3打开如下图所示:
SQLiteStudio.png
4.5 配置MySQLDjango默认使用的数据库是Python自带的SQLite3,SQLite3数据库并不适合用于大型的项目,除此之外,Django还支持以下几种数据库:
MySQL下载地址:http://dev.mysql.com/downloads/mysql/
liujindeMacBook-Pro:~ liujin$ mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.7.23
Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> SET PASSWORD = PASSWORD('123456');
Query OK, 0 rows affected, 1 warning (0.00 sec)
登录成功后重置密码:SET PASSWORD = PASSWORD(‘root’);
注意:
启动mysql时,出现如下错误:
ERROR! The server quit without updating PID file (/usr/local/mysql/data/gujingdeMacBook-Pro.local.pid).
可能的解决方式:
1、可能是/usr/local/mysql/data/数据目录mysql用户没有权限(修改数据目录的权限)
解决方法 :给予权限,执行 sudo chmod -R 777 /usr/local/mysql/data/ 然后重新启动mysql
2、快速启动、结束MySQL服务, 可以使用alias命令
alias mysqlstart=’sudo /usr/local/mysql/support-files/mysql.server start’
alias mysqlstop=’sudo /usr/local/mysql/support-files/mysql.server stop’
查看当前库和表
mysql> show databases; # 查看当前数据库下面的所有库
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)
mysql> use sys; # 切换到test库
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show tables; # 查看test库下面的表
+-----------------------------------------------+
| Tables_in_sys |
+-----------------------------------------------+
| host_summary |
| host_summary_by_file_io |
| host_summary_by_file_io_type |
| host_summary_by_stages |
| host_summary_by_statement_latency |
| host_summary_by_statement_type |
| innodb_buffer_stats_by_schema |
| innodb_buffer_stats_by_table |
| innodb_lock_waits |
| io_by_thread_by_latency |
| io_global_by_file_by_bytes |
| io_global_by_file_by_latency |
| io_global_by_wait_by_bytes |
| io_global_by_wait_by_latency |
| latest_file_io |
| memory_by_host_by_current_bytes |
| memory_by_thread_by_current_bytes |
| memory_by_user_by_current_bytes |
| memory_global_by_current_bytes |
| memory_global_total |
| metrics |
| processlist |
| ps_check_lost_instrumentation |
| schema_auto_increment_columns |
| schema_index_statistics |
| schema_object_overview |
| schema_redundant_indexes |
| schema_table_lock_waits |
| schema_table_statistics |
| schema_table_statistics_with_buffer |
| schema_tables_with_full_table_scans |
| schema_unused_indexes |
| session |
| session_ssl_status |
| statement_analysis |
| statements_with_errors_or_warnings |
| statements_with_full_table_scans |
| statements_with_runtimes_in_95th_percentile |
| statements_with_sorting |
| statements_with_temp_tables |
| sys_config |
| user_summary |
| user_summary_by_file_io |
| user_summary_by_file_io_type |
| user_summary_by_stages |
| user_summary_by_statement_latency |
| user_summary_by_statement_type |
| version |
| wait_classes_global_by_avg_latency |
| wait_classes_global_by_latency |
| waits_by_host_by_latency |
| waits_by_user_by_latency |
| waits_global_by_latency |
| x$host_summary |
| x$host_summary_by_file_io |
| x$host_summary_by_file_io_type |
| x$host_summary_by_stages |
| x$host_summary_by_statement_latency |
| x$host_summary_by_statement_type |
| x$innodb_buffer_stats_by_schema |
| x$innodb_buffer_stats_by_table |
| x$innodb_lock_waits |
| x$io_by_thread_by_latency |
| x$io_global_by_file_by_bytes |
| x$io_global_by_file_by_latency |
| x$io_global_by_wait_by_bytes |
| x$io_global_by_wait_by_latency |
| x$latest_file_io |
| x$memory_by_host_by_current_bytes |
| x$memory_by_thread_by_current_bytes |
| x$memory_by_user_by_current_bytes |
| x$memory_global_by_current_bytes |
| x$memory_global_total |
| x$processlist |
| x$ps_digest_95th_percentile_by_avg_us |
| x$ps_digest_avg_latency_distribution |
| x$ps_schema_table_statistics_io |
| x$schema_flattened_keys |
| x$schema_index_statistics |
| x$schema_table_lock_waits |
| x$schema_table_statistics |
| x$schema_table_statistics_with_buffer |
| x$schema_tables_with_full_table_scans |
| x$session |
| x$statement_analysis |
| x$statements_with_errors_or_warnings |
| x$statements_with_full_table_scans |
| x$statements_with_runtimes_in_95th_percentile |
| x$statements_with_sorting |
| x$statements_with_temp_tables |
| x$user_summary |
| x$user_summary_by_file_io |
| x$user_summary_by_file_io_type |
| x$user_summary_by_stages |
| x$user_summary_by_statement_latency |
| x$user_summary_by_statement_type |
| x$wait_classes_global_by_avg_latency |
| x$wait_classes_global_by_latency |
| x$waits_by_host_by_latency |
| x$waits_by_user_by_latency |
| x$waits_global_by_latency |
+-----------------------------------------------+
101 rows in set (0.00 sec)
查看MySQL端口号。
mysql> show global variables like 'port';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| port | 3306 |
+---------------+-------+
1 row in set (0.00 sec)
创建数据库。
mysql> CREATE DATABASE guest CHARACTER SET utf8;
Query OK, 1 row affected (0.00 sec)
创建guest库,用于我们接下来的项目。
要想用Python2或Python3开发Django项目,推荐使用PyMySQL驱动。
下载地址:https://pypi.org/project/PyMySQL/。
直接在终端安装:pip3 install PyMySQL。
下面写一个mysql_demo.py
from pymysql import cursors, connect
# 连接数据库
cOnn= connect(host='127.0.0.1',
user='root',
password='123456',
db='guest',
charset='utf8mb4',
cursorclass=cursors.DictCursor)
try:
with conn.cursor() as cursor:
# 创建嘉宾数据
sql = 'INSERT INTO sign_guest (realname, phone, email, sign, event_id, ' \
'creat_time) VALUES ("tom", 18800110002, "tom@email.com", 0, 1, NOW());'
cursor.execute(sql)
# 提交事务
conn.commit()
with conn.cursor() as cursors:
# 查询添加的嘉宾
sql = "SELECT realname, phone, eamil sign FROM sign_guest WHERE phOne=%s"
cursor.execute(sql, ('18800110002',))
result = cursor.fetchone()
print(result)
finally:
conn.close()
解释:
那么Django如何连接MySQL数据库呢?只要修改数据库配置settings.py文件即可。
......
DATABASES = {
'default': {
'ENGINE': 'django.db.backends.mysql',
'HOST': '127.0.0.1',
'PORT': '3306',
'NAME': 'guest',
'USER': 'root',
'PASSWORD': '123456',
'OPTIONS': {
'init_command': "SET sql_mode='STRICT_TRANS_TABLES'",
},
}
}
......
配置信息从上到下依次是驱动(ENGINE)、主机地址(HOST)、端口号(PORT)、数据库(NAME)、登录用户名(USER)和登录密码(PASSWORD)。
关于sql_mode的设置,请参考Django文档:
https://docs.djangoproject.com/en/1.10/ref/databases/#mysql-sql-mode
注意:切换了数据库之后,之前SQLite3数据库里的数据并不能复制到MySQL数据库中,所以需要重新执行数据库同步,使数据模型重新在MySQL数据库中生成表。
Traceback (most recent call last):
File "/Users/liujin/Documents/virtualenv3.7Demo/venv/lib/python3.7/site-packages/django/db/backends/mysql/base.py", line 15, in
import MySQLdb as Database
ModuleNotFoundError: No module named 'MySQLdb'
发现有报错!!这是因为Django在连接MySQL数据库时默认使用的是MySQLdb驱动,然而我们并没有安装MySQLdb驱动,前面已经说明它不支持Python 3,而现在使用的是PyMySQL驱动,如何让当前Django通过PyMySQL来连接MySQL数据库呢?
打开…/guest/__ init__.py文件,是一个空文件,添加如下配置:
import pymysql
pymysql.install_as_MySQLdb()
重新执行数据库同步。
(venv) liujindeMacBook-Pro:guest liujin$ python3 manage.py migrate
Operations to perform:
Apply all migrations: admin, auth, contenttypes, sessions, sign
Running migrations:
Applying contenttypes.0001_initial... OK
Applying auth.0001_initial... OK
Applying admin.0001_initial... OK
Applying admin.0002_logentry_remove_auto_add... OK
Applying admin.0003_logentry_add_action_flag_choices... OK
Applying contenttypes.0002_remove_content_type_name... OK
Applying auth.0002_alter_permission_name_max_length... OK
Applying auth.0003_alter_user_email_max_length... OK
Applying auth.0004_alter_user_username_opts... OK
Applying auth.0005_alter_user_last_login_null... OK
Applying auth.0006_require_contenttypes_0002... OK
Applying auth.0007_alter_validators_add_error_messages... OK
Applying auth.0008_alter_user_username_max_length... OK
Applying auth.0009_alter_user_last_name_max_length... OK
Applying sessions.0001_initial... OK
Applying sign.0001_initial... OK
因为跟换了数据库,所以Admin后台超级管理员账号也需要重新创建。
(venv) liujindeMacBook-Pro:guest liujin$ python3 manage.py createsuperuser
查看MySQL数据库,发现已经同步过来了。
mysql> show tables;
+----------------------------+
| Tables_in_guest |
+----------------------------+
| auth_group |
| auth_group_permissions |
| auth_permission |
| auth_user |
| auth_user_groups |
| auth_user_user_permissions |
| django_admin_log |
| django_content_type |
| django_migrations |
| django_session |
| sign_event |
| sign_guest |
+----------------------------+
12 rows in set (0.00 sec)
和SQLiteStudio里面的数据库表一致。
Navicat是一个强大的MySQL数据库管理和开发工具,它也是最常用的MySQL数据库管理工具之一。
Navicat Lite数据库管理工具.png
另外再推荐一个管理工具:SQLyog。这个也不错,增删改查很方便。
Mac 破解版下载地址:
https://pan.baidu.com/s/1cDAZ8a