#!/usr/bin/env python3 #--coding: utf8-- from sqlalchemy.orm import aliased from d12_04 import Employees, session new_emp = aliased(Employees) query = session.query(new_emp).filter(new_emp.dep_id == 5) print(query.count())
#!/usr/bin/env python3 #--coding: utf8-- from d12_04 import Departments, session dep_yy = session.query(Departments).get(8) print(dep_yy) dep_yy.dep_name = '运营部' session.commit() session.close()
#!/usr/bin/env python3
#--coding: utf8--
from d12_04 import Salary, session
from sqlalchemy.orm import aliased
new_sal = aliased(Salary)
for empid in session.query(new_sal.emp_id).filter(new_sal.basic > 8000).filter(
new_sal.extra > 1000):
print(empid)
session.close()
#!/usr/bin/env python3
#--coding: utf8--
from d12_04 import Departments, session
for row in session.query(Departments.dep_name.label('部门')):
print(row.部门)
session.close()
#!/usr/bin/env python3
#--coding: utf8--
from sqlalchemy.orm import sessionmaker, aliased
from d12_04 import session, Employees, Departments, Salary
from sqlalchemy import and_, or_
new_emp = aliased(Employees)
#等于
for row in session.query(new_emp.name, new_emp.phone).filter(new_emp.name == 'john'):
print(row.name, row.phone)
print('#' * 50)
#不等于
for row in session.query(new_emp.name, new_emp.phone).filter(new_emp.name != 'john'):
print(row.name, row.phone)
print('#' * 50)
#模糊查询
for row in session.query(new_emp.name, new_emp.phone).filter(new_emp.name.like('j%')):
print(row.name, row.phone)
print('#' * 50)
#并列查询
for row in session.query(new_emp.name, new_emp.phone).filter(new_emp.name.in_(['bob', 'john'])):
print(row.name, row.phone)
print('#' * 50)
#并列不在
for row in session.query(new_emp.name, new_emp.phone).filter(~new_emp.name.in_(['bob', 'john'])):
print(row.name, row.phone)
print('#' * 50)
#空
for row in session.query(new_emp.name, new_emp.phone).filter(new_emp.name.is_(None)):
print(row.name, row.phone)
print('#' * 50)
#!/usr/bin/env python3
#--coding: utf8--
from d12_04 import Employees, session
from sqlalchemy.orm import aliased
new_emp = aliased(Employees)
for name, phone in session.query(new_emp.name, new_emp.phone):
print(name, phone)
session.close()
#!/usr/bin/env python3
#--coding: utf8--
from d12_04 import Departments, session
from sqlalchemy.orm import aliased
new_dep = aliased(Departments)
for dep_id, dep_name in session.query(new_dep.dep_id,
new_dep.dep_name).\
order_by(new_dep.dep_id)[1:4]: #类似于列表
print(dep_id, '\t', dep_name)
session.close()
#!/usr/bin/env python3
#--coding: utf8--
from d12_04 import Employees, session
from sqlalchemy.orm import aliased
new_dep = aliased(Employees)
result = {}
for name, phone in session.query(new_dep.name, new_dep.phone).\
filter(new_dep.dep_id == 5):
result[name] = phone
print(result)
session.close()
#!/usr/bin/env python3
#--coding: utf8--
from d12_04 import Departments, session
for row in session.query(Departments, Departments.dep_name):
print(row.Departments, row.dep_name)
session.close()
#!/usr/bin/env python3
#--coding: utf8--
#更新字段的时候不能用别名
from d12_04 import Departments, session
query = session.query(Departments).filter(Departments.dep_id == 8)
query.update({Departments.dep_name:'运营'})
session.commit()
session.close()
#!/usr/bin/env python3 #--coding: utf8-- from d12_04 import Employees, session from sqlalchemy.orm import aliased new_emp = aliased(Employees) query = session.query(new_emp.name, new_emp.phone).order_by(new_emp.emp_id) print(query) print() print(query.all()) print() print(query.first()) print() query2 = session.query(new_emp.name, new_emp.phone).filter(new_emp.emp_id == 4) print(query2.one()) print() print(query2.scalar()) session.close()
#!/usr/bin/env python3
#--coding: utf8--
from sqlalchemy.orm import aliased
from d12_04 import Departments, session, Employees
new_emp = aliased(Employees)
new_dep = aliased(Departments)
query = session.query(new_emp.name,
new_dep.dep_name).join(new_dep,
new_emp.dep_id == new_dep.dep_id)
print(query.all())