Python 操作資料庫（1）

在關系資料庫中，資料庫表是一系列二維數組的集合，用來代表和儲存資料對象之間的關系。它由縱向的列和橫向的行組成，例如一個有關作者資訊的名為 authors 的表中，每個列包含的是所有作者的某個特定類型的資訊，比如“姓氏”，而每行則包含了某個特定作者的所有資訊：姓、名、住址等等。

對于特定的資料庫表，列的數目一般事先固定，各列之間可以由列名來識别。而行的數目可以随時、動态變化，每行通常都可以根據某個（或某幾個）列中的資料來識别，稱為候選鍵。

我打算在mytest中建立一個存儲使用者名、使用者密碼、使用者郵箱的表，其結構用二維表格表現如下：

username	password	email
hiekay	123123	[email protected]

特别說明，這裡為了簡化細節，突出重點，對密碼不加密，直接明文儲存，雖然這種方式是很不安全的。

建資料庫表并插入資料

為了在資料庫中建立這個表，需要進入到

mysql>

互動模式中操作。道理在于，如果mytest這個屋子裡面沒有類似家具的各種資料庫表，即使進了屋子也沒有什麼好操作的東西，是以需要先到

mysql>

模式下在屋子裡面擺家具。

進入資料庫互動模式：

$ mysql -u root -p
Enter password: 
           

調用已經建立的資料庫：mytest

mysql> use mytest;
Database changed
mysql> show tables;
Empty set (0.00 sec)
           

用

show tables

指令顯示這個資料庫中是否有資料表了。查詢結果顯示為空。

下面就用如下指令建立一個資料表，這個資料表的内容就是上面所說明的。

mysql> create table users(id int(2) not null primary key auto_increment,username varchar(40),password text,email text)default charset=utf8;
Query OK, 0 rows affected (0.12 sec)
           

建立的這個資料表名稱是：users，其中包含上述字段，可以用下面的方式看一看這個資料表的結構。

mysql> show tables;
+------------------+
| Tables_in_mytest |
+------------------+
| users            |
+------------------+
1 row in set (0.00 sec)

           

查詢顯示，在mytest這個資料庫中，已經有一個表，它的名字是：users。

mysql> desc users;
+----------+-------------+------+-----+---------+----------------+
| Field    | Type        | Null | Key | Default | Extra          |
+----------+-------------+------+-----+---------+----------------+
| id       | int(2)      | NO   | PRI | NULL    | auto_increment |
| username | varchar(40) | YES  |     | NULL    |                |
| password | text        | YES  |     | NULL    |                |
| email    | text        | YES  |     | NULL    |                |
+----------+-------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
           

顯示表users的結構：

• id：每增加一個使用者，id号自動增加一個。
• username：存儲使用者名，類型是varchar(40)
• password：存儲使用者密碼，類型是text
• email：存儲使用者的郵箱，類型是text

特别提醒：在這裡，我沒有對每個字段做注入不得為空等設定，在真正的開發中，或許必須讓username和password不得為空。

這個結構和上面所期望的結構是一樣的，隻不過這個表中還沒有任何資料，是一個空表。可以查詢一下看看：

mysql> select * from users;
Empty set (0.01 sec)
           

目前表是空的，為了能夠在後面用python操作這個資料表，需要向裡面插入點資訊，就隻插入一條吧。

mysql> insert into users(username,password,email) values("hiekay","123123","[email protected]");
Query OK, 1 row affected (0.05 sec)

mysql> select * from users;
+----+----------+----------+------------------+
| id | username | password | email            |
+----+----------+----------+------------------+
|  1 | hiekay   | 123123   | [email protected] |
+----+----------+----------+------------------+
1 row in set (0.00 sec)
           

到目前為止，在

mysql>

中的工作已經完成了，接下來就是用python操作了。

python操作資料庫

要對資料庫進行操作，需要先連接配接它。後面就會講解如何在程式中自動完成了。

>>> import MySQLdb
>>> conn = MySQLdb.connect(host="localhost",user="root",passwd="123123",db="mytest",charset="utf8")
           

完成連接配接的過程，其實是建立了一個

MySQLdb.connect()

的執行個體對象conn，那麼這個對象有哪些屬性呢？

• commit():如果資料庫表進行了修改，送出儲存目前的資料。當然，如果此使用者沒有權限就作罷了，什麼也不會發生。
• rollback():如果有權限，就取消目前的操作，否則報錯。
• cursor([cursorclass]):遊标指針。下面詳解。

連接配接成功之後，開始操作。注意：MySQLdb用遊标（指針）cursor的方式操作資料庫，就是這樣：

>>> cur = conn.cursor()
           

因該子產品底層其實是調用CAPI的，是以，需要先得到目前指向資料庫的指針。這也就提醒我們，在操作資料庫的時候，指針會移動，如果移動到資料庫最後一條了，再查，就查不出什麼來了。看後面的例子就明白了。

下面用cursor()提供的方法來進行操作，方法主要是:

1. 執行指令
2. 接收結果

cursor執行指令的方法:

• execute(query, args):執行單條sql語句。query為sql語句本身，args為參數值的清單。執行後傳回值為受影響的行數。
• executemany(query, args):執行單條sql語句,但是重複執行參數清單裡的參數,傳回值為受影響的行數

例如，要在資料表users中插入一條記錄，使得:username="python",password="123456",email="

[email protected]

"，這樣做：

>>> cur.execute("insert into users (username,password,email) values (%s,%s,%s)",("python","123456","[email protected]"))
1L
           

沒有報錯，并且傳回一個"1L"結果，說明有一行記錄操作成功。不妨用"mysql>"互動方式檢視一下：

mysql> select * from users;
+----+----------+----------+------------------+
| id | username | password | email            |
+----+----------+----------+------------------+
|  1 | hiekay   | 123123   | [email protected] |
+----+----------+----------+------------------+
1 row in set (0.00 sec)
           

咦，奇怪呀。怎麼沒有看到增加的那一條呢？哪裡錯了？可是上面也沒有報錯呀。

在這裡，特别請列位看官注意，通過"cur.execute()"對資料庫進行操作之後，沒有報錯，完全正确，但是不等于資料就已經送出到資料庫中了，還必須要用到"MySQLdb.connect"的一個屬性：commit()，将資料送出上去，也就是進行了"cur.execute()"操作，要将資料送出，必須執行：

>>> conn.commit()
           

在到"mysql>"中運作"select * from users"試一試：

mysql> select * from users;
+----+----------+----------+------------------+
| id | username | password | email            |
+----+----------+----------+------------------+
|  1 | hiekay   | 123123   | [email protected] |
|  2 | python   | 123456   | [email protected] |
+----+----------+----------+------------------+
2 rows in set (0.00 sec)
           

good，very good。果然如此。這就如同編寫一個文本一樣，将文字寫到文本上，并不等于文字已經保留在文本檔案中了，必須執行"CTRL-S"才能儲存。也就是在通過python操作資料庫的時候，以"execute()"執行各種sql語句之後，要讓已經執行的效果儲存，必須運作"commit()"，還要提醒，這個屬性是"MySQLdb.connect()"執行個體的。

再嘗試一下插入多條的那個指令"executemany(query,args)".

>>> cur.executemany("insert into users (username,password,email) values (%s,%s,%s)",(("google","111222","[email protected]"),("facebook","222333","[email protected]"),("github","333444","[email protected]"),("docker","444555","[email protected]")))
4L
>>> conn.commit()
           

到"mysql>"裡面看結果：

mysql> select * from users;
+----+----------+----------+------------------+
| id | username | password | email            |
+----+----------+----------+------------------+
|  1 | hiekay   | 123123   | [email protected] |
|  2 | python   | 123456   | [email protected] |
|  3 | google   | 111222   | [email protected]      |
|  4 | facebook | 222333   | [email protected]      |
|  5 | github   | 333444   | [email protected]      |
|  6 | docker   | 444555   | [email protected]      |
+----+----------+----------+------------------+
6 rows in set (0.00 sec)
           

成功插入了多條記錄。特别請注意的是，在"executemany(query,args)"中，query還是一條sql語句，但是args這時候是一個tuple，這個tuple裡面的元素也是tuple，每個tuple分别對應sql語句中的字段清單。這句話其實被執行多次。隻不過執行過程不顯示給我們看罷了。

已經會插入了，然後就可以有更多動作。

• • *