python操作mysql实战.zip_python操作mysql实例

#coding=utf-8

importMySQLdb#打开数据库连接

db = MySQLdb.connect(host='localhost',user='root',passwd='',db ='company')#使用cursor()方法获取操作游标

cursor =db.cursor()#使用execute方法执行SQL语句

cursor.execute("SELECT VERSION()")#使用 fetchone() 方法获取一条数据库

data =cursor.fetchone()print "Database version : %s" %data#这个方法用来打印整张表的信息

defview_table(tablename):

cursor.execute("SELECT * FROM %s" %(tablename))

results=cursor.fetchall()for row inresults:

i=0while i

i+=1

print '\n'

#创建数据表SQL语句

cursor.execute("DROP TABLE IF EXISTS employee")

table= """CREATE TABLE employee (

first_name char(20) NOT NULL,

last_name char(20),

age int,

sex char(1),

income float

)"""cursor.execute(table)

cursor.execute("DESCRIBE employee")#print cursor.fetchall()#返回的是一个元组

for tup incursor.fetchall():printtup

sql_1= '''INSERT INTO employee (FIRST_NAME, LAST_NAME, AGE, SEX, INCOME)

VALUES ('Micheal', 'Scofield', 21, 'M', 2000)'''sql_2= '''INSERT INTO employee (FIRST_NAME, LAST_NAME, AGE, SEX, INCOME)

VALUES ('John', 'Abruzzi', 31, 'F', 3500)'''sql_3= '''INSERT INTO employee (FIRST_NAME, LAST_NAME, AGE, SEX, INCOME)

VALUES ('Alex', 'Mahone', 35, 'M', 2800)'''sql_4= '''INSERT INTO employee (FIRST_NAME, LAST_NAME, AGE, SEX, INCOME)

VALUES ('Paul', 'Keleman', 28, 'M', 3700)'''

try:

cursor.execute(sql_1)

cursor.execute(sql_2)

cursor.execute(sql_3)

cursor.execute(sql_4)#提交到数据库执行

db.commit()except:#如果上面的语句执行出错就回滚

db.rollback()

view_table('employee')#cursor.execute("SELECT * FROM employee")#results = cursor.fetchall()#for row in results:#fname = row[0]#lname = row[1]#age = row[2]#sex = row[3]#income = row[4]#print "fname=%s,lname=%s,age=%d,sex=%s,income=%d" % \#(fname,lname,age,sex,income)#print row[0],row[1],row[2],row[3],row[4]

sql_5= """UPDATE employee SET age = age+1 WHERE sex = '%c'""" % ('M')

cursor.execute(sql_5)

db.commit()

view_table('employee')#关闭数据库连接

db.close()