Python实现连接MySQL数据库的常见方法总结
作者:欢乐马10 发布时间:2024-01-22 05:28:26
在实际数据分析和建模过程中,我们通常需要从数据库中读取数据,并将其转化为 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 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=[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 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 = [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_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 对象是数据分析和建模过程中的重要一步。
来源:https://juejin.cn/post/7234061982580015161


猜你喜欢
- 首先得抛出个重要的前提观点,大屏幕、宽屏幕根本不是为方便“扫视”准备的。所以说以内容为基础的网站设计不用考虑超宽、超大的场景,甚至包括服务类
- 译者的话:寡妇、孤儿、捧打鸳鸯,这些场面足以让你有想哭的冲动,但当你在排版设计时,同样会碰到这些问题,甚至可能更槽。虽说今天的页面排版软件可
- 神经网络玩得越久就越会尝试一些网络结构上的大改动。先说意图有两个模型:模型A和模型B。模型A的输出可以连接B的输入。将两个小模型连接成一个大
- 假如你正在运行使用MySQL的Web应用程序,那么你把密码或者其他敏感信息保存在应用程序里的机会就很大。保护这些数据免受黑客或者窥探者的获取
- 普通MySQL运行,数据量和访问量不大的话,是足够快的,但是当数据量和访问量剧增的时候,那么就会明显发现MySQL很慢,甚至do
- 作者:bencalie 整理日期:2004年6月15日<xml id="users"> <u
- isset(PHP 3, PHP 4, PHP 5 )isset -- 检测变量是否设置描述bool isset ( mixed var [
- 接着上篇文章《解析SQL 表结构信息查询 含主外键、自增长》里面提到了INFORMATION_SCHEMA视图,其实到了SQL 2005微软
- 利用python pyheatmap包绘制热力图,供大家参考,具体内容如下import matplotlib.pyplot as pltfr
- 本文实例讲述了python面试题之列表声明。分享给大家供大家参考,具体如下:下面程序输出的结果为?val = [['a']*
- 前言了解了D3.js的基本开发和组件以后,我们开始应用它激动人心之处:绚丽的预定义图形,应用D3.js,我们在它的示例文件的基础上稍加变动即
- python判断一个变量是否已经设置的方法:可以使用locals()函数来进行判断。locals()函数会以字典类型返回当前位置的全部局部变
- 本文主要研究的是tornado 多进程模式的相关内容,具体如下。官方文档的helloworld实例中的启动方法:if __name__ ==
- 通过获取子图的label和线型来合并图例注意添加label#导入数据(读者可忽略)pre_lp=total_res#组合模型true=dif
- 本文实例讲述了Python使用sax模块解析XML文件。分享给大家供大家参考,具体如下:XML样例:<?xml version=&qu
- 如下所示:import cv2import os#图片路径im_dir = '/home/suanfa/data/out/20170
- 1、获取插入数据的主键idimport pymysqldatabase = pymysql.connect( host=&quo
- 本文为大家分享了python2.7和NLTK安装教程,具体内容如下系统:Windows 7 Ultimate 64-bitsPython 2
- QSplitter使用户可以通过拖动子面板的边界控制子面板的大小。在我们的例子中,我们使用了两个QSplitter 对三个QFrame 控件
- 在头条看了一篇文章,说五行代码实现人脸识别,一时感兴趣了,来搞搞先是按照文章说的 操作了几步,到后面虽然,import dlib 不报错,但