#通过SQLAlchemy对数据库进行增删改查
# 想要操作数据库 先要打开数据库连接
from create_table import engine
# 创建会话 - 打开数据库连接
from sqlalchemy.orm import sessionmaker
# 创建会话窗口
Session = sessionmaker(engine)
# 打开会话窗口
db_session = Session()
from create_table import User
user_obj = User(name='XJ') # 通过db_session(已经打开的会话窗口)
db_session.add(user_obj)
# 提交执行操作
db_session.commit()
# 关闭会话
db_session.close() # 增加批量数据
from create_table import User
db_session.add_all([
User(name='XJ1'),
User(name='XJ2'),
User(name='XJ3')
])
db_session.commit()
db_session.close()
添加数据
# 更新修改数据 from sqlalchemy.orm import sessionmaker
from create_table import engine,User Session = sessionmaker(engine)
db_session = Session() # 1.修改数据
# SQL语句
# update User set 'name' = '徐建' where id = 2 user = db_session.query(User).filter(User.id==2).update({'name':'徐建'})
db_session.commit()
db_session.close()
修改数据
# 删除数据
from sqlalchemy.orm import sessionmaker
from create_table import engine, User Session = sessionmaker(engine)
db_session = Session() # 1.删除 # SQL语句
# delete from user where id =2 user = db_session.query(User).filter(User.id==2).delete()
print(user) db_session.commit()
db_session.close() # 2.删除多条
res = db_session.query(User).filter(User.name=='XJ2').delete()
db_session.commit()
db_session.close()
print(res)
删除数据
# 查询
# 会话窗口
from sqlalchemy.orm import sessionmaker
from create_table import engine Session = sessionmaker(engine) db_session = Session()
from create_table import User
# 查询所有 # SQL语句
# select * from table user_list = db_session.query(User).all()
for row in user_list:
print(row.id, row.name) # 查询一个
user = db_session.query(User).first()
print(user.name) # 带条件的查询
user_list = db_session.query(User).filter(User.id==2).all()
print(user_list[0].name) user = db_session.query(User).filter_by(id=4).first()
print(user.id, user.name) user_list = db_session.query(User).filter(User.id>=2).all()
for row in user_list:
print(row.id, row.name) db_session.close()
查询数据