Python 操作mysql - 關(guān)系型數(shù)據(jù)庫(kù)存儲(chǔ)

簡(jiǎn)單介紹

關(guān)系型數(shù)據(jù)庫(kù)是一種以“關(guān)系”的方式來(lái)組織和存儲(chǔ)數(shù)據(jù)的數(shù)據(jù)庫(kù)。它使用表（也稱(chēng)為“關(guān)系”）來(lái)表示數(shù)據(jù)，每個(gè)表由一組具有相同性質(zhì)的列和多行數(shù)據(jù)組成。關(guān)系型數(shù)據(jù)庫(kù)的主要特征包括：

1. 數(shù)據(jù)結(jié)構(gòu)化：數(shù)據(jù)以表的形式結(jié)構(gòu)化存儲(chǔ)，表由行和列組成。行代表記錄，列代表字段。
2. 主鍵：每張表都有一個(gè)主鍵，用于唯一標(biāo)識(shí)表中的每一條記錄。主鍵確保數(shù)據(jù)的唯一性。
3. 外鍵：外鍵用于建立表與表之間的關(guān)系，連接不同表的數(shù)據(jù)。
4. 數(shù)據(jù)完整性：關(guān)系型數(shù)據(jù)庫(kù)可以通過(guò)定義約束（如主鍵、外鍵、唯一性等）來(lái)保證數(shù)據(jù)的完整性和有效性。
5. SQL（結(jié)構(gòu)化查詢(xún)語(yǔ)言）：關(guān)系型數(shù)據(jù)庫(kù)使用SQL作為標(biāo)準(zhǔn)接口來(lái)進(jìn)行數(shù)據(jù)查詢(xún)、操作、更新和管理。通過(guò)SQL，用戶(hù)可以輕松地執(zhí)行復(fù)雜的數(shù)據(jù)操作。

常見(jiàn)的關(guān)系型數(shù)據(jù)庫(kù)管理系統(tǒng)（RDBMS）包括：

• MySQL
• PostgreSQL
• Oracle Database
• Microsoft SQL Server
• SQLite

關(guān)系型數(shù)據(jù)庫(kù)在很多應(yīng)用場(chǎng)景中被廣泛使用，特別是對(duì)于需要復(fù)雜查詢(xún)和事務(wù)處理的應(yīng)用。

以mysql為例子 簡(jiǎn)單使用pymysql 操作數(shù)據(jù)庫(kù)

連接數(shù)據(jù)庫(kù)

import  pymysqldef connect_mysql():# 執(zhí)行SQL語(yǔ)句sql = "SELECT * FROM person"cursor.execute(sql)# 獲取查詢(xún)結(jié)果 fetchall()方法獲取所有查詢(xún)結(jié)果rows = cursor.fetchall()# 打印查詢(xún)結(jié)果for row in rows:print(row)#  關(guān)閉數(shù)據(jù)庫(kù)連接db.close()# 主函數(shù)
if __name__ == '__main__':# 創(chuàng)建數(shù)據(jù)庫(kù)連接db = pymysql.connect(host='localhost', user='root', password='111111', port=3306, db='test')# 創(chuàng)建游標(biāo) 利用游標(biāo)可以執(zhí)行SQL語(yǔ)句cursor = db.cursor()connect_mysql()

創(chuàng)建表

def create_table():# 創(chuàng)建 數(shù)據(jù)表 students# 字段名 id 含義 學(xué)號(hào) 類(lèi)型 varchar(20)# 字段名 name 含義 姓名 類(lèi)型 varchar(20)# 字段名 age 含義 年齡 類(lèi)型 intsql = """CREATE TABLE IF NOT EXISTS students (id varchar(20) NOT NULL,name varchar(20) NOT NULL,age int NOT NULL,PRIMARY KEY (id))"""try:# 執(zhí)行SQL語(yǔ)句cursor.execute(sql)# 提交到數(shù)據(jù)庫(kù)執(zhí)行db.commit()print("創(chuàng)建數(shù)據(jù)表 students 成功")except:# 發(fā)生錯(cuò)誤時(shí)回滾db.rollback()print("創(chuàng)建數(shù)據(jù)表 students 失敗")# 主函數(shù)
if __name__ == '__main__':# 創(chuàng)建數(shù)據(jù)庫(kù)連接db = pymysql.connect(host='localhost', user='root', password='111111', port=3306, db='test')# 創(chuàng)建游標(biāo) 利用游標(biāo)可以執(zhí)行SQL語(yǔ)句cursor = db.cursor()create_table()

插入數(shù)據(jù)

插入過(guò)程中要注意事務(wù)的四個(gè)屬性：

1. Atomicity 原子性 事務(wù)是一個(gè)不可分割的工作單位，事務(wù)中包括的諸操作要么都做，要么都不做。
2. Consistency 一致性 事務(wù)必須是數(shù)據(jù)庫(kù)從一個(gè)一致性狀態(tài)變到另一個(gè)一致性狀態(tài)。一致性與原子性是密切相關(guān)的。
3. Isolation 隔離性 一個(gè)事務(wù)的執(zhí)行不能被其他事務(wù)干擾。即一個(gè)事務(wù)內(nèi)部的操作及使用的數(shù)據(jù)對(duì)并發(fā)的其他事務(wù)是隔離的，并發(fā)執(zhí)行的各個(gè)事務(wù)之間不能互相干擾。
4. Durability 持久性 一個(gè)事務(wù)一旦提交，它對(duì)數(shù)據(jù)庫(kù)中數(shù)據(jù)的改變就應(yīng)該是永久性的。接下來(lái)的其他操作或故障不應(yīng)該對(duì)其有任何影響。

 # 創(chuàng)建數(shù)據(jù)庫(kù)連接db = pymysql.connect(host='localhost', user='root', password='111111', port=3306, db='test')# 創(chuàng)建游標(biāo) 利用游標(biāo)可以執(zhí)行SQL語(yǔ)句cursor = db.cursor()
def insert_data():# 插入數(shù)據(jù)# 插入一條數(shù)據(jù) (1001, '張三', 20)sql = "INSERT INTO students (id, name, age) VALUES (%s,%s,%s)"try:# 執(zhí)行SQL語(yǔ)句cursor.execute(sql, ('1001', '張三', 20))# 提交到數(shù)據(jù)庫(kù)執(zhí)行db.commit()print("插入數(shù)據(jù)成功")except:# 發(fā)生錯(cuò)誤時(shí)回滾db.rollback()print("插入數(shù)據(jù)失敗")# 更加通用的插入數(shù)據(jù)方法
def insert_data_more():# 動(dòng)態(tài)傳入字典數(shù)據(jù)data = {'id': '1002','name':'李四','age': 21,}table_name ='students'keys = ','.join(data.keys())  # 字段名values = ','.join(['%s'] * len(data))  # 占位符sql = "INSERT INTO {table_name} ({keys}) VALUES ({values})".format(table_name=table_name, keys=keys, values=values)try:# 執(zhí)行SQL語(yǔ)句cursor.execute(sql, tuple(data.values()))# 提交到數(shù)據(jù)庫(kù)執(zhí)行db.commit()print("插入數(shù)據(jù)成功")except:# 發(fā)生錯(cuò)誤時(shí)回滾db.rollback()print("插入數(shù)據(jù)失敗")

更新數(shù)據(jù)

 # 創(chuàng)建數(shù)據(jù)庫(kù)連接db = pymysql.connect(host='localhost', user='root', password='111111', port=3306, db='test')# 創(chuàng)建游標(biāo) 利用游標(biāo)可以執(zhí)行SQL語(yǔ)句cursor = db.cursor()def update_data():# 更新數(shù)據(jù)# 更新 id 為 1001 的學(xué)生的年齡為 22sql = "UPDATE students SET age = %s WHERE id = %s"try:# 執(zhí)行SQL語(yǔ)句cursor.execute(sql, (22, '1001'))# 提交到數(shù)據(jù)庫(kù)執(zhí)行db.commit()print("更新數(shù)據(jù)成功")except:# 發(fā)生錯(cuò)誤時(shí)回滾db.rollback()print("更新數(shù)據(jù)失敗")

在實(shí)際的數(shù)據(jù)抓取過(guò)程中，大部分情況下需要插入數(shù)據(jù)，但是我們關(guān)心的是會(huì)不會(huì)出現(xiàn)重復(fù)數(shù)據(jù)，如果出現(xiàn)了，我們希望更新數(shù)據(jù)而不是重復(fù)保存一次再實(shí)現(xiàn)一種去重的方法，如果數(shù)據(jù)存在， 則更新數(shù)據(jù)；如果數(shù)據(jù)不存在，則插入數(shù)據(jù)。 另外，這種做法支持靈活的字典傳值。

def insert_or_update_data(data):# 動(dòng)態(tài)傳入字典數(shù)據(jù)table_name ='students'keys = ','.join(data.keys())  # 字段名values = ','.join(['%s'] * len(data))  # 占位符sql = "INSERT INTO {table_name} ({keys}) VALUES ({values}) ON DUPLICATE KEY UPDATE {update_sql}".format(  # 這里的ON DUPLICATE KEY UPDATE 代表如果主鍵重復(fù)，則更新數(shù)據(jù)table_name=table_name, keys=keys, values=values, update_sql=','.join(['{key} = %s'.format(key=key) for key in data.keys()]))# sql 語(yǔ)句完整版本：# INSERT INTO students (id, name, age) VALUES (%s,%s,%s) ON DUPLICATE KEY UPDATE id = %s, name = %s, age = %stry:# 執(zhí)行SQL語(yǔ)句cursor.execute(sql, tuple(data.values()) * 2)# 提交到數(shù)據(jù)庫(kù)執(zhí)行db.commit()print("插入或更新數(shù)據(jù)成功")except:# 發(fā)生錯(cuò)誤時(shí)回滾db.rollback()print("插入或更新數(shù)據(jù)失敗")

刪除數(shù)據(jù)

def delete_data():# 刪除數(shù)據(jù)# 刪除 id 為 1001 的學(xué)生sql = "DELETE FROM students WHERE id = %s"try:# 執(zhí)行SQL語(yǔ)句cursor.execute(sql, ('1001',))# 提交到數(shù)據(jù)庫(kù)執(zhí)行db.commit()print("刪除數(shù)據(jù)成功")except:# 發(fā)生錯(cuò)誤時(shí)回滾db.rollback()print("刪除數(shù)據(jù)失敗")

查詢(xún)數(shù)據(jù)

def select_data():# 查詢(xún)數(shù)據(jù)# 查詢(xún) age 字段大于 20 的學(xué)生的姓名和年齡sql = "SELECT name, age FROM students WHERE age > %s"try:# 執(zhí)行SQL語(yǔ)句cursor.execute(sql, (20))# 獲取查詢(xún)結(jié)果 fetchall()方法獲取所有查詢(xún)結(jié)果rows = cursor.fetchall()# 打印查詢(xún)結(jié)果for row in rows:print(row)except:# 發(fā)生錯(cuò)誤時(shí)回滾db.rollback()print("查詢(xún)數(shù)據(jù)失敗")