How to insert values of variables using python 2.7 into mysql?

Question

I want to insert the value of variable (temp, hum) into my table sensors using mysql database (db name:project). I am using Ubuntu 14.04, and the connection is made between python and MySQL, but I can't put the value of the variable. So any help is welcome, and thanks.

This is my script Python:

from random import *
import socket
import sys
import mysql.connector
from datetime import datetime

temp = randrange(10, 31, 2)
hum = randrange(300, 701, 2)

print temp
print hum

conn=mysql.connector.connect(user='root',password='12345',host='localhost',database='projet');
mycursor=conn.cursor();
mycursor.execute("""INSERT INTO sensors VALUES('$datetime','$temp','$hum')""");
conn.commit()
mycursor.execute("SELECT * FROM sensors")

This is my table where you can find the variables temp and hum and not their values:

WARNING: Please be careful to properly escape those values. Inserting data directly is really dangerous. — tadman
– tadman, Commented Apr 3, 2016 at 12:01

Community · Accepted Answer · 2017-05-23 12:31:32Z

1

That's because you are not passing the variable values into the query. Pass them with the query into the execute() method:

mycursor.execute("""
    INSERT INTO 
        sensors 
    VALUES
        ('$datetime', %s, %s)""", (temp, hum))

where %s are placeholders for the query parameters. Note that the database driver would handle the parameter type conversion and escaping automatically.

As for the $datetime, if you want to have a current date/datetime in this column, look into using NOW() or CURDATE(), see:

MySQL: Curdate() vs Now()

edited May 23, 2017 at 12:31

CommunityBot

11 silver badge

answered Apr 3, 2016 at 12:18

alecxe

476k127 gold badges1.1k silver badges1.2k bronze badges

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

Comments

vozille · Accepted Answer · 2016-04-03 12:19:57Z

0

but i can't put the value of variable

Assuming data-time is a string and the rest are integers

Try

"INSERT INTO sensors VALUES('%s','%d','%d')", %(date, temp, num)

in the execute method

answered Apr 3, 2016 at 12:19

vozille

1151 silver badge10 bronze badges

1 Comment

alecxe Over a year ago

Are you sure the syntax is valid?

Collectives™ on Stack Overflow

How to insert values of variables using python 2.7 into mysql?

2 Answers 2

Comments

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Linked

Related