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

it2025-09-23 52

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:10016 name:Kazuhito.Cappelletti> <Employee emp_no:10017 name:Cristinel.Bouloucos> ------------------------------------------------------------- 2019-08-04 10:25:26,357 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,357 INFO sqlalchemy.engine.base.Engine {'emp_no_1': 10015, 'emp_no_2': 10018} <Employee emp_no:10016 name:Kazuhito.Cappelletti> <Employee emp_no:10017 name:Cristinel.Bouloucos> ------------------------------------------------------------- 2019-08-04 10:25:26,360 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,360 INFO sqlalchemy.engine.base.Engine {'emp_no_1': 10015, 'emp_no_2': 10018} <Employee emp_no:10016 name:Kazuhito.Cappelletti> <Employee emp_no:10017 name:Cristinel.Bouloucos> +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ 2019-08-04 10:25:26,363 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 OR employees.emp_no < %(emp_no_2)s 2019-08-04 10:25:26,363 INFO sqlalchemy.engine.base.Engine {'emp_no_1': 10018, 'emp_no_2': 10003} <Employee emp_no:10001 name:Georgi.Facello> <Employee emp_no:10002 name:Bezalel.Simmel> <Employee emp_no:10019 name:Lillian.Haddadi> <Employee emp_no:10020 name:Mayuko.Warwick> +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ 2019-08-04 10:25:26,366 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 OR employees.emp_no < %(emp_no_2)s 2019-08-04 10:25:26,366 INFO sqlalchemy.engine.base.Engine {'emp_no_1': 10018, 'emp_no_2': 10003} <Employee emp_no:10001 name:Georgi.Facello> <Employee emp_no:10002 name:Bezalel.Simmel> <Employee emp_no:10019 name:Lillian.Haddadi> <Employee emp_no:10020 name:Mayuko.Warwick> @@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@ 2019-08-04 10:25:26,369 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,369 INFO sqlalchemy.engine.base.Engine {'emp_no_1': 10018} <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,372 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,372 INFO sqlalchemy.engine.base.Engine {'emp_no_1': 10018} <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,375 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 IN (%(emp_no_1)s, %(emp_no_2)s, %(emp_no_3)s) 2019-08-04 10:25:26,375 INFO sqlalchemy.engine.base.Engine {'emp_no_1': 10010, 'emp_no_3': 10018, 'emp_no_2': 10015} <Employee emp_no:10010 name:Duangkaew.Piveteau> <Employee emp_no:10015 name:Guoxiang.Nooteboom> <Employee emp_no:10018 name:Kazuhide.Peha> ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ 2019-08-04 10:25:26,378 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 NOT IN (%(emp_no_1)s, %(emp_no_2)s, %(emp_no_3)s) 2019-08-04 10:25:26,378 INFO sqlalchemy.engine.base.Engine {'emp_no_1': 10010, 'emp_no_3': 10018, 'emp_no_2': 10015} <Employee emp_no:10001 name:Georgi.Facello> <Employee emp_no:10002 name:Bezalel.Simmel> <Employee emp_no:10003 name:Parto.Bamford> <Employee emp_no:10004 name:Chirstian.Koblick> <Employee emp_no:10005 name:Kyoichi.Maliniak> <Employee emp_no:10006 name:Anneke.Preusig> <Employee emp_no:10007 name:Tzvetan.Zielinski> <Employee emp_no:10008 name:Saniya.Kalloufi> <Employee emp_no:10009 name:Sumant.Peac> <Employee emp_no:10011 name:Mary.Sluis> <Employee emp_no:10012 name:Patricio.Bridgland> <Employee emp_no:10013 name:Eberhardt.Terkki> <Employee emp_no:10014 name:Berni.Genin> <Employee emp_no:10016 name:Kazuhito.Cappelletti> <Employee emp_no:10017 name:Cristinel.Bouloucos> <Employee emp_no:10019 name:Lillian.Haddadi> <Employee emp_no:10020 name:Mayuko.Warwick> !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! 2019-08-04 10:25:26,382 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.last_name LIKE %(last_name_1)s 2019-08-04 10:25:26,382 INFO sqlalchemy.engine.base.Engine {'last_name_1': 'P%'} <Employee emp_no:10006 name:Anneke.Preusig> <Employee emp_no:10009 name:Sumant.Peac> <Employee emp_no:10010 name:Duangkaew.Piveteau> <Employee emp_no:10018 name:Kazuhide.Peha> $$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$ 2019-08-04 10:25:26,385 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 ORDER BY employees.emp_no 2019-08-04 10:25:26,385 INFO sqlalchemy.engine.base.Engine {'emp_no_1': 10010} <Employee emp_no:10011 name:Mary.Sluis> <Employee emp_no:10012 name:Patricio.Bridgland> <Employee emp_no:10013 name:Eberhardt.Terkki> <Employee emp_no:10014 name:Berni.Genin> <Employee emp_no:10015 name:Guoxiang.Nooteboom> <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,388 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 ORDER BY employees.emp_no ASC 2019-08-04 10:25:26,388 INFO sqlalchemy.engine.base.Engine {'emp_no_1': 10010} <Employee emp_no:10011 name:Mary.Sluis> <Employee emp_no:10012 name:Patricio.Bridgland> <Employee emp_no:10013 name:Eberhardt.Terkki> <Employee emp_no:10014 name:Berni.Genin> <Employee emp_no:10015 name:Guoxiang.Nooteboom> <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,391 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 ORDER BY employees.emp_no DESC 2019-08-04 10:25:26,392 INFO sqlalchemy.engine.base.Engine {'emp_no_1': 10010} <Employee emp_no:10020 name:Mayuko.Warwick> <Employee emp_no:10019 name:Lillian.Haddadi> <Employee emp_no:10018 name:Kazuhide.Peha> <Employee emp_no:10017 name:Cristinel.Bouloucos> <Employee emp_no:10016 name:Kazuhito.Cappelletti> <Employee emp_no:10015 name:Guoxiang.Nooteboom> <Employee emp_no:10014 name:Berni.Genin> <Employee emp_no:10013 name:Eberhardt.Terkki> <Employee emp_no:10012 name:Patricio.Bridgland> <Employee emp_no:10011 name:Mary.Sluis> $$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$ 2019-08-04 10:25:26,395 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 ORDER BY employees.last_name, employees.emp_no DESC 2019-08-04 10:25:26,395 INFO sqlalchemy.engine.base.Engine {'emp_no_1': 10010} <Employee emp_no:10017 name:Cristinel.Bouloucos> <Employee emp_no:10012 name:Patricio.Bridgland> <Employee emp_no:10016 name:Kazuhito.Cappelletti> <Employee emp_no:10014 name:Berni.Genin> <Employee emp_no:10019 name:Lillian.Haddadi> <Employee emp_no:10015 name:Guoxiang.Nooteboom> <Employee emp_no:10018 name:Kazuhide.Peha> <Employee emp_no:10011 name:Mary.Sluis> <Employee emp_no:10013 name:Eberhardt.Terkki> <Employee emp_no:10020 name:Mayuko.Warwick> *********************************************************** 2019-08-04 10:25:26,398 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 LIMIT %(param_1)s 2019-08-04 10:25:26,399 INFO sqlalchemy.engine.base.Engine {'param_1': 4} <Employee emp_no:10001 name:Georgi.Facello> <Employee emp_no:10002 name:Bezalel.Simmel> <Employee emp_no:10003 name:Parto.Bamford> <Employee emp_no:10004 name:Chirstian.Koblick> *********************************************************** 2019-08-04 10:25:26,401 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 LIMIT %(param_1)s, %(param_2)s 2019-08-04 10:25:26,401 INFO sqlalchemy.engine.base.Engine {'param_1': 18, 'param_2': 4} <Employee emp_no:10019 name:Lillian.Haddadi> <Employee emp_no:10020 name:Mayuko.Warwick> ??????????????????????????????????????????????????????????? 2019-08-04 10:25:26,404 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 2019-08-04 10:25:26,404 INFO sqlalchemy.engine.base.Engine {} 20 ??????????????????????????????????????????????????????????? 2019-08-04 10:25:26,409 INFO sqlalchemy.engine.base.Engine SELECT count(*) AS count_1 FROM (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) AS anon_1 2019-08-04 10:25:26,409 INFO sqlalchemy.engine.base.Engine {} 20 ??????????????????????????????????????????????????????????? 2019-08-04 10:25:26,411 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 2019-08-04 10:25:26,411 INFO sqlalchemy.engine.base.Engine {} [<Employee emp_no:10001 name:Georgi.Facello>, <Employee emp_no:10002 name:Bezalel.Simmel>, <Employee emp_no:10003 name:Parto.Bamford>, <Employee emp_no:10004 name:Chirstian.Koblick>, <Employee emp_no:10005 name:Kyoichi.Maliniak>, <Employee emp_no:10006 name:Anneke.Preusig>, <Employee emp_no:10007 name:Tzvetan.Zielinski>, <Employee emp_no:10008 name:Saniya.Kalloufi>, <Employee emp_no:10009 name:Sumant.Peac>, <Employee emp_no:10010 name:Duangkaew.Piveteau>, <Employee emp_no:10011 name:Mary.Sluis>, <Employee emp_no:10012 name:Patricio.Bridgland>, <Employee emp_no:10013 name:Eberhardt.Terkki>, <Employee emp_no:10014 name:Berni.Genin>, <Employee emp_no:10015 name:Guoxiang.Nooteboom>, <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,415 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 LIMIT %(param_1)s 2019-08-04 10:25:26,415 INFO sqlalchemy.engine.base.Engine {'param_1': 1} <Employee emp_no:10001 name:Georgi.Facello> ??????????????????????????????????????????????????????????? 2019-08-04 10:25:26,417 INFO sqlalchemy.engine.base.Engine DELETE FROM employees WHERE employees.emp_no > %(emp_no_1)s 2019-08-04 10:25:26,417 INFO sqlalchemy.engine.base.Engine {'emp_no_1': 10018}

改进：（聚合函数与分组）

运行结果：

2019-08-04 11:00:05,530 INFO sqlalchemy.engine.base.Engine SHOW VARIABLES LIKE 'sql_mode' 2019-08-04 11:00:05,531 INFO sqlalchemy.engine.base.Engine {} 2019-08-04 11:00:05,532 INFO sqlalchemy.engine.base.Engine SHOW VARIABLES LIKE 'lower_case_table_names' 2019-08-04 11:00:05,532 INFO sqlalchemy.engine.base.Engine {} 2019-08-04 11:00:05,534 INFO sqlalchemy.engine.base.Engine SELECT DATABASE() 2019-08-04 11:00:05,535 INFO sqlalchemy.engine.base.Engine {} 2019-08-04 11:00:05,536 INFO sqlalchemy.engine.base.Engine show collation where `Charset` = 'utf8mb4' and `Collation` = 'utf8mb4_bin' 2019-08-04 11:00:05,536 INFO sqlalchemy.engine.base.Engine {} 2019-08-04 11:00:05,539 INFO sqlalchemy.engine.base.Engine SELECT CAST('test plain returns' AS CHAR(60)) AS anon_1 2019-08-04 11:00:05,539 INFO sqlalchemy.engine.base.Engine {} 2019-08-04 11:00:05,541 INFO sqlalchemy.engine.base.Engine SELECT CAST('test unicode returns' AS CHAR(60)) AS anon_1 2019-08-04 11:00:05,541 INFO sqlalchemy.engine.base.Engine {} 2019-08-04 11:00:05,542 INFO sqlalchemy.engine.base.Engine SELECT CAST('test collated returns' AS CHAR CHARACTER SET utf8mb4) COLLATE utf8mb4_bin AS anon_1 2019-08-04 11:00:05,542 INFO sqlalchemy.engine.base.Engine {} 2019-08-04 11:00:05,545 INFO sqlalchemy.engine.base.Engine DESCRIBE `employees` 2019-08-04 11:00:05,545 INFO sqlalchemy.engine.base.Engine {} ------------------------------------------------ 2019-08-04 11:00:05,553 INFO sqlalchemy.engine.base.Engine BEGIN (implicit) 2019-08-04 11:00:05,553 INFO sqlalchemy.engine.base.Engine SELECT count(employees.emp_no) AS count_1 FROM employees 2019-08-04 11:00:05,554 INFO sqlalchemy.engine.base.Engine {} (20,) 2019-08-04 11:00:05,556 INFO sqlalchemy.engine.base.Engine SELECT count(employees.emp_no) AS count_1 FROM employees 2019-08-04 11:00:05,556 INFO sqlalchemy.engine.base.Engine {} 20 ------------------------------------------------ 2019-08-04 11:00:05,559 INFO sqlalchemy.engine.base.Engine SELECT max(employees.emp_no) AS max_1 FROM employees 2019-08-04 11:00:05,559 INFO sqlalchemy.engine.base.Engine {} 10020 ------------------------------------------------ 2019-08-04 11:00:05,561 INFO sqlalchemy.engine.base.Engine SELECT min(employees.emp_no) AS min_1 FROM employees 2019-08-04 11:00:05,561 INFO sqlalchemy.engine.base.Engine {} 10001 ------------------------------------------------ 2019-08-04 11:00:05,563 INFO sqlalchemy.engine.base.Engine SELECT avg(employees.emp_no) AS avg_1 FROM employees 2019-08-04 11:00:05,564 INFO sqlalchemy.engine.base.Engine {} 10010.5000 2019-08-04 11:00:05,566 INFO sqlalchemy.engine.base.Engine SELECT count(employees.emp_no) AS count_1 FROM employees GROUP BY employees.emp_no 2019-08-04 11:00:05,566 INFO sqlalchemy.engine.base.Engine {} [(1,), (1,), (1,), (1,), (1,), (1,), (1,), (1,), (1,), (1,), (1,), (1,), (1,), (1,), (1,), (1,), (1,), (1,), (1,), (1,)]

改进：（关联查询）

import sqlalchemy from sqlalchemy.ext.declarative import declarative_base from sqlalchemy import Column,Integer,String,inspect,DATE,Enum,and_,or_,not_,ForeignKey from sqlalchemy.orm import sessionmaker,relationships from sqlalchemy import func import enum Base = declarative_base() def show(emps): for x in emps: print(x) print('',end='\n\n') class MyEnum(enum.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(Enum(MyEnum),nullable=False) hire_date = Column(DATE,nullable=False) # depts = relationships('Dept_emp') #关联要使用“类名引号+外键”此处不写外键是由于表定义中已经设置了外键的定义 def __repr__(self): return '<{} emp_no:{} name:{} gender={}>'.format(self.__class__.__name__,self.emp_no,'{}.{}'.format(self.first_name,self.last_name),self.gender.value) __str__ = __repr__ class Department(Base): __tablename__ = 'departments' dept_no = Column(String(4),primary_key=True) dept_name = Column(String(40),nullable=False,unique=True) def __repr__(self): return '{} no={} name={}'.format(type(self).__name,self.dept_no,self.dept_name) class Dept_emp(Base): __tablename__ = 'dept_emp' emp_no = Column(Integer,ForeignKey('emplopyees.emp_no'),primary_key=True) #外键中要使用“表名+字段” dept_no = Column(String(4),ForeignKey('departments.dept_no',ondelete='CASCADE'),primary_key=True) from_date = Column(DATE,nullable=False) to_date = Column(DATE,nullable=False) def __repr__(self): return '{} no={} name={}'.format(type(self).__name__,self.emp_no,self.dept_no) 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('----------------------------------------------------------------------') results = session.query(Employee,Dept_emp).filter(Employee.emp_no == Dept_emp.emp_no).filter(Employee.emp_no == 10010).all()#查询使用“类名+属性” print(results) #JOIN print('----------------------------------------------------------------------') results = session.query(Employee,Dept_emp).join(Dept_emp,Employee.emp_no == Dept_emp.emp_no).filter(Employee.emp_no == 10010).all() print(results) results = session.query(func.count(Employee.emp_no)).join(Dept_emp,Employee.emp_no == Dept_emp.emp_no).filter(Employee.emp_no == 10010).all() print(results)#上面的结果只显示一个值（因为以employee表各字段的值来显示自动去重了，但从统计结果看有2个值）

解决两条结果一样被自动去重的问题（关联查询显示所有结果）

import sqlalchemy from sqlalchemy.ext.declarative import declarative_base from sqlalchemy import Column,Integer,String,inspect,Date,Enum,and_,or_,not_,ForeignKey from sqlalchemy.orm import sessionmaker,relationship from sqlalchemy import func import enum Base = declarative_base() def show(emps): for x in emps: print(x) print('',end='\n\n') class MyEnum(enum.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(Enum(MyEnum),nullable=False) hire_date = Column(Date,nullable=False) dept_emps = relationship('Dept_emp') #关联要使用“类名引号+外键”此处不写外键是由于表定义中已经设置了外键的定义 def __repr__(self): return '<{} emp_no:{} name:{} gender={}> relation={}'.format(self.__class__.__name__,self.emp_no,'{}.{}'.format(self.first_name,self.last_name),self.gender.value,self.dept_emps) __str__ = __repr__ class Department(Base): __tablename__ = 'departments' dept_no = Column(String(4),primary_key=True) dept_name = Column(String(40),nullable=False,unique=True) def __repr__(self): return '{} no={} name={}'.format(type(self).__name,self.dept_no,self.dept_name) class Dept_emp(Base): __tablename__ = 'dept_emp' emp_no = Column(Integer, ForeignKey('employees.emp_no',ondelete='CASCADE'),primary_key=True) #外键中要使用“表名+字段” dept_no = Column(String(4),ForeignKey('departments.dept_no',ondelete='CASCADE'),primary_key=True) from_date = Column(Date,nullable=False) to_date = Column(Date,nullable=False) def __repr__(self): return '{} no={} name={}'.format(type(self).__name__,self.emp_no,self.dept_no) 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('----------------------------------------------------------------------') results = session.query(Employee).join(Dept_emp).filter(Employee.emp_no == Dept_emp.emp_no).filter(Employee.emp_no == 10010).all() print(results) #第二种 print('----------------------------------------------------------------------') results = session.query(Employee).join(Dept_emp,Employee.emp_no == Dept_emp.emp_no).filter(Employee.emp_no == 10010).all() print(results) #第三种 print('----------------------------------------------------------------------') results = session.query(Employee).join(Dept_emp,(Employee.emp_no == Dept_emp.emp_no) & (Employee.emp_no == 10010)).all() print(results)

运行结果：

2019-08-04 15:58:59,923 INFO sqlalchemy.engine.base.Engine SHOW VARIABLES LIKE 'sql_mode' 2019-08-04 15:58:59,924 INFO sqlalchemy.engine.base.Engine {} 2019-08-04 15:58:59,925 INFO sqlalchemy.engine.base.Engine SHOW VARIABLES LIKE 'lower_case_table_names' 2019-08-04 15:58:59,926 INFO sqlalchemy.engine.base.Engine {} 2019-08-04 15:58:59,928 INFO sqlalchemy.engine.base.Engine SELECT DATABASE() 2019-08-04 15:58:59,928 INFO sqlalchemy.engine.base.Engine {} 2019-08-04 15:58:59,929 INFO sqlalchemy.engine.base.Engine show collation where `Charset` = 'utf8mb4' and `Collation` = 'utf8mb4_bin' 2019-08-04 15:58:59,929 INFO sqlalchemy.engine.base.Engine {} 2019-08-04 15:58:59,931 INFO sqlalchemy.engine.base.Engine SELECT CAST('test plain returns' AS CHAR(60)) AS anon_1 2019-08-04 15:58:59,931 INFO sqlalchemy.engine.base.Engine {} 2019-08-04 15:58:59,933 INFO sqlalchemy.engine.base.Engine SELECT CAST('test unicode returns' AS CHAR(60)) AS anon_1 2019-08-04 15:58:59,933 INFO sqlalchemy.engine.base.Engine {} 2019-08-04 15:58:59,934 INFO sqlalchemy.engine.base.Engine SELECT CAST('test collated returns' AS CHAR CHARACTER SET utf8mb4) COLLATE utf8mb4_bin AS anon_1 2019-08-04 15:58:59,934 INFO sqlalchemy.engine.base.Engine {} 2019-08-04 15:58:59,936 INFO sqlalchemy.engine.base.Engine DESCRIBE `employees` 2019-08-04 15:58:59,936 INFO sqlalchemy.engine.base.Engine {} 2019-08-04 15:58:59,938 INFO sqlalchemy.engine.base.Engine DESCRIBE `departments` 2019-08-04 15:58:59,938 INFO sqlalchemy.engine.base.Engine {} 2019-08-04 15:58:59,940 INFO sqlalchemy.engine.base.Engine DESCRIBE `dept_emp` 2019-08-04 15:58:59,940 INFO sqlalchemy.engine.base.Engine {} ---------------------------------------------------------------------- 2019-08-04 15:58:59,947 INFO sqlalchemy.engine.base.Engine BEGIN (implicit) 2019-08-04 15:58:59,949 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.gender AS employees_gender, employees.hire_date AS employees_hire_date FROM employees INNER JOIN dept_emp ON employees.emp_no = dept_emp.emp_no WHERE employees.emp_no = dept_emp.emp_no AND employees.emp_no = %(emp_no_1)s 2019-08-04 15:58:59,949 INFO sqlalchemy.engine.base.Engine {'emp_no_1': 10010} 2019-08-04 15:58:59,952 INFO sqlalchemy.engine.base.Engine SELECT dept_emp.emp_no AS dept_emp_emp_no, dept_emp.dept_no AS dept_emp_dept_no, dept_emp.from_date AS dept_emp_from_date, dept_emp.to_date AS dept_emp_to_date FROM dept_emp WHERE %(param_1)s = dept_emp.emp_no 2019-08-04 15:58:59,952 INFO sqlalchemy.engine.base.Engine {'param_1': 10010} [<Employee emp_no:10010 name:Duangkaew.Piveteau gender=F> relation=[Dept_emp no=10010 name=d004, Dept_emp no=10010 name=d006]] ---------------------------------------------------------------------- 2019-08-04 15:58:59,955 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.gender AS employees_gender, employees.hire_date AS employees_hire_date FROM employees INNER JOIN dept_emp ON employees.emp_no = dept_emp.emp_no WHERE employees.emp_no = %(emp_no_1)s 2019-08-04 15:58:59,955 INFO sqlalchemy.engine.base.Engine {'emp_no_1': 10010} [<Employee emp_no:10010 name:Duangkaew.Piveteau gender=F> relation=[Dept_emp no=10010 name=d004, Dept_emp no=10010 name=d006]] ---------------------------------------------------------------------- 2019-08-04 15:58:59,958 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.gender AS employees_gender, employees.hire_date AS employees_hire_date FROM employees INNER JOIN dept_emp ON employees.emp_no = dept_emp.emp_no AND employees.emp_no = %(emp_no_1)s 2019-08-04 15:58:59,958 INFO sqlalchemy.engine.base.Engine {'emp_no_1': 10010} [<Employee emp_no:10010 name:Duangkaew.Piveteau gender=F> relation=[Dept_emp no=10010 name=d004, Dept_emp no=10010 name=d006]]

最新回复(0)