Python操作MySQL-有解無憂

安裝PyMySQL

python中連接mysql的客戶端主要有mysqldb、mysql-connector、pymysql三種，雖說性能上面各有差別，但是主流市場還是以操作便捷、使用簡單為選擇條件，

這里我選擇以純python撰寫的mysql連接庫pymysql為首，

安裝

pip3 install PyMySQL

打開資料庫連接

以下兩種寫法可以打開資料庫連接，回傳db物件，

db = pymysql.connect("127.0.0.1", "root", "132456", "test")
db = pymysql.connect(host="127.0.0.1",user="root",password="132456",database="test")

獲取游標

我們在python中使用mysql介面操作資料庫，對于mysql來說，python就是客戶端，在python客戶端中，執行SQL統一要用到游標物件，游標是資料庫中一個很重要的概念，字面意思就是游動的標記，

我們正常查詢資料，會用變數來接收SQL陳述句查詢回傳的結果集，假設說我們的查詢量非常巨大，達到幾千萬上億行資料，那么我們的服務器記憶體會直線飆升，引起程式崩潰，如果查出來的資料還要遍歷進行處理的話，那么程式崩潰*2

但若是使用游標物件來查詢，查出的結果集不會立刻回傳給客戶端，而是會存盤在mysql服務器上，可以理解成是mysql為用戶查詢操作開設的一個緩沖區，用來存盤查詢結果，然后客戶端從這個緩沖區中獲取資料內容，

從db物件中獲取游標

cursor = db.cursor()

創建表

sql = '''
CREATE TABLE user( 
id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '自增id', 
username VARCHAR(16) NOT NULL COMMENT '姓名', 
sex INT(1) UNSIGNED NOT NULL COMMENT '性別 1男2女', 
age INT(11) UNSIGNED NOT NULL COMMENT '年齡', 
PRIMARY KEY (id) 
)
ENGINE=INNODB CHARSET=utf8 COLLATE=utf8_general_ci; 
'''
cursor.execute(sql)
cursor.close()
db.close()

增刪改

如果你的資料庫支持事務，那么pymysql在你建立游標的時候就自動開啟了事務，所以我們想要讓SQL執行生效，就必須要做提交事務的動作，

sql = '''
INSERT INTO user(username,sex,age) VALUES ('小明',1,18);
'''
try:
    cursor.execute(sql)
except Exception as e:
    db.rollback()
    print(e)
else:
    db.commit()
    print('新增資料行的id是：{}'.format(cursor.lastrowid))

cursor.close()
db.close()

列印結果：

新增資料行的id是：1

修改操作和新增操作是一樣的，當然修改操作取不到自增id，就算取結果也是0，但是它有一個受影響行數，執行execute方法會回傳受影響行數，從游標中也一樣可以取到，

sql = '''
UPDATE user SET age = 19 WHERE id = 1
'''
count = 0
try:
    count = cursor.execute(sql)
except Exception as e:
    db.rollback()
    print(e)
else:
    db.commit()

print(f'受影響行數是：{count}')
print(f'從游標中也可以取到受影響行數：{cursor.rowcount}')

cursor.close()
db.close()

列印結果：

受影響行數是：1
從游標中也可以取到受影響行數：1

洗掉和修改就完全是換條SQL而已，這里就不做多余的演示了，

查詢

查詢資料之后，我們要從游標中取結果，有兩種取法，一次性全取和一行一行的取，

一次性全取 fetchall()

sql = '''
SELECT * FROM user
'''
count = 0
try:
    count = cursor.execute(sql)
except Exception as e:
    print(e)

print(f'查詢到的資料行數是：{count}')
data = cursor.fetchall()
print(data)

cursor.close()
db.close()

列印結果：

查詢到的資料行數是：3
((1, '小明', 1, 19), (2, '小強', 1, 18), (3, '小花', 2, 17))

可以看到從游標中取到的結果集被裝在了元組里，我們可以進一步遍歷元組獲取每一行：

sql = '''
SELECT * FROM user
'''
count = 0
try:
    count = cursor.execute(sql)
except Exception as e:
    print(e)

data = cursor.fetchall()

print(f'查詢到的資料行數是：{count}')

for row in data:
    if row[2] == 1:
        sex = '男'
    elif row[2] == 2:
        sex = '女'
    else:
        sex = '未知'
    print(f'ID號{row[0]}：姓名是{row[1]},性別是{sex},今年{row[3]}歲，')

cursor.close()
db.close()

列印結果：

查詢到的資料行數是：3
ID號1：姓名是小明,性別是男,今年19歲，
ID號2：姓名是小強,性別是男,今年18歲，
ID號3：姓名是小花,性別是女,今年17歲，

一行一行取 fetchone()

sql = '''
SELECT * FROM user
'''
count = 0
try:
    count = cursor.execute(sql)
except Exception as e:
    print(e)

print(f'查詢到的資料行數是：{count}')

for i in range(count):
    row = cursor.fetchone()
    if row[2] == 1:
        sex = '男'
    elif row[2] == 2:
        sex = '女'
    else:
        sex = '未知'
    print(f'ID號{row[0]}：姓名是{row[1]},性別是{sex},今年{row[3]}歲，')

cursor.close()
db.close()

列印結果：

查詢到的資料行數是：3
ID號1：姓名是小明,性別是男,今年19歲，
ID號2：姓名是小強,性別是男,今年18歲，
ID號3：姓名是小花,性別是女,今年17歲，

如果要對查出來的大量資料進行遍歷處理，最好就是用游標一行一行的讀出來處理，在上文獲取游標小節中講了，對變數進行遍歷和從游標中取資料是兩個概念，變數是存在于我們服務器記憶體中的，游標是資料庫的資源句柄，

游標還有一個fetchmany()方法，傳入一個int引數，指定獲取幾條資料，個人覺得是小概率用法，

轉載請註明出處，本文鏈接：https://www.uj5u.com/houduan/67454.html

標籤：Python

上一篇：odoo13之給模塊添加自定義配置項

下一篇：Python中使用 input 函式來獲取輸入