两种写python连接mysql代码的方案

[转载]原文来自华为云开发者联盟 地址:https://huaweicloud.csdn.net/63808a5adacf622b8df8a0b8.html?spm=1001.2101.3001.6650.2&utm_medium=distribute.pc_relevant.none-task-blog-2%7Edefault%7EBlogCommendFromBaidu%7Eactivity-2-122563620-blog-80154538.235%5Ev31%5Epc_relevant_default_base3&depth_1-utm_source=distribute.pc_relevant.none-task-blog-2%7Edefault%7EBlogCommendFromBaidu%7Eactivity-2-122563620-blog-80154538.235%5Ev31%5Epc_relevant_default_base3&utm_relevant_index=3#devmenu10

一、python连接mysql线性代码

# -*- coding: utf-8 -*-
# @Author  : hxy
# @Time    : 2022/1/10 10:51
# @Function:
'''
    数据容器：mysql
    操作数据库的步骤
    1、连接数据库，通过connnect函数链接，生成connection对象
    2、定义我们的游标Cursor,再通过我们游标执行脚本并获取结果
    3、关闭连接
'''
import datetime
import time

import pymysql

# 1、建立mysql连接
conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', password='root', database='test_cases', charset='utf8')
'''
    常用方法：
    1、cursor()使用当前连接创建并返回游标
    2、commit()提交当前事务
    3、rollback()回滚当前事务
    4、close()关闭当前连接
'''
# 2、建立游标
cur = conn.cursor()
'''
    游标操作方法：
    1、execute()执行数据库查询或命令，将结果从数据库返回给客户端
    2、fetchone()获取结果集的下一行
    3、fetchall()获取结果集的所有行
    4、fetchmany()获取结果集的几行
'''
now = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime())
# now=datetime.datetime.now(.strftime("%Y-%m-%d %H:%M:%S"))
# 3、执行脚本
cur.execute('select weaid,success from weather')
print(cur.fetchall())
cur.execute('insert into weather(weaid,success,cre_time) values ("2","2","%s")' % now)
cur.execute('commit')
conn.close()

二、python连接mysql工具类+进行数据驱动+配置模块

即：将python连接mysql相关的代码封装起来，形成一个工具类，在需要使用的时候调用

准备工作：1、key_demo——2、cases|tool——3、cases/case.py|tool/MyDB.py

1、MyDB.py工具类

# -*- coding: utf-8 -*-
# @Author  : hxy
# @Time    : 2022/1/18 15:20
# @Function:
import pymysql


class my_db:
    '''
    动作类：获取数据连接，连接ip,端口，账号密码。。
    '''

    # 构造函数
    def __init__(self):
        try:
            self.dbconn = pymysql.connect(host='127.0.0.1', port=3306, user='root', password='root',
                                          database='test_cases', charset='utf8')
        except Exception as e:
            print('初始化数据库连接失败：%s' % e)

    def close(self):
        self.dbconn.close()

    # query=查询语句
    def select_record(self, query):
        # 查询数据
        print('query:%s' % query)
        try:
            # 建立游标
            db_cursor = self.dbconn.cursor()
            db_cursor.execute(query)
            result = db_cursor.fetchall()
            return result
        except Exception as e:
            print('数据库查询数据失败：%s' % e)
            db_cursor.close()
            exit()

    # 插入
    def execute_insert(self, query):
        print('query:%s' % query)
        try:
            # 建立游标
            db_cursor = self.dbconn.cursor()
            db_cursor.execute(query)
            db_cursor.execute('commit')
            return True
        except Exception as e:
            print('数据库插入数据失败：%s' % e)
            # 事务回滚
            db_cursor.execute('rollback')
            db_cursor.close()
            exit()

2、case.py数据驱动

# -*- coding: utf-8 -*-
# @Author  : hxy
# @Time    : 2022/1/7 15:07
# @Function:
# 针对数据库中的数据做数据驱动
import time
import unittest
from ddt import ddt, data, unpack

from key_demo.tool.MyDB import my_db

# 必须要实例化，不然会报TypeError: select_record() missing 1 required positional argument: 'query'
testdb = my_db()
now = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime())


@ddt
class case(unittest.TestCase):
    # @data(*my_db().select_record('select weaid,success from weather'))
    @data(*testdb.select_record('select weaid,success from weather'))
    @unpack
    def test_1(self, weaid, success):
        print(weaid, success)
        testdb.execute_insert('insert into weather(weaid,success,cre_time) values ("2","2","%s")' % now)


if __name__ == '__main__':
    unittest.main()

运行结果：

这样写的插入语句会被调用多次，要注意@data中执行的操作具体执行次数

换了一个写法，先插入，再查询

# -*- coding: utf-8 -*-
# @Author  : hxy
# @Time    : 2022/1/7 15:07
# @Function:
# 针对数据库中的数据做数据驱动
import time
import unittest
from ddt import ddt, data, unpack

from key_demo.tool.MyDB import my_db

# 必须要实例化，不然会报TypeError: select_record() missing 1 required positional argument: 'query'
testdb = my_db()
now = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime())


@ddt
class case(unittest.TestCase):
    # @data(*my_db().select_record('select weaid,success from weather'))
    @data(testdb.execute_insert('insert into weather(weaid,success,cre_time)values("2","2","%s")' % now))
    # @unpack
    def test_1(self,t):
        print(t)
        res = testdb.select_record('select weaid,success from weather')
        print(res)


if __name__ == '__main__':
    unittest.main()

代码编写的时候有几个注意事项：

1、只执行一条语句，返回结果也只有一个，不需要@unpack解析

2、MyDB.py中定义有返回值，还有ddt中定义的语法return func(self, *args, **kwargs),不能缺少参数

运行结果： 

还有一种方式：

@ddt
class testcase(unittest.TestCase):
    @data(['2', '2'], ['1', '1'])
    @unpack
    def test_1(self, value1, value2):
        testdb.execute_insert('insert into weather(weaid,success,cre_time)values(' + value1 + ',' + value2 + ',%s)' % now)
        res = testdb.select_record('select weaid,success from weather')
        print(res)

可优化的点：data里面的值可以进行数据驱动测试，将数据和代码分离

数据放在yaml或者csv中读取

3、通过配置模块优化数据库连接

3、1定义配置项dbconfig.conf

1.key_demo——2.key_demo/config——3、key_demo/config/dbconfig.conf

#测试环境
[TESTDB]
host=127.0.0.1
port=3306
user=root
password=root
db=test_cases
charset=utf8

[DEVDB]
host=192.168.1.2
port=3306
user=root
password=root
db=test_cases1
charset=utf8

#生产环境
[prdDB]
host=192.168.1.4
port=3306
user=root
password=root
db=test_cases2
charset=utf8

3、2改造工具类的构造函数

# -*- coding: utf-8 -*-
# @Author  : hxy
# @Time    : 2022/1/18 15:20
# @Function:
import pymysql

import configparser


class my_db:
    '''
    动作类：获取数据连接，连接ip,端口，账号密码。。
    '''

    # 构造函数
    def __init__(self, config_file, db):
        # 实例化configparser
        config = configparser.ConfigParser()
        # 从配置文件中读取数据库相关信息
        config.read(config_file)
        host = config[db]['host']
        port = int(config[db]['port'])
        user = config[db]['user']
        password = config[db]['password']
        database = config[db]['database']
        charset = config[db]['charset']
        try:
            self.dbconn = pymysql.connect(host, port, user, password, database, charset)
        except Exception as e:
            print('初始化数据库连接失败：%s' % e)

    def close(self):
        self.dbconn.close()

    # query=查询语句
    def select_record(self, query):
        # 查询数据
        print('query:%s' % query)
        try:
            # 建立游标
            db_cursor = self.dbconn.cursor()
            db_cursor.execute(query)
            result = db_cursor.fetchall()
            return result
        except Exception as e:
            print('数据库查询数据失败：%s' % e)
            db_cursor.close()
            exit()

    # 插入
    def execute_insert(self, query):
        print('query:%s' % query)
        try:
            # 建立游标
            db_cursor = self.dbconn.cursor()
            db_cursor.execute(query)
            db_cursor.execute('commit')
            return True
        except Exception as e:
            print('数据库插入数据失败：%s' % e)
            # 事务回滚
            db_cursor.execute('rollback')
            db_cursor.close()
            exit()

注意事项：configparser配置对象的使用

3、3在用例中的使用

# -*- coding: utf-8 -*-
# @Author  : hxy
# @Time    : 2022/1/7 15:07
# @Function:
# 针对数据库中的数据做数据驱动
import time
import unittest
from ddt import ddt, data, unpack

from key_demo.tool.MyDB import my_db

# 必须要实例化，不然会报TypeError: select_record() missing 1 required positional argument: 'query'
# testdb = my_db('key_demo/config/dbconfig.conf','TESTDB')
config_file='key_demo/config/dbconfig.conf'
db='TESTDB'
testdb=my_db(config_file,db)
now = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime())


@ddt
class case(unittest.TestCase):
    # @data(*my_db().select_record('select weaid,success from weather'))
    @data(testdb.execute_insert('insert into weather(weaid,success,cre_time)values("2","2","%s")' % now))
    # @unpack
    def test_1(self, t):
        print(t)
        res = testdb.select_record('select weaid,success from weather')
        print(res)


if __name__ == '__main__':
    unittest.main()

3、4运行结果