python连接MySQL使用

my向阳而生

于 2024-05-17 12:25:37 发布

阅读量967

点赞数 20

分类专栏： MySQL 文章标签： mysql 数据库

本文链接：https://blog.csdn.net/u014694915/article/details/139001453

版权

MySQL 专栏收录该内容

8 篇文章 0 订阅

订阅专栏

法一： pymysql

步骤 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',   # 数据库名称
)

步骤 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])

步骤 4：关闭游标和数据库连接

最后，我们需要关闭游标对象和数据库连接，以释放资源

# 关闭游标和数据库连接
cursor.close()
conn.close()

法二：SQLAlchemy

使用 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-connector-python

1. 安装MySQL驱动

在开始之前，需要安装Python的MySQL数据库驱动程序。

最常用的驱动程序之一是mysql-connector-python

使用pip进行安装：

pip install mysql-connector-python

2. 连接MySQL数据库

在进行任何数据库操作之前，首先需要建立与MySQL数据库的连接。通常，需要提供数据库的主机名、用户名、密码和数据库名称。

以下是连接到MySQL数据库的示例：

import mysql.connector

# 创建数据库连接
db = mysql.connector.connect(
    host="localhost",  # MySQL服务器地址
    user="username",   # 用户名
    password="password",  # 密码
    database="mydatabase"  # 数据库名称
)

# 创建游标对象，用于执行SQL查询
cursor = db.cursor()

3. 创建数据库

创建一个新的数据库，可以执行以下操作：

# 创建一个名为"mydatabase"的新数据库
cursor.execute("CREATE DATABASE mydatabase")

4. 创建数据表

在数据库中，数据以表格形式组织。在创建表格之前，需要定义表格的结构，包括列名、数据类型和约束。

以下是一个示例：

# 创建一个名为"customers"的数据表
cursor.execute("CREATE TABLE customers (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255), address VARCHAR(255))")

5. 插入数据

插入数据是将数据添加到数据表中的过程。可以使用SQL的INSERT INTO语句来执行插入操作。

以下是一个示例：

# 插入一条记录到"customers"表中
sql = "INSERT INTO customers (name, address) VALUES (%s, %s)"
values = ("John Smith", "123 Main St")
cursor.execute(sql, values)

# 提交更改到数据库
db.commit()

print(f"插入了 {cursor.rowcount} 条记录")

6. 查询数据

查询数据是从数据库中检索数据的过程。使用SQL的SELECT语句来执行查询操作。

以下是一个示例：

# 查询所有记录
cursor.execute("SELECT * FROM customers")

# 获取查询结果
results = cursor.fetchall()

for row in results:
    print(row)

7. 更新数据

更新数据是修改现有数据的过程。使用SQL的UPDATE语句来执行更新操作。

以下是一个示例：

# 更新"customers"表中id为1的记录
sql = "UPDATE customers SET address = %s WHERE id = %s"
values = ("456 Elm St", 1)
cursor.execute(sql, values)

# 提交更改到数据库
db.commit()

print(f"更新了 {cursor.rowcount} 条记录")

8. 删除数据

删除数据是从数据库中删除数据的过程。使用SQL的DELETE语句来执行删除操作。

以下是一个示例：

# 删除"customers"表中id为1的记录
sql = "DELETE FROM customers WHERE id = %s"
values = (1,)
cursor.execute(sql, values)

# 提交更改到数据库
db.commit()

print(f"删除了 {cursor.rowcount} 条记录")

9. 执行事务

事务是一组SQL操作，要么全部成功，要么全部失败。在某些情况下，需要使用事务来确保数据库的完整性。

以下是一个示例：

try:
    db.start_transaction()
    # 执行一系列SQL操作
    cursor.execute("INSERT INTO customers (name, address) VALUES (%s, %s)", ("Alice", "789 Oak St"))
    cursor.execute("UPDATE customers SET address = %s WHERE id = %s", ("789 Elm St", 2))
    db.commit()  # 提交事务
except:
    db.rollback()  # 事务回滚，撤销之前的操作

10. 关闭数据库连接

在完成数据库操作后，不要忘记关闭数据库连接，以释放资源。

cursor.close()  # 关闭游标
db.close()  # 关闭数据库连接

11.完整MySQL数据库编程示例

示例：一个完整的MySQL数据库编程示例
以下是一个完整的MySQL数据库编程示例，包括连接数据库、创建表格、插入数据、查询数据、更新数据和删除数据：

import mysql.connector

# 创建数据库连接
db = mysql.connector.connect(
    host="localhost",
    user="username",
    password="password",
    database="mydatabase"
)

# 创建游标对象
cursor = db.cursor()

# 创建一个名为"customers"的数据表
cursor.execute("CREATE TABLE customers (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255), address VARCHAR(255))")

# 插入记录
sql = "INSERT INTO customers (name, address) VALUES (%s, %s)"
values = [("John Smith", "123 Main St"), ("Alice Johnson", "456 Elm St")]
cursor.executemany(sql, values)
db.commit()

# 查询记录
cursor.execute("SELECT * FROM customers")
results = cursor.fetchall()
for row in results:
    print(row)

# 更新记录
sql = "UPDATE customers SET address = %s WHERE name = %s"
values = ("789 Oak St", "Alice Johnson")
cursor.execute(sql, values)
db.commit()

# 删除记录
sql = "DELETE FROM customers WHERE name = %s"
values = ("John Smith",)
cursor.execute(sql, values)
db.commit()

# 关闭游标和数据库连接
cursor.close()
db.close()

12.安全性注意事项

在进行数据库编程时，请务必注意安全性。避免直接将用户提供的数据插入SQL语句，以防止SQL注入攻击。可以使用参数化查询或ORM（对象关系映射）来增强安全性。

法一、法二函数封装

以上介绍了两种方法来连接 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 两个库，因此也具有较好的跨平台性，可以在不同的操作系统和环境下运行。

模板

1.pymysql模版

# 法一:

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)

2.sqlalchemy模版

1）普通版

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')

2）精简版

# 超级精简版
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://blog.csdn.net/Saki_Python/article/details/134135687
原文链接：https://blog.csdn.net/weixin_44976611/article/details/130733910

my向阳而生

关注

20
点赞
踩
28

收藏

觉得还不错? 一键收藏
0
评论
python连接MySQL使用

其中，username 和 password 分别表示登录 MySQL 数据库所需的用户名和密码，host 和 port 表示 MySQL 数据库的主机名和端口号，database 表示要连接的 MySQL 数据库名称。除了使用 pymysql 库连接 MySQL 数据库之外，我们还可以使用 SQLAlchemy 的 create_engine 函数创建 MySQL 数据库连接引擎，并使用 Pandas 库中的 read_sql 函数直接将查询结果转化为 Pandas dataframe 对象。
复制链接

扫一扫