乐趣区

关于程序员:Python-连接-MySQL-数据库

在理论数据分析和建模过程中,咱们通常须要从数据库中读取数据,并将其转化为 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 多平台公布

退出移动版