mySQL Field Name Titles to avoid

user_12355862 0
Viewed 38

My variable values are derived from the edited grid cell. The function works but the edited field name is named "Read". I fixed it by changing the column name, but I am curious why that is an error and if there are any other field name titles I should avoid.

Message=(1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Read = 'ALL' where User_ID = 'j_adams58'' at line 1")

Table fields

| User_ID | Password | Read | Edit |

 def onCellChanged(self,event):   
        #Establish connection
        self.connect_mysql()

        #Update database
        key_id =   str(self.GetCellValue(event.GetRow(),1))  
        target_col = str(self.GetColLabelValue(event.GetCol()))
        key_col = str(self.GetColLabelValue(1))                                     
        nVal =  self.GetCellValue(event.GetRow(),event.GetCol())
        sql_update = f"""Update {tbl} set {target_col} = %s where {key_col} = %s"""
        row_data = ''
       
        self.cursor.execute(sql_update, (nVal, key_id,))
        
       #Close connection
        self.close_connection()
mysql-python python
1 Answers

Read is a reserved keyword in MySQL, so you shouldn't use it as a column name, but if you really need to, you should be able to access it by putting single backticks around it, like `Read`, but again, it's really bad style and you shouldn't do it. You should also avoid using other keywords, but it's usually best to try the queries you'll run in SQL first so you can check if you can.

See: https://dev.mysql.com/doc/refman/8.0/en/keywords.html#keywords-8-0-detailed-R

user_113921770
Related