I'm really new to SQL, MySQL, and PyMySQL so I'm sorry if this is an easy question.
I'm trying to insert a new record back into a table inside my database. I'm using the following code:
resultValues = ['2022-11-14 00:00:00', 1018, '247 Voice', 18, 81, 93, 1, 9, 'Workload was significantly above expectations, with a 100% increase.', 0, 1, 0, 1, 0, None, 'replace', 'replace', '2022-12-01 14:19:45', 1]
sql3 = """INSERT INTO poi_template_text_locale (Time_Interval, QueueID, NameStr, TemplateID, SectionID, ContentSection, PageSection, LineNum, Sentence, Graph1ID, Graph1Placement, Graph2ID, Graph2Placement, TableID, TablePlacement, Graph1Text, Graph2Text, TSTIMESTAMP, LocaleID) VALUES (%s);"""
cursor.execute(sql3 % (tuple(list(map(str, resultValues)))))
connection.commit()
My aim is to insert all of the data in the 'resultValues' list into each column listed in the 'sql3' query as one record. I've made sure that the order of columns is in the same order as the list data. I got the %s wildcard at the end of the 'sql3' query but I'm not sure about a few things. For a whole list of data, do I need only one wildcard for the whole list, or a wildcard for each item of the list. Is it better for the data to be in a list of a tuple?
Also there are 2 'replace' items in the list. Those are there because I'm not sure how to cast a value as NULL. I tried casting the value as None in python, but that didn't seem to work as it returned an error.
This current code gives me the error:
TypeError: not all arguments converted during string formatting
If I change the code so there are as many %s placeholders as there are list items, I get the same error:
TypeError: not all arguments converted during string formatting
If I remove the tuple function, leaving just list(map(str, resultValues)) as the argument for cursor.execute(), I then recieve this error:
pymysql.err.ProgrammingError: (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 '['2022-11-14 00:00:00', '1018', '247 Voice', '18', '81', '93', '1', '9', 'Worklo' at line 1")
I believe this error is because it doesn't know what to do with the square brackets from the list.
I've been trying to tackle this problem for almost 3 days, and I've done countless research on stack overflow, reddit, discord, and regular web articles - so any and all help would be much appreciated, Thank you
It took some time but I figured it out!
sql3 = """INSERT INTO poi_template_text_locale (Time_Interval, QueueID, NameStr, TemplateID, SectionID, ContentSection, PageSection, LineNum, Sentence, Graph1ID, Graph1Placement, Graph2ID, Graph2Placement, TableID, TablePlacement, Graph1Text, Graph2Text, TSTIMESTAMP, LocaleID) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s);"""
cursor.execute(sql3, (resultValues))
connection.commit()
I put all 19 %s characters into the 'sql3' prompt myself and changed the cursor.execute() function to simply just cursor.execute(sql3, (resultValues)) instead of my previous cursor.execute(sql3 % (tuple(list(map(str, resultValues))))).
To future people who may need this for help - make sure you have the same amount of '%s' characters as you do items to insert into a single record.