《Web接口開發與自動化測試基于Python語言》–讀書筆記
第4章 Django模型
忽然意識到個嚴重問題，我這樣詳細的筆記，貌似有抄書的嫌疑，這樣大家都不買書了，怕被告盜版，是以從後續章節開始，我隻記錄比較重要的知識點，不再做詳細的抄書了，大家如果在閱讀的時候有疑問可以向蟲師請教，或留言給我，我們一起研究。
4.1 設計系統表
模型的基礎知識：
- 每個模型是一個Python類，繼承django.db.models.Model類
- 該模型的每個屬性表示一個資料庫表字段
- 所有這一切，已經給了你一個自動生成資料庫通路的API
本例所需的模型，修改/guest/sign/models.py檔案：
<span style="color:#000000"><code class="language-python"><span style="color:#880000">#! /usr/bin python</span>
<span style="color:#880000"># -*- coding:utf-8 -*-</span>
<span style="color:#000088">from</span> __future__ <span style="color:#000088">import</span> unicode_literals
<span style="color:#000088">from</span> django.db <span style="color:#000088">import</span> models
<span style="color:#880000"># Create your models here.</span>
<span style="color:#880000"># 釋出會表</span>
<span style="color:#000088">class</span> <span style="color:#4f4f4f">Event</span><span style="color:#4f4f4f">(models.Model)</span>:
name = models.CharField(max_length=<span style="color:#006666">100</span>) <span style="color:#880000"># 釋出會标題</span>
limit = models.IntegerField() <span style="color:#880000"># 參加人數</span>
status = models.BooleanField() <span style="color:#880000"># 狀态</span>
address = models.CharField(max_length=<span style="color:#006666">200</span>) <span style="color:#880000"># 位址</span>
start_time = models.DateTimeField(<span style="color:#009900">'events time'</span>) <span style="color:#880000"># 釋出會時間</span>
create_time = models.DateTimeField(auto_now=<span style="color:#000088">True</span>) <span style="color:#880000"># 建立時間（自動擷取目前時間）</span>
<span style="color:#000088">def</span> <span style="color:#009900">__str__</span><span style="color:#4f4f4f">(self)</span>:
<span style="color:#000088">return</span> self.name
<span style="color:#880000"># 嘉賓表</span>
<span style="color:#000088">class</span> <span style="color:#4f4f4f">Guest</span><span style="color:#4f4f4f">(models.Model)</span>:
event = models.ForeignKey(Event) <span style="color:#880000"># 外鍵，關聯釋出會id</span>
realname = models.CharField(max_length=<span style="color:#006666">64</span>) <span style="color:#880000"># 姓名</span>
phone = models.CharField(max_length=<span style="color:#006666">16</span>) <span style="color:#880000"># 電話</span>
email = models.EmailField() <span style="color:#880000"># 郵箱</span>
sign = models.BooleanField() <span style="color:#880000"># 簽到狀态</span>
create_time = models.DateTimeField(auto_now=<span style="color:#000088">True</span>) <span style="color:#880000"># 建立時間（自動擷取目前時間）</span>
<span style="color:#000088">class</span> <span style="color:#4f4f4f">Meta</span>:
unique_together = (<span style="color:#009900">"event"</span>, <span style="color:#009900">"phone"</span>)
<span style="color:#000088">def</span> <span style="color:#009900">__str__</span><span style="color:#4f4f4f">(self)</span>:
<span style="color:#000088">return</span> self.name</code></span>
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
注意：
- Model會為每個表自動生成自增id字段無需聲明；
- 嘉賓表通過event字段關聯釋出會表id字段，一條嘉賓資訊對應某一場釋出會；
- 通過釋出會表id+嘉賓表phone作為聯合主鍵，unique_together用于設定兩個字段為聯合主鍵；
- python3使用__str__()方法、python2使用__unicode__()方法，顯示對應字段。
Django模型字段常用類型
| 類型 | 說明 |
|---|---|
| AutoField | 一個IntegerField類型的自動增量 |
| BooleanField | 用于存放布爾類型的資料（True or False） |
| CharField | 用于存放字元型的資料，需要指定長度max_length |
| DateField | 用于存放日期類型的資料，格式為：YYYY-MM-DD |
| DecimalField | 用于存放小數型的資料 |
| EmailField | 用于存放電子郵件類型的資料 |
| FilePathField | 用于存放檔案路徑類型的資料 |
| FloatField | 用于存放浮點型的資料 |
| IntegerField | 用于存放整數類型的資料，範圍是：-2147483648至2147483647 |
| BigIntegerField | 用于存放大整數類型的資料，最大支援：9223372036854775807 |
| GenericIPAdressField | 用于存放IP位址類型的資料，同時支援IPv4、IPv6，字元串格式 |
| NullBooleanField | 類似BooleanField，但是允許填寫NULL |
| PositiveIntegerField | 用于存放正數或0的整數類型的資料，範圍是：0-2147483647 |
| PositiveSmallIntegerField | 類似PositiveIntegerField，但是範圍是：0-32767 |
| SlugField | Slug是短标簽，隻包含字母、數字、下劃線或字元，它通常在網址中使用，需要定義max_length |
| SmallIntegerField | 類似IntegerField，但是範圍是：-32768至32767 |
| TextField | 用于存放文本類型的資料 |
| TimeField | 用于存放時間類型的資料，格式為：HH:MM[:ss[.uuuuuu]] |
| URLField | 用于存放URL位址 |
| BinaryField | 用于存放原始二進制類型的資料 |
詳細内容可參考官方文檔：
https://docs.djangoproject.com/en/1.10/ref/models/fields/
使用如下指令進行資料庫表的初始化和建立：
python manage.py makemigrations sign
python manage.py migrate
4.2 admin背景管理
将上面建立的釋出會表、嘉賓表，也加入到admin背景進行圖形化的管理：
修改/guest/sign/admin.py檔案：
<span style="color:#000000"><code class="language-python"><span style="color:#000088">from</span> django.contrib <span style="color:#000088">import</span> admin
<span style="color:#000088">from</span> sign.models <span style="color:#000088">import</span> Event, Guest
<span style="color:#880000"># Register your models here.</span>
admin.site.register(Event)
admin.site.register(Guest)</code></span>
- 1
- 2
- 3
- 4
- 5
- 6
通過管理頁面随意增加一條釋出會記錄，我這裡在增加一條中文記錄的時候，出現了編碼錯誤的提示：
UnicodeEncodeError at /admin/sign/event/add/
看這意思是中文編碼問題，印象當中之前看Django官方文檔的時候，有關于中文編碼的問題，于是翻了下之前的筆記，發現是修改/guest/settings.py裡的MIDDLEWARE配置，增加一條：
django.middleware.locale.LocaleMiddleware
但是這裡将Django的admin管理背景的展示從英文變成了本地語言中文，對于上面遇到的錯誤還是沒有幫助，哎，不應該呀，蟲師都能在書中增加一條中文記錄，沒理由我這裡完全按照蟲師講解的一步一步操作過來的會遇到錯誤啊，仔細回頭檢視models.py發現了一個小問題，蟲師在書中也提到了隻是自己當時忽略了，蟲師使用的是python3，而我使用的是python2，在展示清單的函數裡，蟲師使用的是__str__，而我完全照搬書中的代碼，也使用了__str__，這裡就出錯了，如果是python2，應該使用__unicode__才對，修改代碼：
<span style="color:#000000"><code class="language-python"><span style="color:#880000"># 釋出會表</span>
<span style="color:#000088">class</span> <span style="color:#4f4f4f">Event</span><span style="color:#4f4f4f">(models.Model)</span>:
name = models.CharField(max_length=<span style="color:#006666">100</span>) <span style="color:#880000"># 釋出會标題</span>
limit = models.IntegerField() <span style="color:#880000"># 參加人數</span>
status = models.BooleanField() <span style="color:#880000"># 狀态</span>
address = models.CharField(max_length=<span style="color:#006666">200</span>) <span style="color:#880000"># 位址</span>
start_time = models.DateTimeField(<span style="color:#009900">'events time'</span>) <span style="color:#880000"># 釋出會時間</span>
create_time = models.DateTimeField(auto_now=<span style="color:#000088">True</span>) <span style="color:#880000"># 建立時間（自動擷取目前時間）</span>
<span style="color:#880000">#def __str__(self):</span>
<span style="color:#000088">def</span> <span style="color:#009900">__unicode__</span><span style="color:#4f4f4f">(self)</span>:
<span style="color:#000088">return</span> self.name</code></span>
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
再次添加中文記錄，成功，問題解決，後續對于不同版本使用的不同函數，還是要注意啊！
在管理背景清單中顯示更多字段：
修改/guest/sign/admin.py檔案：
<span style="color:#000000"><code class="language-python"><span style="color:#880000">#! /usr/bin python</span>
<span style="color:#880000"># -*- coding:utf-8 -*-</span>
<span style="color:#000088">from</span> django.contrib <span style="color:#000088">import</span> admin
<span style="color:#000088">from</span> sign.models <span style="color:#000088">import</span> Event, Guest
<span style="color:#880000"># Register your models here.</span>
<span style="color:#880000"># 在admin管理背景展示更多字段</span>
<span style="color:#000088">class</span> <span style="color:#4f4f4f">EventAdmin</span><span style="color:#4f4f4f">(admin.ModelAdmin)</span>:
list_display = [<span style="color:#009900">'id'</span>, <span style="color:#009900">'name'</span>, <span style="color:#009900">'status'</span>, <span style="color:#009900">'address'</span>, <span style="color:#009900">'start_time'</span>]
<span style="color:#000088">class</span> <span style="color:#4f4f4f">GuestAdmin</span><span style="color:#4f4f4f">(admin.ModelAdmin)</span>:
list_display = [<span style="color:#009900">'realname'</span>, <span style="color:#009900">'phone'</span>, <span style="color:#009900">'email'</span>, <span style="color:#009900">'sign'</span>, <span style="color:#009900">'create_time'</span>, <span style="color:#009900">'event'</span>]
<span style="color:#880000"># 在admin管理背景注冊釋出會表、嘉賓表</span>
admin.site.register(Event, EventAdmin)
admin.site.register(Guest, GuestAdmin)</code></span>
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
注意：
- EventAdmin繼承admin.ModelAdmin類，其中ModelAdmin提供了很多自定義管理工具；
- list_display數組内是顯示的字段名，必須存在于資料表中。
在管理背景增加搜尋欄、過濾器：
還是修改/guest/sign/admin.py檔案：
<span style="color:#000000"><code class="language-python"><span style="color:#880000">#! /usr/bin python</span>
<span style="color:#880000"># -*- coding:utf-8 -*-</span>
<span style="color:#000088">from</span> django.contrib <span style="color:#000088">import</span> admin
<span style="color:#000088">from</span> sign.models <span style="color:#000088">import</span> Event, Guest
<span style="color:#880000"># Register your models here.</span>
<span style="color:#000088">class</span> <span style="color:#4f4f4f">EventAdmin</span><span style="color:#4f4f4f">(admin.ModelAdmin)</span>:
list_display = [<span style="color:#009900">'id'</span>, <span style="color:#009900">'name'</span>, <span style="color:#009900">'status'</span>, <span style="color:#009900">'address'</span>, <span style="color:#009900">'start_time'</span>] <span style="color:#880000"># 在admin管理背景展示更多字段</span>
search_fields = [<span style="color:#009900">'name'</span>] <span style="color:#880000"># 在admin背景增加搜尋欄</span>
list_filter = [<span style="color:#009900">'status'</span>] <span style="color:#880000"># 在admin背景增加過濾器</span>
<span style="color:#000088">class</span> <span style="color:#4f4f4f">GuestAdmin</span><span style="color:#4f4f4f">(admin.ModelAdmin)</span>:
list_display = [<span style="color:#009900">'realname'</span>, <span style="color:#009900">'phone'</span>, <span style="color:#009900">'email'</span>, <span style="color:#009900">'sign'</span>, <span style="color:#009900">'create_time'</span>, <span style="color:#009900">'event'</span>]
search_fields = [<span style="color:#009900">'realname'</span>, <span style="color:#009900">'phone'</span>]
list_filter = [<span style="color:#009900">'sign'</span>]
<span style="color:#880000"># 在admin管理背景注冊釋出會表、嘉賓表</span>
admin.site.register(Event, EventAdmin)
admin.site.register(Guest, GuestAdmin)</code></span>
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
4.3 基本資料通路
通過如下方法對資料庫中的資料進行通路：
<span style="color:#000000"><code class="language-syslog">root<span style="color:#4f4f4f">@TEST</span><span style="color:#009900">:/home/test/guest</span><span style="color:#880000"># python manage.py shell</span>
<span style="color:#009900">Python</span> <span style="color:#006666">2.7</span>.<span style="color:#006666">12</span> (default, <span style="color:#009900">Nov</span> <span style="color:#006666">19</span> <span style="color:#006666">2016</span>, <span style="color:#006666">06</span><span style="color:#009900">:</span><span style="color:#006666">48</span><span style="color:#009900">:</span><span style="color:#006666">10</span>)
[<span style="color:#009900">GCC</span> <span style="color:#006666">5.4</span>.<span style="color:#006666">0</span> <span style="color:#006666">20160609</span>] on linux2
<span style="color:#009900">Type</span> <span style="color:#009900">"help"</span>, <span style="color:#009900">"copyright"</span>, <span style="color:#009900">"credits"</span> <span style="color:#000088">or</span> <span style="color:#009900">"license"</span> <span style="color:#000088">for</span> more information.
(<span style="color:#009900">InteractiveConsole</span>)
>>> from sign.models import <span style="color:#009900">Event</span>, <span style="color:#009900">Guest</span> <span style="color:#880000"># 導入sign應用下Model中的Event類、Guest類</span>
>>> <span style="color:#009900">Event</span>.objects.all() <span style="color:#880000"># 通過objects.all()擷取全部對象</span>
<<span style="color:#009900">QuerySet</span> [<<span style="color:#009900">Event</span><span style="color:#009900">:</span> 好友婚禮>]>
>>> <span style="color:#009900">Guest</span>.objects.all()
<<span style="color:#009900">QuerySet</span> [<<span style="color:#009900">Guest</span><span style="color:#009900">:</span> <span style="color:#009900">Guest</span> object>]>
>>> </code></span>
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
可以看到當我們查詢Event資料表裡的對象時，傳回内容是我們添加的記錄的name字段，而當我們查詢Guest表的對象時，傳回的卻是對象而不是具體的字段名，這是為什麼？
因為在/guest/sign/models.py檔案中，我們為Event類定義了傳回的字段名，但是Guest類沒有定義傳回的字段名，是以就造成了上述查詢結果的差異。
如果我們也想讓Guest在查詢的時候傳回一個字段名比如realname，而不是傳回一個對象object，隻需要同樣增加如下代碼：
<span style="color:#000000"><code class="language-python"><span style="color:#880000"># 嘉賓表</span>
<span style="color:#000088">class</span> <span style="color:#4f4f4f">Guest</span><span style="color:#4f4f4f">(models.Model)</span>:
……
<span style="color:#000088">def</span> <span style="color:#009900">__unicode__</span><span style="color:#4f4f4f">(self)</span>:
<span style="color:#000088">return</span> self.realname</code></span>
- 1
- 2
- 3
- 4
- 5
- 6
再次查詢Guest的結果就變為：
<span style="color:#000000"><code><span style="color:#006666">>>> </span>Guest.objects.all()
<QuerySet [<Guest: 好友>]>
<span style="color:#006666">>>> </span></code></span>
- 1
- 2
- 3
插入資料：
<span style="color:#000000"><code class="language-syslog">root<span style="color:#006666">@TEST:/home/test/guest# python manage.py shell</span>
Python <span style="color:#006666">2.7</span><span style="color:#006666">.12</span> (default, Nov <span style="color:#006666">19</span> <span style="color:#006666">2016</span>, <span style="color:#006666">06</span>:<span style="color:#006666">48</span>:<span style="color:#006666">10</span>)
[GCC <span style="color:#006666">5.4</span><span style="color:#006666">.0</span> <span style="color:#006666">20160609</span>] on linux2
Type <span style="color:#009900">"help"</span>, <span style="color:#009900">"copyright"</span>, <span style="color:#009900">"credits"</span> <span style="color:#000088">or</span> <span style="color:#009900">"license"</span> <span style="color:#000088">for</span> more information.
(InteractiveConsole)
<span style="color:#006666">>>> </span><span style="color:#000088">from</span> sign.models <span style="color:#000088">import</span> Event, Guest
<span style="color:#006666">>>> </span><span style="color:#000088">from</span> datetime <span style="color:#000088">import</span> datetime
<span style="color:#006666">>>> </span>e1 = Event(name=<span style="color:#009900">'周末聚會'</span>, limit=<span style="color:#006666">200</span>, status=<span style="color:#000088">True</span>, address=<span style="color:#009900">'北京'</span>, start_time=datetime(<span style="color:#006666">2017</span>,<span style="color:#006666">6</span>,<span style="color:#006666">10</span>,<span style="color:#006666">15</span>,<span style="color:#006666">0</span>,<span style="color:#006666">0</span>))
<span style="color:#006666">>>> </span>e1.save()
/usr/local/lib/python2<span style="color:#006666">.7</span>/dist-packages/django/db/models/fields/__init__.py:<span style="color:#006666">1430</span>: RuntimeWarning: DateTimeField Event.start_time received a naive datetime (<span style="color:#006666">2017</span>-<span style="color:#006666">06</span>-<span style="color:#006666">10</span> <span style="color:#006666">15</span>:<span style="color:#006666">00</span>:<span style="color:#006666">00</span>) <span style="color:#000088">while</span> time zone support <span style="color:#000088">is</span> active.
RuntimeWarning)
<span style="color:#006666">>>> </span></code></span>
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
這裡與書中略有差別，書中是在插入資料時，指定了id=2，實際上id是資料表中自增字段，插入資料的時候也不可能先去查下id現在是多少了，是以忽略該字段，直接插入資料也是可以的。
忽略結尾時間警告資訊： 修改/guest/settings.py檔案，設定USE_TZ=False
上面的步驟，先是建立資料，然後調用save()方法儲存至資料表中，也可以将兩步合為一步：
<span style="color:#000000"><code class="language-syslog">>>> <span style="color:#000088">Event</span>.objects.<span style="color:#000088">create</span>(id=<span style="color:#006666">3</span>, name=<span style="color:#009900">'紅米MAX釋出會'</span>, limit=<span style="color:#006666">200</span>, status=<span style="color:#000088">True</span>, address=<span style="color:#009900">'北京會展中心'</span>, start_time=datetime(<span style="color:#006666">2016</span>,<span style="color:#006666">9</span>,<span style="color:#006666">22</span>,<span style="color:#006666">14</span>,<span style="color:#006666">0</span>,<span style="color:#006666">0</span>))
<<span style="color:#000088">Event</span>: 紅米MAX釋出會>
>>> Guest.objects.<span style="color:#000088">create</span>(realname=<span style="color:#009900">'Andy'</span>, phone=<span style="color:#006666">13611001101</span>, email=<span style="color:#009900">'[email protected]'</span>, sign=<span style="color:#000088">False</span>, event_id=<span style="color:#006666">3</span>)
<Guest: Andy>
>>> </code></span>
- 1
- 2
- 3
- 4
- 5
通過指定Event的id=3、Guest的event_id=3，将兩者進行關聯。
查詢資料：
table.objects.create()：一步插入資料。
table.objects.get()：查詢一條比對的結果，傳回結果為對象，傳回結果不存在報DoesNotExist錯誤。
table.objects.filter()：查詢比對的結果，傳回結果為對象清單，傳回結果不存在傳回空清單[]。
<span style="color:#000000"><code class="language-syslog"><span style="color:#880000"># 使用get()查詢name=紅米MAX釋出會的記錄</span>
<span style="color:#006666">>>> </span>e1 = Event.objects.get(name=<span style="color:#009900">'紅米MAX釋出會'</span>)
<span style="color:#880000"># 傳回的結果e1是個對象</span>
<span style="color:#006666">>>> </span>e1
<Event: 紅米MAX釋出會>
<span style="color:#880000"># 從e1對象擷取start_time的值</span>
<span style="color:#006666">>>> </span>e1.start_time
datetime.datetime(<span style="color:#006666">2016</span>, <span style="color:#006666">9</span>, <span style="color:#006666">22</span>, <span style="color:#006666">14</span>, <span style="color:#006666">0</span>, tzinfo=<UTC>)
<span style="color:#880000"># 也可以不指派給e1，而是直接在get()方法後加上字段名來擷取對應的值</span>
<span style="color:#006666">>>> </span>Event.objects.get(name=<span style="color:#009900">'紅米MAX釋出會'</span>).status
<span style="color:#000088">True</span>
<span style="color:#880000"># 同理查詢紅米MAX釋出會的地點</span>
<span style="color:#006666">>>> </span>Event.objects.get(name=<span style="color:#009900">'紅米MAX釋出會'</span>).address
<span style="color:#009900">u'\u5317\u4eac\u4f1a\u5c55\u4e2d\u5fc3'</span>
<span style="color:#880000"># 如果查詢條件結果不存在則會報錯DoesNotExist</span>
<span style="color:#006666">>>> </span>Event.objects.get(name=<span style="color:#009900">'釋出會'</span>).address
Traceback (most recent call last):
File <span style="color:#009900">"<console>"</span>, line <span style="color:#006666">1</span>, <span style="color:#000088">in</span> <module>
File <span style="color:#009900">"/usr/local/lib/python2.7/dist-packages/django/db/models/manager.py"</span>, line <span style="color:#006666">85</span>, <span style="color:#000088">in</span> manager_method
<span style="color:#000088">return</span> getattr(self.get_queryset(), name)(*args, **kwargs)
File <span style="color:#009900">"/usr/local/lib/python2.7/dist-packages/django/db/models/query.py"</span>, line <span style="color:#006666">385</span>, <span style="color:#000088">in</span> get
self.model._meta.object_name
DoesNotExist: Event matching query does <span style="color:#000088">not</span> exist.
<span style="color:#880000"># 使用filter來過濾指定條件擷取對象清單，注意name和contains之間是雙下劃線</span>
<span style="color:#006666">>>> </span>e2 = Event.objects.filter(name__contains=<span style="color:#009900">'釋出會'</span>)
<span style="color:#880000"># 傳回結果e2是個結果清單</span>
<span style="color:#006666">>>> </span>e2
<QuerySet [<Event: 紅米MAX釋出會>]>
<span style="color:#880000"># 既然是清單就可以用如下方式通路其中資料</span>
<span style="color:#006666">>>> </span>e2[<span style="color:#006666">0</span>]
<Event: 紅米MAX釋出會>
<span style="color:#880000"># 關聯查詢示例，先使用get()方法擷取查詢結果對象</span>
<span style="color:#006666">>>> </span>g1 = Guest.objects.get(phone=<span style="color:#009900">'13611001101'</span>)
<span style="color:#880000"># 關聯查詢phone為136……嘉賓所對應的釋出會資訊event</span>
<span style="color:#006666">>>> </span>g1.event
<Event: 紅米MAX釋出會>
<span style="color:#880000"># 該嘉賓對應釋出會的名稱</span>
<span style="color:#006666">>>> </span>g1.event.name
<span style="color:#009900">u'\u7ea2\u7c73MAX\u53d1\u5e03\u4f1a'</span>
<span style="color:#880000"># 該嘉賓對應釋出會的位址</span>
<span style="color:#006666">>>> </span>g1.event.address
<span style="color:#009900">u'\u5317\u4eac\u4f1a\u5c55\u4e2d\u5fc3'</span>
<span style="color:#006666">>>> </span></code></span>
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
删除資料：
table.objects.get().delete()：擷取指定查詢條件的結果對象并删除。
擷取查詢對象，調用delete()方法進行删除：
<span style="color:#000000"><code class="language-syslog"><span style="color:#880000"># 先擷取查詢對象，再調用delete()方法删除</span>
<span style="color:#006666">>>> </span>g2 = Guest.objects.get(phone=<span style="color:#009900">'13611001101'</span>)
<span style="color:#006666">>>> </span>g2.delete()
(<span style="color:#006666">1</span>, {<span style="color:#009900">u'sign.Guest'</span>: <span style="color:#006666">1</span>})
<span style="color:#006666">>>> </span>
<span style="color:#880000"># 不可以重複删除</span>
<span style="color:#006666">>>> </span>Guest.objects.get(phone=<span style="color:#009900">'13611001101'</span>).delete()
Traceback (most recent call last):
File <span style="color:#009900">"<console>"</span>, line <span style="color:#006666">1</span>, <span style="color:#000088">in</span> <module>
File <span style="color:#009900">"/usr/local/lib/python2.7/dist-packages/django/db/models/manager.py"</span>, line <span style="color:#006666">85</span>, <span style="color:#000088">in</span> manager_method
<span style="color:#000088">return</span> getattr(self.get_queryset(), name)(*args, **kwargs)
File <span style="color:#009900">"/usr/local/lib/python2.7/dist-packages/django/db/models/query.py"</span>, line <span style="color:#006666">385</span>, <span style="color:#000088">in</span> get
self.model._meta.object_name
DoesNotExist: Guest matching query does <span style="color:#000088">not</span> exist.
<span style="color:#006666">>>> </span></code></span>
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
更新資料：
table.objects.select_for_update().filter().update()：過濾指定條件的對象清單并調用update()方法更新指定資料。
<span style="color:#000000"><code class="language-syslog"><span style="color:#880000"># 先擷取查詢對象，再更新指定資料，最後儲存至資料表</span>
<span style="color:#006666">>>> </span>g3 = Guest.objects.get(phone=<span style="color:#009900">'13611001101'</span>)
<span style="color:#006666">>>> </span>g3.realname = <span style="color:#009900">'Andy2'</span>
<span style="color:#006666">>>> </span>g3.save()
<span style="color:#006666">>>> </span>
<span style="color:#880000"># 直接使用select_for_update方法過濾指定條件對象清單再調用update方法更新資料并儲存至資料表</span>
<span style="color:#006666">>>> </span>Guest.objects.select_for_update().filter(phone=<span style="color:#009900">'13611001101'</span>).update(realname=<span style="color:#009900">'Andy2'</span>)
<span style="color:#006666">1</span>
<span style="color:#006666">>>> </span></code></span>
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
4.4 SQLite管理工具
Pass
4.5 配置MySQL
安裝MySQL
Pass
安裝MySQL-Python驅動
如果使用Python2版本：安裝MySQL-Python
如果使用Python3版本：安裝PyMySQL
MySQL基本操作
- show databases; # 檢視目前資料庫下面的所有庫
- use test; # 切換到test庫
- show tables; #檢視test庫下面所有表
- show global variables like ‘port’; #檢視MySQL端口号
- CREATE DATABASE guest CHARACTER SET utf8; #建立guest資料庫
書中使用如下代碼示範了如何通過pymysql庫對資料庫進行操作，插入資料，但是書中忽略了一個重要前提，我們從開始建立Django項目的時候，就使用的是SQLite3資料庫，并沒有用MySQL進行過任何操作，這如果直接按代碼執行，隻會提示錯誤沒有guest表，對啊，是沒有啊，上面隻是在基本操作裡，建立了guest庫，但是沒有在guest庫下建立表，是以在執行插入資料的代碼之前，我們應該先建立兩個表：sign_event、sign_guest
<span style="color:#000000"><code class="language-sql"># 建立sign_event表
<span style="color:#000088">CREATE</span> <span style="color:#000088">TABLE</span> <span style="color:#009900">`sign_event`</span> (
<span style="color:#009900">`id`</span> <span style="color:#000088">int</span>(<span style="color:#006666">11</span>) <span style="color:#000088">NOT</span> <span style="color:#000088">NULL</span> AUTO_INCREMENT,
<span style="color:#009900">`name`</span> <span style="color:#000088">varchar</span>(<span style="color:#006666">100</span>) <span style="color:#000088">NOT</span> <span style="color:#000088">NULL</span>,
<span style="color:#009900">`limit`</span> <span style="color:#000088">integer</span>,
<span style="color:#009900">`status`</span> BOOLEAN,
<span style="color:#009900">`address`</span> <span style="color:#000088">varchar</span>(<span style="color:#006666">200</span>) <span style="color:#000088">NOT</span> <span style="color:#000088">NULL</span>,
<span style="color:#009900">`start_time`</span> DATETIME,
<span style="color:#009900">`create_time`</span> DATETIME <span style="color:#000088">DEFAULT</span> <span style="color:#000088">CURRENT_TIMESTAMP</span>,
<span style="color:#000088">PRIMARY</span> <span style="color:#000088">KEY</span> (<span style="color:#009900">`id`</span>)
)ENGINE=InnoDB <span style="color:#000088">DEFAULT</span> CHARSET=utf8;
<span style="color:#000088">alter</span> <span style="color:#000088">table</span> <span style="color:#009900">`sign_event`</span> <span style="color:#000088">add</span> index id(id);</code></span>
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
<span style="color:#000000"><code class="language-sql"># 建立sign_guest表
<span style="color:#000088">CREATE</span> <span style="color:#000088">TABLE</span> <span style="color:#009900">`sign_guest`</span> (
<span style="color:#009900">`id`</span> <span style="color:#000088">int</span>(<span style="color:#006666">11</span>) <span style="color:#000088">NOT</span> <span style="color:#000088">NULL</span> AUTO_INCREMENT,
<span style="color:#009900">`event_id`</span> <span style="color:#000088">int</span>(<span style="color:#006666">11</span>) <span style="color:#000088">NOT</span> <span style="color:#000088">NULL</span>,
<span style="color:#009900">`realname`</span> <span style="color:#000088">varchar</span>(<span style="color:#006666">64</span>) <span style="color:#000088">NOT</span> <span style="color:#000088">NULL</span>,
<span style="color:#009900">`phone`</span> <span style="color:#000088">varchar</span>(<span style="color:#006666">16</span>) <span style="color:#000088">NOT</span> <span style="color:#000088">NULL</span>,
<span style="color:#009900">`email`</span> <span style="color:#000088">varchar</span>(<span style="color:#006666">254</span>) <span style="color:#000088">NOT</span> <span style="color:#000088">NULL</span>,
<span style="color:#009900">`sign`</span> BOOLEAN,
<span style="color:#009900">`create_time`</span> DATETIME <span style="color:#000088">DEFAULT</span> <span style="color:#000088">CURRENT_TIMESTAMP</span>,
<span style="color:#000088">PRIMARY</span> <span style="color:#000088">KEY</span> (<span style="color:#009900">`id`</span>),
<span style="color:#000088">FOREIGN</span> <span style="color:#000088">KEY</span> (<span style="color:#009900">`event_id`</span>) <span style="color:#000088">REFERENCES</span> sign_event(id)
)ENGINE=InnoDB <span style="color:#000088">DEFAULT</span> CHARSET=utf8;
<span style="color:#000088">alter</span> <span style="color:#000088">table</span> <span style="color:#009900">`sign_guest`</span> <span style="color:#000088">add</span> index id(id);
<span style="color:#000088">alter</span> <span style="color:#000088">table</span> <span style="color:#009900">`sign_guest`</span> <span style="color:#000088">add</span> <span style="color:#000088">unique</span>(event_id, phone);</code></span>
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
<span style="color:#000000"><code class="language-python"><span style="color:#880000">#! /usr/bin python</span>
<span style="color:#880000"># -*- coding: utf-8 -*-</span>
<span style="color:#000088">from</span> pymysql <span style="color:#000088">import</span> cursors, connect
<span style="color:#880000"># 連接配接資料庫</span>
conn = connect(host=<span style="color:#009900">'127.0.0.1'</span>,
user=<span style="color:#009900">'root'</span>,
password=<span style="color:#009900">'nsfocus'</span>,
db=<span style="color:#009900">'guest'</span>,
charset=<span style="color:#009900">'utf8mb4'</span>,
cursorclass=cursors.DictCursor)
<span style="color:#000088">try</span>:
<span style="color:#000088">with</span> conn.cursor() <span style="color:#000088">as</span> cursor:
<span style="color:#880000"># 建立釋出會資料</span>
sql = <span style="color:#009900">'INSERT INTO sign_event (name, `limit`, status, address, start_time, create_time) VALUES ("紅米MAX釋出會", 200, 1, "北京會展中心", "2016-09-22 14:00:00", NOW());'</span>
cursor.execute(sql)
<span style="color:#880000"># 送出事務</span>
conn.commit()
<span style="color:#880000"># 建立嘉賓資料</span>
sql = <span style="color:#009900">'INSERT INTO sign_guest (realname, phone, email, `sign`, event_id, create_time) VALUES ("Tom", 18800110002, "[email protected]", 0, 1, NOW());'</span>
cursor.execute(sql)
<span style="color:#880000"># 送出事務</span>
conn.commit()
<span style="color:#000088">with</span> conn.cursor() <span style="color:#000088">as</span> cursor:
<span style="color:#880000"># 查詢剛剛添加的嘉賓</span>
sql = <span style="color:#009900">'SELECT realname, phone, email, sign FROM sign_guest WHERE phone=%s'</span>
cursor.execute(sql, (<span style="color:#009900">'18800110002'</span>,))
result = cursor.fetchone()
<span style="color:#000088">print</span> result
<span style="color:#000088">finally</span>:
conn.close()</code></span>
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
在這個過程中遇到一些問題：
我們安裝了MySQL資料庫後，雖然建立了guest資料庫，但是并沒有建立釋出會表、嘉賓表，是以如果按書中直接執行此py檔案，一定會報錯，提示guest表不存在！！！
如何解決這個問題，很簡單，參考之前models.py檔案裡的設定，先建立好兩個資料表event、guest，再來執行這個py檔案就不會出錯了！
引申出兩個問題：
- 如何使MySQL服務端不隻是監聽本地127.0.0.1這個位址？
- 這就需要修改配置檔案/etc/mysql/mysql.conf.d/mysqld.cnf，将其中的bind-address由127.0.0.1修改為0.0.0.0，重新開機MySQL服務即可/etc/init.d/mysql restart；
- 如果通過遠端來通路MySQL服務端？
- 對MySQL實在不熟，發現用起來很多基礎知識都不懂，查了好半天的資料才找到問題根本，原本以為在配置檔案裡修改各什麼安全設定就能搞定，然而并不是這麼回事，這是由于root使用者在mysql資料庫中存儲的host字段的限制；
- 首先在本地登入MySQL資料庫：mysql -u root -p，然後切換至mysql資料庫：use mysql;，檢視user表中root帳号的記錄：select * from user where user=’root’;，可以發現host字段的值為localhost，這就是為什麼遠端主機無法使用root帳号來通路的原因了；
- 解決辦法就是給host字段增加遠端主機的IP：grant all privileges on *.* to root@”遠端主機IP位址” identified by “root帳号對應的密碼”;，這就相當于給遠端IP賦予了所有的權限，包括遠端通路的權限，然後輸入：flush privileges;，這相當于是重新加載一下mysql權限；
- 至此，就又可以從本地通路也能從遠端主機通路了。
pymysql裡的幾個重要函數：
- connect()：建立資料庫連接配接；
- cursor()：擷取資料庫操作遊标；
- execute()：執行SQL語句；
- commit()：送出資料庫執行；
- close()：關閉資料庫連接配接；
在Django中配置MyDQL
修改配置檔案/guest/settings.py：
<span style="color:#000000"><code class="language-python">DATABASES = {
<span style="color:#009900">'default'</span>: {
<span style="color:#009900">'ENGINE'</span>: <span style="color:#009900">'django.db.backends.mysql'</span>,
<span style="color:#009900">'HOST'</span>: <span style="color:#009900">'127.0.0.1'</span>,
<span style="color:#009900">'PORT'</span>: <span style="color:#009900">'3306'</span>,
<span style="color:#009900">'NAME'</span>: <span style="color:#009900">'guest'</span>,
<span style="color:#009900">'USER'</span>: <span style="color:#009900">'root'</span>,
<span style="color:#009900">'PASSWORD'</span>: <span style="color:#009900">'123456'</span>,
<span style="color:#009900">'OPTIONS'</span>: {
<span style="color:#009900">'init_command'</span>: <span style="color:#009900">"SET sql_mode='STRICT_TRANS_TABLES'"</span>,
},
}
}</code></span>
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
注意：需要先把原資料庫注釋掉。
蟲師的是在本章節，才講解了如何将SQLite3裡的資料同步至MySQL裡，因為并不能這樣複制資料，但是可以使用：
python manage.py migrate
指令來重新執行資料庫同步，是資料模型重新在MySQL資料庫中生成表。這也就是上面我提到的蟲師缺少的部分，隻不過我上面是用笨方法手工重建MySQL的資料表，而這裡是使用資料同步指令。
但是在執行此指令的時候也會遇到問題，即：“如何讓Django通過PyMySQL來連接配接資料庫”？
需要在/guest/__init__.py中加入如下代碼：
<span style="color:#000000"><code class="language-python"><span style="color:#000088">import</span> pymysql
pymysql.install_as_MySQLdb()</code></span>
- 1
- 2
- 3
再次執行migrate指令就不會出錯了，由于更換了MySQL資料庫，是以admin背景的超級管理者的賬号也要重建。
MySQL管理工具
- Navicat
- SQLyog
至此第4章的全部内容都結束了。本章的内容主要圍繞Django的Model層，也就是資料層做簡要介紹。主要知識點就是model.py以及資料在管理背景admin.py裡如何展示。
![HttpRunner 使用注意點[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)