当前位置： 首页 > news >正文

如何运营网站,网站开发综合实训心得,wordpress全站301,广东网站建设公司报价前言 在实际数据分析和建模过程中#xff0c;我们通常需要从数据库中读取数据#xff0c;并将其转化为 Pandas dataframe 对象进行进一步处理。而 MySQL 数据库是最常用的关系型数据库之一#xff0c;因此在 Python 中如何连接 MySQL 数据库并查询数据成为了一个重要的问题…前言 在实际数据分析和建模过程中我们通常需要从数据库中读取数据并将其转化为 Pandas dataframe 对象进行进一步处理。而 MySQL 数据库是最常用的关系型数据库之一因此在 Python 中如何连接 MySQL 数据库并查询数据成为了一个重要的问题。 本文将介绍两种方法来连接 MySQL 数据库并将查询结果转化为 Pandas dataframe 对象第一种方法使用 pymysql 库来连接 MySQL 数据库第二种方法则使用 SQLAlchemy 的 create_engine 函数创建 MySQL 数据库连接引擎。同时针对这两种方法我们还将对代码进行封装和优化提高程序的可读性和健壮性。

1. 使用 pymysql 库连接 MySQL 数据库 1.1 连接 MySQL 数据库 import pymysql# 连接 MySQL 数据库 conn pymysql.connect(host159.xxx.xxx.216, # 主机名port3306, # 端口号MySQL默认为3306userxxxx, # 用户名passwordxxxx, # 密码databasexx, # 数据库名称 ) 在上面的代码中我们通过 pymysql 库的 connect() 函数连接 MySQL 数据库并指定主机名、端口号、用户名、密码和数据库名称等参数。如果连接成功则该函数将返回一个数据库连接对象 conn。  1.2 执行 SQL 查询语句 连接 MySQL 数据库之后我们就可以使用游标对象来执行 SQL 查询语句如下所示

   创建游标对象

   cursor conn.cursor()# 执行 SQL 查询语句 cursor.execute(SELECT * FROM users WHERE genderfemale)# 获取查询结果 result cursor.fetchall() 在上面的代码中我们使用 cursor() 方法创建游标对象 cursor并使用 execute() 方法执行 SQL 查询语句。在执行查询时我们可以使用任何符合 MySQL 语法的 SQL 查询语句。最后我们使用 fetchall() 方法获取查询结果。 1.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 属性来获取查询结果的元数据其中包括字段名等信息。 1.4 关闭游标和数据库连接 最后我们需要关闭游标对象和数据库连接以释放资源。具体代码如下

   关闭游标和数据库连接

   cursor.close() conn.close() 2. 使用 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(mysqlpymysql://username:passwordhost:port/database)步骤 2执行 SQL 查询语句并将结果转化为 Pandas dataframe 对象 import pandas as pd# 执行 SQL 查询语句并将结果转化为 Pandas dataframe 对象 df pd.read_sql(SELECT * FROM users WHERE genderfemale, conengine)# 关闭数据库连接 engine.dispose() 在上面的代码中我们使用 create_engine 函数创建了一个 MySQL 数据库连接引擎。其中我们需要将数据库连接信息输入到一个字符串中并作为函数的参数传入。其中username 和 password 分别表示登录 MySQL 数据库所需的用户名和密码host 和 port 表示 MySQL 数据库的主机名和端口号database 表示要连接的 MySQL 数据库名称。 接着使用使用 pd.read_sql() 函数执行 SQL 查询语句并将数据库连接引擎对象 engine 作为参数传入。在执行查询时我们可以使用任何符合 MySQL 语法的 SQL 查询语句。最后该函数将返回查询结果的 Pandas dataframe 对象。 最后我们需要关闭数据库连接以释放资源。 3. 函数封装 以上介绍了两种方法来连接 MySQL 数据库并将查询结果转化为 Pandas dataframe 对象。为了方便重复使用我们可以将这些代码封装成一个函数。 import pandas as pd import pymysql from sqlalchemy import create_enginedef query_mysql(sql_query, hostNone, portNone, userNone, passwordNone, databaseNone, engineNone):连接 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(hosthost,portport,useruser,passwordpassword,databasedatabase,)# 创建游标对象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, conengine)return df 在上面的代码中我们创建了一个名为 query_mysql 的函数用于连接 MySQL 数据库并执行查询操作。该函数接受以下参数 sql_querySQL 查询语句host主机名默认为 Noneport端口号默认为 Noneuser用户名默认为 Nonepassword密码默认为 Nonedatabase数据库名称默认为 NoneengineSQLAlchemy 的数据库引擎对象默认为 None。 在函数中我们首先判断是否已提供数据库连接引擎对象。如果未提供则使用 pymysql 库连接MySQL 数据库并执行查询操作步骤与前面的第一种方法相同。如果已提供数据库连接引擎对象则使用 SQLAlchemy 库连接 MySQL 数据库并执行查询操作步骤与前面的第二种方法相同。 最后在函数中我们返回查询结果的 Pandas dataframe 对象

   使用 pymysql 库连接 MySQL 数据库

   df1 query_mysql(sql_querySELECT * FROM users WHERE genderfemale,host159.xxx.xxx.216, # 主机名port3306, # 端口号MySQL默认为3306userxxxx, # 用户名passwordxxxx, # 密码databasexx, # 数据库名称 )# 使用 SQLAlchemy 库连接 MySQL 数据库 engine create_engine(mysqlpymysql://xxx:xxxlocalhost:3306/ad) df2 query_mysql(sql_querySELECT * FROM users WHERE genderfemale, engineengine) 通过使用 query_mysql 函数我们可以更加方便地连接 MySQL 数据库并查询数据并且代码量更少、可读性更好。同时由于该函数使用了 pymysql 和 SQLAlchemy 两个库因此也具有较好的跨平台性可以在不同的操作系统和环境下运行。 最后也分享一下个人通过使用的模板

   法一:import pymysql

   import pandas as pddef query_data(sql_query):# 连接数据库conn pymysql.connect(hostxxx.xxx.xxx.xxx, # 主机名port3306, # 端口号MySQL默认为3306userxxx, # 用户名passwordxxx, # 密码databasexxx, # 数据库名称)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, columnscolumns)return dffinally:# 关闭游标和连接cursor.close()conn.close()db_data query_data(sql_query)# 法二: from sqlalchemy import create_engine import pandas as pddef getdata_from_db(query, db, hostxxx.xxx.xxx.xxx, port3306, userxxx, passwordxxx):try:engine create_engine(fmysqlpymysql://{user}:{password}{host}:{port}/{db}?charsetutf8)# 使用 with 语句自动管理连接的生命周期with engine.connect() as conn:data pd.read_sql(query, conn)return dataexcept Exception as e:print(fError occurred when executing SQL query: {e})return Nonedb_data getdata_from_db(sql_query, ad)# 法三超级精简版 from sqlalchemy import create_engine import pandas as pdengine create_engine(fmysqlpymysql://xxx:xxxxxx:3306/xx?charsetutf8) db_data pd.read_sql(sql, engine) db_data.head() 最后说一下在访问数据库时可能存在一些潜在的问题和注意事项。 首先在使用 pandas.read_sql() 时需要在 SQL 查询语句中包含所有必要的过滤条件、排序方式等信息以确保返回的结果集合是正确的而不是整个表或视图中的所有数据。如果没有限制返回的数据量可能会导致内存溢出或其他性能问题。因此在实际应用中推荐使用 LIMIT 等关键字来设置最大返回数据量以便更好地控制查询结果。其次在实际生产环境中为了避免泄漏敏感信息和减少攻击面建议将数据库连接字符串等敏感信息存储在单独的配置文件中并且只授权给有限的用户使用。另外在向 SQL 查询语句中传递参数时也需要进行安全过滤和转义以避免 SQL 注入等安全问题。最后在使用完毕后需要及时关闭数据库连接以释放资源并减少数据库服务器的负载。或者可以使用 with 语句自动管理连接的生命周期。 总之学习如何连接 MySQL 数据库并将查询结果转化为 Pandas dataframe 对象是数据分析和建模过程中的重要一步。希望本文对您有所帮助