一句話概括本文：
內容較多，建議先mark後看，講解了一波MySQL安裝，基本操作，語法速成，DataGrip使用， 連結遠端資料庫問題，爬取Gank.io API介面，儲存到資料，還有遇到的三個問題。
引言：
失蹤人口迴歸，工種從開發變成了打雜後，供自己學習和寫文章的時間也沒以前充裕了， 大部分時間都在處理一些瑣事，唉...在學習Python以後，要處理什麼問題，我第一個想起 的都是它。比如前段時間在玩的微信小遊戲 "萌犬變變變"，前身是網頁版的，只是最近遷移 到了小程式上。
遊戲大概玩法如下：
- 每隔一段時間，天上會掉下一個紙箱，使用者需要用手點開；
- 點開後會有一隻狗子，同樣的狗子可以合併成一隻更高階的狗子；
- 除了點箱子的方式手機狗，還可以直接在犬社用金幣買狗合成；
- 一個星球裡所有狗都集齊了，可以飛到下一個星球，繼續合成不同的狗；
遊戲的賣點：通過合成解鎖各種各樣有趣的狗子。
這樣的小遊戲看上去並不複雜，但是吸金數絕不可小覷，像我這種貧民玩家也 氪了30買月卡，然後排名5W開外：
站在個人視角分析下這個遊戲火的原因：
- 1.很多使用者都有輕度的收集癖，會不遺餘力地收集虛擬物品，以讓自己獲得更多 的滿足感，數目越多，會覺得越刺激；
- 2.誘導社交分享，通過遊戲裡輪盤抽獎，分享獲得兩倍收益的套路，以及各種分享 其他人點開能獲得鑽石，體力之類的套路；使得他在各種群聊擴散，還被微信禁止誘導分享過， 後來很快解封了，不知道是不是裡面有什麼py交易。
- 3.使用者惰性，通過點紙箱子的方式獲取狗子，後面升級科技滿以後，每1s重新整理一個箱子， 你覺得使用者會一直點麼？使用者都是比較懶的，自動點箱子只需花30買，除了自動點還送鑽石， 每天300鑽石，這裡又是一個套路，你需要登入才能領取，遊戲又保證了使用者日活。 絕大部分使用者都會為這個便利買單，因為30真的不貴，殊不知掉入了更深的坑；
- 4.如果你以為你花30買了個會員，掛著等狗子自動合成美滋滋，那你這是太naive了。我們 來算個數，比如合成2級的狗需要兩隻1級的狗，合成3級的狗需要4只1級的狗，算式就是2^(級別-1)， 然後呢，每個星球有四個場景，每個場景有6種狗，最後的宇宙場景還要合成4次，計算下就是 通關一個星球你需要2^(6*4+4-1) = 2^27只一級狗，按照最快每1s生成一隻狗的速度，你需要： 134217728秒=37282小時=1553天=4.25年，So，你會連續掛4年麼？所以後面的合成基本都是靠 買狗，而且狗的價格不是恆定的，越買越貴，所以繼續氪金吧，少年。
- 5.排行榜，刺激土豪使用者虛榮心，資料庫生成幾個牛逼的賬號霸榜，誘導前排玩家氪更多錢；
- 6.貧民玩家也不拋棄，通過輪盤或者各種任務可獲得鑽石，以此維持遊戲日活。
以上就是個人的一些愚見，都差點忘記這個是個開發仔了，說回程式把，關於這個程式， 之前想到用Python做的兩件事：
自動點箱子：
裁剪紙箱的頂部小角，通過adb命令每隔一秒截圖，利用opencv進行模板匹配，獲取圖片 中頂部小角的座標陣列，adb命令模擬點選；
輪盤自動點選：
先把輪盤的每種結果的執行流程都捋下，點哪裡，跳那裡，是否設定延時等，接著利用 adb截圖，利用ocr圖片識別關鍵字，比如'分享'自動關掉，'偷竊'點確定等。
因為覺得沒什麼意思，都不玩了，就沒去整程式了，大概思路就是上面這樣。 說這麼多，只是想說明Python，真香。 嗯，扯得有點遠了，前面的章節學爬蟲，抓取到的資料存過txt，Excel和csv， 這三種對於非開發者來說挺友好的，對於開發仔來說，不存下資料庫就說不過了。
資料庫又分為兩種：關係型資料庫 和 非關係型資料庫， 前者是基於關係模型的資料庫，多個二維表通過表與表間的關聯關係來組成一個資料庫； 後者NoSQL是基於鍵值對的，資料間沒有耦合性，非常高效；
本節使用的就是關係型資料庫裡的MySQL，相信很多童鞋都知道這個東東了， 編寫一波gank.io的爬蟲，爬取所有資料存起來，後續用Flask自己寫介面玩玩～
1.MySQL安裝
環境：阿里雲伺服器 Ubuntu 16.04
依次鍵入下述命令安裝MySQL：
# 安裝MySQL服務，輸入Y後，如圖會讓你輸入密碼，重複輸入確認
sudo apt-get install mysql-server
# 安裝MySQL客戶端
sudo apt-get install mysql-client
# 安裝libmysqlclient，輸入Y
sudo apt-get install libmysqlclient-dev
複製程式碼安裝完後鍵入下述命令驗證是否安裝成功
sudo netstat -tap | grep mysql
複製程式碼2.MySQL基本操作
使用者登入
# 回車後，需要輸入在安裝那裡設定的密碼
mysql -u root -p
複製程式碼檢視資料庫
show databases;
複製程式碼選擇資料庫
use 資料庫名
複製程式碼檢視資料庫裡的所有表
show tables;
複製程式碼停止，開始和重啟MySQL服務
# 開始服務
/etc/init.d/mysql stop
# 停止服務
/etc/init.d/mysql start
# 重啟服務
/etc/init.d/mysql restart
複製程式碼基本的操作就這些，對於資料庫的相關操作，再進入資料庫 後就可以通過資料庫語句完成相關操作了。
3.MySQL資料庫語法速成
MySQL資料型別
# 整型（取值範圍如果加了unsigned，則最大值翻倍）
TINYINT(m) 1個位元組 範圍（-128~127）；
SMALLINT(m) 2個位元組 範圍（-32768~32767）；
MEDIUMINT(m) 3個位元組 範圍（-8388608~8388607）；
INT(m) 4個位元組 範圍（-2147483648~2147483647）；
BIGINT(m) 8個位元組 範圍（+-9.22*10的18次方）；
# 浮點型
FLOAT(m,d) 單精度浮點型 8位精度(4位元組) m總個數，d小數位；
DOUBLE(m,d) 雙精度浮點型 16位精度(8位元組) m總個數，d小數位；
# 字串
# 1.char(n)若存入字元小於n，以空格補齊後面，查詢時再將空格去掉，所以char型別儲存
# 的字串末尾不能有空格。
# 2.char(n)固定長度，不管存幾個字元，都佔用n個位元組
# 3.varchar(n)可變長度，存入的實際字元數+1個位元組（n<=255）或2個位元組(n>255)
# 4.char型別的字串檢索速度要比varchar型別的快
# 5.text型別不能有預設值，varchar查詢速度快於text
CHAR(n) 固定長度，最多255個字元；
VARCHAR(n) 可變長度，最多65535個字元；
TINYTEXT 可變長度，最多255個字元；
TEXT 可變長度，最多65535個字元；
MEDIUMTEXT 可變長度，最多2的24次方-1個字元；
LONGTEXT 可變長度，最多2的32次方-1個字元；
# 二進位制資料
_BLOB 以二進位制方式儲存，不分大小寫，不用指定字符集，只能整體讀出；
_TEXT 以文字方式儲存，英文儲存區分大小寫，可以指定字符集；
# 日期時間型別
DATE 日期
TIME 時間
DATETIME 日期時間
TIMESTAMP 自動儲存記錄修改時間
複製程式碼資料型別的屬性
NULL 資料列可包含NULL值
NOT NULL 資料列不允許包含NULL值
DEFAULT 預設值
PRIMARY KEY 主鍵
AUTO_INCREMENT 自動遞增，適用於整數型別
UNSIGNED 無符號
CHARACTER SET name 指定一個字符集
複製程式碼庫操作相關
# 建庫
CREATE DATABASE 資料庫名;
# 刪庫（刪除資料庫無法恢復！！！），刪除不存在的庫會報
# database doesn't exist的錯誤，故先用IF EXISTS判斷下。
DROP DATABASE IF EXISTS 資料庫名;
複製程式碼表操作相關
# 建表，比如
CREATE TABLE test
(
_id VARCHAR(50) NOT NULL PRIMARY KEY,
dsec TEXT NULL,
images TEXT NULL,
url TEXT NULL,
type VARCHAR(50) DEFAULT '' NULL
);
# 清空表資料，整體刪除，速度較快，會重置Identity（標識列、自增欄位）
TRUNCATE 表名
# 刪除表中資料，逐條刪除，速度較慢，不會重置Identity，配合WHERE關鍵字可以刪除部分
DELETE FROM 表名
# 刪表
DROP TABLE 表名
# 重新命名錶
ALTER TABLE 原表名 RENAME 新表名;
RENAME TABLE 原表名 TO 新表名;
# 增加列
ALTER TABLE 表名 Add column 新欄位 資料型別 AFTER 在哪個欄位後新增
# 刪除列
ALTER TABLE 表名 DROP 欄位名;
# 重新命名列/資料型別
ALTER TABLE 表名 CHANGE 原列名 新列名 資料型別;
# 增加主鍵
ALTER TABLE 表名 ADD PRIMARY KEY (主鍵名);
# 刪除主鍵
ALTER TABLE 表名 DROP PRIMARY KEY;
# 新增唯一索引
ALTER TABLE 表名 ADD UNIQUE 索引名 (列名);
# 新增普通索引
ALTER TABLE 表名 ADD INDEX 索引名 (列名);
# 刪除索引
ALTER TABLE 表名 DROP INDEX 索引名;
# 把表預設的字符集和所有字元列（CHAR, VARCHAR, TEXT）改為新的字符集:
ALTER TABLE 表名 CONVERT TO CHARACTER SET utf8;
# 修改表某一列的編碼
ALTER TABLE 表名 CHANGE 列名 varchar(255) CHARACTER SET utf8;
# 僅僅改變一個表的預設字符集
ALTER TABLE 表名 DEFAULT CHARACTER SET utf8;
複製程式碼增刪改查(INSERT，DELETE，UPDATE，SELECT)
關鍵詞就上面幾個，通過一個完整示例來快速上手MySQL
# 建新資料庫
CREATE DATABASE test
# 新建一個表person，欄位有(自增id，名字，年齡，性別)
CREATE TABLE person(
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(30) NOT NULL DEFAULT '',
age INT,
sex CHAR(2)
);
# 往表中插入5條資料
INSERT INTO person (name, age, sex) VALUES ('小明', 8, '男');
INSERT INTO person (name, age, sex) VALUES ('小紅', 14, '女');
INSERT INTO person (name, age, sex) VALUES ('小白', 4, '男');
INSERT INTO person (name, age, sex) VALUES ('小寶', 6, '男');
INSERT INTO person (name, age, sex) VALUES ('小莉', 16, '女');
# 更新表中資料(不新增WHERE子句篩選，更新的會是整個表的某列)
UPDATE person SET age = 10, sex = '女' WHERE name = '小明'；
# 往表裡插入資料，如果某自動已存在則更新資料
INSERT INTO person (id,name, age, sex) VALUES (1,'小明', 20, '男') ON DUPLICATE KEY UPDATE age = '20'；
# 刪除特定記錄
DELETE FROM person WHERE age < 10;
# 查詢資料
SELECT * FROM person; #查詢所有資料
SELECT name,age FROM person; #查詢特定列
SELECT name AS '姓名',age AS '年齡'FROM person; #為檢索出來的列設定別名
SELECT name FROM person WHERE age > 15 AND age <=20; # 條件查詢
SELECT name FROM person WHERE age BETWEEN 15 AND 20; # 範圍查詢
# 資料求總和,平均值,最大,最小值,記錄數
SELECT SUM(age),AVG(age), MAX(age),MIN(age), COUNT(age) FROM person;
# 查詢的時候排序：升序(ASC)，降序(DESC)
SELECT * FROM person ORDER BY age ASC;
複製程式碼事務
BEGIN # 開始一個事務
COMMIT # 事務確認
ROLLBACK # 事務回滾
複製程式碼關於MySQL的基本語法就到這裡，本節夠用了，其他的後續用到再講～
4.資料庫圖形化工具——DataGrip
一般來講資料庫操作很少寫命令，基本都會依賴一些圖形化工具來提高效率， 關於MySQL的圖形化工具，網上貌似挺多的，大部分用的貌似是Navicat for mysql (我司後臺用的就是這個)，不過我還是選擇了idea全家桶裡的DataGrip，沒有為什麼...
建立資料庫關聯
依次點選 New -> DataSource -> MySQL 如圖依次配置下Host，Database，User，Password，然後Test Connection測試是否 連線成功，成功的話點選Ok粗體文字。
本地是這樣，如果你的資料庫不在本機而是在雲伺服器上，就要另外折騰了。
設定mysql允許遠端訪問
mysql預設是不允許遠端訪問的，筆者用的是阿里雲的伺服器，在連線遠端倉庫 的時候也遇到一些問題，順帶記錄下，方便後來者。(下述操作發生在伺服器上已經安裝了mysql環境後！)
Step 1：雲伺服器開啟安全組裡的3306埠
Step 2：停止mysql服務
/etc/init.d/mysql stop
複製程式碼Step 3：修改my.cnf檔案，註釋掉bind-address = 127.0.0.1，鍵入sq儲存退出；
vim /etc/mysql/my.cnf
複製程式碼Step 4：啟動mysql服務
/etc/init.d/mysql start
複製程式碼Step 5：輸入下述命令檢視當前3306埠的狀態
netstat -an|grep 3306
複製程式碼Step 6：修改訪問許可權
mysql -u root -p # 使用者登入
use mysql; # 選中mysql資料庫
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY '密碼' WITH GRANT OPTION; # 授權
FLUSH PRIVILEGES; # 更新許可權
EXIT # 退出mysql
複製程式碼PS：上面設定的結果是所有ip都能訪問資料庫，如需指定特定ip才能訪問的話，
可以把**'@'%**改成特定ip。還有這裡用的是root賬戶，你可以通過下述命令
建立一個新的使用者，然後用這個使用者進行訪問，可以由此做一些許可權控制操作。
CREATE USER 新使用者 IDENTIFIED BY '密碼';
GRANT ALL PRIVILEGES ON *.* TO '新使用者'@'%' IDENTIFIED BY '密碼' WITH GRANT OPTION; # 授權
FLUSH PRIVILEGES;
複製程式碼Step 7：連線遠端mysql
這裡用的是DataGrip進行連線，右鍵，new -> Data Source -> MySQL 這裡要先配置SSH或者SSL，埠預設是22
再接著配置遠端資料庫相關，埠3306
配置完後點選Test Connection成功後，點選OK即可。
中途如果出現了異常，比如SSH Auth ERROR可能就是SSH密碼錯誤；
除此之外的MySQL異常或問題可自行查閱：
雲伺服器 ECS Linux MySQL 無法遠端連線問題常見錯誤及解決辦法
5.編寫爬蟲程式
準備得差不多了，接著來編寫爬蟲程式了，因為程式碼家已經提供了 API介面，這裡就不一個個網頁爬取了，直接抓介面。
分析下介面： 有六種不同型別的資料：Android, iOS, 休息視訊, 福利, 擴充資源, 前端, 瞎推薦, App 然後每個介面取五個需要的欄位：_id, dsec, images, url, type
所以要做的第一件事：迴圈建表
接著定義一個Gank類
再接著定義一個網資料庫裡插入資料的函式(引數是一個gank物件列表)：
再定義一個爬取介面資料的方法
接著main函式呼叫下，
執行等待程式抓取完成，完成後可以直接程式碼查詢:
或者直接通過DataGrip檢視：
6.遇到的三個問題
欄位長度不夠
介面返回的資料裡有些欄位比如標題和URL巨長，一開始用了varchar(250)的， 報錯提示某列什麼錯誤，後來就全改成TEXT了。
特殊符號和表情問題
因為有些標題裡包含特殊符號和表情，在插入資料的時候報錯，大概是這樣的： Incorrect string value: '\xF0\x9F...' for column 'XXX' at row 1
原因是：UTF-8編碼有可能是兩個、三個、四個位元組。Emoji表情或者某些特殊字元是4個位元組，
而Mysql的utf8編碼最多3個位元組，所以資料插不進去。MySQL在5.5.3版本之後增加了
utf8mb4的編碼，專門用來相容四位元組的unicode。理論上將字符集修改為utf8mb4
不會對已有的utf8編碼讀取產生任何問題。官方解釋：
10.9.1 The utf8mb4 Character Set (4-Byte UTF-8 Unicode Encoding)
解決過程：
Step 1：開啟終端，鍵入：locale my.cnf 定位到檔案位置(window下是my.ini)：
Step 2：vim etc/mysql/my.cnf 追加下述內容，wq儲存：
[mysqld]
character-set-server=utf8mb4
[client]
default-character-set=utf8mb4
[mysql]
default-character-set=utf8mb4
複製程式碼Step 3：重啟MySQL伺服器
Step 4：進入mysql，然後鍵入**show variables like '%character%';**確認設定是否生效
Step 5：更改資料庫，表，列編碼
ALTER DATABASE 資料庫名 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;
ALTER TABLE 表名 CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;
ALTER TABLE 表名 CHANGE 列名 VARCHAR(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;
複製程式碼到此問題就解決了，此時開啟資料庫表可以看到對應記錄已存入，不過是顯示成問號 的形式，讀取到資料顯示到支援emoji表情的頁面上就可以了，比如手機。
DataGrip只能存500條記錄？
開啟一個表看到裡面的資料只有500條，試了幾次還是這樣，以為DataGrip只能儲存500條資料， 後來發現這裡有個501+，分頁，so，點選右面那個類似於播放的按鈕就可以切換區間了！
小結
開頭扯了一下犢子，接著詳細講解了一波MySQL相關的東西，接著寫了一波簡單爬蟲 爬取gank.io，儲存資料的方式又新增了資料庫一種～
參考文獻：
附：最終程式碼(都可以在：github.com/coder-pig/R… 找到)：
# 抓取Gank.io所有文章的爬蟲
import pymysql
import requests as rq
import urllib
import coderpig_n as cn
gank_api = "http://gank.io/api/data/"
# 各種分類的表名：Android，iOS，休息視訊，福利，擴充資源，前端，瞎推薦，App
category_list = ["android", "ios", "video", "meizi", "other", "fed", "random", "app"]
type_list = ["Android", "iOS", "休息視訊", "福利", "擴充資源", "前端", "瞎推薦", "App"]
column_list = ('_id', 'dsec', 'images', 'url', 'type')
def init_db():
db = pymysql.connect(host='localhost', user='root', password='zpj12345', port=3306, db='gank', charset="utf8")
cursor = db.cursor()
try:
for category in category_list:
sql = "CREATE TABLE IF NOT EXISTS %s (" \
"_id VARCHAR(50) NOT NULL," \
"dsec TEXT," \
"images TEXT," \
"url TEXT," \
"type VARCHAR(50) DEFAULT ''," \
"PRIMARY KEY (_id))" % category
cursor.execute(sql)
db.close()
except:
pass
class Gank:
_id = dsec = images = url = type = ''
def __init__(self, _id, dsec, images, url, type):
self._id = _id
self.dsec = dsec
self.images = images
self.url = url
self.type = type
# 以元組的方式返回值
def to_value_tuple(self):
return self._id, self.dsec, self.images, self.url, self.type
def insert_db(gank_list):
db = pymysql.connect(host='localhost', user='root', password='zpj12345', port=3306, db='gank', charset="utf8")
cursor = db.cursor()
try:
for data in gank_list:
if data.type in type_list:
category = category_list[type_list.index(data.type)]
data_tuple = data.to_value_tuple()
sql = 'INSERT INTO {table}({keys}) VALUES ({values})'.format(table=category,
keys=','.join(column_list),
values=','.join(['%s'] * len(data_tuple)))
cursor.execute(sql, data_tuple)
print(data_tuple)
db.commit()
except Exception as e:
print(str(e))
db.rollback()
db.close()
def spider_data(pos):
count = 1
while True:
resp = rq.get(gank_api + urllib.parse.quote(type_list[pos]) + "/50/" + str(count), proxies=cn.get_proxy_ip())
resp_json = resp.json()
print(resp.url)
if resp.status_code == 200 and len(resp_json['results']) != 0:
json_list = []
for result in resp_json['results']:
images = result.get('images')
if images is None:
images = ''
else:
images = images[0]
gank = Gank(result['_id'], result['desc'], images, result.get('url', ''),
result['type'])
json_list.append(gank)
insert_db(json_list)
else:
break
count += 1
if __name__ == '__main__':
init_db()
for i in range(0, len(type_list)):
spider_data(i)
db = pymysql.connect(host='localhost', user='root', password='zpj12345', port=3306, db='gank', charset="utf8")
cursor = db.cursor()
cursor.execute('SELECT * FROM android')
print(cursor.rowcount)
results = cursor.fetchall()
for result in results:
print(result)
cursor.close()
複製程式碼來啊，Py交易啊
想加群一起學習Py的可以加下，智障機器人小Pig，驗證資訊裡包含： Python，python，py，Py，加群，交易，屁眼 中的一個關鍵詞即可通過；
驗證通過後回覆 加群 即可獲得加群連結(不要把機器人玩壞了！！！)～～～ 歡迎各種像我一樣的Py初學者，Py大神加入，一起愉快地交流學♂習，van♂轉py。