python_慕课\Python操作MySQL数据库\2-2 Python-游标对象cursor.py

-- 1-2 Python DB API介绍   https://www.imooc.com/video/9221




python db api : https://www.python.org/dev/peps/pep-0249/ 




-- 1-3 Python开发MySQL环境  https://www.imooc.com/video/9222 


 https://sourceforge.net/projects/mysql-python/#    : python访问mysql接口程序下载 
 
 
 -- 安装python-MySQLdb似乎没报错。但是在python中加载是报下面的错  https://zhidao.baidu.com/question/497339004.html
 
 
 


--   2-1 Python-数据库连接对象 https://www.imooc.com/video/9214 




-- D:\project_py\py_001\imooc\test_connection.py 


import MySQLdb


conn= MySQLdb.Connect(
    host='127.0.0.1',
    port =3306,
    user='root',
    password='1',
    db='immoc',
    charset='utf8'
)


cursor=conn.cursor()
print conn
print cursor


cursor.close()
conn.close()








--  2-2 Python-游标对象cursor   https://www.imooc.com/video/9215


CREATE TABLE `user` (
  `usrid` int(11) NOT NULL ,
  `usrname` varchar(100) default NULL,
  PRIMARY KEY  (`usrid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8






-- D:\project_py\py_001\imooc\test_cursor_methods.py 


import MySQLdb


conn= MySQLdb.Connect(
    host='127.0.0.1',
    port =3306,
    user='root',
    password='1',
    db='immoc',
    charset='utf8'
)


cursor=conn.cursor()


sql='select * from user'
cursor.execute(sql)


print cursor.rowcount
rs=cursor.fetchone()
print rs


rs=cursor.fetchmany(3)
print rs




rs=cursor.fetchall()
for row in rs:
    print 'userid=%s,username=%s'%row




cursor.close()
conn.close()






-- 3-2 Python-实例演示inset-upd  https://www.imooc.com/video/9217 


-- D:\project_py\py_001\imooc\test_iud.py


import MySQLdb


conn= MySQLdb.Connect(
    host='127.0.0.1',
    port =3306,
    user='root',
    password='1',
    db='immoc',
    charset='utf8'
)


cursor=conn.cursor()


sql_insert='insert into user(userid,username) values(10,"name10")'
sql_update='update user set username="nameXX" 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()






-- 4-1 [Python操作MySQL]银行转  https://www.imooc.com/video/9218 




-- 4-2 [Python操作MySQL]准备数  https://www.imooc.com/video/9219 








CREATE TABLE `account` (
  `acctid` int(11) NOT NULL COMMENT '账户id',
  `money` int(11) default NULL COMMENT '余额',
  PRIMARY KEY  (`acctid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8






--  在pycharm中，如何快速建立类、方法 https://segmentfault.com/q/1010000004696562


 在标黄的地方alt+回车试试，或者shift+insert
 
 
 
 --  D:\project_py\py_001\imooc\transer_money.py 
#! coding=utf-8
import sys


import MySQLdb




class TransferMoney( object ):
    def __init__(self, conn):
        self.conn = conn


    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


    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 'check_acct_available' + 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 'reduce_money' + sql
            if cursor.rowcount != 1:
                raise Exception( '帐号%s加款失败' % acctid )
        finally:
            cursor.close()




if __name__ == '__main__':
    source_acctid = sys.argc[1]
    target_acctid = sys.argv[2]
    money = sys.argv[3]


    conn = MySQLdb.Connect( host='127.0.0.1', user='root', password='1', prot=3306, db='immoc' )
    tr_money = TransferMoney( conn )


    try:
        tr_money.transfer( source_acctid, target_acctid, money )
    except Exception as e:
        print '出现问题:' + str( e )
    finally:
        conn.close()






--  https://www.imooc.com/video/9220