【测试平台开发】——02数据持久化技术

一、传统数据库连接——pymysql纯SQL

首先用pip安装pymysql

pip install pymysql

安装完之后创建db对象，连接数据库

import pymysql


# 创建db对象
db = pymysql.connect(
    host='127.0.0.1',
    user='root',
    password='1234567',
    db='ET',
    charset='utf8mb4',
    cursorclass=pymysql.cursors.DictCursor  # 词典
)

操作数据库

def test_conn():
    # 获取游标
    with db.cursor() as cursor:
        sql = "show tables;"
        # 执行sql
        cursor.execute(sql)
        print(sql)
        print(cursor.fetchall())

查询数据库

def test_search():
    # 查询表
    with db.cursor() as c:
        sql = "SELECT * FROM runoob_tbl where runoob_author1=%s"
        c.execute(sql, ["菜鸟教程1"])
        print(c.fetchone())

二、ORM模型——SQLAlchemy

1、定义表结构

    # 表名
    __tablename__ = 'runoob_tb2'
    # 定义字段类型
    runoob_id = Column(Integer, primary_key=True)
    runoob_title2 = Column(String)
    runoob_author2 = Column(String)
    age = Column(Integer)
    submission_date2 = Column(Date)

2、连接数据库并用session绑定连接

host='127.0.0.1'
user='root'
password='1234567'
db='ET'
charset='utf8mb4'


    # create_engine连接各种数据库
    engine = create_engine(
        'mysql+pymysql://{user}:{password}@{host}/{db}'.format(
            user=user,
            password=password,
            host=host,
            db=db
        ),echo=True # 打印相关内容
    )
    # sessionmaker绑定连接
    Session = sessionmaker(bind=engine)
    session = Session()

3、添加数据

    # 数据插入
    u1 = User(
        runoob_id="5",
        runoob_title2="学习 PHP5",
        runoob_author2="菜鸟教程5",
        age="50",
        submission_date2="2021-07-27"
    )
    # 添加数据
    session.add(u1)
    # 提交数据
    session.commit()

数据库查看插入的数据：

4、查询数据

u2 = session.query(User).filter_by(runoob_author2="菜鸟教程5").first()
print(u2.runoob_title2)

5、完整代码

from sqlalchemy import Column, Integer, String, Date, create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker

host='127.0.0.1'
user='root'
password='1234567'
db='ET'
charset='utf8mb4'

Base = declarative_base()

class User(Base):

    # 表名
    __tablename__ = 'runoob_tb2'
    runoob_id = Column(Integer, primary_key=True)
    runoob_title2 = Column(String)
    runoob_author2 = Column(String)
    age = Column(Integer)
    submission_date2 = Column(Date)


def test_orm():
    # create_engine连接各种数据库
    engine = create_engine(
        'mysql+pymysql://{user}:{password}@{host}/{db}'.format(
            user=user,
            password=password,
            host=host,
            db=db
        ),echo=True # 打印相关内容
    )
    # sessionmaker绑定连接
    Session = sessionmaker(bind=engine)
    session = Session()

    # 数据插入
    u1 = User(
        runoob_id="5",
        runoob_title2="学习 PHP5",
        runoob_author2="菜鸟教程5",
        age="50",
        submission_date2="2021-07-27"
    )
    # session.add(u1)
    # session.commit()

    u2 = session.query(User).filter_by(runoob_author2="菜鸟教程5").first()
    print("+++++++++++++++++++++++++++++++++++")
    print("查询结果为："+u2.runoob_title2)

if __name__ == '__main__':
    test_orm()