Error: '1054 (42S22): Unknown column 'Jo' in 'field list''-CodePudding

I'm attempting to execute a simple query that inserts two names, "self.player1", and "self.player2" as VARCHAR datatypes into a table on a MySQL server version 8.0.31. When I call a custom-built function called "execute_query()" provided below, I receive the following error in my terminal:

Error: '1054 (42S22): Unknown column 'Jo' in 'field list''

Function Definition of "execute_query()":


def execute_query(self, query):

        ''' This function takes SQL queries stored
        in Python as strings and passes them
        to the "cursor.execute()" method to 
        execute them on the server'''

        cursor = self.connection.cursor()
        try:
            cursor.execute(query)
            self.connection.commit() # Implements commands detailed in SQL queries
            print(query   "Query successful")
        except Error as err:
            print(f"Error: '{err}'")

The names of the attributes are "player_1" and "player_2", and the values to be inserted are assigned to variable names "self.player1", and "self.player2" as shown below.


query = "USE "   self.db_obj.db_name   ";"
self.db_obj.execute_query(query)
query = "INSERT INTO `match`(player_1, player_2) VALUES ("   self.player1   ","   self.player2   ");"

It is important to mention that the issue is not with the "execute_query()" function as I have been able to successfully execute other queries. Additionally, I have run through numerous other similar issues on this discussion forum and have tried dropping triggers, changing secure file priviliges, and hardcoding the value of my entries into the query as opposed to concatenating the values to other strings to produce the resultant query, and have not had any luck.

The design of the table, match is provided below (Note: I was not able to write out the contents of this query to a text file using the following command:


SELECT * FROM `match`
INTO OUTFILE "test.txt";

as I received this error:

The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

The query that I used to generated the table in the image below is:


DESCRIBE `match`;

CodePudding user response：

Your error says : Error: '1054 (42S22): Unknown column 'Jo' in 'field list''.

The word 'Jo' in the error is not a field name but a player name. You need to modify your query to :

query = "INSERT INTO `match`(player_1, player_2) VALUES ('"   self.player1   "','"   self.player2   "');"

Since string values need to be between single quotes.