Python 数据库连接

#!/usr/bin/env python
#-*-coding:utf-8-*-
#异常处理，with的使用，
class Mycontex(object):
    def __init__(self,name):
        self.name=name

    def __enter__(self):
        print("__enter__")
        return self
    def do_self(self):
        print('do_self')
    def __exit__(self,exc_type,exc_value,traceback):
        print('__exit__')
        print("Error: ",exc_type," info:",exc_value)

if __name__=='__main__':
    with Mycontex('test context') as f:
        print(f.name)
        f.do_self()

#!/usr/bin/env python
#-*-coding:utf-8-*-
import MySQLdb
#连接数据库
conn=MySQLdb.Connect(
    host='127.0.0.1',
    port=3306,
    user='root',
    passwd='123456',
    db='imooc',
    charset='utf8'
    )
cursor=conn.cursor()

sql_insert="insert into user(userid,username) values(10,'name10')"
sql_update="update user set username='name91' where userid=9"
sql_delete="delete from user where userid<3"
#异常处理
try:
    cursor.execute(sql_insert)
    print(cursor.rowcount)
    cursor.execute(sql_update)
    print(cursor.rowcount)
    cursor.execute(sql_delete)
    print(cursor.rowcount)
    conn.commit()
except Exception as e:
    print(e)
    #回滚事物
    conn.rollback()

cursor.close()
conn.close()

#!/usr/bin/env python
#-*-coding:utf-8-*
#银行转账模拟
import sys
import MySQLdb


class TransferMoney(object):
    def __init__(self,conn):
        self.conn=conn
    #检查用户是否存在
    def check_acct_available(self,acctid):
        cursor=self.conn.cursor()
        try:
            sql="select * from account where acctid=%s" % acctid
            cursor.execute(sql)
            print("check_acct_available:"+sql)
            rs=cursor.fetchall()
            if len(rs)!=1:
                raise Exception("账号%s不存在" % acctid)
        finally:
            cursor.close()
            #检查用户是否有足够的钱
    def has_enough_money(self,acctid,money):

        cursor=self.conn.cursor()
        try:
            sql="select * from account where acctid=%s and money>=%s" % (acctid,money)
            cursor.execute(sql)
            print("has_enough_money:"+sql)
            rs=cursor.fetchall()
            if len(rs)!=1:
                raise Exception("账号%s没有足够的钱" % acctid)
        finally:
            cursor.close()
    #用户减少的钱
    def reduce_money(self,acctid,money):
            
        cursor=self.conn.cursor()
        try:
            sql="update account set money=money-%s where acctid=%s" % (money,acctid)

            cursor.execute(sql)
            print("reduce_money:"+sql)
        
            if cursor.rowcount!=1:
                raise Exception("账号%s减款失败" % acctid)
        finally:
            cursor.close()
    def add_money(self,acctid,money):

        cursor=self.conn.cursor()
        try:
            sql="update account set money=money+%s where acctid=%s" % (money,acctid)

            cursor.execute(sql)
            print("add_money:"+sql)
        
            if cursor.rowcount!=1:
                raise Exception("账号%s加款失败" % acctid)
        finally:
            cursor.close()


    def transfer(self,source_acctid,target_acctid,money):
        try:
            self.check_acct_available(source_acctid)
            self.check_acct_available(target_acctid)
            self.has_enough_money(source_acctid,money)
            self.reduce_money(source_acctid,money)
            self.add_money(target_acctid,money)
            self.conn.commit()
        except Exception as e:
            self.conn.rollback()
            raise e


if __name__=='__main__':
    #source_acctid=sys.argv[0]
    #target_acctid=sys.argv[1]
    #money=sys.argv[2]
    source_acctid=input('请输入用户')
    target_acctid=input('转入用户')
    money=input('转入金额')

    conn=MySQLdb.Connect(host='127.0.0.1',user='root',passwd='123456',port=3306,db='imooc')
    tr_money=TransferMoney(conn)
    try:
        tr_money.transfer(source_acctid,target_acctid,money)
    except Exception as e:
        print('出现问题：'+str(e))
    finally:
        conn.close()