---

Python3 爬蟲學習筆記第十章 —— 【數據儲存系列 — MySQL】

---

文章目錄

• 【10.1】MySQL 基本操作語句
• • 數據庫操作
  • 表操作
  • 表的結構
  • 表的數據
• 【10.2】Python 連接 MySQL
• 【10.3】創建表
• 【10.4】插入數據
• 【10.5】更新數據
• 【10.6】刪除數據
• 【10.7】查詢數據
• 【10.8】實戰訓練 — 爬取CSDN博客標題和地址保存到 MySQL

---

【10.1】MySQL 基本操作語句

安裝完 MySQL 后，打開 MySQL x.x Command Line Client - Unicode，輸入密碼即可登錄 MySQL，也可在 MySQL 安裝目錄下打開 cmd 使用命令登錄數據庫

數據庫操作

# 連接數據庫 mysql -u root -p# 退出數據庫 exit# 查看所有的數據庫 SHOW DATABASES;# 創建一個數據庫 CREATE DATABASE X;# 刪除一個數據庫 DROP DATABASE IF EXISTS X;# 使用這個數據庫 USE X;

表操作

# 查看所有的表 SHOW TABLES ;# 創建一個表 CREATE TABLE n(id INT, name VARCHAR(10)); CREATE TABLE m(id INT, name VARCHAR(10), PRIMARY KEY (id), FOREIGN KEY (id) REFERENCES n(id), UNIQUE (name)); CREATE TABLE m(id INT, name VARCHAR(10));# 直接將查詢結果導入或復制到新創建的表 CREATE TABLE n SELECT * FROM m;# 新創建的表與一個存在的表的數據結構類似 CREATE TABLE m LIKE n;# 創建一個臨時表 # 臨時表將在你連接MySQL期間存在。當斷開連接時，MySQL將自動刪除表并釋放所用的空間。也可手動刪除。 CREATE TEMPORARY TABLE l(id INT, name VARCHAR(10));# 直接將查詢結果導入或復制到新創建的臨時表 CREATE TEMPORARY TABLE tt SELECT * FROM n;# 刪除一個存在表 DROP TABLE IF EXISTS m;# 更改存在表的名稱 ALTER TABLE n RENAME m; RENAME TABLE n TO m;# 查看表的結構(以下五條語句效果相同） DESC n; DESCRIBE n; SHOW COLUMNS IN n; SHOW COLUMNS FROM n; EXPLAIN n;# 查看表的創建語句 SHOW CREATE TABLE n;

表的結構

# 添加字段 ALTER TABLE n ADD age VARCHAR(2);# 添加字段時設定位置 ALTER TABLE n ADD age VARCHAR(2) FIRST; ALTER TABLE n ADD age VARCHAR(2) AFTER name;# 修改字段在表中的位置 ALTER TABLE n MODIFY age VARCHAR(2) AFTER name;# 刪除字段 ALTER TABLE n DROP age;# 更改字段屬性和屬性 ALTER TABLE n CHANGE age a INT;# 只更改字段屬性 ALTER TABLE n MODIFY age VARCHAR(7) ;# 改變表的存儲引擎 ALTER TABLE t ENGINE myisam; ALTER TABLE t ENGINE innodb;# 設定自增 初始為1，只能一個字段使用，該字段為主鍵的一部分 ALTER TABLE t AUTO_INCREMENT = 0;

表的數據

# 增加數據 INSERT INTO n VALUES (1, 'tom', '23'), (2, 'john', '22'); INSERT INTO n SELECT * FROM n; # 把數據復制一遍重新插入# 刪除數據 DELETE FROM n WHERE id = 2;# 更改數據 UPDATE n SET name = 'tom' WHERE id = 2;# 數據查找 SELECT * FROM n WHERE name LIKE '%h%';# 數據排序(反序) SELECT * FROM n ORDER BY name, id DESC ;

【10.2】Python 連接 MySQL

import pymysqldb = pymysql.connect(host='localhost', user='root', password='000000', port=3306) cursor = db.cursor() cursor.execute('SELECT VERSION()') data = cursor.fetchone() print('Database version:', data) cursor.execute("CREATE DATABASE spiders DEFAULT CHARACTER SET utf8mb4") db.close()

通過 PyMySQL 的 connect 方法聲明一個 MySQL 連接對象 db，當前 MySQL 數據庫運行在本地，設定 host='localhost'，用戶名為 root，登錄密碼為 000000，運行在 3306 端口，調用 cursor() 方法獲得 MySQL 的操作游標，該游標用來執行 SQL 語句，通過游標操作 execute() 方法寫入 SQL 語句，第一條 SQL 語句獲取 MySQL 的版本信息，調用 fetchone() 方法獲得第一條數據，即 MySQL 的版本號。第二條 SQL 語句執行創建 spiders 數據庫的操作，編碼為 utf8mb4，運行該段代碼將輸出 MySQL 的版本號：

Database version: ('8.0.17',)

【10.3】創建表

import pymysqldb = pymysql.connect(host='localhost', user='root', password='000000', port=3306, db='spiders') cursor = db.cursor() sql = 'CREATE TABLE IF NOT EXISTS students (id VARCHAR(255) NOT NULL, name VARCHAR(255) NOT NULL, age VARCHAR(255) NOT NULL, PRIMARY KEY (id))' cursor.execute(sql) db.close()

該段代碼實現了在 spiders 數據庫里創建了一個名為 students 的表，包含 id、name、age 三個字段，類型依次為 varchar、varchar、int

【10.4】插入數據

import pymysqlid = '17110105' user = 'TRH' age = 20 db = pymysql.connect(host='localhost', user='root', password='000000', port=3306, db='spiders') cursor = db.cursor() sql = 'INSERT INTO students(id, name, age) values(%s, %s, %s)' try:cursor.execute(sql, (id, user, age))db.commit() except:db.rollback() db.close()

commit() 方法的作用是實現數據插入，是真正將語句提交到數據庫執行的方法，使用 try except 語句實現異常處理，如果執行失敗，則調用 rollback() 方法執行數據回滾，保證原數據不被破壞，使用查詢語句可以看到已經插入的數據：

進階操作：將需要插入的數據構造成一個字典，這樣的做法可以讓插入方法無需改動，只需要傳入一個動態變化的字典就行了，改寫原來的代碼如下：

import pymysqldata = {'id': '17110105','name': 'TRH','age': 20 } table = 'students' keys = ', '.join(data.keys()) values = ', '.join(['%s']*len(data)) db = pymysql.connect(host='localhost', user='root', password='000000', port=3306, db='spiders') cursor = db.cursor() sql = 'INSERT INTO {table}({keys}) VALUES ({values})'.format(table=table, keys=keys, values=values) try:cursor.execute(sql, tuple(data.values()))print('數據插入成功！')db.commit() except:print('數據插入失敗！')db.rollback() db.close()

傳入的數是字典，將其定義為 data 變量，表名定義成變量 table，構造插入的字段 id、name 和 age。', '.join(data.keys()) 的結果就是 id, name, age，接著需要構造多個 %s 當作占位符，有三個字段，就需要構造 %s, %s, %s。首先定義長度為 1 的數組 ['%s']，然后用乘法將其擴充為 ['%s', '%s', '%s']，再調用 join() 方法，最終變成 %s, %s, %s。再利用字符串的 format() 方法將表名、字段名和占位符構造出來。最終的 SQL 語句就被動態構造成了如下語句：

INSERT INTO students(id, name, age) VALUES (%s, %s, %s)

【10.5】更新數據

import pymysqldata = {'id': '17110105','name': 'TRH','age': 21 } table = 'students' keys = ', '.join(data.keys()) values = ', '.join(['%s']*len(data)) db = pymysql.connect(host='localhost', user='root', password='000000', port=3306, db='spiders') cursor = db.cursor() sql = 'INSERT INTO {table}({keys}) VALUES ({values}) ON DUPLICATE KEY UPDATE'.format(table=table, keys=keys, values=values) update = ','.join(["{key} = % s".format(key=key) for key in data]) sql += update try:if cursor.execute(sql, tuple(data.values())*2):print('數據插入成功！')db.commit() except:print('數據插入失敗！')db.rollback() db.close()

ON DUPLICATE KEY UPDATE 表示如果主鍵已經存在，就執行更新操作，最終被構造成如下語句：

INSERT INTO students(id, name, age) VALUES (% s, % s, % s) ON DUPLICATE KEY UPDATE id = % s, name = % s, age = % s

【10.6】刪除數據

import pymysqltable = 'students' condition = 'age = 20' db = pymysql.connect(host='localhost', user='root', password='000000', port=3306, db='spiders') cursor = db.cursor() sql = 'DELETE FROM {table} WHERE {condition}'.format(table=table, condition=condition) try:cursor.execute(sql)print('數據刪除成功！')db.commit() except:print('數據刪除失敗！')db.rollback() db.close()

刪除操作直接使用 DELETE 語句，指定要刪除的目標表名和刪除條件即可

【10.7】查詢數據

import pymysqltable = 'students' db = pymysql.connect(host='localhost', user='root', password='000000', port=3306, db='spiders') cursor = db.cursor() sql = 'SELECT * FROM students WHERE age >= 20' try:cursor.execute(sql)print('Count:', cursor.rowcount)one = cursor.fetchone()print('One:', one)results = cursor.fetchall()print('Results:', results)print('Results Type:', type(results))for row in results:print(row) except:print('查詢失敗！')

sql = 'SELECT * FROM students WHERE age >= 20'：構造一條 SQL 語句，將年齡 大于等于20 歲的學生查詢出來

cursor.rowcount：調用 cursor 的 rowcount 屬性獲取查詢結果的條數

cursor.fetchone()：調用 cursor 的 fetchone() 方法，獲取結果的第一條數據，返回結果是元組形式，元組的元素順序跟字段一一對應，即第一個元素就是第一個字段 id，第二個元素就是第二個字段 name，以此類推

cursor.fetchall()：調用 cursor 的 fetchall() 方法，得到結果的所有數據，它是二重元組，每個元素都是一條記錄，本例中顯示的是 3 條數據而不是 4 條，這是因為它的內部實現有一個偏移指針用來指向查詢結果，最開始偏移指針指向第一條數據，取一次之后，指針偏移到下一條數據，這樣再取的話，就會取到下一條數據了。我們最初調用了一次 fetchone 方法，這樣結果的偏移指針就指向下一條數據，fetchall 方法返回的是偏移指針指向的數據一直到結束的所有數據，所以該方法獲取的結果就只剩 3 個了

【10.8】實戰訓練 — 爬取CSDN博客標題和地址保存到 MySQL

利用 requests 庫構建請求，BeautifulSoup 解析庫解析網頁，獲取自己博客文章的標題和地址，將其儲存到本地 MySQL 數據庫中，事先已經創建好了一個 blog 數據庫，并創建了一個名為 article 的數據表，數據表包含 id、title、url 三個字段，其中 id 的 AUTO_INCREMENT 屬性可以使 id 自己增加，PRIMARY KEY 關鍵字用于將 id 定義為主鍵，在解析網頁時，使用了 replace() 方法將表示原創文章的“原”字替換成了空格，并使用了 strip() 方法將字符串中多余的空格去掉

創建 article 數據表：

import pymysqldb = pymysql.connect(host='localhost', user='root', password='000000', port=3306, db='blog') cursor = db.cursor() sql = 'CREATE TABLE IF NOT EXISTS article (id INT NOT NULL AUTO_INCREMENT, title VARCHAR(255) NOT NULL, url VARCHAR(255) NOT NULL, PRIMARY KEY (id))' cursor.execute(sql) db.close()

獲取文章標題和對應的 URL 并將其儲存到 MySQL 中：

import requests import pymysql from bs4 import BeautifulSoupdb = pymysql.connect(host='localhost', user='root', password='000000', port=3306, db='blog') cursor = db.cursor()headers = {'User-Agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/73.0.3683.86 Safari/537.36', } url = "https://blog.csdn.net/qq_36759224"request = requests.get(url, headers=headers) soup = BeautifulSoup(request.text, 'lxml') title_list = soup.find_all('h4') for list in title_list:s = list.a.text.strip()title = s.replace('原', '')url = list.a['href'].strip()# print(title + url)cursor.execute('INSERT INTO article (title, url) VALUES (%s, %s)', (title, url)) db.commit() print('數據寫入完畢！') db.close()

在命令行中使用 SELECT * FROM article; 命令可以查看到數據已經成功獲取并儲存到了數據庫中：

總結

以上是生活随笔為你收集整理的Python3 爬虫学习笔记 C10【数据储存系列 — MySQL】的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。