from myorm import Departments, session q = session.query(Departments).get(1) # get的参数是表的主键 q.dep_name = 'HR' session.commit()
from myorm import Departments, session
q = session.query(Departments).filter(Departments.dep_name == '人事部')
q.update({Departments.dep_name: '人力资源部'})
session.commit()
from myorm import Employees, session
# select * from employees where emp_name='bob' or emp_name='alice';
# select * from employees where emp_name in ('bob', 'alice');
for row in session.query(Employees).\
filter(Employees.emp_name.in_(['bob', 'alice'])):
print(row.emp_name, row.phone)
# select * from employees where emp_name not in ('bob', 'alice');
for row in session.query(Employees).\
filter(~Employees.emp_name.in_(['bob', 'alice'])):
print(row.emp_name, row.phone)
# select * from employees where phone is null;
for row in session.query(Employees).\
filter(Employees.phone.is_(None)):
print(row.emp_name)
# select * from employees where phone is not null;
for row in session.query(Employees).\
filter(Employees.phone.isnot(None)):
print(row.emp_name)
from myorm import Salary, session
from sqlalchemy import and_, or_
# select * from salary where award>4500 and basic>10000;
for row in session.query(Salary).\
filter(and_(Salary.award>4500, Salary.basic>10000)):
print(row.emp_id, row.basic, row.award)
# select * from salary where basic>10000 or award>4500;
for row in session.query(Salary).\
filter(or_(Salary.basic>10000, Salary.award>4500)):
print(row.emp_id, row.basic, row.award)
from myorm import Employees, Departments, Salary, session
# select count(*) from employees;
print(session.query(Employees).count())
# select e.emp_name, d.dep_name from employees as e join departments as d on e.dep_id=d.dep_id;
q = session.query(Employees.emp_name, Departments.dep_name).\
join(Departments, Employees.dep_id==Departments.dep_id)
print(q.all())
q2 = session.query(Employees.emp_name, Salary.date, Salary.basic+Salary.award).\
join(Salary, Employees.emp_id==Salary.emp_id)
print(q2.all())
# >>> user1 = collections.namedtuple('user1', ['name', 'age'])
# >>> bob = user1('Bob Green', 25)
# >>> bob[0]
# 'Bob Green'
# >>> bob[1]
# 25
# >>> bob.name
# 'Bob Green'
# >>> bob.age
# 25
from myorm import Departments, session
for row in session.query(Departments, Departments.dep_name):
print(row.dep_name)
# select dep_name as 部门 from departments;
for row in session.query(Departments.dep_name.label('部门')):
print(row.部门)
# select * from departments order by dep_id
for row in session.query(Departments).order_by(Departments.dep_id):
print(row.dep_id, row.dep_name)
# select * from departments order by dep_id desc;
for row in session.query(Departments).order_by(Departments.dep_id.desc()):
print(row.dep_id, row.dep_name)
from myorm import Employees, session
# all返回的是列表
result = session.query(Employees.emp_name, Employees.phone).all()
print(result)
# first返回符合条件的第一个记录
result = session.query(Employees.emp_name, Employees.phone).\
filter(Employees.emp_name.like('李%')).first()
print(result)
# one要求查询只能有一条记录，否则报错
result = session.query(Employees.emp_name, Employees.phone).\
filter(Employees.emp_name.like('李__')).one()
print(result)
#calar调用one，返回第一列
result = session.query(Employees.emp_name, Employees.phone).\
filter(Employees.emp_name.like('李__')).scalar()
print(result)
from myorm import Departments, Salary, Employees, session
# select * from departments order by dep_id limit 1, 3;?
for row in session.query(Departments).order_by(Departments.dep_id)[1:4]:
print(row.dep_id, row.dep_name)
# select * from departments where dep_id=2;
for row in session.query(Departments).filter(Departments.dep_id == 2):
print(row.dep_id, row.dep_name)
# select * from salary where award>4000 and basic>10000;
for row in session.query(Salary).\
filter(Salary.award>4000).filter(Salary.basic>10000):
print(row.emp_id, row.basic, row.award)
# select * from employees where emp_name like '李%';
for row in session.query(Employees).\
filter(Employees.emp_name.like('李%')):
print(row.emp_name)
# select * from employees where emp_name like '李_';
for row in session.query(Employees).\
filter(Employees.emp_name.like('李_')):
print(row.emp_name)
from myorm import Departments, Employees, session
# select * from departments
for instance in session.query(Departments):
print(instance)
print(instance.dep_id, ':', instance.dep_name)
# select * from departments order by dep_id
for instance in session.query(Departments).order_by(Departments.dep_id):
print(instance.dep_id, ':', instance.dep_name)
# select emp_name, phone from employees;
for name, phone in session.query(Employees.emp_name, Employees.phone):
print('%s: %s' % (name, phone))
from myorm import Departments, session q = session.query(Departments).get(4) session.delete(q) session.commit() # dep_finance = Departments(dep_name='财务部', dep_id=4) # session.add(dep_finance) # session.commit()