基于Pymysql的数据库的增删改查模版(2)——插入

基于Pymysql的数据库的增删改查模版（2）——插入

最近在利用Pymysql进行数据库交互时，为了提高程序的可扩展性，实现了一套基本的数据库查询、插入、更新、删除模版，记录下来供日后使用

ps：考虑到文章的独立阅读性，保持了一些重复的内容

环境

Mysql 5.7.2 理论上对于数据库版本没有特殊要求

pymsql 0.9.3

python >= 3.6 保证格式化字符串的使用

Mysql插入操作语法

mysql或者说sql中的插入语句有两种类型

• 给出全部属性列的值，不用给出列名

  • 插入单条记录

  INSERT INTO `tablename` VALUES (value1,vaule2,...,valueN);
  

  • 插入多条记录

  INSERT INTO `tablename` VALUES 
  (value1,vaule2,...,valueN),
  (value1,vaule2,...,valueN),
  ...
  (value1,vaule2,...,valueN);
  

• 给出指定属性列及对应的值

  • 插入单条记录

  INSERT INTO `tablename` (col1,col2,...,colN) VALUES (value1,vaule2,...,valueN);
  

  • 插入多条记录

  INSERT INTO `tablename` (col1,col2,...,colN) VALUES 
  (value1,vaule2,...,valueN),
  (value1,vaule2,...,valueN),
  ...
  (value1,vaule2,...,valueN);
  

其中value可以为空值，即NULL

事实上，给出全部属性列的值是前一种方法对特例情况，因而不需要给出列名，但是在实现模版时分别处理这两种情况

数据库插入：类模版实现

初始化

class InsertDB:
    def __init__(self):
        self.db = pymysql.connect('localhost', 'root', 'password', 'database_name')
        self.cursor = self.db.cursor()

初始化部分涉及到数据库的连接，这一部分比较简单，主要是创建游标对象self.cursor，用于执行SQL语句

插入事务基本框架

def modify_query(self, query):
    try:
        self.cursor.execute(query)
        self.db.commit()
        return True
    except Exception as error:
        self.db.rollback()
        return str(error)

这一部分使用游标对象执行SQL语句，正确执行后commit结果至数据库，出现错误时进行rollback，并返回错误信息，比较简单

给出全部属性列的值的插入

设定为按照给定的顺序进行插入

def insert_all_info(self, tablename, *args):
    """
    :param tablename: 被插入的数据库表名 
    :param args: 一条记录中全体属性列的值
    """
    # lambda表达式,用于将单引号替换为转义符，将空值更换为NULL进行插入,并为字符类属性值加上引号
    pattern = lambda k: "'" + str(k).replace("'", "\\'") + "'" if len(str(k)) != 0 else 'NULL'
    items = [pattern(item) for item in args]
    query = f"""INSERT INTO {'`' + tablename + '`'} VALUES ( {(','.join(items))} );"""
    result = self.modify_query(query)
    return result

插入的关键有三个部分，分别是：

• python的lambda表达式，用于对传入字段进行预处理
• python的函数的可变参数
• python的格式化字符串，也就是f'xxx' 类型的字符串

下面讲解一下SQL语句生成的部分：

1. *args 参数实际传入一个元组类型的变量，包含了将要插入的属性列对应的值

   • 对于传来的空值，即相应字符串长度为0的参数会被修改为 NULL 进行插入

   • MYSQL的语法支持在数字的两端添加引号，因此，为了保证在SQL语句中字符的正确表示，需要在拼接前人为的加上引号，此处默认添加单引号

   • 注意到以上两步都是在默认value中不包含'，因此才为其添加单引号，因此在处理包含单引号的属性值还需要将其进行转义,表示为转义字符，且由于需要经过python和MYSQL的两次转义，故有 replace("'", "\\'") 进行转义

   • 这三步都由lambda表达式来完成

2. 通过列表生成式以及lambda表达式共同作用生成需要插入的属性值列表，如下所示

["'value1'", "'value2'",..., "'valueN'"]

3. 通过','.join()即可将其拼接成为如下字符串

"'value1'", "'value2'",..., "'valueN'"

4. 生成SQL查询查询语句

INSERT INTO `tablename` VALUES (value1,vaule2,...,valueN);

给出指定属性列及对应的值的插入

def insert_specified_info(self, tablename, **kwargs):
    """
    :param tablename: 被插入的数据库表名
    :param kwargs: 指定的需要插入的属性列及对应值组成的字典
    """
    # lambda表达式，用于将空值更换为NULL进行插入
    pattern = lambda k: "'" + str(k).replace("'", "\\'") + "'" if len(str(k)) != 0 else 'NULL'
    cols = [key.replace("'", "\\'") for key in kwargs.keys()]
    items = [pattern(item[1]) for item in kwargs.items()]
    query = f"""INSERT INTO {'`' + tablename + '`'} ( {','.join(cols)} ) VALUES ( {(','.join(items))} );"""
    result = self.modify_query(query)
    return result

指定属性列的插入与给出所有属性列值的插入比较类似，区别在于需要使用到插入的列名，因此需要修改可变参数的类型为 **kwargs，传入的参数为类似如下字典(dict)类型

{
	'field1':'value1',
   	'field2':'value2',
  	...
	'fieldN': 'valueN'
}

需要获取**kwargs 字典的全部键的名称，同样可以使用列表生成式

cols = [key.replace("'", "\\'") for key in kwargs.keys()]

同样使用','.join() 生成相同的属性字符串

最终生成相应的SQL插入语句

INSERT INTO `tablename` (col1,col2,...,colN) VALUES (value1,vaule2,...,valueN);

完整代码

import pymysql


class InsertDB:
    def __init__(self):
        self.db = pymysql.connect('localhost', 'root', 'mk123456', 'ST')
        # self.db = pymysql.connect('localhost', 'root', 'password', 'database_name')
        self.cursor = self.db.cursor()

    # 管理基本框架
    def modify_query(self, query):
        try:
            self.cursor.execute(query)
            self.db.commit()
            return True
        except Exception as error:
            self.db.rollback()
            return str(error)

    def insert_specified_info(self, tablename, **kwargs):
        # lambda表达式，用于将空值更换为NULL进行插入
        pattern = lambda k: "'" + str(k).replace("'", "\\'") + "'" if len(str(k)) != 0 else 'NULL'
        cols = [key.replace("'", "\\'") for key in kwargs.keys()]
        items = [pattern(item[1]) for item in kwargs.items()]
        query = f"""INSERT INTO {'`' + tablename + '`'} ( {','.join(cols)} ) VALUES ( {(','.join(items))} );"""
        result = self.modify_query(query)
        return result

    def insert_all_info(self, tablename, *args):
        # lambda表达式,用于将单引号替换为转义符将空值更换为NULL进行插入,并为字符类属性值加上引号
        pattern = lambda k: "'" + str(k).replace("'", "\\'") + "'" if len(str(k)) != 0 else 'NULL'
        items = [pattern(item) for item in args]
        query = f"""INSERT INTO {'`' + tablename + '`'} VALUES ( {(','.join(items))} );"""
        result = self.modify_query(query)
        return result

调用示例

insertdb = InsertDB()
result = insertdb.insert_all_info('tablename', value1, value2, ..., valueN)
result1 = insertdb.insert_specified_info('tablename', field1='value1',...,fieldN='valueN')

其中：

• tablename为待插入记录所在的表名
• field为tablename对应的表的属性字段名，
• value 为相应属性字段的值