I cannot INSERT INTO Variable to MYSQL with Python3

Question

import MySQLdb
# Open database connection
payload_list = ["10","20","30","40"]
db = MySQLdb.connect("localhost","","","iot" )

# prepare a cursor object using cursor() method
cursor = db.cursor()

# execute SQL query using execute() method.
sql = "INSERT INTO iot_data (data_ip,data_mac,data_humidity,data_temp,data_time) VALUES('%s', '%s', '%s', '$s', NOW()))"
cursor.execute("INSERT INTO iot_data (data_ip,data_mac,data_humidity,data_temp,data_time) VALUES('%s', '%s', '%s', '$s', NOW()))",(payload_list))

# Fetch a single row using fetchone() method.
db.commit()

# disconnect from server
db.close()

Error

Traceback (most recent call last): File "db.py", line 13, in cursor.execute("INSERT INTO iot_data (data_ip,data_mac,data_humidity,data_temp,data_time) VALUES('%s', '%s', '%s', '$s', NOW()))",(payload_list[0],payload_list[1],payload_list[2],payload_list[3],)) File "/usr/lib/python3/dist-packages/MySQLdb/cursors.py", line 210, in execute query = query % args TypeError: not all arguments converted during string formatting

try removing quotes around %s

jophab
– jophab

2017-02-05 10:38:11 +00:00
Commented Feb 5, 2017 at 10:38 — jophab
– jophab, Commented Feb 5, 2017 at 10:38
Thank you, very useful answer

Sitita Charuraks
– Sitita Charuraks

2017-02-06 04:41:22 +00:00
Commented Feb 6, 2017 at 4:41 — Sitita Charuraks
– Sitita Charuraks, Commented Feb 6, 2017 at 4:41

Sitita Charuraks · Accepted Answer · 2017-02-05 08:52:52Z

1

Here is my solution

#!/usr/bin/python3
import datetime
import MySQLdb
# Open database connection
payload_list = ["HI",2,3,4]
db = MySQLdb.connect("localhost","","","iot" )

# prepare a cursor object using cursor() method
cursor = db.cursor()
your_date = "2017-01-12 19:51:44"
# execute SQL query using execute() method.

sql = """INSERT INTO iot_data (data_ip,data_mac,data_humidity,data_temp,data_time) VALUES(%s, '%s', '%s', '%s', NOW())"""
cursor.execute(sql,payload_list)

# Fetch a single row using fetchone() method.
db.commit()

# disconnect from server
db.close()

answered Feb 5, 2017 at 8:52

Sitita Charuraks

238 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

I cannot INSERT INTO Variable to MYSQL with Python3

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related