忘れないようにメモっとく

機械学習とかプログラミングとか。

MySQL wrapper in python ~ MySQLdb ~

Connection to MySQL

MySQLdb provides python db api interface.
It has connect() method, which parameters are host, user, passwd and so on.
Sample code I tried is as follows.

import MySQLdb

connect = MySQLdb.connect(host='localhost',db='my_db',user='root',passwd='')                                                                                                                    
connect.cursorclass = MySQLdb.cursors.DictCursor
cursor = connect.cursor()

Then, I could get the cursor. DictCursor enables to fetch data as dictionary base.
I can see database information from the cursor.

print cursor.execute('show databases')
print cursor.execute('show tables')
print cursor.execute('select * from my_table')

Cursor method

Cursor has execute() which can tell MySQL sql script. Cursor have to be in "try-except" whenever call "insert" command.
Python's exception seems easy and readable.

try:
    cursor.execute("INSERT INTO my_table VALUES (%s,%s)",(188,'char'))
    connect.commit()
except:
    connect.rollback()

I could extract all information as follows.

cursor.execute('select * from my_table')
rows = cursor.fetchall()

for row in rows:
    print row 
    print int(row["id"])
    a += row["id"]

cursor.close()
connect.close()

It's boring to write sql script everytime. MySQLdb is a little thin wrapper, so I'd like to try another or mapper SQLAlchemy.