---

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) ```