sqlalchemy——基本操作
以下所有代码片段都使用了统一的引用，该引用如下：
from sqlalchemy import create_engine, ForeignKey from sqlalchemy.ext.declarative import declarative_base from sqlalchemy import Column, String, Integer, Table from sqlalchemy.orm import relationship, sessionmaker
一、表结构设计
engine = create_engine("mysql+pymysql://root:root@localhost/study?charset=utf8", echo=True) # 连接数据库,显示SQL语句 Base = declarative_base() # 创建基类 class Student(Base): __tablename__ = 'students' # 指定表名 __table_args__ = { # "mysql_engine":"MyISAM", "mysql_charset":"utf8" } # show create table students 可以查看建表语句;默认是Innodb,lating-1.如果想显示中文需要修改指定建表的类型,同时,engine也要指定编码格式 id = Column(Integer, primary_key=True) #指定主键 name = Column(String(30)) #多个字段名 gender = Column(Integer) std_id = Column(String(10)) teacher = Column(String(30)) def __str__(self): #显示对象的时候打印名字 return self.name Base.metadata.create_all(engine) # 创建所有继承自 Base 的类对应的表
sqlslchemy在数据库中创建表之前，会先检查该表是否存在，如果不存在，才会去创建新表。
class Student(Base): __tablename__ = 'students' # 指定表名 id = Column(Integer, primary_key=True) name = Column(String(30)) gender = Column(Integer) std_id = Column(String(10)) teacher = Column(String(30)) **math = Column(Integer)** #变量字段 def __str__(self): return self.name
虽然新加了一个math 变量（字段），但是由于数据库中已有students表，所以不会去创建新表，match字段也就不存在。
二、基本增删改查
1.插入数据
Session = sessionmaker(bind=engine) # 获取 Session类 ses = Session() # 获取session对象 zhangsan = Student(name="zhangsan", gender=1, std_id='XS331', teacher='hanmeimei', math=90) lisi = Student(name="lisi", gender=1, std_id='XS332', teacher='hanmeimei', math=10) wangwu = Student(name="wangwu", gender=0, std_id='XS333', teacher='lilei', math=60) zhaoliu = Student(name="zhaoliu", gender=0, std_id='XS337', teacher='lilei', math=80) ses.add(zhangsan) # 单条添加 ses.add_all([lisi, wangwu, zhaoliu]) # 批量添加 ses.commit()
2.修改数据
zhangsan = ses.query(Student).filter_by(name='zhangsan').first() #先获取对象 zhangsan.math = 91 #更改对象的属性 ses.commit() #提交
也可以在还创建数据的时候修改，就是在提交之前进行修改：
liqi = Student(name="liqi", gender=1, std_id='XS335', teacher='xiadonghai', math=100) #实例化一个对象 ses.add(liqi) #添加对象，也可以视作添加一条数据 liqi = ses.query(Student).filter_by(name='liqi').first() liqi.gender = 0 ses.commit()
在创建一个对象之后，未添加之前直接改变其属性。
liqi = Student(name="liqi", gender=1, std_id='XS335', teacher='xiadonghai', math=100) liqi.gender = 0 ses.add(liqi) ses.commit()
除此之外还可以使用update。
sess.query(Student).filter_by(name='liming').update({'gender':0}) sess.commit()
3.删除
result = sess.query(Student).filter(Student.id == 1 ).delete() sess.commit() result = sess.query(Student).filter(Student.id == 2 ).first() sess.delete(result) sess.commit()
4.查询
查询操作在数据库操作中总是最复杂的，下面介绍一些常用的操作。
（1）算术操作符
= 操作符 students_99 = sess.query(Student).filter_by(name ='zhangsan').all() students_100 = sess.query(Student).filter(Student.name == 'zhangsan').all() for i in students_100: print(i.name)
！= 操作符 student_101 = sess.query(Student).filter_by(name != 'liming').all() # error student_98 = sess.query(Student).filter(Student.name != 'liming').all() for i in student_98: print(i.name)
> ,<, <=, >=, 操作符 student_60 = sess.query(Student).filter(Student.math > 10).all() student_61= sess.query(Student).filter_by(math>10).all() # error student_62 = sess.query(Student).filter(Student.math < 100).all() student_63 = sess.query(Student).filter(Student.math <= 90).all() student_64 = sess.query(Student).filter(Student.math >= 90).all() student_64 = sess.query(Student).filter(Student.math <> 90).all() # error for i in student_60: print(i.math) for i in student_62: print(i.math) for i in student_63: print(i.math) for i in student_64: print(i.math)
（2）模糊查询
like 操作符 % 代表任意多个字符 _ 代表一个字符 student_17 = sess.query(Student).filter(Student.name.like("%i%")).all() for i in student_17: print(i.name) student_17_2 = sess.query(Student).filter(Student.name.like("%i_i")).all() for i in student_17_2: print(i.name) not like student_70 = sess.query(Student).filter(~Student.name.like("%i_i")).all() student_71 = sess.query(Student).filter(Student.name.notlike("%i_i")).all() for i in student_70: print(i.name) print(student_70 == student_71)
（3）区间选取
in 操作符 student_18 = sess.query(Student).filter(Student.name.in_(['zhangsan','lisi','wangwu'])).all() for i in student_18: print(i.name) not in student_19 = sess.query(Student).filter(~Student.name.in_(['lisi'])).all() student_19_2 = sess.query(Student).filter(Student.name.notin_(['lisi'])).all() for i in student_19: print(i.name) print(student_19 == student_19_2)
（4）分页
student_10 = sess.query(Student).limit(2).offset(1).all() #从第几条开始选取几条 student_11 = sess.query(Student).offset(1).limit(2).all() student_12 = sess.query(Student)[1:3] for i in student_10: print(i.id) for i in student_11: print(i.id) for i in student_12: print(i.id)
（5）排序
order_by 排序 student_33 = sess.query(Student).filter(text("math >= 10")).order_by(~text("math")).all() # 逆序 student_33_2 = sess.query(Student).filter(text("math >= 10")).order_by(text("math")).all() # 顺序 student_33_2_1 = sess.query(Student).filter(text("math >= 10")).order_by(desc(text("math"))).all() # 逆序 student_33_3 = sess.query(Student).order_by(desc(Student.math)).all() # 逆序 student_33_4 = sess.query(Student).order_by(Student.math.desc()).all() # 逆序 student_33_5 = sess.query(Student).order_by(~Student.math).all() # 逆序 student_33_6 = sess.query(Student).order_by(Student.math).all() # 顺序 print(student_31) print(student_32) print("___"* 30) for i in student_33: print(i.math) print("___"* 30) for i in student_33_2: print(i.math) for i in student_33_3: print(i.math) for i in student_33_4: print(i.math) for i in student_33_5: print(i.math) for i in student_33_6: print(i.math)
（6）分组
group_by 分组 student_39 = sess.query(Student).group_by(Student.gender == 1).count() print(student_39)
（7）having：分组之后条件过滤
having student_39_1 = sess.query(Student).group_by(Student.gender == 1).having(Student.math>60).all() for i in student_39_1: print(i)
（8）计数
count student_38 = sess.query(Student).filter_by(gender=1).count() student_38_2 = sess.query(func.count(Student.name),Student.name).group_by(Student.name).all() student_38_3 = sess.query(func.count(Student.name),Student.gender).group_by(Student.gender).all() print(student_38_2) print(student_38_3)
（9）去重
distinct student_40 = sess.query(distinct(Student.name)).all() for i in student_40: print(i)
（10）空值
是否为null student_20 = sess.query(Student).filter(Student.name != None).all() student_21 = sess.query(Student).filter(~Student.name.is_(None)).all() student_22 = sess.query(Student).filter(Student.name.isnot(None)).all() student_21_3 = sess.query(Student).filter(Student.name.is_('zhangsan')).all() # error student_21_2 = sess.query(Student).filter(Student.name.is_(1)).all() for i in student_20: print(i.name) print(student_20 == student_21 == student_22) student_23 = sess.query(Student).filter(Student.math.is_(90)).all() student_24 = sess.query(Student).filter(Student.math == None).all()
（11）逻辑操作
and student_25 = sess.query(Student).filter(Student.gender == 1, Student.math > 70).all() student_26 = sess.query(Student).filter(and_(Student.gender == 1, Student.math > 70)).all() # 别忘了引入该方法 student_27 = sess.query(Student).filter(Student.gender == 1).filter(Student.math > 70).all() for i in student_25: print(i.name) print(student_25 == student_26 == student_27) or student_28 = sess.query(Student).filter(or_(Student.gender == 1, Student.math > 80)).all() for i in student_28: print(i.name)
（12）text：原生sql语句
text 原生sql条件语句 student_31 = sess.query(Student).filter(text("name='lisi'")).first() # 注意值为字符串时的写法 student_32 = sess.query(Student).filter(text("id=1")).first() # 注意值为 数字 的写法 student_31 = sess.query(Student).filter(text("id>1 and math>10")).all() for i in student_31: print(i.name) text 插入变量 student_34 =sess.query(Student).filter(text("gender=:sex and math>:score")).params(sex=1, score=1).all() for i in student_34: print(i.name) from_statement 原生sql语句 student_35 = sess.query(Student).from_statement(text("select * from students where id=:id")).params(id=1).one() student_36 = sess.query(Student).from_statement("select * from students where math>:score").params(score=10).all() student_36_1 = sess.query(Student).from_statement("select * from students where math>10").all() print(student_35) for i in student_36: print(i.name) print(student_36 == student_36_1)
（13）使用别名
student_7 = sess.query(Student.name.label('std_name')).all() print(student_7) for i in student_7: print(i.std_name) print(i[0]) # 第二种取值方式 student_8 = sess.query(Student.name).all() print(student_8) for i in student_8: print(i.name) print(i[0])
（14）取值
根据主键获取对象 students_1 = sess.query(Student).get(1) print(students_1 value指定要获取的字段 返回的是生成器 students_3 = sess.query(Student).value(Student.name) print(students_3) values 指定多个字段 返回的是生成器 students_3_2 = sess.query(Student).values(Student.id,Student.name) print(students_3_2) for i in students_3_2: print(i) 一次获取多个字段值 students_4 = sess.query(Student.name,Student.gender).all() print(students_4) for i in students_4: print(i) 后续添加的方式选择要得到的字段结果 students_5 = sess.query(Student.name).add_columns(Student.gender).all() print(students_5) student_6 = sess.query(Student).filter_by(name="王大麻子").one() print(student_6) 错误用法 error_1 = sess.query(Student).filter_by(and_(Student.id == 1, Student.name == 'lisi')).first() error_2 = sess.query(Student).filter_by(and_(id=1, name='lisi')).first() error_3 = sess.query(Student).filter(and_(id=1, name='lisi')).first() one 有且只有一个，否则报错 student_30 = sess.query(Student).filter(Student.id == 1).one() print(student_30) 可以只有一个或者没有，不能为多个结果，否则报错 student_31 = sess.query(Student).filter(Student.id == 10).one_or_none() print(student_31) student_13 = sess.query(Student).filter_by(id=1).one_or_none() student_14 = sess.query(Student).filter(Student.id == 1).one_or_none() print(student_13) print(student_14)
（15）chain链式调用
student_15 = sess.query(Student).filter_by(gender=1).filter(Student.math>80).all() # student_15_2 = sess.query(Student).filter_by(gender=1).filter_by(math>80).all() # error student_16 = sess.query(Student).filter(Student.gender == 1).filter(Student.math>80).all() for i in student_15: print(i.name) for i in student_16: print(i.name)
