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.

{% highlight python %} 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 )) {% endhighlight %}

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.

Related Articles