Pymysql连接数据库(详细教学)

1.pymysql的基本使用

简单的查询例子：

import pymysql

if __name__ == '__main__':
    # 获取连接对象connection
    connection = pymysql.connect(host="localhost", database="数据库名称", user="root", password="密码")
    # 获取游标
    cursor = connection.cursor()
    # 定义sql语句，这里sql语句中的参数使用%s,可以防止sql注入
    sql = "select * from user where username = %s and password = %s"
    # 执行sql语句,第二个参数传入元组，替代sql语句中的需要的参数
    username = "张三"
    password = "666"
    cursor.execute(sql, (username, password))
    # 获取sql语句返回的数据,fetchall()和fetchone()，获取全部或一个数据
    data = cursor.fetchall()
    # 打印数据,打印的数据是二维元组,例如对于字段：(username,password,age),打印为(("张三","666",18),("李四","777",19))
    print(data)
    # 先关闭游标
    cursor.close()
    # 再关闭连接对象
    connection.close()
    pass

2.pymysql的事务处理

简单的更新sql语句事务的处理例子：

import pymysql

if __name__ == '__main__':
    # 获取连接对象connection
    connection = pymysql.connect(host="localhost", database="数据库名称", user="root", password="密码")
    # 获取游标
    cursor = connection.cursor()
    # 定义sql语句，这里sql语句中的参数使用%s,可以防止sql注入
    sql = "update user set password = %s where username = %s"
    # 执行sql语句,第二个参数传入元组，替代sql语句中的需要的参数
    username = "张三"
    new_password = "999"
    # 对于更新，插入，删除需要改变数据库的语句，需要进行事务处理
    try:
        cursor.execute(sql, (new_password, username))
        # 提交事务,将改变更新到数据库,只有执行此操作后数据库数据才会改变
        cursor.commit()
    except Exception as e:
        # 发生错误则异常回滚，确保数据库不出现问题
        connection.rollback()
        # 打印异常信息
        print(e)
    finally:
        # 先关闭游标
        cursor.close()
        # 再关闭连接对象
        connection.close()

3.pymysql返回字典数据

从数据库获取到的数据是元组类型，对我们来说访问不便，此时可以将数据封装成字典

import pymysql

if __name__ == '__main__':
    # 获取连接对象connection
    connection = pymysql.connect(host="localhost", database="数据库名称", user="root", password="密码")
    # 获取游标
    cursor = connection.cursor()
    # 定义sql语句，这里sql语句中的参数使用%s,可以防止sql注入
    sql = "select * from user where username = %s and password = %s"
    # 执行sql语句,第二个参数传入元组，替代sql语句中的需要的参数
    username = "张三"
    password = "666"
    cursor.execute(sql, (username, password))
    
    # 获取sql语句返回的数据,二维元组,例如(("张三","666",18),("李四","777",19))
    data_tuple = cursor.fetchall()
    # 数据表的字段信息，二维元组,每一个字段比如id是一维元组,例如(("id",not null,xxx),("username",not null,xxx))
    description_tuple = cursor.description
    # 封装数据成字典,存于列表中，例如:[{"username":"张三"},{"username":"李四"},{"username":"王五"}]
    cols = [description[0] for description in description_tuple]
    res_dict = []
    for row in data_tuple:
        res_dict.append(dict(zip(cols, row)))
        pass
    # 打印封装后的字典
    print(res_dict)
    # 先关闭游标
    cursor.close()
    # 再关闭连接对象
    connection.close()

4.pymysql返回字典数据(进阶)

对于项目中很多的查询语句需要返回字典数据，会有大量的重复代码

解决办法：1.函数封装 2.使用装饰器

联想到Java中的面向AOP编程，在python中也可以使用装饰器实现类似的功能，使用函数封装简单，这里不讨论，这里使用装饰器对返回数据进行字典变换(使用函数更简洁，这里使用装饰器仅仅是联想了AOP编程,不过我觉得使用装饰器也有好处，好处就是从数据库获取数据的函数不必添加处理数据返回字典的代码，使其专注于数据的获取，数据的处理交给其他角色，获取和处理进行解耦，便于维护代码)

代码如下：

# 数据字典装饰器
def decorator_return_dict(get_function):
    def wrap(*args, **kwargs):
        data = get_function(*args, **kwargs)
        # 对结果进行封装成字典
        data_tuple = data[0]
        description_tuple = data[1]
        cols = [description[0] for description in description_tuple]
        res_dict = []
        for row in data_tuple:
            res_dict.append(dict(zip(cols, row)))
            pass
        return res_dict

    return wrap

@decorator_return_dict
def get_user_by_id(self, **kwargs):
    connection = pymysql.connect(host="localhost", database="数据库", user="root", password="密码")
    cursor = connection.cursor()
    sql = "select * from user where id = %s"
    cursor.execute(sql, (kwargs["id"],))
    data = cursor.fetchall()
    description = cursor.description
    data = (data, description)
    return data