python3 的 mysql 簡單操作

標籤：sele   count   odi   zha   增刪改查   user   coding   highlight   hit   

一、python 提供的 db 介面

pymysql

兩個基本對象： connection、cursor

串連樣本

# connect_demo.pyimport pymysqldb = pymysql.connect(‘localhost‘, ‘root‘, ‘root‘, ‘imooc‘, charset=‘utf8‘)cursor = db.cursor()print(db)print(cursor)cursor.close()db.close()

輸出

<pymysql.connections.Connection object at 0x7f1e2a852278><pymysql.cursors.Cursor object at 0x7f1e2880e668>

操作遊標

# cursor_demo.pyimport pymysqldb = pymysql.connect(‘localhost‘, ‘root‘, ‘root‘, ‘imooc‘, charset=‘utf8‘)cursor = db.cursor()sql = ‘select * from user‘cursor.execute(sql)print(cursor.rowcount)result = cursor.fetchall()print(result)for row in result:   print(row[0],‘:‘, row[1])cursor.close()db.close()

輸出

((1, ‘lisi‘), (2, ‘zhangsan‘), (3, ‘liuqi‘), (4, ‘white‘))1 : lisi2 : zhangsan3 : liuqi4 : white

 

二、增刪改查

# curd_demo.pyimport pymysqldb = pymysql.connect(‘localhost‘, ‘root‘, ‘root‘, ‘imooc‘, charset=‘utf8‘)cursor = db.cursor()sql_select = "select * from user"sql_insert = "insert into user(userid, username) values(DEFAULT, ‘wuliu‘)"sql_update = "update user set username=‘liuqi‘ where userid=3"sql_delete = "delete from user where userid>4"cursor.execute(sql_select)result = cursor.fetchall()print(result)try:    cursor.execute(sql_insert)    result = cursor.rowcount    print(result)    cursor.execute(sql_update)    result = cursor.rowcount    print(result)    cursor.execute(sql_delete)    result = cursor.rowcount    print(result)    db.commit()except Exception as e:    print(e)    db.rollback()cursor.close()db.close()

輸出

((1, ‘lisi‘), (2, ‘zhangsan‘), (3, ‘liuqi‘), (4, ‘white‘))101

 

三、事務的小樣本

# bank_demo.py# coding:utf8import sysimport pymysqlclass 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("帳號不存在" % 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("chas_enough_mone:" + 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("rad_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 eif __name__ == "__main__":    source_acctid= sys.argv[1]    target_acctid= sys.argv[2]    money = sys.argv[3]    conn = pymysql.connect(‘localhost‘, ‘root‘, ‘root‘, ‘imooc‘, charset=‘utf8‘)    tr_money = TransferMoney(conn)    try:        tr_money.transfer(source_acctid, target_acctid, money)    except Exception as e:        print("出現問題:", str(e))    finally:        conn.close()

命令列運行

 python bank_demo.py 3 1 1000

輸出

check_acct_available:select * from account where acctid=3check_acct_available:select * from account where acctid=1chas_enough_mone:select * from account where acctid=3 and money>1000reduce_money:update account set money=money - 1000 where acctid=3rad_money:update account set money=money + 1000 where acctid=1

 

python3 的 mysql 簡單操作