Python操作数据库

# -*-coding:utf-8-*-
import time
import MySQLdb
#数据库连接 
conn = MySQLdb.connect(host="127.0.0.1", user="root", passwd="123456", db="db_user_re", charset="utf8")
#定义游标
cursor = conn.cursor()

#删除表
sql ="drop table if exists user"; 
cursor.execute(sql)

#创建    
sql = "create table if not exists user(numid int primary key auto_increment,\
                                        name varchar(20),\
                                         pwd varchar(10))"    
cursor.execute(sql)

    
#增加数据
sql = "insert into user values(%s, %s, %s)"
param = (1, "panli", "521")
n = cursor.execute(sql, param)
print 'insert', n

#增加数据
sql = "insert into user (numid, name, pwd) values(%s, %s, %s)"
param = (2, "tiantian", "520")
n = cursor.execute(sql, param)
print 'insert',n

#增加数据
sql = "insert into user (name, pwd) values(%s, %s)"
param = ("tiantian", "520")
n = cursor.execute(sql, param)
print 'insert',n

#增加多条数据
sql = "insert into user values(%s, %s, %s)"
param = ((4, "panli", "521"),(5, "panli", "521"),(6, "panli", "521"))
n = cursor.executemany(sql, param)
print 'insert many', n

#增加多条数据
sql = "insert into user(numid, name, pwd) values(%s, %s, %s)"
param = ((7, "tiantian", "521"),(8, "tiantian", "521"),(9, "tiantian", "521"))
n = cursor.executemany(sql, param)
print 'insert many', n

#增加多条数据
sql = "insert into user(name, pwd) values(%s, %s)"
param = (("tiantian", "521"),("tiantian", "521"),("tiantian", "521"))
n = cursor.executemany(sql, param)
print 'insert many', n

#更新        
sql = "update user set name=%s where name='panli'"       
param = ("tian",)        
n = cursor.execute(sql,param)        
print 'update',n 

#查询        
n = cursor.execute("select * from user")        
print cursor.fetchall()#输出是一个元组

#查询        
n = cursor.execute("select * from user")        
for row in cursor.fetchall():#遍历元组    
    #print row
    i = 0    
    for r in row:        
        print r,
        i+=1
        if(i%3 == 0):
            print "\n"

#删除        
sql = "delete from user where name=%s"     
param =("xxx",)        
n = cursor.execute(sql,param)#属性值一样的会全部删除
print 'delete',n  

#提交
conn.commit()
#关闭
conn.close()