MySQLdbの使い方メモ

#!/usr/bin/env python
# coding: utf-8
 
# import module
import MySQLdb
 
# Get MySQL connection
conn = MySQLdb.connect(
    host = 'localhost',
    port = 3306,
    db = 'database',
    user = 'user',
    passwd = 'password',
    )
 
# Get cursor of DictCursor
# default cursor is MySQLdb.cursors.Cursor.
# default cursor is return tuple.
cur = conn.cursor(MySQLdb.cursors.DictCursor)
 
# Set names
cur.execute("SET NAMES utf8")
 
# Execute SQL(SELECT)
sql = "SELECT * FROM tbl1"
cur.execute(sql)
res1 = cur.fetchall()
res2 = cur.fetchone()
 
# Print record
for row in res1:
    print(row["colum"])
 
print(res2["colum"])
 
# Execute SQL(INSERT and commit)
sql = "INSERT INTO tbl1 (col1, col2, col3) VALUES ('val1', 'val2', 'val3')"
cur.execute(sql)
conn.commit()
 
# Execute SQL(UPDATE and rollback)
sql = "UPDATE tbl1 SET col1='val4' WHERE col1='val1'"
cur.execute(sql)
conn.rollback()
 
# Close connection
cur.close()
conn.close()