第5课、mysql +pytest

ac

 

 

 

 

 

 

import pymysql

db = pymysql.connect(host='127.0.0.1',
                user='root',
                password='SPqaz132457/',
                port=3306,
                database='sql_store',
                # 返回结果转化为字典键字对形式
                cursorclass=pymysql.cursors.DictCursor
)
#
cursor = db.cursor()
sql1 = " SELECT *  FROM customers ORDER BY first_name"
cursor.execute(sql1)
# 返回结果集
result= cursor.fetchall()
print(result)
#取出last_name这一列
# for i in result:
#     print(i['last_name'])

#修改数据
sql2="UPDATE customers SET first_name ='SONG',last_name ='DADADA' WHERE customer_id = 5"
cursor.execute(sql2)
cursor.execute(sql1)
result2= cursor.fetchall()
# 提交修改
db.commit()
print(result2)

 

 

 

 

数据库模块封装：

详见代码块，ke13_api_project_2021_06包，视频可以从12课38分开始

1.数据库连接信息，在config目录的config.py中

import pymysql
from config.config import dbinfo
from nb_log import LogManager
from config.config import ROOT_PATH
import os
logpath = os.path.join(ROOT_PATH, "log")


logger = LogManager("ha").get_logger_and_add_handlers(
    log_filename="yoyo.log",log_path=logpath
)

'''
pip install PyMySQL==0.9.3
'''

# # 配置数据库相关信息
# dbinfo = {
#     "host": "127.0.0.1",
#     "user": "root",
#     "password": "SPqaz132457/",
#     "port": 3306}


class DbConnect(object):
    def __init__(self, db_cof, database=""):
        self.db_cof = db_cof
        logger.info("读取db配置：%s" % str(self.db_cof))
        # 打开数据库连接
        self.db = pymysql.connect(database=database,
                                  cursorclass=pymysql.cursors.DictCursor,
                                  **db_cof)

        # 使用cursor()方法获取操作游标
        self.cursor = self.db.cursor()
        logger.debug("创建游标")

    def select(self, sql):
        # SQL 查询语句
        # sql = "SELECT * FROM EMPLOYEE \
        #        WHERE INCOME > %s" % (1000)
        logger.info("执行查询SQL：%s" % sql)
        self.cursor.execute(sql)
        results = self.cursor.fetchall()
        logger.info("执行查询SQL结果：%s" % str(results))
        return results

    def execute(self, sql):
        # SQL 删除、提交、修改语句
        # sql = "DELETE FROM EMPLOYEE WHERE AGE > %s" % (20)
        logger.info("执行SQL：%s" % sql)
        try:
           self.cursor.execute(sql)  # 执行SQL语句
           self.db.commit()  # 提交修改
        except:
           # 发生错误时回滚
           self.db.rollback()

    def close(self):
        # 关闭连接
        self.db.close()

if __name__ == '__main__':
    db = DbConnect(dbinfo, database="sql_store")

    sql = " SELECT *  FROM customers ORDER BY first_name"
    a = db.select(sql)
    print(a)   # list of dict

    sql2 ="UPDATE customers SET first_name ='SONG',last_name ='DADADA2' WHERE customer_id = 1"
    db.execute(sql2)
    db.close()

ac