sqlalchemy连接MySQL数据库基本操作
sqlalchemy_test.py
#!/usr/bin/env python3.8.6 # _*_ coding: utf-8 _*_ # Description: # Author: qiaoxiaohang <qiaoxiaohang@beyondsoft.com> # Date: 2022/4/8 from config import DB_URI from sqlalchemy import create_engine #todo 创建引擎并链接数据库 # engine = create_engine(DB_URI)#创建引擎 # conn = engine.connect()#链接 # result = conn.execute('select * from boo') # print(result.fetchone()) # conn.close()#关闭链接 # todo 创建ORM模型并映射到数据库中 from sqlalchemy.ext.declarative import declarative_base from sqlalchemy import create_engine,Column,Integer,String from sqlalchemy.orm import sessionmaker engine = create_engine(DB_URI)#建立引擎 Base = declarative_base(engine)# 建立 sql rom基类 session = sessionmaker(engine)() #构建session对象 #todo 创建数据库表 ''' 创建数据库表student class Student(Base): __tablename__ = "student" id = Column(Integer,primary_key=True,autoincrement=True) name = Column(String(50)) age = Column(Integer) sex = Column(String(10)) Base.metadata.create_all()# 将模型映射到数据库中 ''' #todo 新增数据 class Student(Base): __tablename__ = "student" id = Column(Integer,primary_key=True,autoincrement=True) name = Column(String(50)) age = Column(Integer) sex = Column(String(10)) ''' student = Student(name='Tony',age = 16,sex = 'male') session.add(student)#单个数据插入 session.commit() ----------------------------------------------------- session.add_all([ Student(name='Jane', age=16, sex='female'), Student(name='Ben', age=20, sex='male') ])#批量插入数据 session.commit() ''' #todo 查询数据 --sqlalchemy提供了query()方法来查询数据 data_list = session.query(Student).all() print(data_list) for data in data_list: print(data.name,data.age,data.sex) #输出: # [<__main__.Student object at 0x0000016EE319B310>, <__main__.Student object at 0x0000016EE319B280>, <__main__.Student object at 0x0000016EE319B370>] # Tony 16 male # Jane 16 female # Ben 20 male #todo 指定查询 name_list = session.query(Student.name).all() # name_list = session.query(Student.name).first()#获取返回数据的第一行 print(name_list) # 输出:[('Tony',), ('Jane',), ('Ben',)] #todo 使用filter()方法进行筛选过滤 filter_list = session.query(Student.name).filter(Student.age>=18).all() print(filter_list)#输出:[('Tony',), ('Ben',)] #todo 使用order_by()进行排序 item_list = session.query(Student.name, Student.age).order_by(Student.age.desc()).all() # desc()表示倒序 print(item_list) # [('Ben', 20), ('Tony', 18), ('Jane', 16)] # todo 多个查询条件(and和or) # 默认为and, 在filter()中用,分隔多个条件表示and item_list = session.query(Student.name, Student.age, Student.sex).filter( Student.age >= 10, Student.sex == 'female' ).all() print(item_list) # [('Jane', 16, 'female')] from sqlalchemy import or_ # 使用or_连接多个条件 item_list = session.query(Student.name, Student.age, Student.sex).filter( or_(Student.age >= 20, Student.sex == 'female') ).all() print(item_list) # [('Jane', 16, 'female'), ('Ben', 20, 'male')] # todo equal/like/in # 等于 item_list = session.query(Student.name, Student.age, Student.sex).filter( Student.age == 18 ).all() print(item_list) # [('Tony', 18, 'male')] # 不等于 item_list = session.query(Student.name, Student.age, Student.sex).filter( Student.age != 18 ).all() print(item_list) # [('Jane', 16, 'female'), ('Ben', 20, 'male')] # like item_list = session.query(Student.name, Student.age, Student.sex).filter( Student.name.like('%To%') ).all() print(item_list) # [('Tony', 18, 'male')] # in item_list = session.query(Student.name, Student.age, Student.sex).filter( Student.age.in_([16, 20]) ).all() print(item_list) # [('Jane', 16, 'female'), ('Ben', 20, 'male')] # todo count计算个数 count = session.query(Student).count() print(count) # 3 # todo 切片 item_list = session.query(Student.name).all()[:2] print(item_list) # [('Tony',), ('Jane',)] #todo 修改数据 #修改数据可以使用update()方法,update完成后记得执行session.commit() # 修改Tony的age为22 session.query(Student).filter(Student.name == 'Tony').update({'age': 22}) session.commit() item = session.query(Student.name, Student.age).filter(Student.name == 'Tony').first() print(item) #('Tony', 22) # todo 删除数据 # 删除数据使用delete()方法,同样也需要执行session.commit()提交事务 # 删除名称为Ben的数据 session.query(Student).filter(Student.name == 'Ben').delete() session.commit() item_list = session.query(Student.name, Student.age).all() print(item_list)#[('Tony', 22), ('Jane', 16)]
数据库配置参数
config.py
#!/usr/bin/env python3.8.6 # _*_ coding: utf-8 _*_ # Description: # Author: qiaoxiaohang <qiaoxiaohang@beyondsoft.com> # Date: 2022/4/8 HOST = 'localhost' PORT = 3306 USERNAME = 'root' PASSWORD = '123456' DB = 'test' DB_URI = f'mysql+pymysql://{USERNAME}:{PASSWORD}@{HOST}:{PORT}/{DB}'
推荐一个不错的网址:https://www.jb51.net/article/173950.htm