Simple MySQLdb Example

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.

Advertisements
2 comments
  1. happy_camper said:

    Thank you so much!!! I have been trying to work around a bug for hours, and something in your code inspired me. i had been trying to execute the folliwing:

    goodlist = “(1,3,6,8)”
    cursor.execute(“SELECT stuff FROM dbase WHERE id IN %s”, (goodlist,))

    no variation on this syntax would work (i tried EVERYTHING). there was some sort of string formatting error that i still don’t understand. but …i didn’t realise i could build an input string beforehand, so i just tried:

    input = “SELECT stuff FROM dbase WHERE id in ” + goodlist
    cursor.execute(input)

    and it worked like a charm.

    thanks again

    • I think your error was here.

      ”SELECT stuff FROM dbase WHERE id IN %s”, (goodlist,)

      When doing string replace like that you can’t use the comma, you have to use %

      Your new version should work, but here is what you were probably trying to do.

      ”SELECT stuff FROM dbase WHERE id IN %s” % (goodlist,)

      The execute command was assuming (goodlist, ) was a second paramater to its function, not part of the string.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s