Python读写数据库

Python读写数据库

为什么要Python操作数据库？

（1）测试需要：用例检查点、用例数据准备、运维

（2）手工测试：使用工具

（3）自动化测试：编程语言访问数据库

1 Python操作Mysql

mysqlclient库，第三方开发的

安装命令：pip install mysqlclient == 1.3.12

获取数据记录：fetchone，fetchmany，fetchall

import MySQLdb

# 连接数据库，设置连接参数：数据库地址、端口、用户名、密码、所需连接的数据库名、编码方式
conn = MySQLdb.connect(
    host = '192.168.5.249',
    port = '9809',
    user = 'admin',
    password = '123456',
    db = 'TestDB',
    charset = 'utf8'
)

c = conn.cursor()
c.execute("select * from student")

# 打印出查询的所有记录
for i in range(c.rowcount):
    row = c.fetchone()
    if row[1] == 'MySQL'
        print("检查点 ==》 MySQL课程存在，测试通过")
        break

# 插入一条记录并提交事务
c.execute("insert into student(name, sex, year) value ('王少伟', '男', 25)")
conn.commit()
conn.close()

2 Python创建表create

import pymysql

def create_table():
    db = pymysql.connect("localhost", "admin", "123456", "TestDB")
    cursor = db.cursor()
    cursor.execute("DROP TABLE IF EXISTS student")

    sql = """
        create table student(
        first_name char(10) not null,
        last_name char(10),
        age int,
        create_time datetime
        )
    """

    try:
        cursor.execute(sql)
        print("数据库创建成功！")
    except Exception as msg:
        print("创建数据库识别，case:%s" % msg)
    finally:
        db.close()

def main():
    create_table()

if __name__ == "__main__" :
    main()

3 Python插数据insert

import pymysql
import datetime

def insert_record():
    db = pymysql.connect("localhost", "admin", "123456", "TestDB")
    cursor = db.cursor()

    sql = "insert into student(first_name, last_name, age, create_time)"\
        "values('%s', '%s', %d, '%s') % " \
          "('san', 'zhang', 22, datetime.datetime.now())"

    try:
        cursor.execute(sql)
        db.commit()
        print("插入数据成功！")
    except Exception as msg:
        print("插入数据失败，case:%s" % msg)
        db.rollback()
    finally:
        db.close()

def main():
    insert_record()

if __name__ == "__main__":
    main()

4 Python查数据select

import pymysql
import datetime

def select_record():
    db = pymysql.connect("localhost", "admin", "123456", "TestDB")
    cursor = db.cursor()

    sql = "select * from student"

    try:
        cursor.execute(sql)
        resulte = cursor.fetchall()
        for row in resulte:
            first_name = row[0]
            last_name = row[1]
            age = row[2]
            create_time = row[4]
            print(first_name, last_name, age, create_time)
    except Exception as msg:
        print("查询数据失败，case:%s" % msg)
        db.rollback()
    finally:
        db.close()

def main():
    select_record()

if __name__ == "__main__":
    main()