Python操作MySQL
Python操作MySQL
關於MySQL資料庫
開始之前
_mysql模組
MySQLdb模組
1. 第一個例子
2. 建立並填充表
3. 取回資料
4. 字典遊標
5. 列標題
6. 預處理語句
7. 寫入圖片
8. 讀取圖片
9. 事務支援
10. 使用SQLAlchemy
介紹用Python語言操作MySQL資料庫，覆蓋了基礎的Python MySQL程式設計內容。使用MySQLdb模組。
關於MySQL資料庫
MySQL是一個領先的開源資料庫管理系統。它是一個多使用者，多執行緒的資料庫管理系統。MySQL在網上非常流行，它與Linux、Apache、PHP一起組成LAMP平臺，這是一個非常流行的Web平臺。目前，MySQL是由甲骨文擁有。MySQL資料庫可用於最重要的幾個作業系統上。它能夠執行在BSD UNIX，Linux，Windows或Mac作業系統之上。
Wikipedia和YouTube均使用MySQL資料庫。這些站點每天處理數百萬的查詢次數。MySQL包含伺服器系統和嵌入式系統兩個版本。
開始之前
需要安裝幾個軟體包，才能確保可以正確地執行。如果沒有安裝MySQL，必須先安裝它：
$ sudo apt-get install mysql-server
以上命令會安裝MySQL伺服器，還包括一些相關的軟體包。
裝好MySQL資料庫的Python程式設計介面，MySQLdb模組。
接下來，我們會為本文的例子建立一個新的資料庫和資料庫使用者，我們使用MySQL客戶端來操作：
$ mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 30
Server version: 5.0.67-0ubuntu6 (Ubuntu)
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
+--------------------+
2 rows in set (0.00 sec)
使用root賬號連線到資料庫上，並用通過SHOW DATABASES語句列出所有資料庫名稱。
mysql> CREATE DATABASE testdb;
Query OK, 1 row affected (0.02 sec)
建立一個新的資料庫testdb，在本文的例子中都會用到這個資料庫。
mysql> CREATE USER 'testuser'@'localhost' IDENTIFIED BY 'test623';
Query OK, 0 rows affected (0.00 sec)
mysql> USE testdb;
Database changed
mysql> GRANT ALL ON testdb.* TO 'testuser'@'localhost';
Query OK, 0 rows affected (0.00 sec)
mysql> quit;
Bye
建立一個新的資料庫使用者，並且將testdb資料庫中的所有表的操作許可權全部授權給這個使用者。
_mysql模組
_mysql模組直接封裝實現了MySQL的C語言API函式。它與Python標準的資料庫API介面不相容。通常，程式設計師都更加喜歡物件導向的MySQLdb模組。我們也會更加關注後一個模組。在這裡，只是給出一個使用_mysql模組程式設計的小例子。
my-1.py
獲取並列印MySQL資料庫的版本，為此使用SELECT VERSION()這條SQL語句。
MySQLdb模組
MySQLdb是在_mysql模組的基礎上進一步進行封裝，並且與Python的標準資料庫API介面相容，這使得程式碼更易於移植。使用這個模組是操作MySQL資料庫的首選方法。
1. 第一個例子
在第一個例子中，我們來獲取MySQL資料庫的版本。
my-2.py
連線到testdb資料庫並執行 SELECT VERSION()語句。該指令碼返回MySQL資料庫的版本號，並列印到終端。
import MySQLdb as mdb
我們匯入MySQLdb模組。
con = None
我們將con變數初始化成None。在無法建立資料庫連線的時候（例如硬碟空間已滿），我們不能定義該資料庫連線變數。這將會導致在finally語句中執行出錯。
con = mdb.connect('localhost', 'testuser', 'test623', 'testdb');
我們連線到資料庫中，其中connect()方法有四個引數。第一個是MySQL資料庫所在的主機地址，在我們的例子中為'localhost'；第二個引數是資料庫的使用者名稱，其實是該使用者的密碼；最後一個引數是資料庫的名稱。
cur = con.cursor()
cur.execute("SELECT VERSION()")
一旦連線成為，我們將會得到一個cursor（遊標）物件。這個cursor物件用來遍歷結果集中的記錄。我們通過呼叫該curor物件的execute()方法來執行SQL語句。
data = cur.fetchone()
我們開始獲取資料，由於我們只取回一個記錄，因此我們呼叫fetchone()方法。
print "Database version : %s " % data
我們將取回的資料列印到終端。
except mdb.Error, e:
print "Error %d: %s" % (e.args[0],e.args[1])
sys.exit(1)
我們檢查錯誤，這是很重要的，因為與在與資料庫打交道的過程中很容易出錯。
finally:
if con:
con.close()
最後一步，我們釋放連線的資源。
2. 建立並填充表
建立一個表，並在其中填入一些資料。
my-3.py
在這裡使用了with..的語法，在退出with語句之前，會執行con物件的__exit__方法，猜測在該方法中會呼叫commit方法提交事務。
建立一張記錄作家名字的資料庫表，並往其中新增五位作者。
cur.execute("CREATE TABLE IF NOT EXISTS \
Writers(Id INT PRIMARY KEY AUTO_INCREMENT, Name VARCHAR(25)) ENGINE=MyISAM;")
這條SQL語句建立了一個名為Writers的新資料庫，它包含Id和Name兩列。
cur.execute("INSERT INTO Writers(Name) VALUES('Jack London')")
cur.execute("INSERT INTO Writers(Name) VALUES('Honore de Balzac')")
...
使用INSERT語句插入作者到表中。
mysql> SELECT * FROM Writers;
+----+-------------------+
| Id | Name |
+----+-------------------+
| 1 | Jack London |
| 2 | Honore de Balzac |
| 3 | Lion Feuchtwanger |
| 4 | Emile Zola |
| 5 | Truman Capote |
+----+-------------------+
5 rows in set (0.00 sec)
在指令碼執行之後，我們通過MySQL的客戶端工具來查詢Writers表中的所有資料。
3. 取回資料
現在，在往表中插入一些數之後，我們想要將資料取回。
my-4.py
從Writers表中取回所有資料。
cur.execute("SELECT * FROM Writers")
這條SQL語句從Writers表中查詢所有的資料。
rows = cur.fetchall()
fetchall()方法獲取所有記錄。它返回一個結果集，該結果集其實是一個包含元組的元組。在元組內部的第一個元組程式碼表中的一行。
for row in rows:
print row
我們以行的方式將資料列印到終端。
一次性返回所有的資料未必可行，我們可以取逐行依次取回。
my-5.py
我們仍然從Writers表中取回資料，並列印到終端。這次我們逐行獲取資料。
numrows = int(cur.rowcount)
這裡我們首先確認SQL語句查詢結果中包含的行數。
for i in range(numrows):
row = cur.fetchone()
print row[0], row[1]
使用fetchone()方法逐行獲取資料。
4. 字典遊標
在 MySQLdb 模組中有許多種遊標型別。預設的遊標型別以元組的元組形式返回資料。當我們使用字典遊標時，這些資料是以Python字典的形式返回。這樣一來，我們就可以通過列名來訪問資料。
my-6.py
使用字典遊標來列印Writers表的內容。
cur = con.cursor(mdb.cursors.DictCursor)
建立字典遊標。
rows = cur.fetchall()
然後返回所有的資料。
for row in rows:
print "%s %s" % (row["Id"], row["Name"])
接著，使用Writers表的列名來引用相應的資料。
5. 列標題
接下來，展示如何將列標題同表資料一同列印出來。
my-7.py
6. 預處理語句
現在我們開始把重心放到預處理語句上。當寫預處理語句時，我們會在語句中使用佔位符，而不是直接將值填到語句當中去。預處理語句提高了安全性和效能。在Python的標準資料庫API文件中提供了多種不同方式來構建預處理語句。MySQLdb支援其中的一種形式——符合ANSI標準的printf格式串。
my-8.py
修改第4行作者的名字。
cur.execute("UPDATE Writers SET Name = %s WHERE Id = %s",
("Guy de Maupasant", "4"))
以上程式碼展示瞭如何使用預處理語句，我們用到了兩個%s佔位符。在SQL語句被執行之前，相應的佔位符會被替換成實際的值。
mysql> SELECT Name FROM Writers WHERE Id=4;
+------------------+
| Name |
+------------------+
| Guy de Maupasant |
+------------------+
1 row in set (0.00 sec)
可見，位於表第四行的作者的名字被成功更改了。
7. 寫入圖片
一些人喜歡將他們的圖片放到資料庫中，也有一些人是將圖片保留在檔案系統中。當處理數以百萬計的圖片時，我們往往會遇到一些技術的困難。圖片是一種二進位制資料。MySQL中有一種特殊的資料型別用來儲存二進位制資料，它和名稱叫做BLOB（Binary Large Object，二進位制大物件）。
mysql> CREATE TABLE Images(Id INT PRIMARY KEY AUTO_INCREMENT, Data MEDIUMBLOB);
Query OK, 0 rows affected (0.06 sec)
為此，我們建立一個新的資料庫表名稱為Images。
my-9.py
讀取一張png格式的圖片並將其插入到Images表中。
fin = open("chrome.png")
img = fin.read()
開啟圖片檔案並讀入，read()函式返回字串形式的資料。
cursor.execute("INSERT INTO Images SET Data='%s'" % \
mdb.escape_string(img))
字串資料被插入到表中，不過在此之前，它需要經過escape_string()方法處理。轉義字串作為查詢引數使用。這是常見的做法，以避免惡意的SQL隱碼攻擊。
8. 讀取圖片
在上一個例子中，我們已經將一張圖片插入到資料庫表中。現在，我準備將圖片從資料庫中重新取回來。
my-10.py
從Images表中讀取一張圖片。
cursor.execute("SELECT Data FROM Images LIMIT 1")
通過select語句從表中查詢一條記錄。
fout = open('image.png','wb')
然後，開啟一個可寫入的二進位制檔案。
fout.write(cursor.fetchone()[0])
將剛通過SQL語句從資料庫表中取的資料寫入到檔案中。現在我們已經在當前目錄儲存了一張名為image.png的圖片，可以對比下和原來插入表中的圖片是否一致。
9. 事務支援
事務是指在一個或者多個資料庫中對資料的原子操作。在一個事務中，所有SQL語句的影響要麼全部提交到資料庫，要麼就全部回滾。
對於支援事務機制的資料庫，Python介面在建立遊標時，默默地開始一個事務。可以通過遊標物件的commit()方法提交遊標操作引起的所有更新操作，或者使用rollback()方法丟棄這麼更新操作。每個方法執行完後都會開始一個新的事務。
MySQL資料庫有多種不同的儲存引擎。其中最常見的是MyISAM和InnoDB引擎，而MyISAM是預設的一個。需要在資料的安全性和資料庫的速度之間作一個權衡。MyISAM表處理速度比較快，但是它們不支援事務，所以commit()和rollback()方法也沒有實現，這時呼叫這些方法不會做任何事情。另外一方面，InnoDB在預防資料丟失方面比較安全，它們支援事務，但是處理速度相對比較慢。
my-11.py
在這個指令碼中，我們試圖更新三行資料。表儲存引擎為MyISAM。
cursor.execute("UPDATE Writers SET Name = %s WHERE Id = %s",
("Leo Tolstoy", "1"))
cursor.execute("UPDATE Writers SET Name = %s WHERE Id = %s",
("Boris Pasternak", "2"))
這裡我們更改了第一行和第二行的作者名字。
cursor.execute("UPDATE Writer SET Name = %s WHERE Id = %s",
("Leonid Leonov", "3"))
上面的SQL語句會執行出錯，因為不存在名為Writer的表。
conn.rollback()
SQL語句執行出錯後丟擲異常，在異常處理中呼叫rollback()方法，但是事實上它並沒有起作用。
mysql> SELECT * FROM Writers;
+----+-------------------+
| Id | Name |
+----+-------------------+
| 1 | Leo Tolstoy |
| 2 | Boris Pasternak |
| 3 | Lion Feuchtwanger |
| 4 | Guy de Maupasant |
| 5 | Truman Capote |
+----+-------------------+
5 rows in set (0.00 sec)
通過執行這個指令碼，結果正如我們預期的一樣，首兩行已經被更改。
重新建立Writers表。這一次，表的型別將會是InnoDB。MySQL InnoDB資料庫表支援事務。
DROP TABLE Writers;
CREATE TABLE IF NOT EXISTS Writers(Id INT PRIMARY KEY AUTO_INCREMENT,
Name VARCHAR(25)) ENGINE=INNODB;
INSERT INTO Writers(Name) VALUES('Jack London');
INSERT INTO Writers(Name) VALUES('Honore de Balzac');
INSERT INTO Writers(Name) VALUES('Lion Feuchtwanger');
INSERT INTO Writers(Name) VALUES('Emile Zola');
INSERT INTO Writers(Name) VALUES('Truman Capote');
將以上內容儲存到writers.sql檔案中，它被用來重新建立Writers表。
mysql> source writers.sql
Query OK, 0 rows affected (0.03 sec)
Query OK, 0 rows affected (0.10 sec)
Query OK, 1 row affected (0.02 sec)
Query OK, 1 row affected (0.03 sec)
Query OK, 1 row affected (0.02 sec)
Query OK, 1 row affected (0.02 sec)
Query OK, 1 row affected (0.02 sec)
在MySQL命令列中使用source命令載入並執行這些語句。
my-12.py
這次從資料庫表中刪除三行資料，當執行到第三行時會出錯。
Error 1146: Table 'testdb.Writer' doesn't exist
mysql> SELECT * FROM Writers;
+----+-------------------+
| Id | Name |
+----+-------------------+
| 1 | Jack London |
| 2 | Honore de Balzac |
| 3 | Lion Feuchtwanger |
| 4 | Emile Zola |
| 5 | Truman Capote |
+----+-------------------+
5 rows in set (0.00 sec)
錯誤發生在我們提交更改之前，rollback()方法被呼叫，從而實際上沒有刪除操作發生。
10. 使用SQLAlchemy
資料庫表是一個二維表，包含多行多列。把一個表的內容用Python的資料結構表示出來的話，可以用一個list表示多行，list的每一個元素是tuple，表示一行記錄，比如，包含id和name的user表：
[
('1', 'Michael'),
('2', 'Bob'),
('3', 'Adam')
]
Python的DB-API返回的資料結構就是像上面這樣表示的。
但是用tuple表示一行很難看出表的結構。如果把一個tuple用class例項來表示，就可以更容易地看出表的結構來：
class User(object):
def __init__(self, id, name):
self.id = id
self.name = name
[
User('1', 'Michael'),
User('2', 'Bob'),
User('3', 'Adam')
]
這就是傳說中的ORM技術：Object-Relational Mapping，把關聯式資料庫的表結構對映到物件上。是不是很簡單？
但是由誰來做這個轉換呢？所以ORM框架應運而生。
在Python中，最有名的ORM框架是SQLAlchemy。我們來看看SQLAlchemy的用法。
首先通過pip安裝SQLAlchemy：
$pip install sqlalchemy
然後，在MySQL的testdb資料庫中建立user表：
create table user (id varchar(20) primary key, name varchar(20));
insert into user (id, name) values ('1','Liao');
insert into user (id, name) values ('2','Zhao');
insert into user (id, name) values ('3','Qian');
select * from user;
使用SQLAlchemy：my-13.py
第一步，匯入SQLAlchemy，並初始化DBSession：
# 匯入:
from sqlalchemy import Column, String, create_engine
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base
# 建立物件的基類:
Base = declarative_base()
# 定義User物件:
class User(Base):
# 表的名字:
__tablename__ = 'user'
# 表的結構:
id = Column(String(20), primary_key=True)
name = Column(String(20))
# 初始化資料庫連線:
engine = create_engine('mysql+mysqldb://root:liaoxiaofei999@localhost:3306/testdb')
# 建立DBSession型別:
DBSession = sessionmaker(bind=engine)
以上程式碼完成SQLAlchemy的初始化和具體每個表的class定義。如果有多個表，就繼續定義其他class，例如School：
class School(Base):
__tablename__ = 'school'
id = ...
name = ...
create_engine()用來初始化資料庫連線。SQLAlchemy用一個字串表示連線資訊：
'資料庫型別+資料庫驅動名稱://使用者名稱:口令@機器地址:埠號/資料庫名'
你只需要根據需要替換掉使用者名稱、口令等資訊即可。
下面，我們看看如何向資料庫表中新增一行記錄。
由於有了ORM，我們向資料庫表中新增一行記錄，可以視為新增一個User物件：
# 建立session物件:
session = DBSession()
# 建立新User物件:
new_user = User(id='5', name='Bob')
# 新增到session:
session.add(new_user)
# 提交即儲存到資料庫:
session.commit()
# 關閉session:
session.close()
可見，關鍵是獲取session，然後把物件新增到session，最後提交併關閉。Session物件可視為當前資料庫連線。
如何從資料庫表中查詢資料呢？有了ORM，查詢出來的可以不再是tuple，而是User物件。SQLAlchemy提供的查詢介面如下：
建立Session:
session = DBSession()
# 建立Query查詢，filter是where條件，最後呼叫one()返回唯一行，如果呼叫all()則返回所有行:
user = session.query(User).filter(User.id=='5').one()
# 列印型別和物件的name屬性:
print 'type:', type(user)
print 'name:', user.name
# 關閉Session:
session.close()
執行結果如下：
type: <class '__main__.User'>
name: Bob
可見，ORM就是把資料庫表的行與相應的物件建立關聯，互相轉換。
由於關聯式資料庫的多個表還可以用外來鍵實現一對多、多對多等關聯，相應地，ORM框架也可以提供兩個物件之間的一對多、多對多等功能。
例如，如果一個User擁有多個Book，就可以定義一對多關係如下：
class User(Base):
__tablename__ = 'user'
id = Column(String(20), primary_key=True)
name = Column(String(20))
# 一對多:
books = relationship('Book')
class Book(Base):
__tablename__ = 'book'
id = Column(String(20), primary_key=True)
name = Column(String(20))
# “多”的一方的book表是通過外來鍵關聯到user表的:
user_id = Column(String(20), ForeignKey('user.id'))
當我們查詢一個User物件時，該物件的books屬性將返回一個包含若干個Book物件的list。
因此，ORM框架的作用就是把資料庫表的一行記錄與一個物件互相做自動轉換。
正確使用ORM的前提是瞭解關聯式資料庫的原理。
複製程式碼