from dbconn import Departments, Employees, Session, Salary session = Session() # xz=Departments(dep_id=5,dep_name='行政部') # session.add(xz) dev = session.query(Departments).get(5) session.delete(dev) session.commit() session.close()
#!/usr/bin/env python3 # -*- coding:utf-8 -*- from dbconn import Departments,Session,Employees from sqlalchemy import and_,or_ session = Session() qset = session.query(Employees.emp_name,Departments.dep_name).join(Departments,Employees.dep_id==Departments.dep_id) #多表查询,join() print(qset) print(qset.all())
from dbconn import Session, Department, Employee session = Session() query1 = session.query(Department) print(query1.all()) # 返回列表 print(query1.first()) # 返回匹配条件的第一个记录 query2 = session.query(Department.dep_name, Department.dep_id)\ .filter(Department.dep_id==2) print(query2.one()) # one要求查询只有一项,如果有多项则报错 print(query2.scalar()) # 调用one,返回one结果中的第一个字段 query3 = session.query(Department).count() print(query3) query4 = session.query(Employee.emp_name, Department.dep_name)\ .join(Department, Employee.dep_id==Department.dep_id) # 注意:query先写Employee.emp_name,join就要先写Department # query先写Department.dep_name,join就要先写Employee print(query4) for name, dep_name in query4: print('%s: %s' % (name, dep_name))
#!/usr/bin/env python3 # -*- coding:utf-8 -*- from collections import namedtuple from dbconn import Departments, Session session = Session() qset = session.query(Departments.dep_name).filter(Departments.dep_id == 2) #过滤 for result in qset: print(result) session.close()
from dbconn import Session, Department, Employee session = Session() query1 = session.query(Department) print(query1) # 只是一条查询语句 for instance in query1: print(instance) # 取出的是每个部门的实例 dep = query1[0] # 取出第一个实例 print(dep) # 打印实例 print(dep.dep_id, dep.dep_name) # 打印实例的指定属性 print('#' * 40) query2 = session.query(Department).order_by(Department.dep_id) print(query2) for instance in query2: print(instance) print('#' * 40) query3 = session.query(Employee.emp_name, Employee.phone) print(query3) # 只是一条查询语句 print(list(query3)) # query3取值时,返回的是元组,不是列表 for name, phone in query3: print('%s: %s' % (name, phone)) print('#' * 40) query4 = session.query(Department.dep_name.label('部门')) print(query4) for dep in query4: print(dep.部门) print('#' * 40)
#!/usr/bin/env python3 # -*- coding:utf-8 -*- from dbconn import Departments, Session, Employees from sqlalchemy import and_, or_ session = Session() hr = session.query(Departments).get(3) #get用于查询主建为3的对象 print(hr) hr.dep_name = "财务部门" session.commit() session.close()
#!/usr/bin/env python3 # -*- coding:utf-8 -*- from dbconn import Departments, Session, Employees session = Session() """ 大于:> 小于:< """ """ 相等 """ qset = session.query(Employees).filter(Employees.dep_id == 1).filter( Employees.gender == '男') #多filter查询,其实是sql中的and print(qset) for instance in qset: print(instance.emp_name) session.close() """ 不相等 """ qset = session.query(Employees).filter(Employees.dep_id != 1).filter( Employees.gender != '男') #多filter查询,不等于 print(qset) for instance in qset: print(instance.emp_name) session.close() """ 模糊查询 """ qset = session.query(Employees).filter(Employees.dep_id != 1).filter( Employees.emp_name.like('%z%')) #多filter查询,模糊匹配
from dbconn import Session, Department session = Session() # query1 = session.query(Department)\ # .filter(Department.dep_name=='development') # query1.update({'dep_name': '开发部'}) # query2 = session.query(Department).filter(Department.dep_name=='人事部') # dep_hr = query2.one() # dep_hr.dep_name = '人力资源部' dep_fn = session.query(Department).get(2) print(dep_fn) session.delete(dep_fn) session.commit() session.close()
# .filter(Departments.dep_id < 6) ###写在一行会报错 # for dep in qset5: # print(dep.dep_id, dep.dep_name) # qset6 = session.query(Departments).filter(Departments.dep_id.in_([1, 3])) # for dep in qset6: # print(dep.dep_id, dep.dep_name) # qset7 = session.query(Employees).filter(Employees.email.like('*****@*****.**')) # for emp in qset7: # print(emp.emp_name, emp.email) # # qset8 = session.query(Departments.dep_id, Departments.dep_name) # print(qset8) # qset8是SQL语句 # print(qset8.all()) # all方法返回列表 # print(qset8.first()) # first返回all中的第一项 # qset9 = session.query(Departments.dep_id,Departments.dep_name)\ # .filter(Departments.dep_id==6) # # one方法要求查询的结果只有一项,0或多项都报错 # print(qset9.one()) qset10 = session.query(Employees.dep_id, Departments.dep_name)\ .join(Departments) print(qset10.all()) qset11 = session.query(Departments).filter(Departments.dep_name == '人事部') hr = qset11.one() hr.dep_name = '人力资源部' qset12 = session.query(Departments).filter(Departments.dep_id == 6) sales = qset12.one() session.delete(sales) ################ session.commit() session.close()
#!/usr/bin/env python3 # -*- coding:utf-8 -*- from dbconn import Departments,Session,Employees from sqlalchemy import and_,or_ session = Session() finance = session.query(Departments).get(3) qset = session.query(Departments).filter(Departments.dep_name=="财务部门") result = qset.all()[0] result = qset.one() result = qset.first() result = finance print(result) session.delete(result) session.commit() session.close()
from dbconn import Employees, Departments, Session session = Session() ######################################################## # dep = session.query(Departments).get(1) #取出主键是1的部门 # dep.dep_name='人力资源部' # session.commit() ######################################################## emp = session.query(Employees).get(1) session.delete(emp) session.commit()
#!/usr/bin/env python3 # -*- coding:utf-8 -*- from dbconn import Departments, Session, Employees from sqlalchemy import and_, or_ session = Session() qset = session.query(Employees).filter( and_(Employees.dep_id == 2, Employees.gender == '男')) #and qset = session.query(Employees).filter( or_(Employees.dep_id == 2, Employees.dep_id == 1)) #or qsets = session.query(Employees.emp_name).filter( and_(Employees.emp_name == 'zl', Employees.gender == 'man')) #or print(qset) for instance in qset: print(instance.emp_name) print(qset.all()) #返回所有满足条件的结果 print(qset.first()) #返回满足条件的第一个值 print(qsets.one()) #取出所有结果,如果有多个结果则抛出异常 print(qsets.scalar()) #调用one(),返回第一列的结果
from dbconn import Students, emp, Session from sqlalchemy import select, func, Integer, Table, Column, MetaData session = Session() # num1: print('第1题:') print('%-5s %-10s' % ('id', '学生名称')) qset1 = session.query(Students) for data in qset1: print('%-5s %-10s' % (data.id, data.name)) print('*' * 38) # num2: print('第2题:') print('%3s:%-3s' % ('学生名称', '语文')) qset2 = session.query(Students) for data in qset2: print('%3s:%3s' % (data.name, data.chinese)) print('*' * 38) # num3: print('第3题:') print('工作岗位:') qset3 = session.query(emp.job).distinct().all() for data in qset3: print('%s' % data) print('*' * 38) # num4: print('第4题:')
#!/usr/bin/env python3 # -*- coding:utf-8 -*- from dbconn import Departments,Session,Employees session = Session() qset = session.query(Employees.emp_name,Employees.phone) for result in qset: #yuanzhu print(result) for name,phone in qset: print("%s,%s"%(name,phone))
session = Session() # qset = session.query(Employees.name).\ # filter(and_(Employees.dep_id==2,Employees.gender=='女')) # print(qset) # for name in qset: # print(name) ################################### # qset = session.query(Employees.name).\ # filter(or_(Employees.dep_id==2, Employees.gender=='女')) # print(qset) # for name in qset: # print(name) ################################### # qset = session.query(Employees.name, Employees.phone) # print(qset.all()) # 返回列表 # print(qset.first()) # 返回满足条件的第一个值 ################################### # qset = session.query(Employees.name, Employees.phone).\ # filter(Employees.emp_id==1) # print(qset.one()) # 查询必须只有一项,否则报错 # print(qset.scalar()) # 调用one,返回第一列的值 ################################### # qset = session.query(Employees) # print(qset.count()) ################################### qset = session.query(Employees.name, Departments.dep_name).\ join(Departments, Employees.dep_id==Departments.dep_id) print(qset.all()) session.close()
# sw = Employee( # emp_id=3, emp_name='沈炜', # email='*****@*****.**', dep_id=2 # ) # zl = Employee( # emp_id=4, emp_name='钟力', # email='*****@*****.**', dep_id=3 # ) # hn = Employee( # emp_id=5, emp_name='浩宁', # email='*****@*****.**', dep_id=4 # ) # session.add_all([xcw, lyx, sw, zl, hn,]) # 基础查询1:将类作为参数,返回实例构成的列表 qset1 = session.query(Department) # print(qset1) # 只是一个SQL语句,当向它取值时,sql语句才执行 # for bumen in qset1: # print(bumen.dep_id, bumen.dep_name) # 基础查询2:将类变量作为参数,返回的是各个属性构成的元组,元组构成列表 qset2 = session.query(Employee.emp_name, Employee.email) # for data in qset2: # print(data) # 可以对查询的结果进一步应用其他方法 qset3 = session.query(Department).order_by(Department.dep_id) # for bumen in qset3: # print(bumen.dep_id, bumen.dep_name) # get方法,可以根据主键取出实例
from dbconn import Departments, Session session = Session() # sales = Departments(dep_id=6, dep_name='销售') # session.add(sales) # session.commit() ########################## # xs = session.query(Departments).filter(Departments.dep_id==6) # xs = xs.one() # 取出实例 # print(xs) # xs.dep_name = '销售部' # 实例重新赋值 # session.commit() ########################## xs = session.query(Departments).filter(Departments.dep_id==6) xs = xs.one() session.delete(xs) session.commit() session.close()
#!/usr/bin/env python3 # -*- coding:utf-8 -*- from collections import namedtuple from dbconn import Departments,Session """ 命名元组 """ # students = namedtuple('students',['name','age','gender']) #第一个students和第二个students名字要相同 # zhangsan = students('zhangsan',25,'male') session = Session() qset = session.query(Departments,Departments.dep_name)[1:3] print(qset) # for row in qset: print(row) print(row.Departments,row.dep_name)