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= 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= 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 = 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 对象是数据分析和建模过程中的重要一步。盼望本文对您有所帮助！
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。
页:
[1]