快捷導(dǎo)航

Pandas操作MySQL的方法詳解

更新時(shí)間：2022年08月23日 09:29:47 作者：尤而小屋

這篇文章主要介紹了如何使用Pandas來(lái)操作MySQL數(shù)據(jù)庫(kù)。主要是包含查詢MySQL中的數(shù)據(jù)，以及如何往數(shù)據(jù)庫(kù)中寫入數(shù)據(jù)，感興趣的小伙伴可以了解一下

本地?cái)?shù)據(jù)庫(kù)

查看一個(gè)本地?cái)?shù)據(jù)庫(kù)中某個(gè)表的數(shù)據(jù)。這份數(shù)據(jù)是《MySQL經(jīng)典50題》的一個(gè)表之一：

部分習(xí)題答案：

mysql?-u?root?-p???--?安裝mysql，進(jìn)入數(shù)據(jù)庫(kù)輸入暗文密碼

show?databases;??--?顯示全部數(shù)據(jù)庫(kù)
use?test;??--?使用某個(gè)數(shù)據(jù)庫(kù)
show?tables;??--?查看數(shù)據(jù)庫(kù)下的全部表
select?*?from?Student;??--?查看某個(gè)表的全部?jī)?nèi)容

操作MySQL

連接MySQL

以pymysql模塊為例，講解如何連接數(shù)據(jù)庫(kù)。

In [1]：

import?pandas?as?pd

import?pymysql
from?sqlalchemy?import?create_engine

In [2]:

connection?=?pymysql.connect(
??host='127.0.0.1',??#?本機(jī)ip地址
??port=3306,??#?mysql默認(rèn)端口號(hào)
??user="root",??#?用戶名
??password="password",??#?密碼
??charset="utf8",?#?字符集
??db="test"??#?數(shù)據(jù)庫(kù)
)

cur?=?connection.cursor()?#?建立游標(biāo)
cur

Out[2]:

<pymysql.cursors.Cursor at 0x11ddc1190>

connect()方法常用的參數(shù)：

connect() 常用參數(shù)	說(shuō)明
host	主機(jī)ip
user	用戶名
password	密碼
database	數(shù)據(jù)庫(kù)
port	端口號(hào)
charset	字符集

調(diào)用 cursor() 方法即可返回一個(gè)新的游標(biāo)對(duì)象，在連接沒(méi)有關(guān)閉之前，游標(biāo)對(duì)象可以反復(fù)使用

執(zhí)行sql查詢語(yǔ)句

In [3]:

sql="""??#?待執(zhí)行的sql語(yǔ)句
select?*?from?Student;
"""

#?執(zhí)行sql語(yǔ)句
cur.execute(sql)??

Out[3]:

8

In [4]:

cur

結(jié)果表明是個(gè)游標(biāo)對(duì)象：

Out[4]:

<pymysql.cursors.Cursor at 0x11ddc1190>

In [5]:

cur.description

主要返回游標(biāo)的屬性信息，官網(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']

游標(biāo)使用

下圖顯示的是如何取出一條或者多條數(shù)據(jù)（按照順序查詢）

通過(guò)游標(biāo)獲取全部的數(shù)據(jù)：

fetch相關(guān)的函數(shù)都是獲取結(jié)果集中剩下的數(shù)據(jù)，多次調(diào)用的時(shí)候只會(huì)從剩余數(shù)據(jù)中查詢：

當(dāng)?shù)诙握{(diào)用的時(shí)候結(jié)果就是空集。

通過(guò)游標(biāo)獲取查詢的結(jié)果集的特點(diǎn)：

1.可以獲取1條、多條和全部數(shù)據(jù)

2.在獲取數(shù)據(jù)的時(shí)候是按照順序讀取的

3.fetchall函數(shù)返回剩下的所有行

如果是末尾，則返回空元組；
否則返回一個(gè)元組，其元素是每一行的記錄封裝的一個(gè)元組

轉(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ù)庫(kù)中插入數(shù)據(jù)：

import?pandas?as?pd

import?pymysql
from?sqlalchemy?import?create_engine

connection?=?pymysql.connect(
??host='127.0.0.1',??#?本機(jī)ip地址
??port=3306,??#?mysql默認(rèn)端口號(hào)
??user="root",??#?用戶名
??password="11112222",??#?密碼
??charset="utf8",?#?字符集
??db="test"??#?數(shù)據(jù)庫(kù)
)

cur?=?connection.cursor()?#?建立游標(biāo)

#?待執(zhí)行SQL語(yǔ)句
sql="""??
insert?into?test.Student(s_id,?s_name,?s_birth,?s_sex)?values("09","吳越","1998-08-08","男")
"""

#?執(zhí)行sql語(yǔ)句
cur.execute(sql)??

很關(guān)鍵一步，要記得提交，這樣最終才會(huì)寫入數(shù)據(jù)庫(kù)：

connection.commit()

執(zhí)行SQL刪除語(yǔ)句

使用完之后記得關(guān)閉連接：

connection.close()

使用sqlalchemy

第二種常用的方法是通過(guò)sqlalchemy來(lái)連接數(shù)據(jù)庫(kù)：

連接數(shù)據(jù)庫(kù)

import?pandas?as?pd
from?sqlalchemy?import?create_engine

#?依次填寫MySQL的用戶名、密碼、IP地址、端口、數(shù)據(jù)庫(kù)名
#?create_engine("數(shù)據(jù)庫(kù)類型+數(shù)據(jù)庫(kù)驅(qū)動(dòng)://數(shù)據(jù)庫(kù)用戶名:數(shù)據(jù)庫(kù)密碼@IP地址:端口/數(shù)據(jù)庫(kù)"，其他參數(shù))
engine?=?create_engine("mysql+pymysql://root:password@localhost:3306/test")

查詢語(yǔ)句1

查詢語(yǔ)句2

寫入數(shù)據(jù)

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

show?tables;

使用read_sql讀取

使用Pandas自帶的read_sql函數(shù)能夠自行讀取數(shù)據(jù)，讀取上面創(chuàng)建的數(shù)據(jù).

官網(wǎng)：https://pandas.pydata.org/docs/reference/api/pandas.read_sql.html

import?pandas?as?pd
from?sqlalchemy?import?create_engine

#?依次填寫MySQL的用戶名、密碼、IP地址、端口、數(shù)據(jù)庫(kù)名
engine?=?create_engine("mysql+pymysql://root:password@localhost:3306/test")
#?sql語(yǔ)句
sql4?=?"select?*?from?testdf;"
df4?=?pd.read_sql(sql4,?engine)