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)
SQLAlchemy 随笔
最新推荐文章于 2024-08-26 23:19:46 发布