六虎

Python 连接 MySQL 数据库

作者：

Java大猿帅

在

在实践数据剖析和建模过程中，咱们通常需求从数据库中读取数据，并将其转化为 Pandas dataframe 目标进行进一步处理。而 MySQL 数据库是最常用的关系型数据库之一，因而在 Python 中如何衔接 MySQL 数据库并查询数据成为了一个重要的问题。

本文将介绍两种办法来衔接 MySQL 数据库，并将查询成果转化为 Pandas dataframe 目标：榜首种办法运用 pymysql 库来衔接 MySQL 数据库；第二种办法则运用 SQLAlchemy 的 create_engine 函数创立 MySQL 数据库衔接引擎。一起，针对这两种办法，咱们还将对代码进行封装和优化，提高程序的可读性和健壮性。

办法一：运用 pymysql 库衔接 MySQL 数据库

过程 1：衔接 MySQL 数据库

首要，咱们需求运用 pymysql 库来衔接 MySQL 数据库。详细代码如下：

import pymysql
# 衔接 MySQL 数据库
conn = pymysql.connect(
    host='159.xxx.xxx.216',  # 主机名
    port=3306,         # 端口号，MySQL默以为3306
    user='xxxx',       # 用户名
    password='xxxx', # 暗码
    database='xx',   # 数据库称号
)

在上面的代码中，咱们通过 pymysql 库的 connect() 函数衔接 MySQL 数据库，并指定主机名、端口号、用户名、暗码和数据库称号等参数。假如衔接成功，则该函数将回来一个数据库衔接目标 conn。

过程 2：履行 SQL 查询句子
衔接 MySQL 数据库之后，咱们就能够运用游标目标来履行 SQL 查询句子，如下所示：

# 创立游标目标
cursor = conn.cursor()
# 履行 SQL 查询句子
cursor.execute("SELECT * FROM users WHERE gender='female'")
# 获取查询成果
result = cursor.fetchall()

在上面的代码中，咱们运用 cursor() 办法创立游标目标 cursor，并运用 execute() 办法履行 SQL 查询句子。在履行查询时，咱们能够运用任何契合 MySQL 语法的 SQL 查询句子。最终，咱们运用 fetchall() 办法获取查询成果。

过程 3：将查询成果转化为 Pandas dataframe 目标

获取查询成果之后，咱们需求将其转化为 Pandas dataframe 目标，以便于进行进一步的数据处理和剖析。详细代码如下

import pandas as pd
# 将查询成果转化为 Pandas dataframe 目标
df = pd.DataFrame(result, columns=[i[0] for i in cursor.description])

在上面的代码中，咱们运用 pd.DataFrame() 办法将查询成果转化为 Pandas dataframe 目标。在转化过程中，咱们需求指定字段名，能够通过游标目标的 description 特点来获取查询成果的元数据，其间包括字段名等信息。

过程 4：封闭游标和数据库衔接

最终，咱们需求封闭游标目标和数据库衔接，以开释资源。详细代码如下：

# 封闭游标和数据库衔接
cursor.close()
conn.close()

办法二：运用 SQLAlchemy 的 create_engine 函数衔接 MySQL 数据库

除了运用 pymysql 库衔接 MySQL 数据库之外，咱们还能够运用 SQLAlchemy 的 create_engine 函数创立 MySQL 数据库衔接引擎，并运用 Pandas 库中的 read_sql 函数直接将查询成果转化为 Pandas dataframe 目标。

# 过程 1：创立 MySQL 数据库衔接引擎
from sqlalchemy import create_engine
# 创立 MySQL 数据库衔接引擎
engine = create_engine('mysql+pymysql://username:password@host:port/database')
过程 2：履行 SQL 查询句子并将成果转化为 Pandas dataframe 目标
import pandas as pd
# 履行 SQL 查询句子，并将成果转化为 Pandas dataframe 目标
df = pd.read_sql("SELECT * FROM users WHERE gender='female'", con=engine)
# 封闭数据库衔接
engine.dispose()

在上面的代码中，咱们运用 create_engine 函数创立了一个 MySQL 数据库衔接引擎。其间，咱们需求将数据库衔接信息输入到一个字符串中，并作为函数的参数传入。其间，username 和 password 分别表明登录 MySQL 数据库所需的用户名和暗码，host 和 port 表明 MySQL 数据库的主机名和端口号，database 表明要衔接的 MySQL 数据库称号。

接着运用运用 pd.read_sql() 函数履行 SQL 查询句子，并将数据库衔接引擎目标 engine 作为参数传入。在履行查询时，咱们能够运用任何契合 MySQL 语法的 SQL 查询句子。最终，该函数将回来查询成果的 Pandas dataframe 目标。

最终，咱们需求封闭数据库衔接，以开释资源。

函数封装

以上介绍了两种办法来衔接 MySQL 数据库，并将查询成果转化为 Pandas dataframe 目标。为了便利重复运用，咱们能够将这些代码封装成一个函数。

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 数据库，履行查询，并将查询成果转化为 Pandas DataFrame 目标。
    :param sql_query: SQL 查询句子
    :param host: 主机名，默以为 None
    :param port: 端口号，默以为 None
    :param user: 用户名，默以为 None
    :param password: 暗码，默以为 None
    :param database: 数据库称号，默以为 None
    :param engine: SQLAlchemy 的数据库引擎目标，默以为 None
    :return: Pandas DataFrame 目标
    """
    # 假如未供给数据库衔接引擎，则运用 pymysql 库衔接 MySQL 数据库
    if engine is None:
        # 衔接 MySQL 数据库
        conn = pymysql.connect(
            host=host,
            port=port,
            user=user,
            password=password,
            database=database,
        )
        # 创立游标目标
        cursor = conn.cursor()
        # 履行 SQL 查询句子
        cursor.execute(sql_query)
        # 获取查询成果
        result = cursor.fetchall()
        # 将查询成果转化为 Pandas DataFrame 目标
        df = pd.DataFrame(result, columns=[i[0] for i in cursor.description])
        # 封闭游标和数据库衔接
        cursor.close()
        conn.close()
    # 假如已供给数据库衔接引擎，则运用 SQLAlchemy 库衔接 MySQL 数据库
    else:
        # 履行 SQL 查询句子，并将成果转化为 Pandas DataFrame 目标
        df = pd.read_sql(sql_query, con=engine)
    return df

在上面的代码中，咱们创立了一个名为 query_mysql 的函数，用于衔接 MySQL 数据库，并履行查询操作。该函数接受以下参数：

  • sql_query：SQL 查询句子；
  • host：主机名，默以为 None；
  • port：端口号，默以为 None；
  • user：用户名，默以为 None；
  • password：暗码，默以为 None；
  • database：数据库称号，默以为 None；
  • engine：SQLAlchemy 的数据库引擎目标，默以为 None。

在函数中，咱们首要判断是否已供给数据库衔接引擎目标。假如未供给，则运用 pymysql 库衔接MySQL 数据库，并履行查询操作，过程与前面的榜首种办法相同。假如已供给数据库衔接引擎目标，则运用 SQLAlchemy 库衔接 MySQL 数据库，并履行查询操作，过程与前面的第二种办法相同。

最终，在函数中咱们回来查询成果的 Pandas dataframe 目标。

# 运用 pymysql 库衔接 MySQL 数据库
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',   # 数据库称号
)
# 运用 SQLAlchemy 库衔接 MySQL 数据库
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 函数，咱们能够更加便利地衔接 MySQL 数据库并查询数据，而且代码量更少、可读性更好。一起，因为该函数运用了 pymysql 和 SQLAlchemy 两个库，因而也具有较好的跨渠道性，能够在不同的操作系统和环境下运转。

最终也共享一下个人通过运用的模板：

# 法一:
import pymysql
import pandas as pd
def query_data(sql_query):
    # 衔接数据库
    conn = pymysql.connect(
        host='xxx.xxx.xxx.xxx',  # 主机名
        port=3306,         # 端口号，MySQL默以为3306
        user='xxx',       # 用户名
        password='xxx', # 暗码
        database='xxx',   # 数据库称号
    )
    try:
        # 创立游标目标
        cursor = conn.cursor()
        # 履行 SQL 查询句子
        cursor.execute(sql_query)
        # 获取查询成果
        result = cursor.fetchall()
        # 获取查询成果的字段名和元数据
        columns = [col[0] for col in cursor.description]
        # 将查询成果封装到 Pandas DataFrame 中
        df = pd.DataFrame(result, columns=columns)
        return df
    finally:
        # 封闭游标和衔接
        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()

最终，说一下在访问数据库时，或许存在一些潜在的问题和注意事项。

  • 首要，在运用 pandas.read_sql() 时，需求在 SQL 查询句子中包含一切必要的过滤条件、排序方法等信息，以确保回来的成果集合是正确的，而不是整个表或视图中的一切数据。假如没有限制回来的数据量，或许会导致内存溢出或其他性能问题。因而，在实践运用中，引荐运用 LIMIT 等关键字来设置最大回来数据量，以便更好地控制查询成果。
  • 其次，在实践出产环境中，为了防止泄漏灵敏信息和削减攻击面，建议将数据库衔接字符串等灵敏信息存储在独自的配置文件中，而且只授权给有限的用户运用。另外，在向 SQL 查询句子中传递参数时，也需求进行安全过滤和转义，以防止 SQL 注入等安全问题。
  • 最终，在运用完毕后，需求及时封闭数据库衔接，以开释资源并削减数据库服务器的负载。或许，能够运用 with 句子自动办理衔接的生命周期。

总归，学习如何衔接 MySQL 数据库并将查询成果转化为 Pandas dataframe 目标是数据剖析和建模过程中的重要一步。希望本文对您有所帮助！

本文由mdnice多渠道发布

更多文章