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!