Python pymysql連接數(shù)據(jù)庫并將查詢結(jié)果轉(zhuǎn)化為Pandas dataframe

更新時間：2023年05月18日 09:53:53 作者：奕澤

這篇文章主要為大家介紹了Python pymysql連接數(shù)據(jù)庫并將結(jié)果轉(zhuǎn)化為Pandas dataframe實現(xiàn)方法詳解，有需要的朋友可以借鑒參考下，希望能夠有所幫助，祝大家多多進步，早日升職加薪

兩種方法來連接 MySQL 數(shù)據(jù)庫

在實際數(shù)據(jù)分析和建模過程中，我們通常需要從數(shù)據(jù)庫中讀取數(shù)據(jù)，并將其轉(zhuǎn)化為 Pandas dataframe 對象進行進一步處理。而 MySQL 數(shù)據(jù)庫是最常用的關(guān)系型數(shù)據(jù)庫之一，因此在 Python 中如何連接 MySQL 數(shù)據(jù)庫并查詢數(shù)據(jù)成為了一個重要的問題。

本文將介紹兩種方法來連接 MySQL 數(shù)據(jù)庫，并將查詢結(jié)果轉(zhuǎn)化為 Pandas dataframe 對象：第一種方法使用 pymysql 庫來連接 MySQL 數(shù)據(jù)庫；第二種方法則使用 SQLAlchemy 的 create_engine 函數(shù)創(chuàng)建 MySQL 數(shù)據(jù)庫連接引擎。同時，針對這兩種方法，我們還將對代碼進行封裝和優(yōu)化，提高程序的可讀性和健壯性。

方法一：使用 pymysql 庫連接 MySQL 數(shù)據(jù)庫

步驟 1：連接 MySQL 數(shù)據(jù)庫

首先，我們需要使用 pymysql 庫來連接 MySQL 數(shù)據(jù)庫。具體代碼如下：

import pymysql
# 連接 MySQL 數(shù)據(jù)庫
conn = pymysql.connect(
    host='159.xxx.xxx.216',  # 主機名
    port=3306,         # 端口號，MySQL默認為3306
    user='xxxx',       # 用戶名
    password='xxxx', # 密碼
    database='xx',   # 數(shù)據(jù)庫名稱
)

在上面的代碼中，我們通過 pymysql 庫的 connect() 函數(shù)連接 MySQL 數(shù)據(jù)庫，并指定主機名、端口號、用戶名、密碼和數(shù)據(jù)庫名稱等參數(shù)。如果連接成功，則該函數(shù)將返回一個數(shù)據(jù)庫連接對象 conn。

步驟 2：執(zhí)行 SQL 查詢語句連接 MySQL 數(shù)據(jù)庫之后，我們就可以使用游標對象來執(zhí)行 SQL 查詢語句，如下所示：

# 創(chuàng)建游標對象
cursor = conn.cursor()
# 執(zhí)行 SQL 查詢語句
cursor.execute("SELECT * FROM users WHERE gender='female'")
# 獲取查詢結(jié)果
result = cursor.fetchall()

在上面的代碼中，我們使用 cursor() 方法創(chuàng)建游標對象 cursor，并使用 execute() 方法執(zhí)行 SQL 查詢語句。在執(zhí)行查詢時，我們可以使用任何符合 MySQL 語法的 SQL 查詢語句。最后，我們使用 fetchall() 方法獲取查詢結(jié)果。

步驟 3：將查詢結(jié)果轉(zhuǎn)化為 Pandas dataframe 對象

獲取查詢結(jié)果之后，我們需要將其轉(zhuǎn)化為 Pandas dataframe 對象，以便于進行進一步的數(shù)據(jù)處理和分析。具體代碼如下

import pandas as pd
# 將查詢結(jié)果轉(zhuǎn)化為 Pandas dataframe 對象
df = pd.DataFrame(result, columns=[i[0] for i in cursor.description])

在上面的代碼中，我們使用 pd.DataFrame() 方法將查詢結(jié)果轉(zhuǎn)化為 Pandas dataframe 對象。在轉(zhuǎn)化過程中，我們需要指定字段名，可以通過游標對象的 description 屬性來獲取查詢結(jié)果的元數(shù)據(jù)，其中包括字段名等信息。

步驟 4：關(guān)閉游標和數(shù)據(jù)庫連接

最后，我們需要關(guān)閉游標對象和數(shù)據(jù)庫連接，以釋放資源。具體代碼如下：

# 關(guān)閉游標和數(shù)據(jù)庫連接
cursor.close()
conn.close()

方法二：使用 SQLAlchemy 的 create_engine 函數(shù)連接 MySQL 數(shù)據(jù)庫

除了使用 pymysql 庫連接 MySQL 數(shù)據(jù)庫之外，我們還可以使用 SQLAlchemy 的 create_engine 函數(shù)創(chuàng)建 MySQL 數(shù)據(jù)庫連接引擎，并使用 Pandas 庫中的 read_sql 函數(shù)直接將查詢結(jié)果轉(zhuǎn)化為 Pandas dataframe 對象。

# 步驟 1：創(chuàng)建 MySQL 數(shù)據(jù)庫連接引擎
from sqlalchemy import create_engine
# 創(chuàng)建 MySQL 數(shù)據(jù)庫連接引擎
engine = create_engine('mysql+pymysql://username:password@host:port/database')
步驟 2：執(zhí)行 SQL 查詢語句并將結(jié)果轉(zhuǎn)化為 Pandas dataframe 對象
import pandas as pd
# 執(zhí)行 SQL 查詢語句，并將結(jié)果轉(zhuǎn)化為 Pandas dataframe 對象
df = pd.read_sql("SELECT * FROM users WHERE gender='female'", con=engine)
# 關(guān)閉數(shù)據(jù)庫連接
engine.dispose()

在上面的代碼中，我們使用 create_engine 函數(shù)創(chuàng)建了一個 MySQL 數(shù)據(jù)庫連接引擎。其中，我們需要將數(shù)據(jù)庫連接信息輸入到一個字符串中，并作為函數(shù)的參數(shù)傳入。其中，username 和 password 分別表示登錄 MySQL 數(shù)據(jù)庫所需的用戶名和密碼，host 和 port 表示 MySQL 數(shù)據(jù)庫的主機名和端口號，database 表示要連接的 MySQL 數(shù)據(jù)庫名稱。

接著使用使用 pd.read_sql() 函數(shù)執(zhí)行 SQL 查詢語句，并將數(shù)據(jù)庫連接引擎對象 engine 作為參數(shù)傳入。在執(zhí)行查詢時，我們可以使用任何符合 MySQL 語法的 SQL 查詢語句。最后，該函數(shù)將返回查詢結(jié)果的 Pandas dataframe 對象。

最后，我們需要關(guān)閉數(shù)據(jù)庫連接，以釋放資源。

函數(shù)封裝

以上介紹了兩種方法來連接 MySQL 數(shù)據(jù)庫，并將查詢結(jié)果轉(zhuǎn)化為 Pandas dataframe 對象。為了方便重復(fù)使用，我們可以將這些代碼封裝成一個函數(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ù)庫，執(zhí)行查詢，并將查詢結(jié)果轉(zhuǎn)化為 Pandas DataFrame 對象。
    :param sql_query: SQL 查詢語句
    :param host: 主機名，默認為 None
    :param port: 端口號，默認為 None
    :param user: 用戶名，默認為 None
    :param password: 密碼，默認為 None
    :param database: 數(shù)據(jù)庫名稱，默認為 None
    :param engine: SQLAlchemy 的數(shù)據(jù)庫引擎對象，默認為 None
    :return: Pandas DataFrame 對象
    """
    # 如果未提供數(shù)據(jù)庫連接引擎，則使用 pymysql 庫連接 MySQL 數(shù)據(jù)庫
    if engine is None:
        # 連接 MySQL 數(shù)據(jù)庫
        conn = pymysql.connect(
            host=host,
            port=port,
            user=user,
            password=password,
            database=database,
        )
        # 創(chuàng)建游標對象
        cursor = conn.cursor()
        # 執(zhí)行 SQL 查詢語句
        cursor.execute(sql_query)
        # 獲取查詢結(jié)果
        result = cursor.fetchall()
        # 將查詢結(jié)果轉(zhuǎn)化為 Pandas DataFrame 對象
        df = pd.DataFrame(result, columns=[i[0] for i in cursor.description])
        # 關(guān)閉游標和數(shù)據(jù)庫連接
        cursor.close()
        conn.close()
    # 如果已提供數(shù)據(jù)庫連接引擎，則使用 SQLAlchemy 庫連接 MySQL 數(shù)據(jù)庫
    else:
        # 執(zhí)行 SQL 查詢語句，并將結(jié)果轉(zhuǎn)化為 Pandas DataFrame 對象
        df = pd.read_sql(sql_query, con=engine)
    return df

在上面的代碼中，我們創(chuàng)建了一個名為 query_mysql 的函數(shù)，用于連接 MySQL 數(shù)據(jù)庫，并執(zhí)行查詢操作。該函數(shù)接受以下參數(shù)：

sql_query：SQL 查詢語句；
host：主機名，默認為 None；
port：端口號，默認為 None；
user：用戶名，默認為 None；
password：密碼，默認為 None；
database：數(shù)據(jù)庫名稱，默認為 None；
engine：SQLAlchemy 的數(shù)據(jù)庫引擎對象，默認為 None。

在函數(shù)中，我們首先判斷是否已提供數(shù)據(jù)庫連接引擎對象。如果未提供，則使用 pymysql 庫連接MySQL 數(shù)據(jù)庫，并執(zhí)行查詢操作，步驟與前面的第一種方法相同。如果已提供數(shù)據(jù)庫連接引擎對象，則使用 SQLAlchemy 庫連接 MySQL 數(shù)據(jù)庫，并執(zhí)行查詢操作，步驟與前面的第二種方法相同。

最后，在函數(shù)中我們返回查詢結(jié)果的 Pandas dataframe 對象。

# 使用 pymysql 庫連接 MySQL 數(shù)據(jù)庫
df1 = query_mysql(
    sql_query="SELECT * FROM users WHERE gender='female'",
    host='159.xxx.xxx.216',  # 主機名
    port=3306,         # 端口號，MySQL默認為3306
    user='xxxx',       # 用戶名
    password='xxxx', # 密碼
    database='xx',   # 數(shù)據(jù)庫名稱
)
# 使用 SQLAlchemy 庫連接 MySQL 數(shù)據(jù)庫
engine = create_engine('mysql+pymysql://xxx:xxx@localhost:3306/ad')
df2 = query_mysql(sql_query="SELECT * FROM users WHERE gender='female'", engine=engine)

通過使用 query_mysql 函數(shù)，我們可以更加方便地連接 MySQL 數(shù)據(jù)庫并查詢數(shù)據(jù)，并且代碼量更少、可讀性更好。同時，由于該函數(shù)使用了 pymysql 和 SQLAlchemy 兩個庫，因此也具有較好的跨平臺性，可以在不同的操作系統(tǒng)和環(huán)境下運行。

最后也分享一下個人通過使用的模板：

# 法一:
import pymysql
import pandas as pd
def query_data(sql_query):
    # 連接數(shù)據(jù)庫
    conn = pymysql.connect(
        host='xxx.xxx.xxx.xxx',  # 主機名
        port=3306,         # 端口號，MySQL默認為3306
        user='xxx',       # 用戶名
        password='xxx', # 密碼
        database='xxx',   # 數(shù)據(jù)庫名稱
    )
    try:
        # 創(chuàng)建游標對象
        cursor = conn.cursor()
        # 執(zhí)行 SQL 查詢語句
        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)閉游標和連接
        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 語句自動管理連接的生命周期
        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')
# 法三：超級精簡版
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()

最后，說一下在訪問數(shù)據(jù)庫時，可能存在一些潛在的問題和注意事項。

首先，在使用 pandas.read_sql() 時，需要在 SQL 查詢語句中包含所有必要的過濾條件、排序方式等信息，以確保返回的結(jié)果集合是正確的，而不是整個表或視圖中的所有數(shù)據(jù)。如果沒有限制返回的數(shù)據(jù)量，可能會導(dǎo)致內(nèi)存溢出或其他性能問題。因此，在實際應(yīng)用中，推薦使用 LIMIT 等關(guān)鍵字來設(shè)置最大返回數(shù)據(jù)量，以便更好地控制查詢結(jié)果。
其次，在實際生產(chǎn)環(huán)境中，為了避免泄漏敏感信息和減少攻擊面，建議將數(shù)據(jù)庫連接字符串等敏感信息存儲在單獨的配置文件中，并且只授權(quán)給有限的用戶使用。另外，在向 SQL 查詢語句中傳遞參數(shù)時，也需要進行安全過濾和轉(zhuǎn)義，以避免 SQL 注入等安全問題。
最后，在使用完畢后，需要及時關(guān)閉數(shù)據(jù)庫連接，以釋放資源并減少數(shù)據(jù)庫服務(wù)器的負載。或者，可以使用 with 語句自動管理連接的生命周期。

總之，學(xué)習(xí)如何連接 MySQL 數(shù)據(jù)庫并將查詢結(jié)果轉(zhuǎn)化為 Pandas dataframe 對象是數(shù)據(jù)分析和建模過程中的重要一步。希望本文對您有所幫助，更多關(guān)于Python pymysql連接數(shù)據(jù)庫的資料請關(guān)注腳本之家其它相關(guān)文章！

您可能感興趣的文章: