def default(self, *vpath, **params):
current_session = Session()
try:
method = getattr(self, "handle_" + cherrypy.request.method, None)
if not method:
methods = [x.replace("handle_", "")
for x in dir(self) if x.startswith("handle_")]
cherrypy.response.headers["Allow"] = ",".join(methods)
raise cherrypy.HTTPError(405, "Method not implemented.")
return method(current_session, *vpath, **params)
except:
self.log_exception("restresource.default() exception")
return "There was an error attempting to service the request"
finally:
current_session.close()
#数据库增 from dbconn import Departments, Session, Salary, Employees from sqlalchemy.orm import sessionmaker session = Session() # hr=Departments(dep_name='运维部') # print(hr.dep_name) # print(hr.dep_id) # session.add(hr) # session.commit() # print(hr.dep_id) ############################################# # ops = Departments(dep_id=6,dep_name='营销部') # dev = Departments(dep_id=7,dep_name='开发部') # session.add_all([ops,dev]) ############################################# # jjy = Employees( # name='姜俊羽', # gender='男', # birth_date='1995-10-6', # phone='15700064723', # email='*****@*****.**', # dep_id=1 # ) # hsm = Employees( # name='何孙淼', # gender='女', # birth_date='1998-10-8', # phone='18866663333', # email='*****@*****.**', # dep_id=4
from dbconn import Departments, Employees, Salary, Session session = Session() ############################# # hr = Departments(dep_id=1, dep_name='人事部') # session.add(hr) ############################# # ops = Departments(dep_id=2, dep_name='运维部') # dev = Departments(dep_id=3, dep_name='开发部') # qa = Departments(dep_id=4, dep_name='测试部') # finance = Departments(dep_id=5, dep_name='财务部') # ui = Departments(dep_id=6, dep_name='设计部') # session.add_all([ops, dev, qa, finance, ui]) ############################# # yc = Employees( # emp_id=1, # emp_name='杨晨', # email='*****@*****.**', # dep_id=2 # ) # zyp = Employees( # emp_id=2, # emp_name='郑云鹏', # email='*****@*****.**', # dep_id=2 # ) # lzj = Employees( # emp_id=3, # emp_name='李注江', # email='*****@*****.**', # dep_id=2
from dbconn import Departments, Session, Salary
session = Session()
qset1 = session.query(Departments).order_by(Departments.dep_id)
print(qset1) # qset1仅仅是一条SQL语句
for dep in qset1: # 使用qset的时候，dep是一个个的实例
print(dep)
for dep in qset1:
print('%s: %s' % (dep.dep_id, dep.dep_name))
########################################################
qset2 = session.query(Departments.dep_id, Departments.dep_name)
print(qset2)
for did, dname in qset2:
print(did, dname)
########################################################
qset3 = session.query(Departments)[1:3] # 返回的不是SQL语句
print(qset3)
for dep in qset3:
print(dep.dep_name)
########################################################
qset4 = session.query(Departments.dep_name).\
filter(Departments.dep_id==2)
print(qset4)
for dep in qset4:
print(dep.dep_name)
from dbconn import Departments, Employees, Session session = Session() # 创建会话类的实例 # hr = Departments(dep_id=1, dep_name='人事部') # session.add(hr) # dev = Departments(dep_id=2, dep_name='开发部') # ops = Departments(dep_id=3, dep_name='运维部') # market = Departments(dep_id=4, dep_name='市场部') # finance = Departments(dep_id=5, dep_name='财务部') # session.add_all([dev, ops, market, finance]) #################################### # dzh = Employees( # emp_id=1, # emp_name='董志华', # birth_date='1993-9-4', # email='*****@*****.**', # dep_id=2 # ) # zc = Employees( # emp_id=2, # emp_name='郑聪', # birth_date='1992-4-10', # email='*****@*****.**', # dep_id=2 # ) # fxq = Employees( # emp_id=3, # emp_name='方兴清', # birth_date='1995-8-22', # email='*****@*****.**', # dep_id=3
from dbconn import Department, Employee, Salary, Session
session = Session()
dep_dev = Department(dep_name='development')
dep_hr = Department(dep_name='人事部')
dep_op = Department(dep_name='运维部')
dep_fn = Department(dep_id=2, dep_name='财务部')
# print(dep_dev.dep_name)
# print(dep_dev)
# print(dep_dev.dep_id)
# session.add(dep_dev)
# session.add_all([dep_hr, dep_op])
# session.add(dep_fn)
wh = Employee(
emp_name='王贺',
gender='male',
birth_date='1993-1-1',
phone='17788990022',
email='*****@*****.**',
dep_id=1
)
lj = Employee(
emp_name='李俊',
gender='male',
birth_date='1995-10-1',
phone='13355667788',
email='*****@*****.**',
dep_id=4
)
zzh = Employee(
emp_name='赵子浩',
#!/usr/bin/env python3 # _*_coding:utf8_*_ from dbconn import Departments, Session hr = Departments(dep_id=1, dep_name='hr') ops = Departments(dep_id=2, dep_name='operations') dev = Departments(dep_id=3, dep_name='development') finance = Departments(dep_id=4, dep_name='财务部') deps = [ops, dev] print(hr.dep_id, hr.dep_name) session = Session() session.add(hr) session.add_all(deps) session.add(finance) session.commit() session.close()
from dbconn import Department, Session, Employee, Salary
session = Session()
dep_dev = Department(dep_name='development')
dep_hr = Department(dep_name='人事部')
dep_op = Department(dep_name='运维部')
# session.add_all([dep_hr,dep_op])
#session.add(dep_dev)
my = Employee(emp_name='马云',
gender='malse',
birth_date='1991-01-02',
phone='15988663322',
email='*****@*****.**',
dep_id=3)
mht = Employee(emp_name='马化腾',
gender='malse',
birth_date='1991-05-02',
phone='15988776655',
email='*****@*****.**',
dep_id=3)
lyh = Employee(emp_name='李彦宏',
gender='malse',
birth_date='1991-06-02',
phone='15999886655',
email='*****@*****.**',
dep_id=3)
my201810 = Salary(emp_id=1, date='2018-10-01', basic=15000, awards=5000)
session.add(my201810)
#session.add_all([my,mht,lyh])
#SQLAlchemy
#向表中添加内容
from dbconn import Session, Departments, Employees, Salary
hr = Departments(dep_name='hr') ##向表Departments添加hr
print(hr.dep_id) # 此时还没有在数据库中创建记录，所以是None
print(hr.dep_name)
session = Session() # 建立到数据库的会话连接
session.add(hr) # 真正向数据库写入记录
# op=Departments(dep_id=2,dep_name='运维部')
# session=Session()
# session.add(op)
# dev=Departments(dep_id=3,dep_name='开发部')
# qa=Departments(dep_id=4,dep_name='测试部')
# session=Session()
# session.add_all([dev,qa])
##向employees表添加内容
bob = Employees(emp_id=1,
emp_name='Bob',
gender='male',
birth_date='1995-08-23',
email='*****@*****.**',
dep_id='1')
john = Employees(emp_id=2,
emp_name='John',
gender='male',
birth_date='1992-06-2',
#!/usr/bin/env python3 # _*_coding:utf8_*_ from dbconn import Session, Departments # cw = Departments(dep_id=5,dep_name='cw') session = Session() det = session.query(Departments).get(5) session.delete(det) # qset = session.query(Departments.dep_id,Departments.dep_name) # for id , name in qset: # print(id,name) # session.add(cw) session.commit() session.close()
from dbconn import Departments, Session, Employees
from sqlalchemy import and_, or_
session = Session()
query1 = session.query(Departments)
# print(query1)
# print(list(query1))
# for dep in query1:
# print('%s: %s' % (dep.dep_id, dep.dep_name))
query2 = session.query(Departments.dep_id, Departments.dep_name)
# print(query2)
# print(list(query2))
# for dep in query2:
# print('%s: %s' % (dep.dep_id, dep.dep_name))
query3 = session.query(Departments).order_by(Departments.dep_id)
# for dep in query3:
# print('%s: %s' % (dep.dep_id, dep.dep_name))
query4 = session.query(Departments).order_by(Departments.dep_id)[:2]
# for dep in query4:
# print('%s: %s' % (dep.dep_id, dep.dep_name))
query5 = session.query(Departments).filter(Departments.dep_id == 2)
# print(query5)
# print(list(query5))
# for dep in query5:
# print('%s: %s' % (dep.dep_id, dep.dep_name))
query6 = session.query(Departments).filter(Departments.dep_id == 3).filter(
from dbconn import Departments, Session, Employees session = Session() modify1 = session.query(Departments).filter(Departments.dep_name=='hr') # hr = modify1.one() # print(hr) # hr.dep_name = '人力资源部' delete = session.query(Employees).filter(Employees.emp_id==11) emp = delete.one() session.delete(emp) session.commit() session.close()
from dbconn import Departments, Session session = Session() q1 = session.query(Departments).filter(Departments.dep_name=='运维部') dep = q1.one() # print(dep) session.delete(dep) 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.in_([1, 2])) #in过滤
qset = session.query(Employees).filter(
~Employees.dep_id.in_([1, 2])) #not in过滤(区反)
qset = session.query(Employees).filter(
Employees.dep_id.is_(None)) #is_(None)取空
qset = session.query(Employees).filter(
Employees.dep_id.isnot_(None)) #isnot 取非空
print(qset)
from dbconn import Session, Department, Employee, Salary session = Session() ################################# # hr = Department(dep_id=1, dep_name='人事部') # finance = Department(dep_id=2, dep_name='财务部') # ops = Department(dep_id=3, dep_name='运维部') # dev = Department(dep_id=4, dep_name='开发部') # qa = Department(dep_id=5, dep_name='测试部') # session.add_all([hr, finance, ops, dev, qa]) ################################# # wt = Employee( # emp_id=1, # emp_name='王涛', # email='*****@*****.**', # dep_id=3 # ) # zj = Employee( # emp_id=2, # emp_name='张钧', # email='*****@*****.**', # dep_id=3 # ) # sy = Employee( # emp_id=3, # emp_name='苏艳', # email='*****@*****.**', # dep_id=1 # ) # wjy = Employee( # emp_id=4,
from dbconn import Departments, Employees, Session session = Session() hr = Departments(dep_name='人事部') ops = Departments(dep_name='运维部') dev = Departments(dep_name='开发部') qa = Departments(dep_name='测试部') finance = Departments(dep_name='财务部') xz = Departments(dep_name='行政部') # session.add(hr) # session.add_all([ops, dev, qa, finance, xz]) ####################################### # gyh = Employees( # emp_name='耿宇航', # gender='男', # birth_date='1993-8-23', # email='*****@*****.**', # dep_id=2 # ) # zjy = Employees( # emp_name='张钧溢', # gender='男', # birth_date='1990-10-15', # email='*****@*****.**', # dep_id=2 # ) # jp = Employees( # emp_name='蒋鹏', # gender='男', # birth_date='1995-3-23',
from dbconn import Session, Departements, Employees session = Session() # 创建会话实例 ############################################### # 增加，直接通过创建实例实现 # hr = Departements(dep_id=1, dep_name='人事部') # session.add(hr) ############################################### # 增加多项 # ops = Departements(dep_id=2, dep_name='运维部') # dev = Departements(dep_id=3, dep_name='开发部') # qa = Departements(dep_id=4, dep_name='测试部') # finance = Departements(dep_id=5, dep_name='财务部') # sales = Departements(dep_id=6, dep_name='销售部') # market = Departements(dep_id=7, dep_name='市场部') # session.add_all([ops, dev, qa, finance, sales, market]) ############################################### # wt = Employees( # emp_id=1, # emp_name='王涛', # email='*****@*****.**', # dep_id=3 # ) # zj = Employees( # emp_id=2, # emp_name='张钧', # email='*****@*****.**', # dep_id=3 # ) # sy = Employees( # emp_id=3,
from dbconn import Employees, Departments, Session
session = Session()
# query1 = session.query(Departments)
# print(query1) # query1只是个sql语句，不真正连接数据库，只有取数据时才连接
# for dep in query1:
# print(dep) # 返回的是Departments的实例
# print(dep.dep_id, dep.dep_name)
# print('-' * 30)
##########################################
# query2 = session.query(Employees.name, Employees.phone)
# print(query2)
# for item in query2:
# print(item) # 因为只查询一部分属性，所以返回值是属性构成的元组
# for name, phone in query2:
# print('%s: %s' % (name, phone))
##########################################
# query3 = session.query(Departments.dep_name.label('部门'))
# print(query3)
# for dep in query3:
# print(dep.部门)
##########################################
# query4 = session.query(Departments).order_by(Departments.dep_id)
# print(query4)
# for dep in query4:
# print(dep)
##########################################
# query5 = session.query(Employees)[2:4]
# print(query5) # query5是由实例构成的列表，因取切片时，需要到数据库中取出数据
##########################################
# query6 = session.query(Departments).filter(Departments.dep_id==2)
from dbconn import Salary, Session, Departments, Employees
session = Session()
#查询数据库,返回实体类的实例
qset1 = session.query(Departments)
print(qset1) #此时只是一条SQL语句,不真正连接数据库,返回实例
print(list(qset1)) # 取值的时候，才会连接数据库
for dep in qset1:
print('部门ID: %s, 部门名称: %s' % (dep.dep_id, dep.dep_name))
#################################
# 如果查询某些字段，返回的是元组
qset2 = session.query(Employees.emp_name, Employees.email)
print(qset2) # qset2是SQL语句
print(list(qset2)) # 取值是元组
#排序,可以对执行结果进一步操作
qset3 = session.query(Departments).order_by(Departments.dep_id)
for dep in qset3:
print(dep.dep_id, dep.dep_name)
# 排序，取切片
qset4 = session.query(Departments).order_by(Departments.dep_id)[2:4]
print(qset4) # 因为qset4执行了切片取值，所以它不是sql语句了
for dep in qset4:
print(dep.dep_id, dep.dep_name)
#################################
# 过滤，查找2号部门的员工
qset5 = session.query(Employees).filter(Employees.dep_id == 2)
#数据库查
from dbconn import Employees, Departments, Session
session = Session()
query1 = session.query(Departments)
print(query1)
# for dep in query1:
# print(dep) #返回的是Departments的实例
# print(dep.dep_id,dep.dep_name)
# print('-'*30)
#################################################
# query2 = session.query(Employees.name,Employees.phone)
# print(query2)
# for item in query2:
# print(item)
# for name,phone in query2:
# print('%s:%s'%(name,phone))
#################################################
# query3 = session.query(Departments.dep_name.label('部门'))
# print(query3)
# for dep in query3:
# print(dep.部门)
#################################################
# query4 = session.query(Departments).order_by(Departments.dep_id)
# for dep in query4:
# print(dep)
#################################################
# query5 = session.query(Employees)[2:4] #切片后查询到的不是实例了，而是由实例构成的列表
# print(query5)
#################################################
wj = Employees(
emp_id=1,
name='王俊',
gender='男',
phone='15678789090',
email='*****@*****.**',
dep_id=3
)
wwc = Employees(
emp_id=2,
name='吴伟超',
gender='男',
phone='13499887755',
email='*****@*****.**',
dep_id=3
)
dzj = Employees(
emp_id=3, name='董枝俊', gender='男', phone='18900998877', email='*****@*****.**', dep_id=3
)
ltd = Employees(emp_id=4, name='李通达', gender='男', phone='13378904567', email='*****@*****.**', dep_id=2)
wxy = Employees(emp_id=5, name='王秀燕', gender='女', phone='15098765432', email='*****@*****.**', dep_id=2)
gq = Employees(emp_id=6, name='高琦', gender='女', phone='15876543212', email='*****@*****.**', dep_id=1)
wzf = Employees(emp_id=7, name='王召飞', gender='男', phone='15609871234', email='*****@*****.**', dep_id=1)
sy = Employees(emp_id=8, name='孙燕', gender='女', phone='18567895435', email='*****@*****.**', dep_id=4)
gpf = Employees(emp_id=9, name='高鹏飞', gender='男', phone='13566889900', email='*****@*****.**', dep_id=2)
emps = [wj, wwc, dzj, ltd, wxy, gq, wzf, sy, gpf]
session = Session()
session.add_all(emps)
session.commit()
session.close()
from dbconn import Session, Salary aug2018 = Salary(date='2018-8-10', emp_id=1, basic=10000, awards=2000) session = Session() session.add(aug2018) session.commit() session.close()
from dbconn import Departments, Employees, Salary, Session # 创建会话实例，用于连接数据库 session = Session() # 创建部门实例 hr = Departments(dep_id=1, dep_name='人事部') ops = Departments(dep_id=2, dep_name='运维部') dev = Departments(dep_id=3, dep_name='开发部') qa = Departments(dep_id=4, dep_name='测试部') finance = Departments(dep_id=5, dep_name='财务部') market = Departments(dep_id=6, dep_name='市场部') sales = Departments(dep_id=7, dep_name='销售部') # 在数据库中创建记录 deps = [hr, ops, dev, qa, finance, market, sales] session.add_all(deps) session.commit() # 确认至数据库 # 关闭会话 session.close()
from dbconn import Employees,Session zhangsan=Employees(emp_id=1,name='张三',gender="男",phone='13311530548',email='*****@*****.**',dep_id=1) lisi=Employees(emp_id=2,name='李四',gender="男",phone='13311530555',email='*****@*****.**',dep_id=2) harry=Employees(emp_id=3,name='harry',gender="女",phone='13311530555',email='*****@*****.**',dep_id=3) alice=Employees(emp_id=4,name='alice',gender="女",phone='13311530555',email='*****@*****.**',dep_id=4) session=Session() session.add_all([zhangsan,lisi,harry,alice]) session.commit() session.close()
# create / retrieve / update / delete from dbconn import Session, Departments, Employees # 创建到数据库的会话连接 session = Session() # 插入数据 # hr = Departments(dep_id=1, dep_name='人事部') # ops = Departments(dep_id=2, dep_name='运维部') # dev = Departments(dep_id=3, dep_name='开发部') # qa = Departments(dep_id=4, dep_name='测试部') # sales = Departments(dep_id=5, dep_name='销售部') # market = Departments(dep_id=6, dep_name='市场部') # session.add_all([hr, ops, dev, qa, sales, market]) # lb = Employees( # emp_id=1, emp_name='刘备', # birth_date='1982-3-15', email='*****@*****.**', dep_id=1 # ) # km = Employees( # emp_id=2, emp_name='孔明', # birth_date='1983-8-22', email='*****@*****.**', dep_id=1 # ) # zf = Employees( # emp_id=3, emp_name='张飞', # birth_date='1988-10-1', email='*****@*****.**', dep_id=2 # ) # gy = Employees( # emp_id=4, emp_name='关羽', # birth_date='1986-7-19', email='*****@*****.**', dep_id=3 # )
#-*-coding:utf-8-*- from dbconn import Department, Employees, Salary, Session session = Session() # hr =Department(dep_id=1,dep_name='HR') # 创建实例,一个实例一条记录 ##session.add(hr) #将表记录添加到数据库 ## session.commit() #确认提交 # ops = Department(dep_id=2, dep_name='运维部') # dev = Department(dep_id=3, dep_name='开发部') # qs = Department(dep_id=4, dep_name='测试部') # fn = Department(dep_name='财务部') # sqlalchemy把主键设置为了自动增长 # session.add_all([hr,ops, dev, qs, fn]) #批量添加记录 # session.commit() ################################################## # tc = Employees(emp_id=100, emp_name='田超', email='*****@*****.**', dep_id=1) # by = Employees(emp_id=101, emp_name='白宇', email='*****@*****.**', dep_id=2) # bss = Employees(emp_id=200, emp_name='白尚松', email='*****@*****.**', dep_id=2) # hdy = Employees(emp_id=201, emp_name='何东阳', email='*****@*****.**', dep_id=3) # fg = Employees(emp_id=300, emp_name='房果', email='*****@*****.**', dep_id=2) # wfh = Employees(emp_id=301, emp_name='巫菲红', email='*****@*****.**', dep_id=1) # fcw = Employees(emp_id=302, emp_name='冯翠雯', email='*****@*****.**', dep_id=4) # sz = Employees(emp_id=400, emp_name='孙正', email='*****@*****.**', dep_id=2) # zqh = Employees(emp_id=401, emp_name='章勤浩', email='*****@*****.**', dep_id=3) # cjl = Employees(emp_id=500, emp_name='陈佳乐', email='*****@*****.**', dep_id=5) # hpw = Employees(emp_id=600, emp_name='黄平武', email='*****@*****.**', dep_id=4) # bh = Employees(emp_id=700, emp_name='柏宏', email='*****@*****.**', dep_id=2) # tr = Employees(emp_id=800, emp_name='唐瑞', email='*****@*****.**', dep_id=5) # lj = Employees(emp_id=900, emp_name='鲁俊', email='*****@*****.**', dep_id=3)
#!/usr/bin/env python3 # -*- coding:utf-8 -*- from dbconn import Departments, Session, Employees, Salary session = Session() #创建session会话 dev = Departments(dep_name='development') print(dev.dep_name) print(dev.dep_id) dev = Departments(dep_name="人事部", dep_id=2) session.add(dev) #添加一个 ops = Departments(dep_name="运维部") finance = Departments(dep_name="财务部") session.add_all([ops, finance]) #添加多个 session.commit() #print(dev.dep_id) session.close()
from dbconn import Session, Departments, Employees, Salary # 创建会话实例，将来对数据库实现增删改查，都是通过session的方法实现 session = Session() ################################## # 创建部门 # hr = Departments(dep_id=1, dep_name='人事部') # finance = Departments(dep_id=2, dep_name='财务部') # ops = Departments(dep_id=3, dep_name='运维部') # dev = Departments(dep_id=4, dep_name='开发部') # qa = Departments(dep_id=5, dep_name='测试部') # market = Departments(dep_id=6, dep_name='市场部') # session.add_all([hr, finance, ops, dev, qa, market]) ################################## # 创建员工 # lb = Employees( # emp_id=1, emp_name='刘备', birth_date='1970-9-10', # email='*****@*****.**', dep_id=1 # ) # zgl = Employees( # emp_id=2, emp_name='诸葛亮', birth_date='1973-8-9', # email='*****@*****.**', dep_id=2 # ) # gy = Employees( # emp_id=3, emp_name='关羽', birth_date='1990-5-12', # email='*****@*****.**', dep_id=3 # ) # zf = Employees( # emp_id=4, emp_name='张飞', birth_date='1991－4－18', # email='*****@*****.**', dep_id=3 # )
from dbconn import Session, Departments, Employees
# 1. 创建一个会话实例
session = Session()
#################################
# 查询数据库，返回实体类的实例
# qset1 = session.query(Departments)
# print(qset1) # 此时只是一条SQL语句，不真正连接数据库
# print(list(qset1)) # 取值的时候，才会连接数据库
# for dep in qset1:
# print('部门ID: %s, 部门名称: %s' % (dep.dep_id, dep.dep_name))
#################################
# 如果查询某些字段，返回的是元组
# qset2 = session.query(Employees.emp_name, Employees.email)
# print(qset2) # qset2是SQL语句
# print(list(qset2)) # 取值是元组
#################################
# 排序，可以对执行结果进一步操作
# qset3 = session.query(Departments).order_by(Departments.dep_id)
# for dep in qset3:
# print(dep.dep_id, dep.dep_name)
#################################
# 排序，取切片
# qset4 = session.query(Departments).order_by(Departments.dep_id)[2:4]
# print(qset4) # 因为qset4执行了切片取值，所以它不是sql语句了
# for dep in qset4:
# print(dep.dep_id, dep.dep_name)
#################################
# 过滤，查找2号部门的员工
# qset5 = session.query(Employees).filter(Employees.dep_id==2)
# create / retrieve / update / delete from dbconn import Departments, employees, salary, Session # 创建到数据库的会话实例 session = Session() # 执行增删改查操作 # hr = Departments(dep_id=1, dep_name='人事部') # ops = Departments(dep_id=2, dep_name='运维部') # dev = Departments(dep_id=3, dep_name='开发部') # qa = Departments(dep_id=4, dep_name='测试部') # market = Departments(dep_id=5, dep_name='市场部') # sales = Departments(dep_id=6, dep_name='销售部') # session.add_all([hr, ops, dev, qa, market, sales]) # u1 = employees( # emp_id=1, emp_name='涂文良', # email='*****@*****.**', dep_id=2 # ) # u2 = employees( # emp_id=2, emp_name='刘常艳', # email='*****@*****.**', dep_id=3 # ) # # session.add_all([u1, u2]) # # qset1 =session.query(Departments) # print(qset1) # for dep in qset1: # # print(dep.dep_id, dep.dep_name)
