mysqldb

#reading

import MySQLdb

db= MySQLdb.connect(host="localhost", user="python-test", passwd="python",db="python-test")#连接

cursor = db.cursor()
stmt = "SELECT * from books"
cursor.execute(stmt)#执行动作

rows = cursor.fetchall ()
for row in rows:
    print "Row: "
    for col in row :
        print "Column: %s" % (col)
    print "End of Row"
print "Number of rows returned: %d" % cursor.rowcount

cursor.close()

db.close() #关闭

#updating

import MySQLdb
import MySQLdb.cursors

def get_column_name( data, prompt, names ) :
    value=-1
    while value == -1:
        idx = 1
        for col in data :
            print str(idx) + ": " + col
            names.append( col )
            idx = idx + 1
        value = int( raw_input(prompt) )
        if value < 1 or value >= idx :
            value = -1
    return value


conn = MySQLdb.Connect(host='localhost',user='root',passwd='',db='dragon') #连接
cursor = conn.cursor(cursorclass=MySQLdb.cursors.DictCursor)

cursor.execute("SELECT * FROM books")
data = cursor.fetchone()

names = []
old_value = get_column_name( data, "Which column do you want to change records for? ", names )
names = []
new_value = get_column_name( data, "Which column do you want to change records to? ", names )

old_val = raw_input("What value do you want to change for " + names[old_value-1] + ": ")
new_val = raw_input("What value do you want to change to for " + names[new_value-1] + ": ")

stmt = "UPDATE books SET " + names[new_value-1] + " = '"+ new_val + "' WHERE " + names[old_value-1] + " = '" + old_val + "'"
print stmt
cursor.execute(stmt)
print "Rows affected: " + str(cursor.rowcount)


cursor.close()
conn.commit()
conn.close()

#writing

import MySQLdb

db= MySQLdb.connect(host="localhost", user="python-test", passwd="python",
db="python-test")
try:
    title = raw_input("Please enter a book title: ")
    if title == "" :
        exit
    author = raw_input("Please enter the author's name: ")
    pubdate = int( raw_input("Enter the publication year: ") )
except:
    print "Invalid value"
    exit

print "Title: [" + title + "]"
print "Author: ["+ author + "]"
print "Publication Date: " + str(pubdate)

cursor = db.cursor()

stmt = "INSERT INTO Books (BookName, BookAuthor, PublicationDate) VALUES ('"
stmt = stmt + title
stmt = stmt + "', '"
stmt = stmt + author
stmt = stmt + "', "
stmt = stmt + str(pubdate)
stmt = stmt + ")"
cursor.execute(stmt)
print "Record added!"

cursor.close ()
db.commit ()