---

theme: smartblue

公眾號：尤而小屋
作者：Peter
編輯：Peter

大家好，我是Peter~

本文介紹的是如何使用Pandas來操作MySQL資料庫。主要是包含查詢MySQL中的資料，以及往資料庫中寫入資料。

先安裝兩個庫：

• pymysql
• sqlalchemy

python pip install pymysql pip install sqlalchemy

本地資料庫

檢視一個本地資料庫中某個表的資料。這份資料是《MySQL經典50題》的一個表之一：

部分習題答案：

```sql mysql -u root -p -- 安裝mysql，進入資料庫輸入暗文密碼

show databases; -- 顯示全部資料庫 use test; -- 使用某個資料庫 show tables; -- 檢視資料庫下的全部表 select * from Student; -- 檢視某個表的全部內容 ```

操作MySQL

連線MySQL

以pymysql模組為例，講解如何連線資料庫。

In [1]：

``` import pandas as pd

import pymysql from sqlalchemy import create_engine ```

In [2]:

```python connection = pymysql.connect( host='127.0.0.1', # 本機ip地址 port=3306, # mysql預設埠號 user="root", # 使用者名稱 password="password", # 密碼 charset="utf8", # 字符集 db="test" # 資料庫 )

cur = connection.cursor() # 建立遊標 cur ```

Out[2]:

<pymysql.cursors.Cursor at 0x11ddc1190>

connect()方法常用的引數：

| connect() 常用引數 | 說明 | | ------------------ | ------ | | host | 主機ip | | user | 使用者名稱 | | password | 密碼 | | database | 資料庫 | | port | 埠號 | | charset | 字符集 |

呼叫 cursor() 方法即可返回一個新的遊標物件，在連線沒有關閉之前，遊標物件可以反覆使用

執行sql查詢語句

In [3]:

```python sql=""" # 待執行的sql語句 select * from Student; """

執行sql語句

cur.execute(sql)
```

Out[3]:

8

In [4]:

cur

結果表明是個遊標物件：

Out[4]:

<pymysql.cursors.Cursor at 0x11ddc1190>

In [5]:

cur.description

主要返回遊標的屬性資訊，官網的描述為：

Out[5]:

(('s_id', 253, None, 20, 20, 0, False), ('s_name', 253, None, 20, 20, 0, True), ('s_birth', 253, None, 20, 20, 0, True), ('s_sex', 253, None, 20, 20, 0, True))

In [6]:

```python

列名

columns = [col[0] for col in cur.description] columns ```

Out[6]:

['s_id', 's_name', 's_birth', 's_sex']

遊標使用

下圖顯示的是如何取出一條或者多條資料（按照順序查詢）

通過遊標獲取全部的資料：

fetch相關的函式都是獲取結果集中剩下的資料，多次呼叫的時候只會從剩餘資料中查詢：

當第二次呼叫的時候結果就是空集。

通過遊標獲取查詢的結果集的特點：

1. 可以獲取1條、多條和全部資料
2. 在獲取資料的時候是按照順序讀取的
3. fetchall函式返回剩下的所有行
4. 如果是末尾，則返回空元組；
5. 否則返回一個元組，其元素是每一行的記錄封裝的一個元組

轉成DataFrame

```python

列名

columns = [col[0] for col in cur.description]

資料集合

data = [] for i in cur.fetchall(): data.append(i)

df = pd.DataFrame(data,columns=columns) ```

儲存成CSV資料

SQL插入資料

往MySQL資料庫中插入資料：

```python import pandas as pd

import pymysql from sqlalchemy import create_engine

connection = pymysql.connect( host='127.0.0.1', # 本機ip地址 port=3306, # mysql預設埠號 user="root", # 使用者名稱 password="11112222", # 密碼 charset="utf8", # 字符集 db="test" # 資料庫 )

cur = connection.cursor() # 建立遊標

待執行SQL語句

sql="""
insert into test.Student(s_id, s_name, s_birth, s_sex) values("09","吳越","1998-08-08","男") """

執行sql語句

cur.execute(sql)
```

很關鍵一步，要記得提交，這樣最終才會寫入資料庫：

python connection.commit()

執行SQL刪除語句

使用完之後記得關閉連線：

python connection.close()

使用sqlalchemy

第二種常用的方法是通過sqlalchemy來連線資料庫：

連線資料庫

```python import pandas as pd from sqlalchemy import create_engine

依次填寫MySQL的使用者名稱、密碼、IP地址、埠、資料庫名

create_engine("資料庫型別+資料庫驅動://資料庫使用者名稱:資料庫密碼@IP地址:埠/資料庫"，其他引數)

engine = create_engine("mysql+pymysql://root:password@localhost:3306/test") ```

查詢語句1

查詢語句2

寫入資料

將Pandas中的DataFrame寫入新的表testdf中：

python show tables;

使用read_sql讀取

使用Pandas自帶的read_sql函式能夠自行讀取資料，讀取上面建立的資料.

官網：http://pandas.pydata.org/docs/reference/api/pandas.read_sql.html

```python import pandas as pd from sqlalchemy import create_engine

依次填寫MySQL的使用者名稱、密碼、IP地址、埠、資料庫名

engine = create_engine("mysql+pymysql://root:password@localhost:3306/test")

sql語句

sql4 = "select * from testdf;" df4 = pd.read_sql(sql4, engine) ```