Python基礎之操作MySQL數據庫
一、數據庫操作
1.1 安裝PyMySQL
pip install PyMySQL
1.2 連接數據庫
python連接test數據庫
import pymysql
host = 'localhost' # 主機地址
username = 'root' # 數據庫用戶名
password = '' # 數據庫密碼
db_name = 'test' # 數據庫名稱
# 創建connect對象
connect = pymysql.connect(host=host, user=username, password=password, database=db_name)
# 獲取遊標對象
cursor = connect.cursor()
# 查詢數據庫版本
cursor.execute('SELECT VERSION()')
# 從查詢結果集中獲取下一行數據，返回值為一個值的序列
result = cursor.fetchone()
# 打印結果
print(result)
# 關閉遊標
cursor.close()
# 關閉數據庫連接
connect.close()
執行結果：
(‘10.4.17-MariaDB’,)
1.3 創建數據表
創建一個默認編碼格式為utf8的數據表users
id：int類型，不能為空，有自增屬性，主鍵約束
name：varchar類型，長度最多為10字符，可以為空
age：int類型，可以為空
import pprint
import pymysql
host = 'localhost' # 主機地址
username = 'root' # 數據庫用戶名
password = '' # 數據庫密碼
db_name = 'test' # 數據庫名稱
# 創建connect對象
connect = pymysql.connect(host=host, user=username, password=password, database=db_name)
# 獲取遊標對象
cursor = connect.cursor()
# 創建數據表的SQL命令
create_sql = '''
CREATE TABLE `users`(
`id` INT NOT NULL AUTO_INCREMENT,
`name` VARCHAR(10) NULL,
`age` INT NULL,
PRIMARY KEY (`id`))
DEFAULT CHARACTER SET = utf8;
'''
# 創建數據表
cursor.execute(create_sql)
# 查詢我們創建的數據表的結構
cursor.execute('DESC users')
# 從查詢結果中獲取結果的所有（或者剩餘）行數據，返回值為包含序列的序列（例如元組序列）
result = cursor.fetchall()
# 打印結果
pprint.pprint(result)
# 關閉遊標
cursor.close()
# 關閉數據庫連接
connect.close()
執行結果：
((‘id’, ‘int(11)’, ‘NO’, ‘PRI’, None, ‘auto_increment’),
(‘name’, ‘varchar(10)’, ‘YES’, ”, None, ”),
(‘age’, ‘int(11)’, ‘YES’, ”, None, ”))
1.4 插入，查詢數據
插入3行數據：
id：1，name：路飛，age：18
id：2，name：娜美，age：19
id：3，name：索隆，age：20
import pprint
import pymysql
host = 'localhost' # 主機地址
username = 'root' # 數據庫用戶名
password = '' # 數據庫密碼
db_name = 'test' # 數據庫名稱
# 創建connect對象,插入中文時需要指定編碼格式
connect = pymysql.connect(host=host, user=username, password=password, database=db_name, charset='utf8')
# 獲取遊標對象查詢返回字典
cursor = connect.cursor(pymysql.cursors.DictCursor)
# 插入數據的SQL命令
insert_sql = '''
INSERT INTO users (id, name, age)
VALUES (1, '路飛', 18),(2, '娜美', 19),(3, '索隆', 20)
'''
try:
# 插入數據到數據表
cursor.execute(insert_sql)
# 提交任何掛起的事務到數據庫
connect.commit()
except Exception as e:
# 發送數據回滾,回滾到事務開始時的狀態
connect.rollback()
# 查詢數據
cursor.execute('SELECT * FROM users')
# 隻返回一行數據
# result_one = cursor.fetchone()
# print('---fetchone---')
# pprint.pprint(result_one)
# 返回全部數據
result_all = cursor.fetchall()
print('---fetchall---')
pprint.pprint(result_all)
# 關閉遊標
cursor.close()
# 關閉數據庫連接
connect.close()
執行結果：
—fetchall—
[{‘age’: 18, ‘id’: 1, ‘name’: ‘路飛’},
{‘age’: 19, ‘id’: 2, ‘name’: ‘娜美’},
{‘age’: 20, ‘id’: 3, ‘name’: ‘索隆’}]
1.5 更新，查詢數據
更新數據id：3，name：山治，age：21
import pprint
import pymysql
host = 'localhost' # 主機地址
username = 'root' # 數據庫用戶名
password = '' # 數據庫密碼
db_name = 'test' # 數據庫名稱
# 創建connect對象,插入中文時需要指定編碼格式
connect = pymysql.connect(host=host, user=username, password=password, database=db_name, charset='utf8')
# 獲取遊標對象查詢返回字典
cursor = connect.cursor(pymysql.cursors.DictCursor)
# 查詢數據
cursor.execute('SELECT * FROM users')
# 返回更新前全部數據
result_all = cursor.fetchall()
print('---更新前---')
pprint.pprint(result_all)
# 更新數據的SQL命令
update_sql = '''
UPDATE users SET name = '山治',age = 21 WHERE id = 3
'''
try:
# 更新數據到數據表
cursor.execute(update_sql)
# 提交任何掛起的事務到數據庫
connect.commit()
except Exception as e:
# 發送數據回滾,回滾到事務開始時的狀態
connect.rollback()
# 查詢數據
cursor.execute('SELECT * FROM users')
# 返回更新後全部數據
result_all = cursor.fetchall()
print('---更新後---')
pprint.pprint(result_all)
# 關閉遊標
cursor.close()
# 關閉數據庫連接
connect.close()
執行結果：
—更新前—
[{‘age’: 18, ‘id’: 1, ‘name’: ‘路飛’},
{‘age’: 19, ‘id’: 2, ‘name’: ‘娜美’},
{‘age’: 20, ‘id’: 3, ‘name’: ‘索隆’}]
—更新後—
[{‘age’: 18, ‘id’: 1, ‘name’: ‘路飛’},
{‘age’: 19, ‘id’: 2, ‘name’: ‘娜美’},
{‘age’: 21, ‘id’: 3, ‘name’: ‘山治’}]
1.6 刪除，查詢數據
刪除'age': 19, 'id': 2, 'name': '娜美'該行數據
import pprint
import pymysql
host = 'localhost' # 主機地址
username = 'root' # 數據庫用戶名
password = '' # 數據庫密碼
db_name = 'test' # 數據庫名稱
# 創建connect對象,插入中文時需要指定編碼格式
connect = pymysql.connect(host=host, user=username, password=password, database=db_name, charset='utf8')
# 獲取遊標對象查詢返回字典
cursor = connect.cursor(pymysql.cursors.DictCursor)
# 查詢數據
cursor.execute('SELECT * FROM users')
# 返回刪除前全部數據
result_all = cursor.fetchall()
print('---刪除前---')
pprint.pprint(result_all)
# 刪除數據的SQL命令
update_sql = '''
DELETE FROM users WHERE id = 2
'''
try:
# 刪除數據表的數據
cursor.execute(update_sql)
# 提交任何掛起的事務到數據庫
connect.commit()
except Exception as e:
# 發送數據回滾,回滾到事務開始時的狀態
connect.rollback()
# 查詢數據
cursor.execute('SELECT * FROM users')
# 返回刪除後全部數據
result_all = cursor.fetchall()
print('---刪除後---')
pprint.pprint(result_all)
# 關閉遊標
cursor.close()
# 關閉數據庫連接
connect.close()
執行結果：
—刪除前—
[{‘age’: 18, ‘id’: 1, ‘name’: ‘路飛’},
{‘age’: 19, ‘id’: 2, ‘name’: ‘娜美’},
{‘age’: 21, ‘id’: 3, ‘name’: ‘山治’}]
—刪除後—
[{‘age’: 18, ‘id’: 1, ‘name’: ‘路飛’}, {‘age’: 21, ‘id’: 3, ‘name’: ‘山治’}]
二、連接與遊標對象的方法
2.1 連接對象的方法
.close()方法：
馬上關閉數據連接（而不是當__del__方法被調用的時候）。此後連接變得不可用，再次訪問本連接對象會觸發一個錯誤，使用本連接對象的遊標對象，也會導致例外發生。在關閉連接對象之前，沒有提交（commit）對數據庫的改變將會導致一個隱含的回滾動作（rollback），這將丟棄之前的數據改變操作。
.commit()方法：
提交任何掛起的事務到數據庫中。
.rollback()方法：
對於支持事務的數據庫。調用此方法將導致數據庫回滾到事務開始時的狀態。
.cursor()方法：
方法返回給定連接上建立的遊標對象（Cursor Object），如果數據庫沒有提供對應的遊標對象，那麼有程序來模擬實現遊標功能。
2.2 遊標對象的方法
.close()方法：
立即關閉遊標（不論__del__方法是否已被調用），此後遊標對象就變得不可用瞭。
.execute(operation[,parameters])方法：
準備和執行數據庫操作。所提供的參數將會被綁定到語句中的變量，變量的定義和數據庫模塊有關。
.executemany(operation,seq_of_parameters)方法：
準備和執行數據庫操作，然後以序列形式的函數來執行該操作。
.fetchone()方法：
從查詢結果中獲取下一行數據，返回值為一個值的序列，如果沒有更多數據則返回None。
.fetchmany([size=cursor.arraysize])方法：
從查詢結果中獲取下一組行數據，返回值為包含序列的序列，如果沒有數據返回時，則返回空序列。每次調用要獲取的行數由參數指定，如果沒有指定行數，則遊標的arraysize屬性決定要獲取的行數。
.fetchall()方法：
從查詢結果中獲取所有（或者剩餘）行數據，返回值為包含序列的序列。
.nextset()方法：
此方法將遊標跳到下一個可用的結果集並丟棄當前結果集的所有行，如果沒有更有查詢結果集則返回None，否則返回True，接下來的fetch操作將會從新結果集返回數據瞭。
.setinputsizes(sizes)方法：
此方法可用在調用.execute系列方法之前使用，用於預定義內存區域。size參數接收一個序列類型的值，每一個元素對應一個輸入參數，該元素應該是一個類型對象，對於將要使用的參數，或者是一個整數，用於指定字符串的最大長度。如果元素是None，則沒有預定義的內存區域作為保留區域。
.setoutputsize(size[,column])方法：
為一個很大的列設置緩沖區大小，不指定將使用默認大小。
三、事務
事務是數據庫管理系統執行過程中的一個邏輯單位，由一個有限的數據庫操作序列構成，事務的目的性是為瞭保證數據的一致性。假設銀行轉賬操作，從A賬戶轉賬100元到B賬戶需要進行至少兩次的數據庫修改操作，A賬戶餘額需要減少100元，B賬戶餘額需要增加100元，如果因為由於外部原因導致程序意外終止，就會操作數據出錯，事務就是防止此情況的發生。
數據庫事務擁有四個特性，習慣稱之為ACID特性：
1、原子性（Atomicity）：事務作為一個整體被執行，包含在其中的對數據庫的操作要麼全部被執行，要麼不執行。
2、一致性（Consistency）：事務應確保數據庫的狀態從一個一致狀態轉變為另一個一致狀態，一致狀態的含義是數據庫中的數據應滿足完整性約束。
3、隔離性（Isolation）：多個事務並發執行時，一個事務的執行不應影響其他事務的執行。
4、持久性（Durability）：已被提交的事務對數據庫的修改應該永久保存在數據庫中。
import pprint
import pymysql
host = 'localhost' # 主機地址
username = 'root' # 數據庫用戶名
password = '' # 數據庫密碼
db_name = 'test' # 數據庫名稱
# 創建connect對象,插入中文時需要指定編碼格式
connect = pymysql.connect(host=host, user=username, password=password, database=db_name, charset='utf8')
# 獲取遊標對象查詢返回字典
cursor = connect.cursor(pymysql.cursors.DictCursor)
# 正確的插入數據的SQL命令
insert_sql1 = '''
INSERT INTO users (name, age)
VALUES ('羅賓', 18),('喬巴', 16)
'''
# 錯誤的插入數據的SQL命令
insert_sql2 = '''
INSERT INTO users (name, age)
VALUES ('弗蘭奇')
'''
try:
# 插入數據到數據表
cursor.execute(insert_sql1)
cursor.execute(insert_sql2)
# 提交任何掛起的事務到數據庫
connect.commit()
except Exception as e:
# 執行失敗發送數據回滾,回滾到事務開始時的狀態
connect.rollback()
# 查詢數據
cursor.execute('SELECT * FROM users')
# 返回全部數據
result_all = cursor.fetchall()
print('---fetchall---')
pprint.pprint(result_all)
# 關閉遊標
cursor.close()
# 關閉數據庫連接
connect.close()
上例中執行瞭兩條SQL語句，一條正確的一條錯誤的，隻要有一個錯誤，兩條都不會生效，rollback方法會回滾當前遊標的所有操作。
到此這篇關於Python基礎之操作MySQL數據庫的文章就介紹到這瞭,更多相關Python操作MySQL內容請搜索WalkonNet以前的文章或繼續瀏覽下面的相關文章希望大傢以後多多支持WalkonNet！
推薦閱讀：
- 教你怎麼用Python操作MySql數據庫
- Python全棧之學習MySQL(3)
- 淺析python連接數據庫的重要事項
- python 基於PYMYSQL使用MYSQL數據庫
- Python接入MySQL實現增刪改查的實戰記錄