Python and MySQL (MySQLdb)

If you need to access MySQL with Python MySQLdb is your friend.

Very good User guide can be found here. I lost few hairs when i was first playing with this so i decided to create small tutorial of my own. Hopefully will save some hair from someone

This is very basic stuff

Installing MySQLdb

In ubuntu system you need to install packet called python-mysqldb. Sources itself can be found from here

apt-get install python-mysqldb

Connecting database

 import MySQLdb   
  try:    
    conn = MySQLdb.connect(host=address,user=username,passwd=password,db=database)   
  except:    
    # Connection failed  

Executing commands

Here we pass two variables into INSERT command, first you need to create a cursor, then execute against this cursor.

After execute, commit must be applied or changes wont happen in your database.

 cursor = conn.cursor()  
 cursor.execute("""INSERT INTO data (col1,col2) VALUES (%s, %s)""", (var1,var2,))  
 conn.commit()  

TIP: After execute, you can get primary ID from previous insert with:

 print str(cursor.insert_id())  

Select from database

  sql = "SELECT * FROM stuff"  
  # Query  
  conn.query(sql)  

At this point, your query is executed, now you need to store your results

  # Store results  
  result_store = conn.store_result()  

Note: By default mysql db will return you only 1 row from result set, so if you specify maxrows=0 in your fetch_row, it will return all rows from the results set.

  # Fetch row from store  
  results = result_store.fetch_row(maxrows=0)  

Oh Yes.. this was my biggest issue, i didn't realize that result tuple had one tuple inside of it, stupid me!

Basically at this point if you print results output is:

 >>> print results  
 (('row1col1', 'row1col2'), ('row2col1', 'row2col2'))  

You can simply loop trough results with for each or get individual row by giving and index, row1 is naturally index number of 0 and row2 is index number of 1

 >>> row1 = results[1]  
 >>> print row1  
 ('row2col1', 'row2col2')  

And same applies to column..

 >>> col1 = row1[1]  
 >>> print col1  
 row2col2  

Closing connection

 conn.close()