Python連接MySQL數(shù)據(jù)庫

Python標(biāo)準(zhǔn)數(shù)據(jù)庫接口為Python DB-API, Python DB-API為開發(fā)人員提供了數(shù)據(jù)庫應(yīng)用 編程接口。

Python 數(shù)據(jù)庫接口支持非常多的數(shù)據(jù)庫,你可以選擇適合你項(xiàng)目的數(shù)據(jù)庫:

  • GadFly
  • mSQL
  • MySQL
  • PostgreSQL
  • Microsoft SQL Server 2000 Informix
  • Interbase Oracle Sybase

你可以訪問Python數(shù)據(jù)庫接口及API查看詳細(xì)的支持?jǐn)?shù)據(jù)庫列表。

不同的數(shù)據(jù)庫你需要下載不同的DB API模塊,例如你需要訪問Oracle數(shù)據(jù)庫和Mysql數(shù)據(jù),你需要下載Oracle和MySQL數(shù)據(jù)庫模塊。

DB-API是一個規(guī)范。它定義了一系列必須的對象和數(shù)據(jù)庫存取方式, 以便為各種各樣的底層數(shù)據(jù)庫系統(tǒng)和多種多樣的數(shù)據(jù)庫接口程序提供一致的訪問接口。

Python的DB-API,為大多數(shù)的數(shù)據(jù)庫實(shí)現(xiàn)了接口,使用它連接各數(shù)據(jù)庫后,就可以用相同 的方式操作各數(shù)據(jù)庫。

Python DB-API使用流程:

  1. 引入API模塊。
  2. 獲取與數(shù)據(jù)庫的連接。
  3. 執(zhí)行SQL語句和存儲過程。
  4. 關(guān)閉數(shù)據(jù)庫連接。

1. MySQLdb

MySQLdb是用于Python鏈接Mysql數(shù)據(jù)庫的接口，它實(shí)現(xiàn)了Python 數(shù)據(jù)庫API規(guī)范V2.0，基于MySQL C API上建立的。

安裝

直接使用pip進(jìn)行安裝，在此之前需要安裝一些系統(tǒng)依賴包。

  • CentOS
yum install gcc python-devel mysql-devel zlib-devel openssl-devel
  • Ubuntu
sudo apt-get install libmysqlclient-dev libmysqld-dev python-dev python-setuptools

安裝完依賴，直接使用pip安裝，MySQLdb模塊的名字在pip上叫MySQL-python。

pip install MySQL-python

常用函數(shù)

Python DB API 2.0 對事務(wù)提供了兩個方法：

  • commit() 提交
  • rollback() 回滾

cursor用來執(zhí)行命令的方法:

  • callproc(self, procname, args) 用來執(zhí)行存儲過程,接收的參數(shù)為存儲過程名和參數(shù)列表,返回值為受影響的行數(shù)
  • execute(self, query, args) 執(zhí)行單條sql語句,接收的參數(shù)為sql語句本身和使用的參數(shù)列表,返回值為受影響的行數(shù)
  • executemany(self, query, args) 執(zhí)行單挑sql語句,但是重復(fù)執(zhí)行參數(shù)列表里的參數(shù),返回值為受影響的行數(shù)
  • nextset(self) 移動到下一個結(jié)果集

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

  • fetchall(self) 接收全部的返回結(jié)果行.
  • fetchmany(self, size=None) 接收size條返回結(jié)果行.如果size的值大于返回的結(jié)果行的數(shù)量,則會返回cursor.arraysize條數(shù)據(jù).
  • fetchone(self) 返回一條結(jié)果行.
  • rowcount 這是一個只讀屬性,并返回執(zhí)行execute() 方法后影響的行數(shù)。
  • scroll(self, value, mode='relative') 移動指針到某一行; 如果mode='relative',則表示從當(dāng)前所在行移動value條,如果 mode='absolute',則表示從結(jié)果集的第一行移動value條.

實(shí)例

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

# 連接數(shù)據(jù)庫
conn = mdb.connect('localhost', 'root', 'root')

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

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

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

# 使用cursor()方法獲取操作游標(biāo)
cursor = conn.cursor()
# 因該模塊底層其實(shí)是調(diào)用CAPI的，所以，需要先得到當(dāng)前指向數(shù)據(jù)庫的指針。

try:
    # 創(chuàng)建數(shù)據(jù)庫
    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)
    
    #創(chuàng)建表
    TABLE_NAME = 'user'
    cursor.execute('CREATE TABLE %s(id int primary key,name varchar(30))' %TABLE_NAME)
    
    # 插入單條數(shù)據(jù)
    sql = 'INSERT INTO user values("%d","%s")' %(1,"jack")
    
    # 不建議直接拼接sql，占位符方面可能會出問題，execute提供了直接傳值
    value = [2,'John']
    cursor.execute('INSERT INTO test values(%s,%s)',value)
    
    # 批量插入數(shù)據(jù)
    values = []
    for i in range(3, 20):
        values.append((i,'kk'+str(i)))
    cursor.executemany('INSERT INTO user values(%s,%s)',values)
    
    # 查詢數(shù)據(jù)條目
    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
        
    # 查詢所有記錄
    # 重置游標(biāo)位置，偏移量:大于0向后移動;小于0向前移動，mode默認(rèn)是relative
    # relative:表示從當(dāng)前所在的行開始移動; 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))
    
    # 如果沒有設(shè)置自動提交事務(wù)，則這里需要手動提交一次
    conn.commit()
except:
    import traceback
    traceback.print_exc()
    # 發(fā)生錯誤時會滾
    conn.rollback()
finally:
    # 關(guān)閉游標(biāo)連接
    cursor.close()
    # 關(guān)閉數(shù)據(jù)庫連接
    conn.close()

查詢時返回字典結(jié)構(gòu)

MySQLdb默認(rèn)查詢結(jié)果都是返回tuple，通過使用不同的游標(biāo)可以改變輸出格式，這里傳遞一個cursors.DictCursor參數(shù)。

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
# 當(dāng)使用位置參數(shù)或字典管理參數(shù)時，必須導(dǎo)入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取回大結(jié)果集的技巧

普通的操作無論是fetchall()還是fetchone()都是先將數(shù)據(jù)載入到本地再進(jìn)行計(jì)算，大量的數(shù)據(jù)會導(dǎo)致內(nèi)存資源消耗光。解決辦法是使用SSCurosr光標(biāo)來處理。

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

2. PyMySQL

PyMySQL是一個純Python寫的MySQL客戶端，它的目標(biāo)是替代MySQLdb，可以在CPython、PyPy、IronPython和Jython環(huán)境下運(yùn)行。PyMySQL在MIT許可下發(fā)布。

PyMySQL的性能和MySQLdb幾乎相當(dāng)，如果對性能要求
不是特別的強(qiáng)，使用PyMySQL將更加方便。

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

安裝

pip install pymysql

實(shí)例

#!/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:
    # 創(chuàng)建數(shù)據(jù)庫
    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)
    
    #創(chuàng)建表
    TABLE_NAME = 'user'
    cursor.execute('CREATE TABLE %s(id int primary key,name varchar(30))' %TABLE_NAME)
    
    # 批量插入紀(jì)錄
    values = []
    for i in range(20):
        values.append((i,'kk'+str(i)))
    cursor.executemany('INSERT INTO user values(%s,%s)',values)
    
    # 查詢數(shù)據(jù)條目
    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()
    # 發(fā)生錯誤時會滾
    conn.rollback()
finally:
    # 關(guān)閉游標(biāo)連接
    cursor.close()
    # 關(guān)閉數(shù)據(jù)庫連接
    conn.close()

輸出結(jié)果：

total records: 20
id name
{u'id': 10, u'name': u'kk10'}
{u'id': 11, u'name': u'kk11'}
{u'id': 12, u'name': u'kk12'}
{u'id': 13, u'name': u'kk13'}
{u'id': 14, u'name': u'kk14'}
{u'id': 15, u'name': u'kk15'}
{u'id': 16, u'name': u'kk16'}
{u'id': 17, u'name': u'kk17'}
{u'id': 18, u'name': u'kk18'}
{u'id': 19, u'name': u'kk19'}

參考：

最后編輯于 ：
?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請聯(lián)系作者
【社區(qū)內(nèi)容提示】社區(qū)部分內(nèi)容疑似由AI輔助生成，瀏覽時請結(jié)合常識與多方信息審慎甄別。
平臺聲明：文章內(nèi)容（如有圖片或視頻亦包括在內(nèi)）由作者上傳并發(fā)布，文章內(nèi)容僅代表作者本人觀點(diǎn)，簡書系信息發(fā)布平臺，僅提供信息存儲服務(wù)。

相關(guān)閱讀更多精彩內(nèi)容

友情鏈接更多精彩內(nèi)容