在Python程序中操作MySQL的基本方法

2019-11-25 17:07:02

字體：大中小

來源：轉載

供稿：網友

Python操作Mysql

最近在學習python，這種腳本語言毫無疑問的會跟數據庫產生關聯，因此這里介紹一下如何使用python操作mysql數據庫。我python也是零基礎學起，所以本篇博客針對的是python初學者，大牛可以選擇繞道。

另外，本篇基于的環境是Ubuntu13.10，使用的python版本是2.7.5。
MYSQL數據庫

MYSQL是一個全球領先的開源數據庫管理系統。它是一個支持多用戶、多線程的數據庫管理系統，與Apache、PHP、Linux共同組成LAMP平臺，在web應用中廣泛使用，例如Wikipedia和YouTube。MYSQL包含兩個版本：服務器系統和嵌入式系統。
環境配置

在我們開始語法學習之前，還需要按裝mysql和python對mysql操作的模塊。

安裝mysql:

sudo apt-get install mysql-server

安裝過程中會提示你輸入root帳號的密碼，符合密碼規范即可。

接下來，需要安裝python對mysql的操作模塊：

sudo apt-get install python-mysqldb

這里需要注意：安裝完python-mysqldb之后，我們默認安裝了兩個python操作模塊，分別是支持C語言API的_mysql和支持Python API的MYSQLdb。稍后會重點講解MYSQLdb模塊的使用。

接下來，我們進入MYSQL，創建一個測試數據庫叫testdb。創建命令為：

create database testdb;

然后，我們創建一個測試賬戶來操作這個testdb數據庫，創建和授權命令如下：

create user 'testuser'@'127.0.0.1' identified by 'test123';grant all privileges on testdb.* to 'testuser'@'127.0.0.1';_mysql module

_mysql模塊直接封裝了MYSQL的C語言API函數，它與python標準的數據庫API接口是不兼容的。我更推薦大家使用面向對象的MYSQLdb模塊才操作mysql，這里只給出一個使用_mysql模塊的例子，這個模塊不是我們學習的重點，我們只需要了解有這個模塊就好了。

#!/usr/bin/python# -*- coding: utf-8 -*-import _mysqlimport systry: con = _mysql.connect('127.0.0.1', 'testuser', 'test123', 'testdb') con.query("SELECT VERSION()") result = con.use_result() print "MYSQL version : %s " % result.fetch_row()[0]except _mysql.Error, e: print "Error %d: %s %s" % (e.args[0], e.args[1]) sys.exit(1)finally: if con:  con.close()

這個代碼主要是獲取當前mysql的版本，大家可以模擬敲一下這部分代碼然后運行一下。
MYSQLdb module

MYSQLdb是在_mysql模塊的基礎上進一步進行封裝，并且與python標準數據庫API接口兼容，這使得代碼更容易被移植。Python更推薦使用這個MYSQLdb模塊來進行MYSQL操作。

#!/usr/bin/python# -*- coding: utf-8 -*-import MySQLdb as mysqltry: conn = mysql.connect('127.0.0.1', 'testuser', 'test123', 'testdb') cur = conn.cursor() cur.execute("SELECT VERSION()") version = cur.fetchone() print "Database version : %s" % versionexcept mysql.Error, e: print "Error %d:%s" % (e.args[0], e.args[1]) exit(1)finally: if conn:  conn.close()

我們導入了MySQLdb模塊并把它重命名為mysql，然后調用MySQLdb模塊的提供的API方法來操作數據庫。同樣也是獲取當前主機的安裝的mysql版本號。
創建新表

接下來，我們通過MySQLdb模塊創建一個表，并在其中填充部分數據。實現代碼如下：

#!/usr/bin/python# -*- coding: utf-8 -*-import MySQLdb as mysqlconn = mysql.connect('127.0.0.1', 'testuser', 'test123', 'testdb');with conn: cur = conn.cursor() cur.execute("DROP TABLE IF EXISTS writers"); cur.execute("CREATE TABLE writers(id INT PRIMARY KEY AUTO_INCREMENT, name varchar(25))") cur.execute("insert into writers(name) values('wangzhengyi')") cur.execute("insert into writers(name) values('bululu')") cur.execute("insert into writers(name) values('chenshan')")

這里使用了with語句。with語句會執行conn對象的enter()和__exit()方法，省去了自己寫try/catch/finally了。

執行完成后，我們可以通過mysql-client客戶端查看是否插入成功，查詢語句：

select * from writers;

查詢結果如下：

2015729102658336.jpg (965×174)

查詢數據

剛才往表里插入了部分數據，接下來，我們從表中取出插入的數據，代碼如下：

#!/usr/bin/pythonimport MySQLdb as mysqlconn = mysql.connect('127.0.0.1', 'testuser', 'test123', 'testdb');with conn: cursor = conn.cursor() cursor.execute("select * from writers") rows = cursor.fetchall() for row in rows:  print row

查詢結果如下：

(1L, 'wangzhengyi')(2L, 'bululu')(3L, 'chenshan')

dictionary cursor

我們剛才不論是創建數據庫還是查詢數據庫，都用到了cursor。在MySQLdb模塊有許多種cursor類型，默認的cursor是以元組的元組形式返回數據的。當我們使用dictionary cursor時，數據是以python字典形式返回的。這樣我們就可以通過列名獲取查詢數據了。

還是剛才查詢數據的代碼，改為dictionary cursor只需要修改一行代碼即可，如下所示：

#!/usr/bin/pythonimport MySQLdb as mysqlconn = mysql.connect('127.0.0.1', 'testuser', 'test123', 'testdb');with conn: cursor = conn.cursor(mysql.cursors.DictCursor) cursor.execute("select * from writers") rows = cursor.fetchall() for row in rows:  print "id is %s, name is %s" % (row['id'], row['name'])

使用dictionary cursor，查詢結果如下：

id is 1, name is wangzhengyiid is 2, name is bululuid is 3, name is chenshan

預編譯

之前寫過php的同學應該對預編譯很了解，預編譯可以幫助我們防止sql注入等web攻擊還能幫助提高性能。當然，python肯定也是支持預編譯的。預編譯的實現也比較簡單，就是用%等占位符來替換真正的變量。例如查詢id為3的用戶的信息，使用預編譯的代碼如下：

#!/usr/bin/pythonimport MySQLdb as mysqlconn = mysql.connect('127.0.0.1', 'testuser', 'test123', 'testdb');with conn: cursor = conn.cursor(mysql.cursors.DictCursor) cursor.execute("select * from writers where id = %s", "3") rows = cursor.fetchone() print "id is %d, name is %s" % (rows['id'], rows['name'])

我這里使用了一個%s的占位符來替換“3”，代表需要傳入的是一個字符串類型。如果傳入的不是string類型，則會運行報錯。
事務

事務是指在一個或者多個數據庫中對數據的原子操作。在一個事務中，所有的SQL語句的影響要不就全部提交到數據庫，要不就全部都回滾。

對于支持事務機制的數據庫，python接口在創建cursor的時候就開始了一個事務。可以通過cursor對象的commit()方法來提交所有的改動，也可以使用cursor對象的rollback方法來回滾所有的改動。

我這里寫一個代碼，對不存在的表進行插入操作，當拋出異常的時候，調用rollback進行回滾，實現代碼如下：

#!/usr/bin/python# -*- coding: utf-8 -*-import MySQLdb as mysqltry: conn = mysql.connect('127.0.0.1', 'testuser', 'test123', 'testdb'); cur = conn.cursor() cur.execute("insert into writers(name) values('wangzhengyi4')") cur.execute("insert into writers(name) values('bululu5')") cur.execute("insert into writerss(name) values('chenshan6')") conn.commit()except mysql.Error, e: if conn:  conn.rollback()  print "Error happens, rollback is call"finally: if conn:  conn.close()

執行結果如下：