腳本之家服務器常用軟件

快捷導航

Pandas操作MySQL的方法詳解

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

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

本地數(shù)據(jù)庫

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

部分習題答案：

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() 方法即可返回一個新的游標對象，在連接沒有關閉之前，游標對象可以反復使用

執(zhí)行sql查詢語句

In [3]:

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

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

Out[3]:

8

In [4]:

cur

結果表明是個游標對象：

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相關的函數(shù)都是獲取結果集中剩下的數(shù)據(jù)，多次調(diào)用的時候只會從剩余數(shù)據(jù)中查詢：

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

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

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

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

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

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

轉(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)??

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

connection.commit()

執(zhí)行SQL刪除語句

使用完之后記得關閉連接：

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.pydata.org/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)