[大数据]MySQL数据库INSERT INTO语句带参数报错

使用MySQL的插入语句时，忽然发现，插入非数字的参数时，数据库会报错

mysql.connector.errors.ProgrammingError: 1054 (42S22): Unknown column '2ddd22' in 'field list'

cloumn后面为要插入的参数

import mysql.connector

class MysqlGroup(object):
    def __init__(self,host,user,password,database,charset):
        self.mydb=mysql.connector.connect(host = host,user = user,port = 3306,password = password,database = database,charset = charset,buffered = True)
        self.mycursor=self.mydb.cursor(buffered = True)
    
    def mysql_increase(self, surface_name, column_name1, column_name2, parameter1, parameter2):  
        self.mycursor.execute(
            "INSERT INTO %s (%s, %s) VALUES (%s, %s)" % (surface_name,column_name1, column_name2, parameter1, parameter2))
        self.mydb.commit()

                               
con1 = MysqlGroup('数据库地址', '数据库用户名', '数据库密码', '数据库名', '编码方式')    
jk = con1.mysql_increase('data','user', 'password','2ddd22','3333')

报错原因，数据库错误的把要插入的值识别为列名

解决办法在SQL语句中的要插入的值两边加上单引号'，代码如图

import mysql.connector

class MysqlGroup(object):
    def __init__(self,host,user,password,database,charset):
        self.mydb=mysql.connector.connect(host = host,user = user,port = 3306,password = password,database = database,charset = charset,buffered = True)
        self.mycursor=self.mydb.cursor(buffered = True)
    
    def mysql_increase(self, surface_name, column_name1, column_name2, parameter1, parameter2):  
        self.mycursor.execute(
            "INSERT INTO %s (%s, %s) VALUES ('%s', '%s')" % (surface_name,column_name1, column_name2, parameter1, parameter2))
        self.mydb.commit()

                               
con1 = MysqlGroup('数据库地址', '数据库用户名', '数据库密码', '数据库名', '编码方式')    
jk = con1.mysql_increase('data','user', 'password','2ddd22','3333')

缺点，加了单引号之后，SQL语句就无法识别参数Null之类的关键字，会将其当做字符处理。使用关键字需要直接写入SQL语句。如

import mysql.connector

class MysqlGroup(object):
    def __init__(self,host,user,password,database,charset):
        self.mydb=mysql.connector.connect(host = host,user = user,port = 3306,password = password,database = database,charset = charset,buffered = True)
        self.mycursor=self.mydb.cursor(buffered = True)
    
    def mysql_insert_null(self, surface_name, column_name1, column_name2):  
        self.mycursor.execute(
            "INSERT INTO %s (%s, %s) VALUES (Null, Null)" % (surface_name,column_name1, column_name2))
        self.mydb.commit()

                               
con1 = MysqlGroup('数据库地址', '数据库用户名', '数据库密码', '数据库名', '编码方式')    
jk = con1.mysql_increase('data','user', 'password')

往data表中user列和password列插入空值