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 数据库，并执行查询操纵。该函数接受以下参数：

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

在函数中，我们首先判断是否已提供数据库毗连引擎对象。如果未提供，则使用 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()

复制代码

末了，说一下在访问数据库时，可能存在一些埋伏的问题和注意事项。

• 首先，在使用 pandas.read_sql() 时，需要在 SQL 查询语句中包罗所有必要的过滤条件、排序方式等信息，以确保返回的效果聚集是精确的，而不是整个表或视图中的所有数据。如果没有限定返回的数据量，可能会导致内存溢出或其他性能问题。因此，在现实应用中，推荐使用 LIMIT 等关键字来设置最大返回数据量，以便更好地控制查询效果。
  
• 其次，在现实生产环境中，为了避免走漏敏感信息和淘汰攻击面，建议将数据库毗连字符串等敏感信息存储在单独的设置文件中，而且只授权给有限的用户使用。另外，在向 SQL 查询语句中转达参数时，也需要举行安全过滤和转义，以避免 SQL 注入等安全问题。
  
• 末了，在使用完毕后，需要及时关闭数据库毗连，以释放资源并淘汰数据库服务器的负载。或者，可以使用 with 语句自动管理毗连的生命周期。
  

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

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