//test.py
#!/usr/bin/python
# -*- coding: UTF-8 -*-
import MySQLdb
# 打开数据库连接
db = MySQLdb.connect("localhost","root","11111111","TESTDB" )
# 使用cursor()方法获取操作游标
cursor = db.cursor()
# 使用execute方法执行SQL语句
cursor.execute("SELECT VERSION()")
# 使用 fetchone() 方法获取一条数据
data = cursor.fetchone()
print "Database version : %s " % data
cursor.execute('DROP TABLE IF EXISTS EMPLOYEE')
sql = """CREATE TABLE EMPLOYEE (
FIRST_NAME CHAR(20) NOT NULL,
LAST_NAME CHAR(20),
AGE INT,
SEX CHAR(1),
INCOME FLOAT )"""
cursor.execute(sql)
sql1 = """INSERT INTO EMPLOYEE(FIRST_NAME,
LAST_NAME, AGE, SEX, INCOME)
VALUES ('Mac', 'Mohan', 20, 'M', 2000)"""
sql2 = """INSERT INTO EMPLOYEE(FIRST_NAME,
LAST_NAME, AGE, SEX, INCOME)
VALUES ('Jack', 'dansor', 25, 'F', 38000)"""
try:
cursor.execute(sql1)
cursor.execute(sql2)
db.commit()
except:
print "Rollback in case there is any error"
db.rollback()
sql3 = "SELECT * FROM EMPLOYEE
WHERE INCOME>'%d'" % (1000)
try:
cursor.execute(sql3)
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)
except:
print 'Error: unable to fecth data'
sql4 = "UPDATE EMPLOYEE SET AGE=AGE+1 WHERE SEX='M'"
try:
cursor.execute(sql4)
db.commit()
except Exception, tup:
print 'Update failure: ', tup
db.rollback()
sql5 = "DELETE FROM EMPLOYEE WHERE SEX='F'"
try:
cursor.execute(sql5)
db.commit()
except Exception, tup:
print "Delete failure: ", tup
db.rollback()
try:
cursor.execute(sql3)
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)
except:
print 'Error: unable to fecth data'
# 关闭数据库连接
db.close()
//result
# python test.py
Database version : 5.5.58-0ubuntu0.14.04.1
fname:Mac,lname=Mohan,age=20,sex=M,income=2000
fname:Jack,lname=dansor,age=25,sex=F,income=38000
fname:Mac,lname=Mohan,age=21,sex=M,income=2000
Finally:
mysql数据库在python这样的脚本里非常容易使用，值得看看！