python连接mysql简单操作的例子_Python操作MySQL案例

#!/usr/bin/env python#coding:utf-8#name:money.py

importsysimportMySQLdbclassTransferMoney(object):def __init__(self,conn):

self.conn=conn#用于检查是否存在转账用户或者被转账用户

defcheck_user_exist(self, userid):

cursor=self.conn.cursor()try:

sql= "select * from account where userid = %s" %userid

cursor.execute(sql)print "\033[;32m验证用户是否存在： \033[0m" +sqlexceptException,e:raise Exception('执行sql错误：%s' %e)else:

rs=cursor.fetchall()if len(rs) != 1:raise Exception ("账号%s不存在" %userid)finally:

cursor.close()#用于检查是用户是否有足够的钱转给别人

defhas_enough_money(self,source_userid,money):

cursor=self.conn.cursor()try:

sql= "select * from account where userid = %s and money > %s" %(source_userid,money)

cursor.execute(sql)print "\033[;32m检查是否有足够的钱: \033[0m" +sqlexceptException,e:raise Exception('执行sql错误：%s' %e)else:

rs=cursor.fetchall()if len(rs) != 1:raise Exception ("账号%s余额不足" %source_userid)finally:

cursor.close()#用于减去转掉的部份金额

defreduce_money(self,source_userid,money):

cursor=self.conn.cursor()try:

sql= "update account set money = money - %s where userid=%s" %(money,source_userid)

cursor.execute(sql)print "\033[;32m从源账户%s里扣掉对应的金额: \033[0m" % (source_userid) +sqlexceptException,e:raise Exception('执行sql错误：%s' %e)else:

rs=cursor.rowcountif rs!=1:raise Exception("账号%s减款失败" %source_userid)finally:

cursor.close()#用于把别人转过来的钱加到目标用户的金额上

defadd_money(self,target_userid,money):

cursor=self.conn.cursor()try:

sql="update account set money = money + %s where userid=%s" %(money,target_userid)

cursor.execute(sql)print '\033[;32m目标账户%s加上转过来的金额：\033[0m' % (target_userid) +sqlexceptException,e:raise Exception('执行sql错误：%s' %e)else:

rs=cursor.rowcountif rs!=1:raise Exception("账号%s加钱失败" %target_userid)finally:

cursor.close()#用于转账后的查询账号的金额

defcheck_money(self,source_userid):

cursor=self.conn.cursor()try:

sql="select * from account where userid=%s" %(source_userid)

cursor.execute(sql)exceptException,e:raise Exception('执行sql错误：%s' %e)else:

rs=cursor.fetchall()for row inrs:print "userid=%d, 现在的金额=%d" %rowfinally:

cursor.close()#主函数，调用以上的函数形成一个事务

deftransfer(self, source_userid, target_userid, money):try:

self.check_user_exist(source_userid)

self.check_user_exist(target_userid)

self.has_enough_money(source_userid,money)

self.reduce_money(source_userid,money)

self.add_money(target_userid,money)

self.conn.commit()

self.check_money(source_userid)

self.check_money(target_userid)exceptException as e:

self.conn.rollback()raiseeif __name__ == '__main__':

source_userid= sys.argv[1]

target_userid= sys.argv[2]

money= sys.argv[3]

conn= MySQLdb.connect(host='127.0.0.1',user='root',passwd='123456',port=3306,db='python')

tr_money=TransferMoney(conn)try:

tr_money.transfer(source_userid,target_userid,money)exceptException as e:print "\033[;31m出现问题：\033[0m" +str(e)finally:

conn.close()