#-*- encoding: utf-8 -*-

import os, sys, string
import MySQLdb

# 连接数据库　
try:
    conn = MySQLdb.connect(host='localhost',user='root',passwd='xxxx',db='test1')
except Exception, e:
    print e
    sys.exit()

# 获取cursor对象来进行操作

cursor = conn.cursor()# 查询时返回元组
#cursor = conn.cursor(cursorclass = MySQLdb.cursors.DictCursor)# 查询时返回字典

# 创建表
sql = "create table if not exists test1(name varchar(128) primary key, age int(4))"
cursor.execute(sql)
# 插入数据
sql = "insert into test1(name, age) values ('%s', %d)" % ("zhaowei", 23)
try:
    cursor.execute(sql)
except Exception, e:
    print e

sql = "insert into test1(name, age) values ('%s', %d)" % ("张三", 21)
try:
    cursor.execute(sql)
except Exception, e:
    print e
# 插入多条

sql = "insert into test1(name, age) values (%s, %s)" 
val = (("李四", 24), ("王五", 25), ("洪六", 26))
try:
    cursor.executemany(sql, val)
except Exception, e:
    print e

#查询出数据
sql = "select * from test1"
cursor.execute(sql)
alldata = cursor.fetchall()
# 如果有数据返回，就循环输出, alldata是有个二维的列表
if alldata:
    for rec in alldata:
        print rec[0], rec[1]

#更新    
sql = "update test1 set name=%s where age=23"   
param = ("赵薇")
try:
    cursor.execute(sql,param)    
except Exception, e:
    print e  
   
#删除    
sql = "delete from test1 where name=%s"   
param =("赵薇")  
try:
    cursor.execute(sql,param)    
except Exception, e:
    print e  

conn.commit()#提交，否则可能不会提交到db生效

cursor.close()

conn.close()

最后修改于 2013-12-22