MySQL ERROR - Error 1054: Unknown column in 'where clause'

Question

I have a list of dictionaries that is being passed as an argument to the updateResource() after cleaning the data.

#method to clean the dictionary data
def getResource(self):
    try:
        for i in range(len(data)):
            sys_name = data[i]["system_name"]
            team = data[i]["fdc_inv_sa_team"]
            sys_name = re.sub('.1DC.com|.1dc.com|.1dc.COM |.1DC.COM\\b', '', str(sys_name))
            sys_name = str(sys_name.strip('"[]"'))
            team = str(team).replace('\\n', '')
            team = str(str(team).strip("[]"))
            data[i]["system_name"] = sys_name
            data[i]["fdc_inv_sa_team"] = team
            return data
    except Exception, e:
        logger.error("Error : ", str(e))

Here is the method that takes list of dictionaries as an argument and updates the database after doing a few checks.

#method to update the database with the dictionary key-value pair.
def updateResource(self, data):
    for i in range(len(data)):
        self.arg1 = data[i]["system_name"]
        self.arg2 = data[i]["fdc_inv_sa_team"]
        try:
            query1_row = self.cursor.execute(self.select_query %self.arg1)
            if query1_row:
                print "Success"
            else:
                self.cursor.execute(self.insert_query, (self.arg1, self.arg2, "Resource Not Present In Echo_Resource Table", \
                                                    str(datetime.now())))
                self.cnx.commit()

        except MySQLdb.Error as e:
            logger.error("Error %d: %s" % (e.args[0],e.args[1]))
        except Exception, e:
            logger.error("Error : ", str(e))

Here are the example queries -

select_query = "SELECT resource_id, resource_name, support_contact_id \
              FROM echo_resource \
             WHERE resource_name = (%s) \
               AND inactive = 0;"


update_query = "UPDATE echo_resource \
               SET support_contact_id = ( \
                   SELECT contact_id FROM contacts WHERE last_name = (%s)), \
                   update_date = (%s) \
             WHERE resource_name = (%s);"


contact_echo_resource_query = "SELECT 1 \
                  FROM echo_resource \
                 WHERE resource_name = (%s) \
                   AND support_contact_id = (SELECT contact_id \
                                               FROM contacts \
                                              WHERE last_name = (%s));"

contacts_query = "SELECT 1 \
                FROM contacts \
               WHERE last_name = (%s);"

insert_query = "INSERT INTO echo_resource_log VALUES(%s, %s, %s, %s);"

Structure of echo_resource table -

resource_id varchar(40) NO  PRI 
resource_name   varchar(255)    YES MUL 
description longtext    YES     
ip_address  varchar(40) YES     
resource_tag    varchar(40) YES     
support_contact_id  int(11) YES MUL 
last_found_date_time    datetime    YES     

Error message -

[2017-07-17 18:14:31,794] {updateEchoResource.py:82} DEBUG - Arguments for the queries : n3bvap049, X2Linux_NSS
[2017-07-17 18:14:31,795] {updateEchoResource.py:121} ERROR - Error 1054: Unknown column 'n3bvap049' in 'where clause'

Answer 1

Well isn't it obvious, cause you are missing single quote '' around your column values and thus it's getting considered as column name too

WHERE resource_name = (%s) \
                      ^...here

So you will have to concatanate your value with your query (OR) better yet use parameterized query