[Python程序课程设计] 利用sqlalchemy封装mysql操作

在此之前，你需要自行安装sqlalchemy相关库。

1.增

创造一个Session实例，之后调用add(Emp实例)

Session_class = sessionmaker(bind=engine)
Session = Session_class()
emp_obj = Emp(id=id, name=name, age=age, salary=salary)
Session.add(emp_obj)
Session.commit()
Session.close()

2.查

依次按照id age salary 升序排序，使用query().order_by()

all_emp = Session.query(Emp).order_by(Emp.id.asc(),Emp.age.asc(),Emp.salary.asc()).all()

3.改

用filter().update({:})

Session.query(Emp).filter(Emp.id == id).update({"salary": salary})

4.删

用query().filter().delete()删除

Session.query(Emp).filter(Emp.id == id).delete()

代码如下：

import sqlalchemy
import pymysql
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String
from sqlalchemy.orm import sessionmaker

engine = create_engine("mysql+pymysql://cp328:cP+86743175@localhost/test",
                       encoding='utf8')

Base = declarative_base()  # 生成orm基类


class Emp(Base):
    __tablename__ = 'Emp'  # 表名
    id = Column(String(32), primary_key=True)
    name = Column(String(32))
    age = Column(String(64))
    salary = Column(String(64))
Base.metadata.create_all(engine)  # 创建表结构





class Emphelper(object):
    def new_built(self):
        print("=================新建员工=============================\n")
        Session_class = sessionmaker(bind=engine)
        Session = Session_class()

        id = input("工号：")
        name = input("姓名：")
        age = input("年龄：")
        salary = input("工资：")
        emp_obj = Emp(id=id, name=name, age=age, salary=salary)
        Session.add(emp_obj)
        Session.commit()
        Session.close()
        print("保存成功")

    def check(eelf):
        print("==================查看================================\n")
        Session_class = sessionmaker(bind=engine)
        Session = Session_class()
        all_emp = Session.query(Emp).order_by(Emp.id.asc(),Emp.age.asc(),Emp.salary.asc()).all()
        print("工号(id)   名字(name)   年龄(age)     工资(salary)\n")
        for emp in all_emp:
            print(emp.id,"       ",emp.name,"         ", emp.age, "          ", emp.salary,"\n")
        Session.close()

    def revise(self):
        print("==================修改================================\n")
        Session_class = sessionmaker(bind=engine)
        Session = Session_class()
        id = input("输入要修改的工号：")
        if Session.query(Emp).filter_by(id = id).first() == None:
            print("查无此人")
        else:
            salary = input("输入修改后的工资：")
            Session.query(Emp).filter(Emp.id == id).update({"salary": salary})
        Session.commit()
        Session.close()

    def delete(self):
        print("==================删除================================\n")
        Session_class = sessionmaker(bind=engine)
        Session = Session_class()
        id = input("输入要删除元的工号：")
        if Session.query(Emp).filter_by(id = id).first() == None:
            print("查无此人")
        else:
            Session.query(Emp).filter(Emp.id == id).delete()
            print("删除成功")
        Session.commit()
        Session.close()


work = Emphelper()
while True:
    print("---------------------------------------------------------------\n")
    print("1)新建员工         2）查看        3）修改        4）删除     5)退出\n")
    print("===============================================================\n")
    choice = input("请输入")


    if choice == '1':
        work.new_built()

    if choice == '2':
        work.check()

    if choice == '3':
        work.revise()

    if choice == '4':
        work.delete()

    if choice == '5':
        break

效果图