• 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 rs
    10 print rs[1]
    11 
    12 rs=cursor.fetchmany(3)
    13 print rs
    14 
    15 rs=cursor.fetchall()
    16 print rs
    17 
    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" %row
    31 
    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.rowcount
    11     cursor.execute(sql_update)
    12     print cursor.rowcount
    13     cursor.execute(sql_delete)
    14     print cursor.rowcount
    15 except Exception as e:
    16     print e
    17     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 e
    16 
    17     def check_acct_available(self,accid):
    18         cursor = self.conn.cursor()
    19         try:
    20             sql = "select * from account where accid=%s" % accid
    21             cursor.execute(sql)
    22             print "check_acct_available:"+sql
    23             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:" + sql
    35             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:" + sql
    46             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:" + sql
    57             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
    
    
    
    本性的苏醒,往往在遭遇真实之后。
  • 相关阅读:
    学生分数排序
    union 的一个简单例子,搜狗笔试题
    枚举的初始化赋值
    unsigned 赋值负数输出情况 & printf输出格式
    sizeof中的表达式不执行
    三级指针:百度试题
    生成器案例,#采集日志
    简述装饰器
    装饰器(转载)
    python写监控并发警报邮件
  • 原文地址:https://www.cnblogs.com/chance88/p/6543783.html
Copyright © 2020-2023  润新知