Python實(shí)現(xiàn)連接MySQL數(shù)據(jù)庫(kù)的常見方法總結(jié)
在實(shí)際數(shù)據(jù)分析和建模過(guò)程中，我們通常需要從數(shù)據(jù)庫(kù)中讀取數(shù)據(jù)，并將其轉(zhuǎn)化為 Pandas dataframe 對(duì)象進(jìn)行進(jìn)一步處理。而 MySQL 數(shù)據(jù)庫(kù)是最常用的關(guān)系型數(shù)據(jù)庫(kù)之一，因此在 Python 中如何連接 MySQL 數(shù)據(jù)庫(kù)并查詢數(shù)據(jù)成為了一個(gè)重要的問(wèn)題。
本文將介紹兩種方法來(lái)連接 MySQL 數(shù)據(jù)庫(kù)，并將查詢結(jié)果轉(zhuǎn)化為 Pandas dataframe 對(duì)象：第一種方法使用 pymysql 庫(kù)來(lái)連接 MySQL 數(shù)據(jù)庫(kù)；第二種方法則使用 SQLAlchemy 的 create_engine 函數(shù)創(chuàng)建 MySQL 數(shù)據(jù)庫(kù)連接引擎。同時(shí)，針對(duì)這兩種方法，我們還將對(duì)代碼進(jìn)行封裝和優(yōu)化，提高程序的可讀性和健壯性。
方法一：使用 pymysql 庫(kù)連接 MySQL 數(shù)據(jù)庫(kù)
步驟 1：連接 MySQL 數(shù)據(jù)庫(kù)
首先，我們需要使用 pymysql 庫(kù)來(lái)連接 MySQL 數(shù)據(jù)庫(kù)。具體代碼如下：
import pymysql
# 連接 MySQL 數(shù)據(jù)庫(kù)
conn = pymysql.connect(
host='159.xxx.xxx.216', # 主機(jī)名
port=3306, # 端口號(hào)，MySQL默認(rèn)為3306
user='xxxx', # 用戶名
password='xxxx', # 密碼
database='xx', # 數(shù)據(jù)庫(kù)名稱
)在上面的代碼中，我們通過(guò) pymysql 庫(kù)的 connect() 函數(shù)連接 MySQL 數(shù)據(jù)庫(kù)，并指定主機(jī)名、端口號(hào)、用戶名、密碼和數(shù)據(jù)庫(kù)名稱等參數(shù)。如果連接成功，則該函數(shù)將返回一個(gè)數(shù)據(jù)庫(kù)連接對(duì)象 conn。
步驟 2：執(zhí)行 SQL 查詢語(yǔ)句
連接 MySQL 數(shù)據(jù)庫(kù)之后，我們就可以使用游標(biāo)對(duì)象來(lái)執(zhí)行 SQL 查詢語(yǔ)句，如下所示：
# 創(chuàng)建游標(biāo)對(duì)象
cursor = conn.cursor()
# 執(zhí)行 SQL 查詢語(yǔ)句
cursor.execute("SELECT * FROM users WHERE gender='female'")
# 獲取查詢結(jié)果
result = cursor.fetchall()在上面的代碼中，我們使用 cursor() 方法創(chuàng)建游標(biāo)對(duì)象 cursor，并使用 execute() 方法執(zhí)行 SQL 查詢語(yǔ)句。在執(zhí)行查詢時(shí)，我們可以使用任何符合 MySQL 語(yǔ)法的 SQL 查詢語(yǔ)句。最后，我們使用 fetchall() 方法獲取查詢結(jié)果。
步驟 3：將查詢結(jié)果轉(zhuǎn)化為 Pandas dataframe 對(duì)象
獲取查詢結(jié)果之后，我們需要將其轉(zhuǎn)化為 Pandas dataframe 對(duì)象，以便于進(jìn)行進(jìn)一步的數(shù)據(jù)處理和分析。具體代碼如下
import pandas as pd # 將查詢結(jié)果轉(zhuǎn)化為 Pandas dataframe 對(duì)象 df = pd.DataFrame(result, columns=[i[0] for i in cursor.description])
在上面的代碼中，我們使用 pd.DataFrame() 方法將查詢結(jié)果轉(zhuǎn)化為 Pandas dataframe 對(duì)象。在轉(zhuǎn)化過(guò)程中，我們需要指定字段名，可以通過(guò)游標(biāo)對(duì)象的 description 屬性來(lái)獲取查詢結(jié)果的元數(shù)據(jù)，其中包括字段名等信息。
步驟 4：關(guān)閉游標(biāo)和數(shù)據(jù)庫(kù)連接
最后，我們需要關(guān)閉游標(biāo)對(duì)象和數(shù)據(jù)庫(kù)連接，以釋放資源。具體代碼如下：
# 關(guān)閉游標(biāo)和數(shù)據(jù)庫(kù)連接 cursor.close() conn.close()
方法二：使用 SQLAlchemy 的 create_engine 函數(shù)連接 MySQL 數(shù)據(jù)庫(kù)
除了使用 pymysql 庫(kù)連接 MySQL 數(shù)據(jù)庫(kù)之外，我們還可以使用 SQLAlchemy 的 create_engine 函數(shù)創(chuàng)建 MySQL 數(shù)據(jù)庫(kù)連接引擎，并使用 Pandas 庫(kù)中的 read_sql 函數(shù)直接將查詢結(jié)果轉(zhuǎn)化為 Pandas dataframe 對(duì)象。
# 步驟 1：創(chuàng)建 MySQL 數(shù)據(jù)庫(kù)連接引擎
from sqlalchemy import create_engine
# 創(chuàng)建 MySQL 數(shù)據(jù)庫(kù)連接引擎
engine = create_engine('mysql+pymysql://username:password@host:port/database')
步驟 2：執(zhí)行 SQL 查詢語(yǔ)句并將結(jié)果轉(zhuǎn)化為 Pandas dataframe 對(duì)象
import pandas as pd
# 執(zhí)行 SQL 查詢語(yǔ)句，并將結(jié)果轉(zhuǎn)化為 Pandas dataframe 對(duì)象
df = pd.read_sql("SELECT * FROM users WHERE gender='female'", con=engine)
# 關(guān)閉數(shù)據(jù)庫(kù)連接
engine.dispose()在上面的代碼中，我們使用 create_engine 函數(shù)創(chuàng)建了一個(gè) MySQL 數(shù)據(jù)庫(kù)連接引擎。其中，我們需要將數(shù)據(jù)庫(kù)連接信息輸入到一個(gè)字符串中，并作為函數(shù)的參數(shù)傳入。其中，username 和 password 分別表示登錄 MySQL 數(shù)據(jù)庫(kù)所需的用戶名和密碼，host 和 port 表示 MySQL 數(shù)據(jù)庫(kù)的主機(jī)名和端口號(hào)，database 表示要連接的 MySQL 數(shù)據(jù)庫(kù)名稱。
接著使用使用 pd.read_sql() 函數(shù)執(zhí)行 SQL 查詢語(yǔ)句，并將數(shù)據(jù)庫(kù)連接引擎對(duì)象 engine 作為參數(shù)傳入。在執(zhí)行查詢時(shí)，我們可以使用任何符合 MySQL 語(yǔ)法的 SQL 查詢語(yǔ)句。最后，該函數(shù)將返回查詢結(jié)果的 Pandas dataframe 對(duì)象。
最后，我們需要關(guān)閉數(shù)據(jù)庫(kù)連接，以釋放資源。
函數(shù)封裝
以上介紹了兩種方法來(lái)連接 MySQL 數(shù)據(jù)庫(kù)，并將查詢結(jié)果轉(zhuǎn)化為 Pandas dataframe 對(duì)象。為了方便重復(fù)使用，我們可以將這些代碼封裝成一個(gè)函數(shù)。
import pandas as pd
import pymysql
from sqlalchemy import create_engine
def query_mysql(sql_query, host=None, port=None, user=None, password=None, database=None, engine=None):
"""
連接 MySQL 數(shù)據(jù)庫(kù)，執(zhí)行查詢，并將查詢結(jié)果轉(zhuǎn)化為 Pandas DataFrame 對(duì)象。
:param sql_query: SQL 查詢語(yǔ)句
:param host: 主機(jī)名，默認(rèn)為 None
:param port: 端口號(hào)，默認(rèn)為 None
:param user: 用戶名，默認(rèn)為 None
:param password: 密碼，默認(rèn)為 None
:param database: 數(shù)據(jù)庫(kù)名稱，默認(rèn)為 None
:param engine: SQLAlchemy 的數(shù)據(jù)庫(kù)引擎對(duì)象，默認(rèn)為 None
:return: Pandas DataFrame 對(duì)象
"""
# 如果未提供數(shù)據(jù)庫(kù)連接引擎，則使用 pymysql 庫(kù)連接 MySQL 數(shù)據(jù)庫(kù)
if engine is None:
# 連接 MySQL 數(shù)據(jù)庫(kù)
conn = pymysql.connect(
host=host,
port=port,
user=user,
password=password,
database=database,
)
# 創(chuàng)建游標(biāo)對(duì)象
cursor = conn.cursor()
# 執(zhí)行 SQL 查詢語(yǔ)句
cursor.execute(sql_query)
# 獲取查詢結(jié)果
result = cursor.fetchall()
# 將查詢結(jié)果轉(zhuǎn)化為 Pandas DataFrame 對(duì)象
df = pd.DataFrame(result, columns=[i[0] for i in cursor.description])
# 關(guān)閉游標(biāo)和數(shù)據(jù)庫(kù)連接
cursor.close()
conn.close()
# 如果已提供數(shù)據(jù)庫(kù)連接引擎，則使用 SQLAlchemy 庫(kù)連接 MySQL 數(shù)據(jù)庫(kù)
else:
# 執(zhí)行 SQL 查詢語(yǔ)句，并將結(jié)果轉(zhuǎn)化為 Pandas DataFrame 對(duì)象
df = pd.read_sql(sql_query, con=engine)
return df在上面的代碼中，我們創(chuàng)建了一個(gè)名為 query_mysql 的函數(shù)，用于連接 MySQL 數(shù)據(jù)庫(kù)，并執(zhí)行查詢操作。該函數(shù)接受以下參數(shù)：
- sql_query：SQL 查詢語(yǔ)句；
- host：主機(jī)名，默認(rèn)為 None；
- port：端口號(hào)，默認(rèn)為 None；
- user：用戶名，默認(rèn)為 None；
- password：密碼，默認(rèn)為 None；
- database：數(shù)據(jù)庫(kù)名稱，默認(rèn)為 None；
- engine：SQLAlchemy 的數(shù)據(jù)庫(kù)引擎對(duì)象，默認(rèn)為 None。
在函數(shù)中，我們首先判斷是否已提供數(shù)據(jù)庫(kù)連接引擎對(duì)象。如果未提供，則使用 pymysql 庫(kù)連接MySQL 數(shù)據(jù)庫(kù)，并執(zhí)行查詢操作，步驟與前面的第一種方法相同。如果已提供數(shù)據(jù)庫(kù)連接引擎對(duì)象，則使用 SQLAlchemy 庫(kù)連接 MySQL 數(shù)據(jù)庫(kù)，并執(zhí)行查詢操作，步驟與前面的第二種方法相同。
最后，在函數(shù)中我們返回查詢結(jié)果的 Pandas dataframe 對(duì)象。
# 使用 pymysql 庫(kù)連接 MySQL 數(shù)據(jù)庫(kù)
df1 = query_mysql(
sql_query="SELECT * FROM users WHERE gender='female'",
host='159.xxx.xxx.216', # 主機(jī)名
port=3306, # 端口號(hào)，MySQL默認(rèn)為3306
user='xxxx', # 用戶名
password='xxxx', # 密碼
database='xx', # 數(shù)據(jù)庫(kù)名稱
)
# 使用 SQLAlchemy 庫(kù)連接 MySQL 數(shù)據(jù)庫(kù)
engine = create_engine('mysql+pymysql://xxx:xxx@localhost:3306/ad')
df2 = query_mysql(sql_query="SELECT * FROM users WHERE gender='female'", engine=engine)通過(guò)使用 query_mysql 函數(shù)，我們可以更加方便地連接 MySQL 數(shù)據(jù)庫(kù)并查詢數(shù)據(jù)，并且代碼量更少、可讀性更好。同時(shí)，由于該函數(shù)使用了 pymysql 和 SQLAlchemy 兩個(gè)庫(kù)，因此也具有較好的跨平臺(tái)性，可以在不同的操作系統(tǒng)和環(huán)境下運(yùn)行。
最后也分享一下個(gè)人通過(guò)使用的模板：
# 法一:
import pymysql
import pandas as pd
def query_data(sql_query):
# 連接數(shù)據(jù)庫(kù)
conn = pymysql.connect(
host='xxx.xxx.xxx.xxx', # 主機(jī)名
port=3306, # 端口號(hào)，MySQL默認(rèn)為3306
user='xxx', # 用戶名
password='xxx', # 密碼
database='xxx', # 數(shù)據(jù)庫(kù)名稱
)
try:
# 創(chuàng)建游標(biāo)對(duì)象
cursor = conn.cursor()
# 執(zhí)行 SQL 查詢語(yǔ)句
cursor.execute(sql_query)
# 獲取查詢結(jié)果
result = cursor.fetchall()
# 獲取查詢結(jié)果的字段名和元數(shù)據(jù)
columns = [col[0] for col in cursor.description]
# 將查詢結(jié)果封裝到 Pandas DataFrame 中
df = pd.DataFrame(result, columns=columns)
return df
finally:
# 關(guān)閉游標(biāo)和連接
cursor.close()
conn.close()
db_data = query_data(sql_query)
# 法二:
from sqlalchemy import create_engine
import pandas as pd
def getdata_from_db(query, db, host='xxx.xxx.xxx.xxx', port=3306, user='xxx', password='xxx'):
try:
engine = create_engine(f'mysql+pymysql://{user}:{password}@{host}:{port}/{db}?charset=utf8')
# 使用 with 語(yǔ)句自動(dòng)管理連接的生命周期
with engine.connect() as conn:
data = pd.read_sql(query, conn)
return data
except Exception as e:
print(f"Error occurred when executing SQL query: {e}")
return None
db_data = getdata_from_db(sql_query, 'ad')
# 法三：超級(jí)精簡(jiǎn)版
from sqlalchemy import create_engine
import pandas as pd
engine = create_engine(f'mysql+pymysql://xxx:xxx@xxx:3306/xx?charset=utf8')
db_data = pd.read_sql(sql, engine)
db_data.head()最后，說(shuō)一下在訪問(wèn)數(shù)據(jù)庫(kù)時(shí)，可能存在一些潛在的問(wèn)題和注意事項(xiàng)。
- 首先，在使用 pandas.read_sql() 時(shí)，需要在 SQL 查詢語(yǔ)句中包含所有必要的過(guò)濾條件、排序方式等信息，以確保返回的結(jié)果集合是正確的，而不是整個(gè)表或視圖中的所有數(shù)據(jù)。如果沒有限制返回的數(shù)據(jù)量，可能會(huì)導(dǎo)致內(nèi)存溢出或其他性能問(wèn)題。因此，在實(shí)際應(yīng)用中，推薦使用 LIMIT 等關(guān)鍵字來(lái)設(shè)置最大返回?cái)?shù)據(jù)量，以便更好地控制查詢結(jié)果。
- 其次，在實(shí)際生產(chǎn)環(huán)境中，為了避免泄漏敏感信息和減少攻擊面，建議將數(shù)據(jù)庫(kù)連接字符串等敏感信息存儲(chǔ)在單獨(dú)的配置文件中，并且只授權(quán)給有限的用戶使用。另外，在向 SQL 查詢語(yǔ)句中傳遞參數(shù)時(shí)，也需要進(jìn)行安全過(guò)濾和轉(zhuǎn)義，以避免 SQL 注入等安全問(wèn)題。
- 最后，在使用完畢后，需要及時(shí)關(guān)閉數(shù)據(jù)庫(kù)連接，以釋放資源并減少數(shù)據(jù)庫(kù)服務(wù)器的負(fù)載。或者，可以使用 with 語(yǔ)句自動(dòng)管理連接的生命周期。
總之，學(xué)習(xí)如何連接 MySQL 數(shù)據(jù)庫(kù)并將查詢結(jié)果轉(zhuǎn)化為 Pandas dataframe 對(duì)象是數(shù)據(jù)分析和建模過(guò)程中的重要一步。
到此這篇關(guān)于Python實(shí)現(xiàn)連接MySQL數(shù)據(jù)庫(kù)的常見方法總結(jié)的文章就介紹到這了,更多相關(guān)Python連接MySQL內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！
相關(guān)文章
這篇文章主要為大家詳細(xì)介紹了python利用sklearn包編寫決策樹源代碼，具有一定的參考價(jià)值，感興趣的小伙伴們可以參考一下2017-12-12
如何將自己的python代碼發(fā)布在pip install給別人使用你知道嗎
這篇文章主要介紹了python如何發(fā)布自已的pip項(xiàng)目，方便大家學(xué)習(xí)，小編覺得挺不錯(cuò)的，現(xiàn)在分享給大家，也給大家做個(gè)參考。一起跟隨小編過(guò)來(lái)看看吧2021-08-08
下面小編就為大家分享一篇Python之pandas讀寫文件亂碼的解決方法，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。一起跟隨小編過(guò)來(lái)看看吧2018-04-04
Python開發(fā)入門之如何制作一個(gè)簡(jiǎn)單的桌面應(yīng)用
這篇文章主要給大家介紹了關(guān)于Python開發(fā)入門之如何制作一個(gè)簡(jiǎn)單的桌面應(yīng)用的相關(guān)資料,我們不僅可以使用Python的圖像處理庫(kù)，如PIL等來(lái)實(shí)現(xiàn)圖片的處理和識(shí)別,同時(shí)你還可以設(shè)計(jì)和開發(fā)具有圖形界面的桌面應(yīng)用程序,需要的朋友可以參考下2023-08-08
python sorted函數(shù)原理解析及練習(xí)
這篇文章主要介紹了python sorted函數(shù)原理解析及練習(xí),文中通過(guò)示例代碼介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值,需要的朋友可以參考下2020-02-02
本篇文章主要介紹了Python內(nèi)置函數(shù)的使用方法，小編覺得挺不錯(cuò)的，現(xiàn)在分享給大家，也給大家做個(gè)參考。一起跟隨小編過(guò)來(lái)看看吧2021-10-10
這篇文章主要介紹了pycharm配置QtDesigner的超詳細(xì)方法,本文給大家介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或工作具有一定的參考借鑒價(jià)值，需要的朋友可以參考下2021-01-01
Python深度學(xué)習(xí)實(shí)戰(zhàn)PyQt5菜單和工具欄功能作用
本文詳細(xì)解讀通過(guò) QtDesigner 創(chuàng)建主窗口、菜單欄和工具欄，并以菜單項(xiàng) “退出” 為例關(guān)聯(lián)系統(tǒng)定義的動(dòng)作處理方法。有需要的朋友可以借鑒參考下，希望能夠有所幫助2021-10-10
pytorch機(jī)器學(xué)習(xí)softmax回歸的簡(jiǎn)潔實(shí)現(xiàn)
這篇文章主要介紹了為大家介紹了pytorch機(jī)器學(xué)習(xí)中softmax回歸的簡(jiǎn)潔實(shí)現(xiàn)方式，有需要的朋友可以借鑒參考下，希望能夠有所幫助2021-10-10
本篇文章介紹了我在爬蟲過(guò)程中總結(jié)的幾個(gè)必備技巧，都是經(jīng)過(guò)實(shí)驗(yàn)的，通讀本篇對(duì)大家的學(xué)習(xí)或工作具有一定的價(jià)值，需要的朋友可以參考下2021-10-10