Skip to Content
All memories

MySQL with Python

 — #python#mysql

Do a dynamic query to a MySQL database by a python script.

You have to create a database and a table.

This is the python script, below this you can see the explanation of the script.

#!/usr/bin/env python

import MySQLdb
import time
import datetime
import random

conn = MySQLdb.connect(host= "localhost",user= "***",passwd="***",db="***")

c=conn.cursor()

def dynamic_data_entry():

     unix = int(time.time())
     date = str(datetime.datetime.fromtimestamp(unix).strftime('%Y-%m-%d %H:%M:%S'))
     value = random.randrange(0,10)

     c.execute("INSERT INTO *** (***, ***) VALUES (%s, %s)",(date, value))

     conn.commit()

for i in range(10):
     dynamic_data_entry()
     time.sleep(1)

c.close
conn.close()

Explanation of the code

This is the connection to the database.

conn = MySQLdb.connect(host= "localhost",user= "***",passwd="***",db="***")
  • User: the user to connect to the database (you can use the root user)
  • Passwd: the password of the user that you used
  • Db: the database that you want to use (ex. “readings”; put only the name without the extension “.db”)

This is the query to the database.

c.execute("INSERT INTO *** (***, ***) VALUES (%s, %s)",(date, value))
  • 1° ***: the table’s name
  • _ and 3° _: the columns’ name

The script print into the MySQL database the date and a random number for 10 times; you can change the numer of times by changing the (**) number in the script.

for i in range(10):

Now open the database if you like to see how it looks now.