Python pymysql

wh7577發表於2021-09-09
PythonMySql

安裝

安裝mysql資料庫的難度和oracle資料庫簡直沒得比，安裝步驟如下：

安裝MariaDB

yum install mariadb mariadb-server  # 安裝，centos7預設的mysql就是mariadb
systemctl start mariadb  # 啟動mariadb
systemctl enable mariadb  # 開機自啟動
mysql_secure_installation  # 設定root密碼
mysql -uroot -p  # 登入

安裝pymysql

pip install pymysql

基本操作

資料庫基本操作主要是：

  1. 建立連線
  2. 獲取遊標
  3. 執行sql
  4. 提交事務：針對非查詢性SQL

程式碼

import pymysql

# connect函式開啟資料庫連線
conn = pymysql.connect(host='192.168.110.13', user='root', password='123456', database='student')
# cursor方法建立遊標物件cur
cur = conn.cursor()
# execute方法執行SQL語句
cur.execute("SELECT VERSION()")
# fetchone方法獲取單條資料
data = cur.fetchone()
print ('Database version : {}'.format(data))
# 關閉遊標
cur.close()
# 關閉資料庫連線
conn.close()

DDL

DDL：資料定義語言。包括建立表，建立索引等等

import pymysql

# connect函式開啟資料庫連線
conn = pymysql.connect(host='192.168.110.13', user='root', password='123456', database='student')

# cursor方法建立遊標物件cur
cur = conn.cursor()

# 建立表
sql = '''create table user (
         name char(20) not null,
         age int,  
         sex char(1))'''

cur.execute(sql)

# 關閉遊標
cur.close()
# 關閉資料庫連線
conn.close()

DML

DML：資料操作語言，包含增刪改三項操作。

insert

import pymysql

# connect函式開啟資料庫連線
conn = pymysql.connect(host='192.168.110.13', user='root', password='123456', database='student')

# cursor方法建立遊標物件cur
cur = conn.cursor()

# 建立表
sql = '''insert into user(name, age, sex) values('suncle', 18, 'm')'''

try:
    # 執行sql語句
    cur.execute(sql)
    # 提交到資料庫執行
    conn.commit()
except:
    # 如果發生錯誤則回滾
    conn.rollback()

# 關閉遊標
cur.close()
# 關閉資料庫連線
conn.close()

update

import pymysql

# connect函式開啟資料庫連線
conn = pymysql.connect(host='192.168.110.13', user='root', password='123456', database='student')

# cursor方法建立遊標物件cur
cur = conn.cursor()

# 建立表
sql = '''update user t set t.age = 20 where t.name='suncle' '''

try:
    # 執行sql語句
    cur.execute(sql)
    # 提交到資料庫執行
    conn.commit()
except:
    # 如果發生錯誤則回滾
    conn.rollback()

# 關閉遊標
cur.close()
# 關閉資料庫連線
conn.close()

delete

import pymysql

# connect函式開啟資料庫連線
conn = pymysql.connect(host='192.168.110.13', user='root', password='123456', database='student')

# cursor方法建立遊標物件cur
cur = conn.cursor()

# 建立表
sql = '''delete from user where age=20 '''

try:
    # 執行sql語句
    cur.execute(sql)
    # 提交到資料庫執行
    conn.commit()
except:
    # 如果發生錯誤則回滾
    conn.rollback()

# 關閉遊標
cur.close()
# 關閉資料庫連線
conn.close()

QUERY

基礎查詢

主要有三個函式

  • cursor.fetchall 返回行的元組
  • cursor.fetchmany 返回行的元組， 可以指定返回前N行 相當於對fetchall切片fetchall[:N]
  • cursor.fetchone 返回首行， 相當於fetchall[0]

查詢語句如下：

cur.execute('''select * from user t where t.age<=19;''')

三種方法得到的結果分別為：

cur.fetchall()  # (('suncle', 18, 'm'), ('suncle1', 19, 'm'))

cur.fetchmany(1)  # (('suncle', 18, 'm'),)

cur.fetchone()  # ('suncle', 18, 'm')

可見：每行資料也是一個元組， 元組的內容由sql決定

如果要讓返回的資料帶上列名，也就是要返回字典，那麼就需要用到cursors.DictCursor。

DictCursor

建立cursor時建立DictCursor型別的就可以fetch回來字典形式的結果了

程式碼

import pymysql
conn = pymysql.connect(host='192.168.110.13', user='root', password='123456', database='student')

# 建立cursor時指定cursor引數cursor=pymysql.cursors.DictCursor表示cursor型別
cur = conn.cursor(cursor=pymysql.cursors.DictCursor)
cur.execute('''select * from user t where t.age<=20;''')
cur.fetchall()

fetchall返回結果為：

[{'age': 18, 'name': 'suncle', 'sex': 'm'},
 {'age': 19, 'name': 'suncle1', 'sex': 'm'},
 {'age': 20, 'name': 'suncle2', 'sex': 'm'}]

返回每一行記錄都是一個字典，整體結果是由字典組成的列表。而預設的cursor是由元組組成的元組。

引數化查詢

基礎的SQL隱碼攻擊

import pymysql

conn = pymysql.connect(host='192.168.110.13', user='root', password='123456', database='student')
cur = conn.cursor()

def get_user(age=18):
    sql = '''select * from user t where t.age<={};'''.format(age)
    cur.execute(sql)
    return cur.fetchall()

get_user()  # 返回(('suncle', 18, 'm'),)

get_user('18 or 1=1')  # 返回(('suncle', 18, 'm'), ('suncle1', 19, 'm'))

當傳入引數的age中帶sql條件的時候，就會發生sql注入，使得結果可能並不滿足要求。

為了解決sql注入，我們可以使用引數化查詢。

使用引數化查詢

以上程式碼做以下修改之後就可以避免sql注入

import pymysql


conn = pymysql.connect(host='192.168.110.13', user='root', password='123456', database='student')
cur = conn.cursor()

def get_user(age=18):
    # 不管資料庫定義的是什麼型別，統一使用%s
    sql = '''select * from user t where t.age<=%s;'''.format(age)
    cur.execute(sql, (age, ))  # 引數化查詢
    return cur.fetchall()

引數化查詢最大的優勢在於避免了SQL隱碼攻擊，同時引數化之後避免了sql多次硬解析，能提高查詢效率。所以，總是應該使用引數化查詢。

上下文管理

資料庫連線和遊標都支援上下文管理。

遊標

檢視cur例項對應Cursor類的方法

cur = conn.cursor()
help(cur)

對應的with語句使用如下

with cur:
    cur.execute('''select * from user''')

cur.execute('''select * from user''')  # 丟擲錯誤：ProgrammingError: Cursor closed

with語句塊結束之後cur就已經關閉了。

連線

透過help命令檢視Connection類的__enter__和__exit__兩種方法的實現

conn = pymysql.connect(host='192.168.110.13', user='root', password='123456', database='student')
help(conn)  # conn是Connection類

檢視結果如下：

 |  __enter__(self)
 |      Context manager that returns a Cursor
 |  
 |  __exit__(self, exc, value, traceback)
 |      On successful exit, commit. On exception, rollback
  • __enter__方法會返回一個遊標
  • __exit__方法：如果成功推出就會自動提交commit，如果發生異常就會回滾rollback

對應的with語句使用如下

with conn as cur:
    cur.execute('''update user t set t.age = 20 where t.name='suncle' ''')

cur.execute('''select * from user''')  # 退出with塊之後遊標仍然沒有關閉

雖然遊標沒有關閉， 但是資料庫操作已經提交。

遊標和連線共同上下文管理

with conn as cur:
    with cur:
        cur.execute('''update user t set t.age = 20 where t.name='suncle' ''')

退出整個上下文管理塊之後，遊標會關閉，並且會自動提交。

資料庫連線池

一般來說，應用程式訪問資料庫的過程是：

  1. 裝載資料庫驅動程式
  2. 建立資料庫連線
  3. 訪問資料庫，執行sql語句
  4. 斷開資料庫連線

相對於效能正常的SQL的執行效率來說，建立連線是一個費時的活動，而且系統還要為每一個連線分配記憶體資源。在現在web請求的大併發量情況下，必然會導致頻繁的資料庫操作。而頻繁的進行資料庫連線操作勢必佔用很多的系統資源，使得系統的響應速度下降，嚴重的甚至會造成伺服器的崩潰。

引入資料庫連線池技術之後，應用程式訪問資料庫的過程是：

  1. 請求資料庫操作時，從連線池中取出建立好的資料庫連線
  2. 執行sql語句
  3. 不斷開資料庫連線，而是放回連線池中，等待下次使用

連線池還有個優點就是能控制資料庫的壓力，當大量使用者同時湧入時，連線池只會使用池限制資料庫連線數目，而不會不停的向資料庫請求連線，最後導致伺服器崩潰。

Python實現資料庫連線池

  • 使用佇列Queue儲存資料庫連線

程式碼如下

from queue import Queue
import pymysql

class ConnectionPool():  # args和kwargs用來接收資料庫url資訊
    def __init__(self, size, *args, **kwargs):
        self.args = args
        self.kwargs = kwargs
        self.size = size
        self.pool = Queue(maxsize=self.size)
        for _ in range(self.size):
            self.pool.put(self._connect())
            
    def _connect(self):
        return pymysql.connect(*self.args, **self.kwargs)
    
    @staticmethod
    def _close(conn):
        conn.close()
    
    def get_connection(self):
        return self.pool.get()
 
    def return_connection(self, conn):
        return self.pool.put(conn)
 
    def close_pool(self):
        while not self.is_empty():
            self._close(self.pool.get())
 
    def is_empty(self):
        return self.pool.empty()
    
    def is_full(self):
        return self.pool.full()
    
    def current_connection_count(self):
        return self.pool.qsize()
    
    
pool = ConnectionPool(20, host='192.168.110.13', user='root', password='123456', database='student')

conn = pool.get_connection()  # 獲取資料庫連線
print(conn)  # 
print(pool.current_connection_count())  # 19
cur = conn.cursor()
cur.execute("SELECT VERSION()")
data = cur.fetchone()
print ('Database version : {}'.format(data[0]))
cur.close()
pool.return_connection(conn)  # 關閉遊標之後需要回收資料庫連線
print(pool.current_connection_count())  # 20

記得幫我點贊哦！

念念不忘，必有迴響，小夥伴們幫我點個贊吧，非常感謝。

> 我是職場亮哥，YY高階軟體工程師、四年工作經驗，拒絕鹹魚爭當龍頭的斜槓程式設計師。
>
> 聽我說，進步多，程式人生一把梭
>
> 如果有幸能幫到你，請幫我點個【贊】，給個關注，如果能順帶評論給個鼓勵，將不勝感激。

本人所有文章、回答都與版權保護平臺有合作，著作權歸職場亮哥所有，未經授權，轉載必究！

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/2480/viewspace-2826386/，如需轉載，請註明出處，否則將追究法律責任。

相關文章