ToB企服应用市场:ToB评测及商务社交产业平台

标题: Python 毗连 MySQL 数据库 [打印本页]
作者: 羊蹓狼    时间: 2024-6-11 11:17
标题: Python 毗连 MySQL 数据库
在现实数据分析和建模过程中，我们通常需要从数据库中读取数据，并将其转化为 Pandas dataframe 对象举行进一步处置处罚。而 MySQL 数据库是最常用的关系型数据库之一，因此在 Python 中如何毗连 MySQL 数据库并查询数据成为了一个重要的问题。
本文将介绍两种方法来毗连 MySQL 数据库，并将查询效果转化为 Pandas dataframe 对象：第一种方法使用 pymysql 库来毗连 MySQL 数据库；第二种方法则使用 SQLAlchemy 的 create_engine 函数创建 MySQL 数据库毗连引擎。同时，针对这两种方法，我们还将对代码举行封装和优化，提高步伐的可读性和健壮性。
方法一：使用 pymysql 库毗连 MySQL 数据库

步调 1：毗连 MySQL 数据库
首先，我们需要使用 pymysql 库来毗连 MySQL 数据库。具体代码如下：
  1. import pymysql
  3. # 连接 MySQL 数据库
  4. conn = pymysql.connect(
  5.     host='159.xxx.xxx.216',  # 主机名
  6.     port=3306,         # 端口号，MySQL默认为3306
  7.     user='xxxx',       # 用户名
  8.     password='xxxx', # 密码
  9.     database='xx',   # 数据库名称
  10. )
复制代码
在上面的代码中，我们通过 pymysql 库的 connect() 函数毗连 MySQL 数据库，并指定主机名、端口号、用户名、暗码和数据库名称等参数。如果毗连成功，则该函数将返回一个数据库毗连对象 conn。
步调 2：执行 SQL 查询语句
毗连 MySQL 数据库之后，我们就可以使用游标对象来执行 SQL 查询语句，如下所示：
  1. # 创建游标对象
  2. cursor = conn.cursor()
  4. # 执行 SQL 查询语句
  5. cursor.execute("SELECT * FROM users WHERE gender='female'")
  7. # 获取查询结果
  8. result = cursor.fetchall()
复制代码
在上面的代码中，我们使用 cursor() 方法创建游标对象 cursor，并使用 execute() 方法执行 SQL 查询语句。在执行查询时，我们可以使用任何符合 MySQL 语法的 SQL 查询语句。末了，我们使用 fetchall() 方法获取查询效果。
步调 3：将查询效果转化为 Pandas dataframe 对象
获取查询效果之后，我们需要将其转化为 Pandas dataframe 对象，以便于举行进一步的数据处置处罚和分析。具体代码如下
  1. import pandas as pd
  4. # 将查询结果转化为 Pandas dataframe 对象
  5. df = pd.DataFrame(result, columns=[i[0] for i in cursor.description])
复制代码
在上面的代码中，我们使用 pd.DataFrame() 方法将查询效果转化为 Pandas dataframe 对象。在转化过程中，我们需要指定字段名，可以通过游标对象的 description 属性来获取查询效果的元数据，此中包罗字段名等信息。
步调 4：关闭游标和数据库毗连
末了，我们需要关闭游标对象和数据库毗连，以释放资源。具体代码如下：
  1. # 关闭游标和数据库连接
  2. cursor.close()
  3. conn.close()
复制代码
方法二：使用 SQLAlchemy 的 create_engine 函数毗连 MySQL 数据库

除了使用 pymysql 库毗连 MySQL 数据库之外，我们还可以使用 SQLAlchemy 的 create_engine 函数创建 MySQL 数据库毗连引擎，并使用 Pandas 库中的 read_sql 函数直接将查询效果转化为 Pandas dataframe 对象。
  1. # 步骤 1：创建 MySQL 数据库连接引擎
  2. from sqlalchemy import create_engine
  4. # 创建 MySQL 数据库连接引擎
  5. engine = create_engine('mysql+pymysql://username:password@host:port/database')
  7. 步骤 2：执行 SQL 查询语句并将结果转化为 Pandas dataframe 对象
  8. import pandas as pd
  10. # 执行 SQL 查询语句，并将结果转化为 Pandas dataframe 对象
  11. df = pd.read_sql("SELECT * FROM users WHERE gender='female'", con=engine)
  13. # 关闭数据库连接
  14. 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 对象。为了方便重复使用，我们可以将这些代码封装成一个函数。
  1. import pandas as pd
  2. import pymysql
  3. from sqlalchemy import create_engine
  6. def query_mysql(sql_query, host=None, port=None, user=None, password=None, database=None, engine=None):
  7.     """
  8.     连接 MySQL 数据库，执行查询，并将查询结果转化为 Pandas DataFrame 对象。
  9.    
  10.     :param sql_query: SQL 查询语句
  11.     :param host: 主机名，默认为 None
  12.     :param port: 端口号，默认为 None
  13.     :param user: 用户名，默认为 None
  14.     :param password: 密码，默认为 None
  15.     :param database: 数据库名称，默认为 None
  16.     :param engine: SQLAlchemy 的数据库引擎对象，默认为 None
  17.    
  18.     :return: Pandas DataFrame 对象
  19.     """
  20.     # 如果未提供数据库连接引擎，则使用 pymysql 库连接 MySQL 数据库
  21.     if engine is None:
  22.         # 连接 MySQL 数据库
  23.         conn = pymysql.connect(
  24.             host=host,
  25.             port=port,
  26.             user=user,
  27.             password=password,
  28.             database=database,
  29.         )
  31.         # 创建游标对象
  32.         cursor = conn.cursor()
  34.         # 执行 SQL 查询语句
  35.         cursor.execute(sql_query)
  37.         # 获取查询结果
  38.         result = cursor.fetchall()
  40.         # 将查询结果转化为 Pandas DataFrame 对象
  41.         df = pd.DataFrame(result, columns=[i[0] for i in cursor.description])
  43.         # 关闭游标和数据库连接
  44.         cursor.close()
  45.         conn.close()
  47.     # 如果已提供数据库连接引擎，则使用 SQLAlchemy 库连接 MySQL 数据库
  48.     else:
  49.         # 执行 SQL 查询语句，并将结果转化为 Pandas DataFrame 对象
  50.         df = pd.read_sql(sql_query, con=engine)
  52.     return df
复制代码
在上面的代码中，我们创建了一个名为 query_mysql 的函数，用于毗连 MySQL 数据库，并执行查询操纵。该函数接受以下参数：

在函数中，我们首先判断是否已提供数据库毗连引擎对象。如果未提供，则使用 pymysql 库毗连MySQL 数据库，并执行查询操纵，步调与前面的第一种方法雷同。如果已提供数据库毗连引擎对象，则使用 SQLAlchemy 库毗连 MySQL 数据库，并执行查询操纵，步调与前面的第二种方法雷同。
末了，在函数中我们返回查询效果的 Pandas dataframe 对象。
  1. # 使用 pymysql 库连接 MySQL 数据库
  2. df1 = query_mysql(
  3.     sql_query="SELECT * FROM users WHERE gender='female'",
  4.     host='159.xxx.xxx.216',  # 主机名
  5.     port=3306,         # 端口号，MySQL默认为3306
  6.     user='xxxx',       # 用户名
  7.     password='xxxx', # 密码
  8.     database='xx',   # 数据库名称
  9. )
  11. # 使用 SQLAlchemy 库连接 MySQL 数据库
  12. engine = create_engine('mysql+pymysql://xxx:xxx@localhost:3306/ad')
  13. df2 = query_mysql(sql_query="SELECT * FROM users WHERE gender='female'", engine=engine)
复制代码
通过使用 query_mysql 函数，我们可以更加方便地毗连 MySQL 数据库并查询数据，而且代码量更少、可读性更好。同时，由于该函数使用了 pymysql 和 SQLAlchemy 两个库，因此也具有较好的跨平台性，可以在差异的操纵体系和环境下运行。
末了也分享一下个人通过使用的模板：
  1. # 法一:
  3. import pymysql
  4. import pandas as pd
  6. def query_data(sql_query):
  7.     # 连接数据库
  8.     conn = pymysql.connect(
  9.         host='xxx.xxx.xxx.xxx',  # 主机名
  10.         port=3306,         # 端口号，MySQL默认为3306
  11.         user='xxx',       # 用户名
  12.         password='xxx', # 密码
  13.         database='xxx',   # 数据库名称
  14.     )
  16.     try:
  17.         # 创建游标对象
  18.         cursor = conn.cursor()
  20.         # 执行 SQL 查询语句
  21.         cursor.execute(sql_query)
  23.         # 获取查询结果
  24.         result = cursor.fetchall()
  26.         # 获取查询结果的字段名和元数据
  27.         columns = [col[0] for col in cursor.description]
  29.         # 将查询结果封装到 Pandas DataFrame 中
  30.         df = pd.DataFrame(result, columns=columns)
  32.         return df
  34.     finally:
  35.         # 关闭游标和连接
  36.         cursor.close()
  37.         conn.close()
  39. db_data = query_data(sql_query)
  41. # 法二:
  42. from sqlalchemy import create_engine
  43. import pandas as pd
  45. def getdata_from_db(query, db, host='xxx.xxx.xxx.xxx', port=3306, user='xxx', password='xxx'):
  46.     try:
  47.         engine = create_engine(f'mysql+pymysql://{user}:{password}@{host}:{port}/{db}?charset=utf8')
  48.         # 使用 with 语句自动管理连接的生命周期
  49.         with engine.connect() as conn:
  50.             data = pd.read_sql(query, conn)
  51.         return data
  52.     except Exception as e:
  53.         print(f"Error occurred when executing SQL query: {e}")
  54.         return None
  55.    
  56. db_data = getdata_from_db(sql_query, 'ad')
  58. # 法三：超级精简版
  59. from sqlalchemy import create_engine
  60. import pandas as pd
  62. engine = create_engine(f'mysql+pymysql://xxx:xxx@xxx:3306/xx?charset=utf8')
  63. db_data = pd.read_sql(sql, engine)
  64. db_data.head()
复制代码
末了，说一下在访问数据库时，可能存在一些埋伏的问题和注意事项。

总之，学习如何毗连 MySQL 数据库并将查询效果转化为 Pandas dataframe 对象是数据分析和建模过程中的重要一步。盼望本文对您有所帮助！

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。



欢迎光临 ToB企服应用市场:ToB评测及商务社交产业平台 (https://dis.qidao123.com/) Powered by Discuz! X3.4