pymysql - python 3.6 on AWS Lambda - handling inserting data into SQL statements

Question

I have been spending hours trying to and searching for how to insert strings and decimals into a sql insert statement. I cannot seem to find an answer that works. I am using AWS Lambda with Python 3.6 to process data. I continue to have issues getting the values of variables to inserted into my SQL statement. I am getting the following error message.

My code:

timeStamp = 123456789.123456
thing = "Testing/IoT"
statement = "INSERT INTO `cycles` (`timeStamp`, `thing`) VALUES ({}, {})"
theData = (timeStamp, thing)
logger.info(statement, theData)
cursor = conn.cursor()
cursor.execute(statement, theData)
conn.commit()

Error message:

not all arguments converted during string formatting: TypeError Traceback (most recent call last): File "/var/task/recordCyclesRDS.py", line 56, in handler logger.info(statement, theData) File "/var/lang/lib/python3.6/logging/init.py", line 1306, in info self._log(INFO, msg, args, **kwargs) File "/var/lang/lib/python3.6/logging/init.py", line 1442, in _log self.handle(record) File "/var/lang/lib/python3.6/logging/init.py", line 1452, in handle self.callHandlers(record) File "/var/lang/lib/python3.6/logging/init.py", line 1514, in callHandlers hdlr.handle(record) File "/var/lang/lib/python3.6/logging/init.py", line 863, in handle self.emit(record) File "/var/runtime/awslambda/bootstrap.py", line 442, in emit msg = self.format(record) File "/var/lang/lib/python3.6/logging/init.py", line 838, in format return fmt.format(record) File "/var/lang/lib/python3.6/logging/init.py", line 575, in format record.message = record.getMessage() File "/var/lang/lib/python3.6/logging/init.py", line 338, in getMessage msg = msg % self.args TypeError: not all arguments converted during string formatting

I can make this work:

statement = "INSERT INTO `cycles` (`timeStamp`, `thing`) VALUES (123456.123456, 'Testing/IoT')"

Chris Pollard Chris Pollard · Accepted Answer · 2017-11-13T00:37:35

Try this:

timeStamp = 123456789.123456
thing = "Testing/IoT"
statement = "INSERT INTO `cycles` (`timeStamp`, `thing`) VALUES ({}, {})"
theData = (str(timeStamp), thing)
logger.info(statement, theData)
cursor = conn.cursor()
cursor.execute(statement, theData)
conn.commit()

pymysql - python 3.6 on AWS Lambda - handling inserting data into SQL statements

1 Answers