py連線mysql常用驅動的兩種對比

MySQLdb

https://github.com/PyMySQL/mysqlclient-python
這是基於c語言開發，適用於py2, 目前適用於py3, 在github上好久沒維護更新。

安裝

pip install MySQL-python pip install MySQL-python

常用函式

Python DB API 2.0 對事務提供了兩個方法：

• commit() 提交

• rollback() 回滾

cursor用來執行命令的方法:

• callproc(self, procname, args) 用來執行儲存過程,接收的引數為儲存過程名和引數列表,返回值為受影響的行數

• execute(self, query, args) 執行單條sql語句,接收的引數為sql語句本身和使用的引數列表,返回值為受影響的行數

• executemany(self, query, args) 執行單挑sql語句,但是重複執行引數列表裡的引數,返回值為受影響的行數

• nextset(self) 移動到下一個結果集

cursor用來接收返回值的方法:

• fetchall(self) 接收全部的返回結果行.

• fetchmany(self, size=None) 接收size條返回結果行.如果size的值大於返回的結果行的數量,則會返回cursor.arraysize條資料.

• fetchone(self) 返回一條結果行.

• rowcount 這是一個只讀屬性,並返回執行execute() 方法後影響的行數。

• scroll(self, value, mode='relative') 移動指標到某一行; 如果mode='relative',則表示從當前所在行移動value條,如果 mode='absolute',則表示從結果集的第一行移動value條.

例項

#!/usr/bin/env python
# -*- coding: utf-8 -*-
import MySQLdb as mdb

# 連線資料庫
conn = mdb.connect('localhost', 'root', 'root')

# 也可以使用關鍵字引數
conn = mdb.connect(host='127.0.0.1', port=3306, user='root', passwd='root', db='test', charset='utf8')

# 也可以使用字典進行連線引數的管理
config = {
    'host': '127.0.0.1',
    'port': 3306,
    'user': 'root',
    'passwd': 'root',
    'db': 'test',
    'charset': 'utf8'
}
conn = mdb.connect(**config)

# 如果使用事務引擎，可以設定自動提交事務，或者在每次操作完成後手動提交事務conn.commit()
conn.autocommit(1)    # conn.autocommit(True) 

# 使用cursor()方法獲取操作遊標
cursor = conn.cursor()
# 因該模組底層其實是呼叫CAPI的，所以，需要先得到當前指向資料庫的指標。

try:
    # 建立資料庫
    DB_NAME = 'test'
    cursor.execute('DROP DATABASE IF EXISTS %s' %DB_NAME)
    cursor.execute('CREATE DATABASE IF NOT EXISTS %s' %DB_NAME)
    conn.select_db(DB_NAME)

    #建立表
    TABLE_NAME = 'user'
    cursor.execute('CREATE TABLE %s(id int primary key,name varchar(30))' %TABLE_NAME)

    # 插入單條資料
    sql = 'INSERT INTO user values("%d","%s")' %(1,"jack")

    # 不建議直接拼接sql，佔位符方面可能會出問題，execute提供了直接傳值
    value = [2,'John']
    cursor.execute('INSERT INTO test values(%s,%s)',value)

    # 批量插入資料
    values = []
    for i in range(3, 20):
        values.append((i,'kk'+str(i)))
    cursor.executemany('INSERT INTO user values(%s,%s)',values)

    # 查詢資料條目
    count = cursor.execute('SELECT * FROM %s' %TABLE_NAME)
    print 'total records: %d' %count
    print 'total records:', cursor.rowcount

    # 獲取表名資訊
    desc = cursor.description
    print "%s %3s" % (desc[0][0], desc[1][0])

    # 查詢一條記錄
    print 'fetch one record:'
    result = cursor.fetchone()
    print result
    print 'id: %s,name: %s' %(result[0],result[1])

    # 查詢多條記錄
    print 'fetch five record:'
    results = cursor.fetchmany(5)
    for r in results:
        print r

    # 查詢所有記錄
    # 重置遊標位置，偏移量:大於0向後移動;小於0向前移動，mode預設是relative
    # relative:表示從當前所在的行開始移動; absolute:表示從第一行開始移動
    cursor.scroll(0,mode='absolute')
    results = cursor.fetchall()
    for r in results:
        print r

    cursor.scroll(-2)
    results = cursor.fetchall()
    for r in results:
        print r

    # 更新記錄
    cursor.execute('UPDATE %s SET name = "%s" WHERE id = %s' %(TABLE_NAME,'Jack',1))
    # 刪除記錄
    cursor.execute('DELETE FROM %s WHERE id = %s' %(TABLE_NAME,2))

    # 如果沒有設定自動提交事務，則這裡需要手動提交一次
    conn.commit()
except:
    import traceback
    traceback.print_exc()
    # 發生錯誤時會滾
    conn.rollback()
finally:
    # 關閉遊標連線
    cursor.close()
    # 關閉資料庫連線
    conn.close()

查詢時返回字典結構

MySQLdb預設查詢結果都是返回tuple，通過使用不同的遊標可以改變輸出格式，這裡傳遞一個cursors.DictCursor引數。

import MySQLdb.cursors

conn = MySQLdb.connect(host='localhost', user='root', passwd='root', db='test', cursorclass=MySQLdb.cursors.DictCursor)
cursor = conn.cursor()

cursor.execute('select * from user')
r = cursor.fetchall()
print r
# 當使用位置引數或字典管理引數時，必須匯入MySQLdb.cursors模組

# 也可以用下面的寫法
import MySQLdb as mdb
conn  = mdb.connect('localhost', 'root', 'root', 'test')
cursor = conn.cursor(cursorclass=mdb.cursors.DictCursor)

cursor.execute('select * from user')
r = cursor.fetchall()
print r

MySQLdb取回大結果集的技巧

普通的操作無論是fetchall()還是fetchone()都是先將資料載入到本地再進行計算，大量的資料會導致記憶體資源消耗光。解決辦法是使用SSCurosr游標來處理。

• 解決mysqldb查詢大量資料導致記憶體使用過高的問題

• MySQLdb取回大結果集的技巧

然而，在python3下，MySQLdb模組不再提供支援，此時可以使用另一個模組PyMySQL，它支援python2和python3。

PyMySQL

PyMySQL是一個純Python寫的MySQL客戶端，它的目標是替代MySQLdb，可以在CPython、PyPy、IronPython和Jython環境下執行。PyMySQL在MIT許可下發布。

PyMySQL的效能和MySQLdb幾乎相當，如果對效能要求

不是特別的強，使用PyMySQL將更加方便。

PyMySQL的使用方法和MySQLdb幾乎一樣。

安裝

pip install pymysql

例項

#!/usr/bin/env python
# -*- coding: utf-8 -*-
import pymysql

config = {
    'host': '127.0.0.1',
    'port': 3306,
    'user': 'root',
    'passwd': 'root',
    'charset':'utf8mb4',
    'cursorclass':pymysql.cursors.DictCursor
    }
conn = pymysql.connect(**config)
conn.autocommit(1)
cursor = conn.cursor()

try:
    # 建立資料庫
    DB_NAME = 'test'
    cursor.execute('DROP DATABASE IF EXISTS %s' %DB_NAME)
    cursor.execute('CREATE DATABASE IF NOT EXISTS %s' %DB_NAME)
    conn.select_db(DB_NAME)

    #建立表
    TABLE_NAME = 'user'
    cursor.execute('CREATE TABLE %s(id int primary key,name varchar(30))' %TABLE_NAME)

    # 批量插入紀錄
    values = []
    for i in range(20):
        values.append((i,'kk'+str(i)))
    cursor.executemany('INSERT INTO user values(%s,%s)',values)

    # 查詢資料條目
    count = cursor.execute('SELECT * FROM %s' %TABLE_NAME)
    print 'total records:', cursor.rowcount

    # 獲取表名資訊
    desc = cursor.description
    print "%s %3s" % (desc[0][0], desc[1][0])

    cursor.scroll(10,mode='absolute')
    results = cursor.fetchall()
    for result in results:
        print result

except:
    import traceback
    traceback.print_exc()
    # 發生錯誤時會滾
    conn.rollback()
finally:
    # 關閉遊標連線
    cursor.close()
    # 關閉資料庫連線
    conn.close()

參考
https://foofish.net/python-mysql.html