# -*-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()
Python操作数据库
最新推荐文章于 2024-09-10 15:45:22 发布