实用：python中sqlalchemy中mysql数据库的简单查询和复杂查询

import sqlalchemy
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column,Integer,String,inspect,DATE,Enum,and_,or_,not_
from sqlalchemy.orm import sessionmaker

Base = declarative_base()

def show(emps):
    for x in emps:
        print(x)
    print('',end='\n\n')

class MyEnum(Enum):
    M = 'M'
    F = 'F'


class Employee(Base):
    __tablename__ = 'employees'
    emp_no = Column(Integer,primary_key=True)
    birth_date = Column(DATE,nullable=False)
    first_name = Column(String(14),nullable=False)
    last_name = Column(String(16),nullable=False)
    # gender = Column(MyEnum())
    hire_date = Column(DATE,nullable=False)

    def __repr__(self):
        return '<{} emp_no:{} name:{}>'.format(self.__class__.__name__,self.emp_no,'{}.{}'.format(self.first_name,self.last_name))

    __str__ = __repr__


host = '172.20.10.11'
port = '3306'
user = 'root'
passwd = '123456'
database = 'test'


conn_str = 'mysql+pymysql://{}:{}@{}:{}/{}'.format(user,passwd,host,port,database)
engine = sqlalchemy.create_engine(conn_str,echo=True)
Base.metadata.create_all(engine)
Session = sessionmaker()
session = Session(bind=engine)

#简单条件查询
print('#############################################################')
emps = session.query(Employee).filter(Employee.emp_no > 10015)
show(emps)

#复杂条件查询
#1.AND
print('-------------------------------------------------------------')
emps = session.query(Employee).filter(Employee.emp_no > 10015).filter(Employee.emp_no < 10018)
show(emps)
print('-------------------------------------------------------------')
emps = session.query(Employee).filter(and_(Employee.emp_no > 10015,Employee.emp_no < 10018))
show(emps)
print('-------------------------------------------------------------')
emps = session.query(Employee).filter((Employee.emp_no > 10015) & (Employee.emp_no < 10018))
show(emps)

#2.OR
print('+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++')
emps = session.query(Employee).filter(or_(Employee.emp_no > 10018,Employee.emp_no < 10003))
show(emps)
print('+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++')
emps = session.query(Employee).filter((Employee.emp_no > 10018) | (Employee.emp_no < 10003))
show(emps)

#3.NOT
print('@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@')
emps = session.query(Employee).filter(not_(Employee.emp_no < 10018))
show(emps)
print('@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@')
emps = session.query(Employee).filter(~(Employee.emp_no < 10018))
show(emps)


#4.IN
print('^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^')
emplist = [10010,10015,10018]
emps = session.query(Employee).filter(Employee.emp_no.in_(emplist))
show(emps)

#5.NOT IN
print('^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^')
emplist = [10010,10015,10018]
emps = session.query(Employee).filter(~Employee.emp_no.in_(emplist))
show(emps)

#6.LIKE
print('!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!')
emps = session.query(Employee).filter(Employee.last_name.like('P%'))
show(emps)

#7.ASC（升序）
print('$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$')
emps = session.query(Employee).filter(Employee.emp_no > 10010).order_by(Employee.emp_no)
show(emps)
print('$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$')
emps = session.query(Employee).filter(Employee.emp_no > 10010).order_by(Employee.emp_no.asc())
show(emps)

#8.DESC（降序）
print('$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$')
emps = session.query(Employee).filter(Employee.emp_no > 10010).order_by(Employee.emp_no.desc())
show(emps)

#9.多列排序
print('$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$')
emps = session.query(Employee).filter(Employee.emp_no > 10010).order_by(Employee.last_name).order_by(Employee.emp_no.desc())
show(emps)

#10.分页
print('***********************************************************')
emps = session.query(Employee).limit(4)
show(emps)
print('***********************************************************')
emps = session.query(Employee).limit(4).offset(18)
show(emps)

#11.消费者方法
#总行数（聚合函数）
print('???????????????????????????????????????????????????????????')
emps = session.query(Employee)
print(len(list(emps)))
print('???????????????????????????????????????????????????????????')
print(emps.count())
#取所有数据
print('???????????????????????????????????????????????????????????')
print(emps.all())
#取一行
# print(emps.one()) #返回一行，如果查询结果为多行则抛异常
print('???????????????????????????????????????????????????????????')
print(emps.limit(1).one())
#删除delete by query
print('???????????????????????????????????????????????????????????')
session.query(Employee).filter(Employee.emp_no > 10018).delete()
# session.commit() #提交则删除

运行结果：

2019-08-04 10:25:26,329 INFO sqlalchemy.engine.base.Engine SHOW VARIABLES LIKE 'sql_mode'
2019-08-04 10:25:26,329 INFO sqlalchemy.engine.base.Engine {}
2019-08-04 10:25:26,331 INFO sqlalchemy.engine.base.Engine SHOW VARIABLES LIKE 'lower_case_table_names'
2019-08-04 10:25:26,331 INFO sqlalchemy.engine.base.Engine {}
2019-08-04 10:25:26,334 INFO sqlalchemy.engine.base.Engine SELECT DATABASE()
2019-08-04 10:25:26,334 INFO sqlalchemy.engine.base.Engine {}
2019-08-04 10:25:26,336 INFO sqlalchemy.engine.base.Engine show collation where `Charset` = 'utf8mb4' and `Collation` = 'utf8mb4_bin'
2019-08-04 10:25:26,337 INFO sqlalchemy.engine.base.Engine {}
2019-08-04 10:25:26,339 INFO sqlalchemy.engine.base.Engine SELECT CAST('test plain returns' AS CHAR(60)) AS anon_1
2019-08-04 10:25:26,339 INFO sqlalchemy.engine.base.Engine {}
2019-08-04 10:25:26,341 INFO sqlalchemy.engine.base.Engine SELECT CAST('test unicode returns' AS CHAR(60)) AS anon_1
2019-08-04 10:25:26,341 INFO sqlalchemy.engine.base.Engine {}
2019-08-04 10:25:26,342 INFO sqlalchemy.engine.base.Engine SELECT CAST('test collated returns' AS CHAR CHARACTER SET utf8mb4) COLLATE utf8mb4_bin AS anon_1
2019-08-04 10:25:26,342 INFO sqlalchemy.engine.base.Engine {}
2019-08-04 10:25:26,344 INFO sqlalchemy.engine.base.Engine DESCRIBE `employees`
2019-08-04 10:25:26,344 INFO sqlalchemy.engine.base.Engine {}
#############################################################
2019-08-04 10:25:26,350 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)
2019-08-04 10:25:26,351 INFO sqlalchemy.engine.base.Engine SELECT employees.emp_no AS employees_emp_no, employees.birth_date AS employees_birth_date, employees.first_name AS employees_first_name, employees.last_name AS employees_last_name, employees.hire_date AS employees_hire_date 
FROM employees 
WHERE employees.emp_no > %(emp_no_1)s
2019-08-04 10:25:26,351 INFO sqlalchemy.engine.base.Engine {'emp_no_1': 10015}
<Employee emp_no:10016 name:Kazuhito.Cappelletti>
<Employee emp_no:10017 name:Cristinel.Bouloucos>
<Employee emp_no:10018 name:Kazuhide.Peha>
<Employee emp_no:10019 name:Lillian.Haddadi>
<Employee emp_no:10020 name:Mayuko.Warwick>


-------------------------------------------------------------
2019-08-04 10:25:26,354 INFO sqlalchemy.engine.base.Engine SELECT employees.emp_no AS employees_emp_no, employees.birth_date AS employees_birth_date, employees.first_name AS employees_first_name, employees.last_name AS employees_last_name, employees.hire_date AS employees_hire_date 
FROM employees 
WHERE employees.emp_no > %(emp_no_1)s AND employees.emp_no < %(emp_no_2)s
2019-08-04 10:25:26,354 INFO sqlalchemy.engine.base.Engine {'emp_no_1': 10015, 'emp_no_2': 10018}
<Employee emp_no:10