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)