python实现的MySQL增删改查操作实例小结
本文实例总结了python实现的MySQL增删改查操作。分享给大家供大家参考,具体如下:
代码片段一
连接并执行sql
#encoding:UTF-8 importMySQLdb conn=MySQLdb.Connect( host='127.0.0.1', port=3306, user='root', passwd='123456', db='imooc', charset='utf8' ) cursor=conn.cursor() printconn printcursor sql="select*fromuser" cursor.execute(sql)#执行
取数据
printcursor.rowcount #取数据 #fetchone()获取一条数据 #fetchany(3)获取多条 #fetchall()#获取客户缓冲区的所有数据 #rs=cursor.fetchone() #printrs # #rs=cursor.fetchmany(2) #printrs # #rs=cursor.fetchall() #printrs #rs=cursor.fetchall() #forrowinrs: #print"userid=%s,username=%s"%row
更新数据库
#sql_insert="insertintouser(userid,username)values(10,'name10')" #sql_update="updateusersetusername='name91'whereuserid=9" #sql_delete="deletefromuserwhereuserid<3" #cursor.execute(sql_insert) #cursor.execute(sql_update) #cursor.execute(sql_delete) ##执行完后提交 #conn.commit() ##发生异常时回滚 #try: #sql_insert="insertintouser(userid,username)values(10,'name10')" #sql_update="updateusersetusername='name91'whereuserid=9" #sql_delete="deletefromuserwhereuserid<3" #cursor.execute(sql_insert) #cursor.execute(sql_update) #cursor.execute(sql_delete) #conn.commit() #exceptExceptionase: #printe #conn.rollback() cursor.close() conn.close()
代码片段2银行实例
#coding:UTF-8 importsys importMySQLdb classTransferMoney(object): def__init__(self,conn): self.conn=conn deftranfer(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() exceptExceptionase: self.conn.rollback() raisee defcheck_acct_available(self,acctid): cursor=self.conn.cursor() try: sql="select*fromaccountwhereacctid=%s"%acctid cursor.execute(sql) rs=cursor.fetchall() iflen(rs)!=1: raiseException("账号%s不存在"%acctid) finally: cursor.close() defhas_enough_money(self,acctid,money): cursor=self.conn.cursor() try: sql="select*fromaccountwhereacctid=%sandmoney>%s"%(acctid,money) cursor.execute(sql) print"has_enough_money:"+sql rs=cursor.fetchall() iflen(rs)!=1: raiseException("账号%s没有足够的钱"%acctid) finally: cursor.close() defreduce_money(self,acctid,money): cursor=self.conn.cursor() try: sql="updateaccountsetmoney=money-%swhereacctid=%s"%(money,acctid) cursor.execute(sql) print"reduce_money:"+sql ifcursor.rowcount!=1: raiseException("账号%s减款失败"%acctid) finally: cursor.close() defadd_money(self,acctid,money): cursor=self.conn.cursor() try: sql="updateaccountsetmoney=money+%swhereacctid=%s"%(money,acctid) cursor.execute(sql) print"reduce_money:"+sql ifcursor.rowcount!=1: raiseException("账号%s加款失败"%acctid) finally: cursor.close() if__name__=="__main__": source_acctid=sys.argv[1] target_acctid=sys.argv[2] money=sys.argv[3] conn=MySQLdb.Connect( host='127.0.0.1', port=3306, user='root', passwd='123456', db='imooc', charset='utf8' ) tr_money=TransferMoney(conn) try: tr_money.tranfer(source_acctid,target_acctid,money) exceptExceptionase: print"出现问题了"+str(e) finally: conn.close()
更多关于Python相关内容感兴趣的读者可查看本站专题:《Python+MySQL数据库程序设计入门教程》、《Python常见数据库操作技巧汇总》、《Python数学运算技巧总结》、《Python数据结构与算法教程》、《Python函数使用技巧总结》、《Python字符串操作技巧汇总》、《Python入门与进阶经典教程》及《Python文件与目录操作技巧汇总》
希望本文所述对大家Python程序设计有所帮助。