公眾號：尤而小屋
作者：Peter
編輯：Peter
大家好，我是Peter~
本文介紹的是如何使用Pandas來操作MySQL數(shù)據(jù)庫。主要是包含查詢MySQL中的數(shù)據(jù)，以及如何往數(shù)據(jù)庫中寫入數(shù)據(jù)。
先安裝兩個庫：
pip install pymysql
pip install sqlalchemy
本地數(shù)據(jù)庫
查看一個本地數(shù)據(jù)庫中某個表的數(shù)據(jù)。這份數(shù)據(jù)是《MySQL經(jīng)典50題》的一個表之一：
《MySQL經(jīng)典50題》Pandas版本！
《SQL必知必會》萬字精華！
MySQL必須掌握4種語言！
面試必備：SQL排名和窗口函數(shù)
部分習(xí)題答案：
mysql -u root -p -- 安裝mysql，進入數(shù)據(jù)庫輸入暗文密碼
show databases; -- 顯示全部數(shù)據(jù)庫
use test; -- 使用某個數(shù)據(jù)庫
show tables; -- 查看數(shù)據(jù)庫下的全部表
select * from Student; -- 查看某個表的全部內(nèi)容
操作MySQL
連接MySQL
以pymysql模塊為例，講解如何連接數(shù)據(jù)庫。
In [1]：
import pandas as pd
import pymysql
from sqlalchemy import create_engine
In [2]:
connection = pymysql.connect(
host='127.0.0.1', # 本機ip地址
port=3306, # mysql默認端口號
user='root', # 用戶名
password='password', # 密碼
charset='utf8', # 字符集
db='test' # 數(shù)據(jù)庫
)
cur = connection.cursor() # 建立游標
cur
Out[2]:
<pymysql.cursors.Cursor at 0x11ddc1190>
connect()方法常用的參數(shù)：
| connect() 常用參數(shù) | 說明 |
|---|
| host | 主機ip |
| user | 用戶名 |
| password | 密碼 |
| database | 數(shù)據(jù)庫 |
| port | 端口號 |
| charset | 字符集 |
調(diào)用 cursor() 方法即可返回一個新的游標對象，在連接沒有關(guān)閉之前，游標對象可以反復(fù)使用
執(zhí)行sql查詢語句
In [3]:
sql=''' # 待執(zhí)行的sql語句
select * from Student;
'''
# 執(zhí)行sql語句
cur.execute(sql)
Out[3]:
8
In [4]:
cur
結(jié)果表明是個游標對象：
Out[4]:
<pymysql.cursors.Cursor at 0x11ddc1190>
In [5]:
cur.description
主要返回游標的屬性信息，官網(wǎng)的描述為：
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]:
# 列名
columns = [col[0] for col in cur.description]
columns
Out[6]:
['s_id', 's_name', 's_birth', 's_sex']
游標使用
下圖顯示的是如何取出一條或者多條數(shù)據(jù)（按照順序查詢）
通過游標獲取全部的數(shù)據(jù)：
fetch相關(guān)的函數(shù)都是獲取結(jié)果集中剩下的數(shù)據(jù)，多次調(diào)用的時候只會從剩余數(shù)據(jù)中查詢：
當(dāng)?shù)诙握{(diào)用的時候結(jié)果就是空集。
通過游標獲取查詢的結(jié)果集的特點：
- 在獲取數(shù)據(jù)的時候是按照順序讀取的
- 否則返回一個元組，其元素是每一行的記錄封裝的一個元組
轉(zhuǎn)成DataFrame
# 列名
columns = [col[0] for col in cur.description]
# 數(shù)據(jù)集合
data = []
for i in cur.fetchall():
data.append(i)
df = pd.DataFrame(data,columns=columns)
保存成CSV數(shù)據(jù)
SQL插入數(shù)據(jù)
往MySQL數(shù)據(jù)庫中插入數(shù)據(jù)：
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' # 數(shù)據(jù)庫
)
cur = connection.cursor() # 建立游標
# 待執(zhí)行SQL語句
sql='''
insert into test.Student(s_id, s_name, s_birth, s_sex) values('09','吳越','1998-08-08','男')
'''
# 執(zhí)行sql語句
cur.execute(sql)
很關(guān)鍵一步，要記得提交，這樣最終才會寫入數(shù)據(jù)庫：
connection.commit()
執(zhí)行SQL刪除語句
使用完之后記得關(guān)閉連接：
connection.close()
使用sqlalchemy
第二種常用的方法是通過sqlalchemy來連接數(shù)據(jù)庫：
連接數(shù)據(jù)庫
import pandas as pd
from sqlalchemy import create_engine
# 依次填寫MySQL的用戶名、密碼、IP地址、端口、數(shù)據(jù)庫名
# create_engine('數(shù)據(jù)庫類型+數(shù)據(jù)庫驅(qū)動://數(shù)據(jù)庫用戶名:數(shù)據(jù)庫密碼@IP地址:端口/數(shù)據(jù)庫'，其他參數(shù))
engine = create_engine('mysql+pymysql://root:password@localhost:3306/test')
查詢語句1
查詢語句2
寫入數(shù)據(jù)
將Pandas中的DataFrame寫入新的表testdf中：
show tables;
使用read_sql讀取
使用Pandas自帶的read_sql函數(shù)能夠自行讀取數(shù)據(jù)，讀取上面創(chuàng)建的數(shù)據(jù).
官網(wǎng)：https://pandas./docs/reference/api/pandas.read_sql.html
import pandas as pd
from sqlalchemy import create_engine
# 依次填寫MySQL的用戶名、密碼、IP地址、端口、數(shù)據(jù)庫名
engine = create_engine('mysql+pymysql://root:password@localhost:3306/test')
# sql語句
sql4 = 'select * from testdf;'
df4 = pd.read_sql(sql4, engine)
