Python MySQL 数据库

喜欢ヅ旅行 2021-11-14 12:14 360阅读 0赞

#### python DB API ####

*  python访问数据库的统一接口规范,完成不同数据库的访问
 *  包含的内容: `connection cursor exceptions`
 *  访问数据库流程: 1.创建connection 2.获取cursor 3.操作 4.关闭cursor 5.关闭connection
    
    #### python MySQL开发环境 ####
    
    ![1205669-20190701165326228-985976292.png][]
    
    #### 连接对象connection ####
 *  连接对象: python客户端与数据库的网络连接
 *  创建方法: `pymysql.connect(参数)`  
    ![1205669-20190701165342799-74031243.png][]
 *  connection对象支持的方法:  
    ![1205669-20190701165354775-10614444.png][]
 *  例子:

import pymysql
    
    conn = pymysql.connect(
        host = '127.0.0.1',
        port = 3306,
        user = 'root',
        db = 'database1',
        charset = 'utf-8')
    cursor = conn.cursor()
    
    print(conn)
    print(cursor)
    
    cursor.close()
    conn.close()

#### 游标对象cursor ####

*  游标对象: 用于执行查询和获取结果
 *  cursor对象支持的方法:  
    ![1205669-20190701165442975-1523160464.png][]
 *  `execute()`方法: 执行SQL,将结果从数据库获取到客户端  
    ![1205669-20190701165455996-1039285642.png][]
 *  `fetch*()`方法: 移动rownumber,返回数据  
    ![1205669-20190701165508423-420760824.png][]
    
    #### 实例 - select查询数据 ####
 *  流程  
    ![1205669-20190701165520907-1565146266.png][]
 *  代码

import pymysql
    
    conn = pymysql.connect(
        host = '127.0.0.1',
        port = 3306,
        user = 'root',
        db = 'database1',
        charset = 'utf-8'
    )
    cursor = conn.cursor()
    
    sql = "select * from table"
    cursor.execute(sql)
    
    print(cursor.rowcount)
    
    rs = cursor.fetchone()
    print(rs)
    
    rs = cursor.fetchmany(3)
    print(rs)
    
    rs = cursor.fetchall()
    print(rs)
    
    cursor.close()
    conn.close()

#### 实例 - insert/update/delete更新数据 ####

*  流程  
    ![1205669-20190701165533935-1681774147.png][]
 *  事务: 访问和更新数据库的一个程序执行单元  
    ![1205669-20190701165546098-1555590176.png][]
 *  开发中怎样使用事务  
    1.关闭自动commit: 设置conn.autocommit(False),默认就是这个状态  
    2.正常结束事务: conn.commit()  
    3.异常结束任务: conn.rollback()
 *  代码

import pymysql
    
    conn = pymysql.connect(
        host = '127.0.0.1',
        port = 3306,
        user = 'root',
        db = 'database1',
        charset = 'utf-8'
    )
    cursor = conn.cursor()
    
    sql_insert = "insert into database1(userid,username) values(10,'name10')"
    sql_update = "update database1 set username='name91' where userid = 9"
    sql_delete = "delete from database1 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() #提交,更新数据库
    exception Exception as e:
        print e
        conn.rollback() #回滚,只要有一条有错就啥都不提交了
    
    cursor.close()
    conn.close()

#### 银行转账实例 - A给B转账100元 ####

*  流程  
    ![1205669-20190701165603735-581350930.png][]
 *  实例数据库
 *  代码

# coding:utf8
    import pymysql
    import sys
    
    class 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("账号%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("has_enough_money:" + sql)
                rs = cursor.fetchall()
                if len(rs) != 1:
                    raise Exception("账号%s没有足够的钱" % acctid)
            finally:
                cursor.close()
    
        def reduce_money(self,acctid):
            cursor = self.conn.cursor()
            try:
                sql = "update account set money=money-%s where acctid=%s" % (money,acctid)
                cursor.execute(sql)
                print("reduce_money:" + sql)
                rs = cursor.fetchall()
                if cursor.rowcount != 1:
                    raise Exception("账号%s减款失败" % acctid)
            finally:
                cursor.close()
    
        def add_money(self,acctid):
            cursor = self.conn.cursor()
            try:
                sql = "update account set money=money+%s where acctid=%s" % (money, acctid)
                cursor.execute(sql)
                print("reduce_money:" + sql)
                rs = cursor.fetchall()
                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)
                self.add_money(target_acctid)
                self.conn.commit()
            except Exception as e:
                self.conn.rollback()
                raise e
    
    if __name__ == "__main__":
        source_acctid = sys.argv[1]
        target_acctid = sys.argv[2]
        money = sys.argv[3]
    
        conn = pymysql.connect(host='127.0.0.1',user='root',
                    passwd='123456',port=3306,db='database1')
        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.cnblogs.com/peng8098/p/python\_basis\_9.html

[1205669-20190701165326228-985976292.png]: /images/20211114/d809cadf3103459e83fed67932142434.png
[1205669-20190701165342799-74031243.png]: /images/20211114/778f3915d36b47ceafa556a70ed6d01c.png
[1205669-20190701165354775-10614444.png]: /images/20211114/fdcb89dc10d641ef9656d2c15f531c01.png
[1205669-20190701165442975-1523160464.png]: /images/20211114/c67719bf415b4a70b92a4d7af126da5e.png
[1205669-20190701165455996-1039285642.png]: /images/20211114/86c5528c9d1d4854804581fe09efaeb8.png
[1205669-20190701165508423-420760824.png]: /images/20211114/379804840f8d4513a9378baafb014ab8.png
[1205669-20190701165520907-1565146266.png]: /images/20211114/fde46db71c2f442eb0ab0082c3d12770.png
[1205669-20190701165533935-1681774147.png]: /images/20211114/61518884b75f4a04ae44e81a007b2a2c.png
[1205669-20190701165546098-1555590176.png]: /images/20211114/8d005a2ed3f641e088947ec0c112338b.png
[1205669-20190701165603735-581350930.png]: /images/20211114/4fd64c686a0b4ef0ae65bb945cdde0a3.png