在理论数据分析和建模过程中，咱们通常须要从数据库中读取数据，并将其转化为 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 pdimport pymysqlfrom sqlalchemy import create_enginedef 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 pymysqlimport pandas as pddef 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_engineimport pandas as pddef 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_engineimport pandas as pdengine = 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多平台公布