【测试平台开发】——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()
三、自动化平台系列文章汇总
不积跬步,无以致千里;不集小流,无以成江海。
如转载本文,请还多关注一下我的博客:https://www.cnblogs.com/Owen-ET/;
我的Github地址:https://github.com/Owen-ET————————————
无善无恶心之体, 有善有恶意之动, 知善知恶是良知, 为善去恶是格物。