SQLAlchemy 随笔

from sqlalchemy import create_engine, text, Column, String, Integer, Enum, DECIMAL, Boolean,func
from sqlalchemy.orm import declarative_base, sessionmaker

DATABASE = 'demo1125'
HOST = 'localhost'
PORT = 3306
USERNAME = 'root'
PASSWORD = 'root'

#通过格式化字符串拼接数据库连接信息（用户名、密码、主机、端口、数据库名）
DB_URL = "mysql+pymysql://{}:{}@{}:{}/{}".format(USERNAME,PASSWORD,HOST,PORT,DATABASE)

#使用 create_engine 方法创建数据库连接
engine = create_engine(DB_URL)

#使用 declarative_base() 创建一个基类，所有模型类都将继承这个基类
Base = declarative_base()

#通过 sessionmaker 创建会话类，并实例化会话对象，用于数据库操作
session = sessionmaker(bind=engine)()

#定义一个 Teachers 类，继承自前面创建的基类，用于映射数据库中的 teachers 表
class Teachers(Base):
    __tablename__ = 'teachers'

    id = Column(Integer,autoincrement=True,primary_key=True)
    name = Column(String(10),nullable=False)
    gender = Column(Enum('男','女','保密'),default='保密')
    age = Column(Integer)
    subject = Column(String(10),nullable=False)
    salary = Column(DECIMAL(9,3))
    is_del = Column(Boolean,default=False)
   
    #打印模型对象的信息
    def __str__(self):
        return '{},{},{},{},{},{},{}'.format(self.id,self.name,self.gender,self.age,self.subject,self.salary,self.is_del)

# Base.metadata.drop_all(engine)
#
# Base.metadata.create_all(engine)

def add_data():

    new_teacher = Teachers(name='张三', age=31, gender='男',subject='数学', salary=5555.55)
    new_teacher1 = Teachers(name='李四', age=29, gender='女', subject='英语', salary=6666.66)
    new_teacher2 = Teachers(name='王五', age=28, subject='语文', salary=7777.77)
    new_teacher = Teachers(name='老六', age=30, gender='男', subject='其它', salary=5555.55)

    session.add_all([new_teacher,new_teacher1,new_teacher2])

    session.commit()

def update_data():
    # query 查询的值不能修改
    # data = session.query(Teachers).filter(Teachers.name=='张三').all()
    data = session.get(Teachers,1)
    data.salary = 8888.88
    print(data)

    session.commit()

def delete_data():
    data = session.get(Teachers, 1)

    # session.rollback()
    # session.delete(data)
    data.is_del = True

    session.commit()

def search_data():
    # data = session.query(Teachers).filter(Teachers.salary>7000).all()

    data = session.query(Teachers).filter_by(is_del=False).all()

    for d in data:
        print(d)

def column_sum():
    # result = session.query(func.sum(Teachers.salary))
    # result = session.query(func.min(Teachers.salary))
    # result = session.query(func.max(Teachers.salary))
    result = session.query(func.avg(Teachers.salary))
    print(result.scalar())

if __name__ == '__main__':
    # add_data()
    # update_data()
    # delete_data()
    # search_data()
    column_sum()

# with engine.connect() as conn:
#     result = conn.execute(text('select * from goods'))
#     for r in result:
#         print(r)