[DB-SIG] Re: A little example for MySql

[Andy Dustman]

There are actually a lot of things about that example I don't care for.
One is a gratituous use of try ... except clauses (i.e. the except does
what the Python interpreter does when there is no except). Another is the
use of * in SQL and default columns on INSERT. And then there are several
single-row INSERTs into the same table.

So here's a modified version that uses a few more features of Python,
MySQL, and MySQLdb, and, IMHO, is a lot more comprehensible.

#!/usr/bin/python

import MySQLdb

mydb = MySQLdb.Connect(db='test')
cursor = mydb.cursor()
	
# create a new table, dropping the old one if it exists

stmt = "DROP TABLE IF EXISTS COLORS"
cursor.execute(stmt)

stmt = """CREATE TABLE COLORS (
	COLOR varchar(32) DEFAULT '' NOT NULL,
	NCOLOR INT UNSIGNED NOT NULL AUTO_INCREMENT,
	PRIMARY KEY (NCOLOR)
)"""
cursor.execute(stmt)

# multi-row insert
colors = ( ('red',), ('blue',), ('yellow',), )
stmt = "INSERT INTO COLORS (COLOR) VALUES (%s)"
cursor.executemany(stmt, colors)

# grand finale test

stmt = "select NCOLOR, COLOR from COLORS"
cursor.execute(stmt)
resultSet = cursor.fetchall()

for i, color in resultSet:
	print "Color number",i,"is", color

mydb.close()

-- 
andy dustman  |  programmer  |  comstar.net is part of the Globix network
telephone: 770.485.6025 / 706.549.7689 | icq: 32922760 | pgp: 0xc72f3f1d
"Therefore, sweet knights, if you may doubt your strength or courage, 
come no further, for death awaits you all, with nasty, big, pointy teeth!"