Syntax formatting for SQL Insert into statements in Python 3

Question

I am attempting to insert a Date, Time, and Temperature value from sensors I have installed in the field. I have all the computations and variables working as intended but the sql statement fails. Here is the a rough format of how the argument is formatted:

  arg=('2019-07-21', '07:00:00', 29.323330729166656)

My SQL table is formatted as follows:

Date (as DATE), Time (as TIME), Temperature (as DOUBLE)

However, the insert into statement fails due to the standard 1064 error in MariaDB. " (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ':00:00,29.323330729166656)' at line 1")"

Here is the SQL statement:

for row in arg:
        print(row)
        cursor.execute("INSERT INTO flirtest (Date, Time, Temperature) VALUES (%s,%s,%s)" % row)
db.commit()

I think this error is due to the quotes around the first two variables. But I am honestly not sure. How should these statements be properly formatted in python? Should quotes be included or not?

Do you need a trailing comma in that array?

Rick James
– Rick James

2019-08-05 23:32:42 +00:00
Commented Aug 5, 2019 at 23:32 — Rick James
– Rick James, Commented Aug 5, 2019 at 23:32

OnPoint · Accepted Answer · 2019-08-05 20:35:31Z

1

1. in your case, arg is a Tuple, so it goes thorugh 3 times: in the first round, row is '2019-07-21', in the secound round row is '07:00:00', you get it.

Just put "[ ]" around it to set it to a list, then there is only 1 round with everything :)

2. Yours should then work too, but try this:

arg=[('2019-07-21', '07:00:00', 29.323330729166656)]

for row in arg:
        print(row)
        cursor.execute("INSERT INTO flirtest (Date, Time, Temperature) VALUES (%s,%s,%s)", row)
db.commit()

But im not 100% sure, because I use mysql :)

answered Aug 5, 2019 at 20:35

OnPoint

1391 silver badge6 bronze badges

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

1 Comment

Guayule Over a year ago

Thank you! The extra brackets was the fix!

Rick James · Accepted Answer · 2019-08-05 23:35:22Z

0

I don't know python, but I think this will be closer:

arg=('2019-07-21', '07:00:00', 29.323330729166656,)
cursor.execute("INSERT INTO flirtest (Date, Time, Temperature) VALUES (%s,%s,%s)", %row)
db.commit()

answered Aug 5, 2019 at 23:35

Rick James

144k15 gold badges144 silver badges255 bronze badges

Collectives™ on Stack Overflow

Syntax formatting for SQL Insert into statements in Python 3

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related