MySQL database insert into statement with parameters Error

When using MySQL insert statement, I suddenly found that when inserting non numeric parameters, the database will report an error

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

Cloumn is followed by the parameter to be inserted

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('database address', 'database username', 'database password', 'database name', 'encoding method')    
jk = con1.mysql_increase('data','user', 'password','2ddd22','3333')

The reason for the error is that the database incorrectly identifies the value to be inserted as a column name
The solution is to add single quotes’ to both sides of the value to be inserted in the SQL statement, with the following code.
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(‘database address’, ‘database username’, ‘database password’, ‘database name’, ‘encoding method’)
jk = con1.mysql_increase(‘data’,’user’, ‘password’,’2ddd22′,’3333′)
Disadvantage, after adding single quotes, the SQL statement will not recognize the keyword like Null and will treat it as a character. To use keywords, you need to write the SQL statement directly. For example：
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(‘database address’, ‘database username’, ‘database password’, ‘database name’, ‘encoding method’)
jk = con1.mysql_increase(‘data’,’user’, ‘password’)
Insert null values into the user and password columns of the data table

Similar Posts: