Simple MySQLdb Example Posted on May 03, 2009 by Dave Fowler

MySQLdb is a python interface to MySQL. I wrote the following simple script for some database tests and thought it might be handy to others looking for tutorials or examples.

It simply connects to MySQL, drops and creates a database called 'testdb', makes a table called 'waves' with columns 'sin', 'cos', 'tan', and 'date' and fills the table with a thousand data points of the trig functions performed on the date in 5 minute intervals leading up to the current date.

The code and comments I think are fairly straight forward, so I will just paste and link to the text version.

DATABASE_HOST = "localhost"
DATABASE_USER = "root"
DATABASE_NAME = "testdb"
DATABASE_PASSWD = "YOUR PASSWORD HERE"
DATABASE_PORT = 3306

import MySQLdb

# Connect to the Database
db=MySQLdb.connect(host=DATABASE_HOST,user=DATABASE_USER,
 passwd=DATABASE_PASSWD, port=int(DATABASE_PORT))

# Make the database cursor
cursor = db.cursor()

# Drop and create the database
cursor.execute("drop database %s; create database %s;" % (DATABASE_NAME, DATABASE_NAME))

# Re connect to database using db=DATABASE_NAME
db=MySQLdb.connect(host=DATABASE_HOST,user=DATABASE_USER,
 passwd=DATABASE_PASSWD, db=DATABASE_NAME, port=int(DATABASE_PORT))
cursor = db.cursor()

# Create the table for the wave data
from math import sin, cos, tan
cursor.execute("""CREATE TABLE waves (
id INT NOT NULL AUTO_INCREMENT,
PRIMARY KEY(id),
sin FLOAT,
cos FLOAT,
tan FLOAT,
date INT);
""")

# Insert the sine wave data
from datetime import datetime

def STAMP( dt ):
  """ turns a python datetime object into a unix time stamp (seconds) """
  import time
  return int(time.mktime( dt.timetuple() ))

now = STAMP( datetime.now() )
five_mins = 60*5

sql = "INSERT INTO waves (sin, cos, tan, date) VALUES (%s, %s, %s, %s);"

# Insert the data into the table
for i in range(1000):
  s = now - i*five_mins
  cursor.execute(sql % ( sin(s), cos(s), tan(s), s ))

I really need to get some syntax highlighting... More documentation on MySQLdb can be found here. If you have any questions leave them in the comments.