| |
| |
| |
| ''' |
| @File : query_table_data.py |
| @Desc : 查询 |
| ''' |
| |
| |
| from sqlalchemy.sql import text |
| from sqlalchemy import and_, or_ |
| |
| from model import Emp, Dept |
| |
| |
| class QueryTableData: |
| """ |
| 查询 |
| """ |
| def __init__(self, session): |
| self.session = session |
| |
| def query_data_01(self): |
| |
| rows = self.session.query(Emp).all() |
| for row in rows: |
| print(row.emp_id, row.emp_name) |
| |
| r2 = self.session.query(Emp.emp_id, Emp.emp_name).first() |
| print(r2) |
| |
| r3 = self.session.query(Emp.emp_id, Emp.emp_name).filter(Emp.emp_id==1).first() |
| print(r3) |
| |
| r4 = self.session.query(Emp.emp_id, Emp.emp_name).filter(Emp.emp_name.like("%三%")).first() |
| print(r4) |
| |
| r5 = self.session.query( |
| Emp.emp_id.label('id'), Emp.emp_name.label('name') |
| ).filter(Emp.emp_name.like("%三%")).first() |
| print(r5.id, r5.name) |
| |
| r6 = self.session.query(Emp).from_statement( |
| text("SELECT * FROM emp where emp_name=:emp_name") |
| ).params(emp_name='张三').first() |
| print(r6.emp_id, r6.emp_name) |
| |
| r7 = self.session.query(Emp).filter( |
| text("emp_id<=:emp_id and emp_name=:emp_name") |
| ).params(emp_id=1, emp_name='张三').order_by(Emp.emp_id).first() |
| print(r7.emp_id, r7.emp_name) |
| |
| rows8 = self.session.query(Emp).filter(Emp.emp_id.in_([1,3,4])).all() |
| for row81 in rows8: |
| print(row81.emp_id, row81.emp_name) |
| |
| rows9 = self.session.query(Emp).filter(Emp.emp_id.between(1, 3)).all() |
| for row91 in rows9: |
| print(row91.emp_id, row91.emp_name) |
| |
| r10 = self.session.query(Emp.emp_id, Emp.emp_name).filter( |
| and_(Emp.emp_id==1, Emp.emp_name=='张三') |
| ).first() |
| print(r10) |
| |
| r11 = self.session.query(Emp.emp_id, Emp.emp_name).filter( |
| or_(Emp.emp_id==3, Emp.emp_name=='张三') |
| ).first() |
| print(r11) |
| |
| r12 = self.session.query(Emp).filter(~Emp.emp_id.in_([1,2,4])).first() |
| print(r12.emp_id, r12.emp_name) |
| |
| cursor = self.session.execute( |
| text('select * from emp where emp_id=:emp_id'), params={"emp_id": 1} |
| ) |
| r13s = cursor.fetchall() |
| for r13 in r13s: |
| print(r13) |
| |
| def query_data_02(self): |
| |
| |
| r11 = self.session.query(Emp).filter( |
| Emp.emp_id.in_(self.session.query(Emp.emp_id).filter_by(emp_name='张三')) |
| ).first() |
| print(r11.emp_id, r11.emp_name) |
| |
| r12 = self.session.query(Emp, Dept).filter(Emp.dept_id == Dept.dept_id).first() |
| print(r12[0].emp_name, r12[1].dept_name) |
| |
| r13s = self.session.query(Emp).join(Dept).all() |
| for r13 in r13s: |
| print(r13) |
| |
| r14s = self.session.query(Emp).join(Dept, isouter=True).all() |
| for r14 in r14s: |
| print(r14.emp_name) |
| |
| q1 = self.session.query(Emp.emp_id, Emp.emp_name).filter(Emp.emp_id >= 1) |
| q2 = self.session.query(Dept.dept_id, Dept.dept_name).filter(Dept.dept_id >= 1) |
| r2s = q1.union(q2).all() |
| for r2 in r2s: |
| print(r2) |
| |
| q1 = self.session.query(Emp.emp_id, Emp.emp_name).filter(Emp.emp_id >= 1) |
| q2 = self.session.query(Dept.dept_id, Dept.dept_name).filter(Dept.dept_id >= 1) |
| r2s = q1.union_all(q2).all() |
| for r2 in r2s: |
| print(r2) |
| |
| |
| |
| def main(self): |
| """ |
| 主函数 |
| """ |
| self.query_data_01() |
| self.query_data_02() |
| |
| |
| |
| |
| ''' |
| @File : add_table_data.py |
| @Desc : 新增 |
| ''' |
| |
| |
| |
| |
| from model import Emp, Dept, Student, Course, Student2Course |
| |
| |
| class AddTableData: |
| """ |
| 新增 |
| """ |
| def __init__(self, session): |
| self.session = session |
| |
| def single_table_add_data(self): |
| """ |
| 单表新增 |
| """ |
| self.session.add( |
| Dept(dept_name="开发") |
| ) |
| |
| self.session.add_all([ |
| Dept(dept_name="销售"), |
| Dept(dept_name="运营"), |
| Dept(dept_name="宣传") |
| ]) |
| self.session.commit() |
| |
| def one_to_many_table_add_data(self): |
| """ |
| 一对多表新增 |
| """ |
| dept_1 = Dept(dept_name="Python") |
| dept_2 = Dept(dept_name='Java') |
| dept_1.emp_list = [ |
| Emp(emp_name='张三'), |
| Emp(emp_name='李四') |
| ] |
| dept_2.emp_list = [ |
| Emp(emp_name='王二') |
| ] |
| self.session.add_all([dept_1, dept_2]) |
| self.session.commit() |
| |
| def many_to_many_table_add_data(self): |
| """ |
| 多对多表新增 |
| """ |
| course_1 = Course(course_name='语文') |
| course_2 = Course(course_name='数学') |
| course_1.student_list = [ |
| Student(student_name="张三"), |
| Student(student_name="李四"), |
| ] |
| course_2.student_list = [ |
| Student(student_name="王二") |
| ] |
| self.session.add_all([course_1, course_2]) |
| self.session.commit() |
| |
| def main(self): |
| """ |
| 主函数 |
| """ |
| |
| self.single_table_add_data() |
| |
| self.one_to_many_table_add_data() |
| |
| self.many_to_many_table_add_data() |
| |
| |
| |
| |
| ''' |
| @File : delete_table_data.py |
| @Desc : 删除 |
| ''' |
| |
| |
| |
| |
| from model import Emp |
| |
| |
| class DeleteTableData: |
| """ |
| 删除 |
| """ |
| def __init__(self, session): |
| self.session = session |
| |
| def delete_data(self): |
| """ |
| 删除 |
| """ |
| self.session.query(Emp).filter(Emp.emp_id==2).delete() |
| self.session.commit() |
| |
| def main(self): |
| """ |
| 主函数 |
| """ |
| self.delete_data() |
| |
| |
| |
| |
| |
| ''' |
| @File : model.py |
| @Desc : orm 模型 |
| ''' |
| |
| |
| from sqlalchemy.ext.declarative import declarative_base |
| from sqlalchemy import Column, ForeignKey, UniqueConstraint |
| from sqlalchemy import Integer, String, Date |
| from sqlalchemy.orm import relationship |
| |
| |
| |
| Base = declarative_base() |
| |
| |
| class Emp(Base): |
| __tablename__ = 'emp' |
| |
| emp_id = Column(Integer, primary_key=True, autoincrement=True) |
| emp_name = Column(String(20), nullable=False) |
| birthday = Column(Date) |
| sex = Column(Integer) |
| |
| |
| dept_id = Column(Integer, ForeignKey('dept.dept_id')) |
| |
| dept = relationship('Dept', backref='emp_list') |
| |
| |
| class Dept(Base): |
| __tablename__ = 'dept' |
| |
| dept_id = Column(Integer, primary_key=True, autoincrement=True) |
| dept_name = Column(String(50), nullable=False) |
| |
| __table_args__ = ( |
| UniqueConstraint('dept_id', 'dept_name', name='dept_id_name'), |
| ) |
| |
| |
| class Student(Base): |
| __tablename__ = 'student' |
| |
| student_id = Column(Integer, primary_key=True, autoincrement=True) |
| student_name = Column(String(20), nullable=False) |
| |
| |
| course_list = relationship('Course', secondary='student2course', backref='student_list') |
| |
| class Course(Base): |
| __tablename__ = 'course' |
| |
| course_id = Column(Integer, primary_key=True, autoincrement=True) |
| course_name = Column(String(20), nullable=False) |
| |
| class Student2Course(Base): |
| __tablename__ = 'student2course' |
| |
| id = Column(Integer, primary_key=True, autoincrement=True) |
| student_id = Column(Integer, ForeignKey('student.student_id')) |
| course_id = Column(Integer, ForeignKey('course.course_id')) |
| |
| |
| |
| ''' |
| @File : init_conn.py |
| @Desc : 初始化数据库连接 |
| ''' |
| |
| |
| from sqlalchemy import create_engine |
| |
| |
| class InitConn: |
| """ |
| 初始化数据库连接 |
| """ |
| def __init__(self): |
| conn_base_str = "{driver}://{username}:{password}@{host_port}/{database}{parameter}" |
| conn_param = { |
| 'driver': 'mysql+mysqlconnector', |
| 'username': 'root', |
| 'password': '123456', |
| 'host_port': '127.0.0.1:3306', |
| 'database': 'study', |
| 'parameter': '?auth_plugin=mysql_native_password&charset=utf8', |
| } |
| self.engine = create_engine( |
| conn_base_str.format_map(conn_param), |
| max_overflow=0, |
| pool_size=5, |
| pool_timeout=30, |
| pool_recycle=-1 |
| ) |
| |
| def get_engine(self): |
| """ |
| 获取数据库连接初始化结果 |
| """ |
| return self.engine |
/Users/song/Code/sqlalchemy_mysql_learn00/python_sqlalchemy_mysql/main.py
| |
| |
| |
| ''' |
| @File : main.py |
| @Desc : 主测试文件 |
| ''' |
| |
| |
| from sqlalchemy import create_engine |
| from sqlalchemy.orm import sessionmaker, scoped_session |
| from add_table_data import AddTableData |
| from delete_table_data import DeleteTableData |
| |
| |
| from init_conn import InitConn |
| from model import Base |
| from query_table_data import QueryTableData |
| from update_table_data import UpdateTableData |
| |
| |
| |
| |
| |
| |
| |
| if __name__ == '__main__': |
| engine = InitConn().get_engine() |
| SessionFactory = sessionmaker(bind=engine) |
| |
| |
| session = scoped_session(SessionFactory) |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| DeleteTableData(session).main() |
| |
| |
| session.remove() |
| |
| |
| |
| ''' |
| @File : update_table_data.py |
| @Desc : 修改 |
| ''' |
| |
| |
| import datetime |
| |
| from model import Emp |
| |
| |
| class UpdateTableData: |
| """ |
| 修改 |
| """ |
| def __init__(self, session): |
| self.session = session |
| |
| def update_data(self): |
| """ |
| 修改 |
| """ |
| self.session.query(Emp).filter(Emp.emp_id==1).update({'birthday': datetime.datetime.now()}) |
| self.session.commit() |
| |
| def main(self): |
| """ |
| 主函数 |
| """ |
| self.update_data() |
| |
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· 清华大学推出第四讲使用 DeepSeek + DeepResearch 让科研像聊天一样简单!
· 推荐几款开源且免费的 .NET MAUI 组件库
· 实操Deepseek接入个人知识库
· 易语言 —— 开山篇
· Trae初体验
2022-02-12 css 四种基本选择器
2022-02-12 四种基本选择器
2022-02-12 css的三种引入方式
2022-02-12 html中元素之间的关系
2022-02-12 块级元素 行内元素
2022-02-12 代码提示 thisArg argArray
2022-02-12 如何监听一个对象