Python標(biāo)準(zhǔn)數(shù)據(jù)庫(kù)接口為Python DB-API, Python DB-API為開(kāi)發(fā)人員提供了數(shù)據(jù)庫(kù)應(yīng)用 編程接口矾麻。
Python 數(shù)據(jù)庫(kù)接口支持非常多的數(shù)據(jù)庫(kù),你可以選擇適合你項(xiàng)目的數(shù)據(jù)庫(kù):
- GadFly
- mSQL
- MySQL
- PostgreSQL
- Microsoft SQL Server 2000 Informix
- Interbase Oracle Sybase
你可以訪問(wèn)Python數(shù)據(jù)庫(kù)接口及API查看詳細(xì)的支持?jǐn)?shù)據(jù)庫(kù)列表纱耻。
不同的數(shù)據(jù)庫(kù)你需要下載不同的DB API模塊,例如你需要訪問(wèn)Oracle數(shù)據(jù)庫(kù)和Mysql數(shù)據(jù),你需要下載Oracle和MySQL數(shù)據(jù)庫(kù)模塊。
DB-API是一個(gè)規(guī)范险耀。它定義了一系列必須的對(duì)象和數(shù)據(jù)庫(kù)存取方式, 以便為各種各樣的底層數(shù)據(jù)庫(kù)系統(tǒng)和多種多樣的數(shù)據(jù)庫(kù)接口程序提供一致的訪問(wèn)接口弄喘。
Python的DB-API,為大多數(shù)的數(shù)據(jù)庫(kù)實(shí)現(xiàn)了接口,使用它連接各數(shù)據(jù)庫(kù)后,就可以用相同 的方式操作各數(shù)據(jù)庫(kù)。
Python DB-API使用流程:
- 引入API模塊甩牺。
- 獲取與數(shù)據(jù)庫(kù)的連接蘑志。
- 執(zhí)行SQL語(yǔ)句和存儲(chǔ)過(guò)程。
- 關(guān)閉數(shù)據(jù)庫(kù)連接贬派。
1. MySQLdb
MySQLdb是用于Python鏈接Mysql數(shù)據(jù)庫(kù)的接口急但，它實(shí)現(xiàn)了Python 數(shù)據(jù)庫(kù)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 對(duì)事務(wù)提供了兩個(gè)方法：
-
commit()提交 -
rollback()回滾
cursor用來(lái)執(zhí)行命令的方法:
-
callproc(self, procname, args)用來(lái)執(zhí)行存儲(chǔ)過(guò)程,接收的參數(shù)為存儲(chǔ)過(guò)程名和參數(shù)列表,返回值為受影響的行數(shù) -
execute(self, query, args)執(zhí)行單條sql語(yǔ)句,接收的參數(shù)為sql語(yǔ)句本身和使用的參數(shù)列表,返回值為受影響的行數(shù) -
executemany(self, query, args)執(zhí)行單挑sql語(yǔ)句,但是重復(fù)執(zhí)行參數(shù)列表里的參數(shù),返回值為受影響的行數(shù) -
nextset(self)移動(dòng)到下一個(gè)結(jié)果集
cursor用來(lái)接收返回值的方法:
-
fetchall(self)接收全部的返回結(jié)果行. -
fetchmany(self, size=None)接收size條返回結(jié)果行.如果size的值大于返回的結(jié)果行的數(shù)量,則會(huì)返回cursor.arraysize條數(shù)據(jù). -
fetchone(self)返回一條結(jié)果行. -
rowcount這是一個(gè)只讀屬性,并返回執(zhí)行execute() 方法后影響的行數(shù)。 -
scroll(self, value, mode='relative')移動(dòng)指針到某一行; 如果mode='relative',則表示從當(dāng)前所在行移動(dòng)value條,如果 mode='absolute',則表示從結(jié)果集的第一行移動(dòng)value條.
實(shí)例
#!/usr/bin/env python
# -*- coding: utf-8 -*-
import MySQLdb as mdb
# 連接數(shù)據(jù)庫(kù)
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è)置自動(dòng)提交事務(wù)撒穷，或者在每次操作完成后手動(dòng)提交事務(wù)conn.commit()
conn.autocommit(1) # conn.autocommit(True)
# 使用cursor()方法獲取操作游標(biāo)
cursor = conn.cursor()
# 因該模塊底層其實(shí)是調(diào)用CAPI的，所以裆熙，需要先得到當(dāng)前指向數(shù)據(jù)庫(kù)的指針端礼。
try:
# 創(chuàng)建數(shù)據(jù)庫(kù)
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，占位符方面可能會(huì)出問(wèn)題入录，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向后移動(dòng);小于0向前移動(dòng)，mode默認(rèn)是relative
# relative:表示從當(dāng)前所在的行開(kāi)始移動(dòng); absolute:表示從第一行開(kāi)始移動(dòng)
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))
# 如果沒(méi)有設(shè)置自動(dòng)提交事務(wù)纷跛，則這里需要手動(dòng)提交一次
conn.commit()
except:
import traceback
traceback.print_exc()
# 發(fā)生錯(cuò)誤時(shí)會(huì)滾
conn.rollback()
finally:
# 關(guān)閉游標(biāo)連接
cursor.close()
# 關(guān)閉數(shù)據(jù)庫(kù)連接
conn.close()
查詢時(shí)返回字典結(jié)構(gòu)
MySQLdb默認(rèn)查詢結(jié)果都是返回tuple喻括，通過(guò)使用不同的游標(biāo)可以改變輸出格式，這里傳遞一個(gè)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ù)時(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é)果集的技巧
普通的操作無(wú)論是fetchall()還是fetchone()都是先將數(shù)據(jù)載入到本地再進(jìn)行計(jì)算，大量的數(shù)據(jù)會(huì)導(dǎo)致內(nèi)存資源消耗光唤崭。解決辦法是使用SSCurosr光標(biāo)來(lái)處理拷恨。
然而，在python3下谢肾，MySQLdb模塊不再提供支持腕侄，此時(shí)可以使用另一個(gè)模塊PyMySQL，它支持python2和python3芦疏。
2. PyMySQL
PyMySQL是一個(gè)純Python寫的MySQL客戶端冕杠，它的目標(biāo)是替代MySQLdb，可以在CPython酸茴、PyPy分预、IronPython和Jython環(huán)境下運(yùn)行。PyMySQL在MIT許可下發(fā)布薪捍。
PyMySQL的性能和MySQLdb幾乎相當(dāng)笼痹，如果對(duì)性能要求
不是特別的強(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ù)庫(kù)
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ā)生錯(cuò)誤時(shí)會(huì)滾
conn.rollback()
finally:
# 關(guān)閉游標(biāo)連接
cursor.close()
# 關(guān)閉數(shù)據(jù)庫(kù)連接
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'}
參考：
