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)