python操作mysql数据库练习

python操作mysql数据库练习

　　本次练习是在windows下，mysql版本为5.7，python版本为2.7.5,集成环境为pycharm。

　　创建表时，enign在innodb下支持事务，其他可能不支持。

create table account(    accid int(11) default null ,    money int(11) default null )engine=innodb;

 

一、测试环境

 1 #coding=utf-8 2 import MySQLdb 3  4 conn=MySQLdb.connect(host='127.0.0.1',port=3306,user='root',passwd='771994001o',db='immc',charset='utf8') 5 cursor=conn.cursor() 6  7 print conn 8 print cursor 9 10 cursor.close()11 conn.close()

二、相关函数

#支持execute()执行一个数据库查询命令,执行sql,将结果从数据库放回到客户端
#fetchon()取结果集的下一行,移动rownumbr,返回数据
# fetchmany()取结果集的下几行
# fetchall()取结果集中的剩余所有行
# rowcount()最近一次execute返回数据的行数或影响行数
# close()关闭游标对象

三、使用select查询数据

 1 conn=MySQLdb.connect(host='127.0.0.1',port=3306,user='root',passwd='771994001o',db='immc',charset='utf8') 2 cursor=conn.cursor() 3  4 sql="select * from manager" 5 cursor.execute(sql) 6 print cursor.rowcount 7  8 rs=cursor.fetchone() 9 print rs10 print rs[1]11 12 rs=cursor.fetchmany(3)13 print rs14 15 rs=cursor.fetchall()16 print rs17 18 cursor.close()19 conn.close()20 21 22 conn=MySQLdb.connect(host='127.0.0.1',port=3306,user='root',passwd='771994001o',db='immc',charset='utf8')23 cursor=conn.cursor()24 25 sql="select * from manager"26 cursor.execute(sql)27 28 rs=cursor.fetchall()29 for row in rs:30     print "UserId:%s,UserName:%s" %row31 32 cursor.close()33 conn.close()

 

四、更新数据库

 1 conn=MySQLdb.connect(host='127.0.0.1',port=3306,user='root',passwd='771994001o',db='immc',charset='utf8') 2 cursor=conn.cursor() 3  4 sql_insert="insert into manager values(10010,'李隆基')" 5 sql_update="update manager set name='李开' where id=10023" 6 sql_delete="delete from manager where d<1003" 7  8 try: 9     cursor.execute(sql_insert)10     print cursor.rowcount11     cursor.execute(sql_update)12     print cursor.rowcount13     cursor.execute(sql_delete)14     print cursor.rowcount15 except Exception as e:16     print e17     conn.rollback()18 19 conn.commit()20 21 cursor.close()22 conn.close()

 

五、实例演练

开始事务-检测A和B账户是否可用-检测账户A是否有100块-账户A减去100快，账户B加上100块-提交事务

 1 import sys 2 class TransfrMoney(object): 3     def __init__(self,conn): 4         self.conn=conn 5     def transfer(self,source_accid,target_accid,money): 6         try: 7             self.check_acct_available(source_accid) 8             self.check_acct_available(target_accid) 9             self.enough_moner(source_accid,money)10             self.reduce_money(source_accid,money)11             self.add_money(target_accid,money)12             self.conn.commit()13         except Exception as e:14             self.conn.rollback()15             raise e16 17     def check_acct_available(self,accid):18         cursor = self.conn.cursor()19         try:20             sql = "select * from account where accid=%s" % accid21             cursor.execute(sql)22             print "check_acct_available:"+sql23             rs = cursor.fetchall()24             if len(rs) != 1:25                 raise Exception("账号%s不存在" % accid)26         finally:27             cursor.close()28 29     def enough_moner(self,accid, money):30         cursor = self.conn.cursor()31         try:32             sql = "select * from account where accid=%s and money >=%s" %(accid,money)33             cursor.execute(sql)34             print "enough_moner:" + sql35             rs = cursor.fetchall()36             if len(rs) != 1:37                 raise Exception("账号%s没有足够的钱" % accid)38         finally:39             cursor.close()40     def reduce_money(self,accid,money):41         cursor = self.conn.cursor()42         try:43             sql = "update account set money =money-%s where accid=%s" % (money, accid)44             cursor.execute(sql)45             print "reduce_money:" + sql46             rs = cursor.fetchall()47             if cursor.rowcount != 1:48                 raise Exception("账号%s减款失败" % accid)49         finally:50             cursor.close()51     def add_money(self,accid,money):52         cursor = self.conn.cursor()53         try:54             sql = "update account set money =money+%s where accid=%s" % (money, accid)55             cursor.execute(sql)56             print "add_money:" + sql57             rs = cursor.fetchall()58             if cursor.rowcount != 1:59                 raise Exception("账号%s加款失败" % accid)60         finally:61             cursor.close()62 63 if __name__=="__main__":64     source_accid=sys.argv[1]65     target_accid=sys.argv[2]66     money=sys.argv[3]67 68 conn=MySQLdb.connect(host='127.0.0.1',port=3306,user='root',passwd='771994001o',db='immc',charset='utf8')69 tr_money=TransfrMoney(conn)70 try:71     tr_money.transfer(source_accid,target_accid,money)72 except Exception as e:73     print "出现问题"+str(e)74 finally:75     conn.close()

　　点击run——Edit Configuratopn...---Scrip parameters选项输入测试数据。

2017-03-13 16:58:35