Python and MySQLdb quick note

Quick note for basic stuff in python:

#import stuff
import MySQLdb

# connect
try:
    db = MySQLdb.connect(host="localhost",
                         user="user",
                         passwd="password",
                         db="mydb")

except Exception, e:
    sys.exit("Problem connecting to DB: %s" % e)

# Cursor
cur = db.cursor()

# Defer warnings if needed, drop table will warn if table is not existing
tbl1 = "mytable"
cur._defer_warnings = True
cur.execute('DROP TABLE IF EXISTS %s;' % tbl1)
cur_defer_warnings = False

# Create table
cur.execute('CREATE TABLE %s (\
data1\
data2\
); % tbl1

# Insert data
## Could use tuple also (x,)
values = []
values.append = "data1"
values.append = "data2"

# SQlite uses ?, MySQLdb %s, join '%s', you know...
val_str = ', '.join('?' * len(values))
val_str = val_str.replace('?', '%s')
query_str = "INSERT INTO %s VALUES (%s);" % (tbl1, val_str)
# Me not like unicode?
# values = [x.encode('ascii', 'xmlcharrefreplace') for x in values]

# MySQLdb uses tuple for values
values = tuple(values)
try:
    cur.execute(query_str, values)
except:
    # Da debug ;)
    print("----- Query -----")
    print(cur._last_executed)

print("Inserted")

Links:

PEP 249

MySQLdb User’s Guide

 

Leave a Reply

Your email address will not be published. Required fields are marked *