SALalchemy增删改查
原创
SALalchemy增删改查
原创
少年包青菜
修改于 2020-05-13 07:16:01
修改于 2020-05-13 07:16:01
代码可运行
运行总次数：0
代码可运行
1.单表操作
1.1创建表
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, INT, String
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
# Base 是 ORM的基类
Base = declarative_base()
# 1.创建表
class User(Base):
# 表名，最好设定和类名一样
__tablename__ = 'user'
# id int类型，主键，自增长
id = Column(INT, primary_key=True, autoincrement=True)
name = Column(String(32), index=True)
age = Column(INT)
gender = Column(String(100))
# 2.创建数据引擎
engine = create_engine('mysql+pymysql://root:123@192.168.1.163:3306'
'/my_test?charset=utf8')
# 3.将继承 Base 的类全部转化成表
Base.metadata.create_all(engine)
# 添加字段
def add_column(my_engine, table_name, column):
"""
:param my_engine: engine 导入的引擎
:param table_name: 表名 __tablename__
:param column: # 列名
:return:
"""
# 获取列名
column_name = column.compile(dialect=engine.dialect)
# 获取属性
column_type = column.type.compile(engine.dialect)
print(column_name, column_type)
# my_engine.execute('ALTER TABLE {} ADD COLUMN {} {}'.format(table_name, column_name, column_type))
add_column(engine, 'user', User.gender)1.2 增、删、改、查
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
from 单表操作.create_table import User
# 1.有一个创建连接的引擎
engine = create_engine(
'mysql+pymysql://root:123@192.168.1.163:3306/my_test?charset=utf8'
)
# 2.有一个会话窗口
Session_window = sessionmaker(engine)
# 3.建立会话
db_session = Session_window()1.2.1 增
# 新增一条数据
# db_session.add(User(name='bob', age=22))
# 插入多条数据
db_session.add_all([
User(name='hello', age=12),
User(name='shit', age=18),
])1.2.2 删
# 删除数据，可以多个条件
db_session.query(User).filter(User.id >= 3, User.age >= 15).delete()
db_session.commit()1.2.3 改
db_session.query(User).filter(User.id >= 2).update({'age': 50})
db_session.commit()1.2.4 查
# 1.查询单条数据，查询出来是一个列表
# user_obj_list = db_session.query(User).all()
# for user_obj in user_obj_list:
# print(user_obj.name, user_obj.age)
# 2.带条件的查询
# user_obj = db_session.query(User).filter(User.name == 'leon').first() # type:User
# print(user_obj.name, user_obj.age)
user_obj_list = db_session.query(User).filter(User.id >= 2, User.age <= 20)
for user_obj in user_obj_list:
print(user_obj.name, user_obj.age)db_session.commit()
db_session.close()2.外键一对多
2.1创建表
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, INT, VARCHAR, ForeignKey
from sqlalchemy.orm import relationship
from sqlalchemy import create_engine
Base = declarative_base()
class Student(Base):
__tablename__ = 'student'
id = Column(INT, primary_key=True, autoincrement=True)
name = Column(VARCHAR(32))
# 外键关联，表名，小写
class_id = Column(INT, ForeignKey('classname.id'))
# relationship 类名，大写
# backref: 反向查询关键字
stu_to_class = relationship('ClassName', backref='class_to_stu')
class ClassName(Base):
__tablename__ = 'classname'
id = Column(INT, primary_key=True, autoincrement=True)
class_name = Column(VARCHAR(32))
# 创建引擎
engine = create_engine('mysql+pymysql://root:123@192.168.1.163:3306'
'/foreign_test?charset=utf8')
Base.metadata.create_all(engine)2.2插入数据、查看数据
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
from 外键一对多.create_table_ForeignKey import Student, ClassName
# 1.创建引擎
engine = create_engine('mysql+pymysql://root:123@192.168.1.163:3306'
'/foreign_test?charset=utf8')
# 2.有一个会话窗口
Session_window = sessionmaker(engine)
# 3.建立会话
db_session = Session_window()2.2.1 插入数据
# 正向外键关联插入数据
# db_session.add(Student(name='leon', stu_to_class=ClassName(class_name='linux')))
# 反向插入数据
class_obj = ClassName(class_name='GoLang')
class_obj.class_to_stu = [Student(name='Bob'), Student(name='shit')]
db_session.add(class_obj)2.2.2 查看数据
# 外键关联正向查询
# stu_list = db_session.query(Student).filter(Student.id >= 1)
# for stu_obj in stu_list: # type: Student
# print(stu_obj.name, stu_obj.stu_to_class.class_name)
# 外键关联反向查询
class_list = db_session.query(ClassName).filter(ClassName.id >= 2).all()
for class_obj in class_list:
class_name = class_obj.class_name
# 根据班级反向取到所有的学生
students_list = class_obj.class_to_stu
for stu_obj in students_list:
print(class_obj, stu_obj)db_session.commit()
db_session.close()3.多对多操作
3.1 创建表，注意 secondary 关键字
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker, relationship
from sqlalchemy import VARCHAR, INT, Column, create_engine, ForeignKey
Base = declarative_base()
class Man(Base):
__tablename__ = 'man'
id = Column(INT, primary_key=True)
name = Column(VARCHAR(32))
# secondary: 中间媒介
# backref: 反向查询关键字
# secondary: 媒介类是 couples
man_to_woman = relationship('Woman', backref='woman_to_man', secondary='couples')
class Woman(Base):
__tablename__ = 'woman'
id = Column(INT, primary_key=True)
name = Column(VARCHAR(32))
# 一个 man 可以对应多个 woman
# 一个 woman 可以对应多个 man
class Couples(Base):
__tablename__ = 'couples'
id = Column(INT, primary_key=True)
man_id = Column(INT, ForeignKey('man.id'))
woman_id = Column(INT, ForeignKey('woman.id'))
engine = create_engine('mysql+pymysql://root:123@192.168.1.163:3306'
'/many_test?charset=utf8')
Base.metadata.create_all(engine)3.2 插入数据、查看数据
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
from 外键一对多.create_table_ForeignKey import Student, ClassName
# 1.创建引擎
engine = create_engine('mysql+pymysql://root:123@192.168.1.163:3306'
'/many_test?charset=utf8')
# 2.有一个会话窗口
Session_window = sessionmaker(engine)
# 3.建立会话
db_session = Session_window()3.2.1 插入数据
# 增加数据，正向relationship
# man_obj = Man(name='李雷')
# man_obj.man_to_woman = [Woman(name='罗玉凤'), Woman(name='韩梅梅')]
# db_session.add(man_obj)
# 增加数据，方向relationship
woman_obj = Woman(name='王菲')
woman_obj.woman_to_man = [Man(name='窦唯'), Man(name='李亚鹏')]
db_session.add(woman_obj)2.2.2 查询数据
# 正向操作
# man_list = db_session.query(Man).filter(Man.id >= 1).all()
# for man_obj in man_list:
# man_name = man_obj.name
# woman_list = man_obj.man_to_woman
# for woman_obj in woman_list:
# print(man_name, woman_obj.name)
# 反向操作
man_list = db_session.query(Woman).filter(Woman.name == '王菲').first().woman_to_man
for man_obj in man_list:
print(man_obj.name)db_session.commit()
db_session.close()4.部分高级操作
4.1 创建表
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, VARCHAR, INT, create_engine
from sqlalchemy.orm import sessionmaker
Base = declarative_base()
class User(Base):
__tablename__ = 'user'
id = Column(INT, primary_key=True, autoincrement=True)
name = Column(VARCHAR(32), index=True)
age = Column(INT)
gender = Column(VARCHAR(8))
engine = create_engine('mysql+pymysql://root:123@192.168.1.163:3306'
'/senior_test?charset=utf8')
Base.metadata.create_all(engine)4.2 查看数据
from sqlalchemy.orm import sessionmaker
from 高级操作.create_table import User
engine = create_engine('mysql+pymysql://root:123@192.168.1.163:3306'
'/senior_test?charset=utf8')
Session_window = sessionmaker(engine)
db_session = Session_window()4.2.1 or_
# or_
# id=1 或者 id=3 的
# user_list = db_session.query(User).filter(or_(User.id == 1, User.id == 3)).all()
# for user_obj in user_list:
# print(user_obj.__dict__)4.2.2 and_
# and_
# 新版本其实 逗号本身已经带了 and_ 的这效果，看情况使用
# user_list = db_session.query(User).filter(and_(User.id == 1, User.name == '张三'))
# for user_obj in user_list:
# print(user_obj.__dict__)4.2.3 query(指定字段)
# 查询特定字段，只会查出指定字段
# user_list = db_session.query(User.name, User.gender).all()
# for user_obj in user_list:
# print(user_obj)4.2.4 order_by 排序
# 升序
# user_list = db_session.query(User).order_by(User.id).all()
# 降序
# user_list = db_session.query(User).order_by(User.id.desc()).all()
# for user_obj in user_list:
# print(user_obj.__dict__)4.2.5 between，在范围内
# user_list = db_session.query(User).filter(User.id.between(2, 3)).order_by(User.id).all()
# for user_obj in user_list:
# print(user_obj.__dict__)4.2.6 in_，在指定集合内
# user_list = db_session.query(User).filter(User.id.in_([1, 3, 2])).order_by(User.id).all()
# for user_obj in user_list:
# print(user_obj.__dict__)4.2.7 notin_，排除指定范围的，与 in_ 互斥
# user_list = db_session.query(User).filter(User.id.notin_([3, 2])).order_by(User.id).all()
# for user_obj in user_list:
# print(user_obj.__dict__)4.3 更新数据
# 本身自增加的写法
# 引用增加
# 所有的 User.age 全部自增长 1
db_session.query(User).update({User.age: User.age + 1})db_session.commit()
db_session.close()原创声明：本文系作者授权腾讯云开发者社区发表，未经许可，不得转载。
如有侵权，请联系 cloudcommunity@tencent.com 删除。
原创声明：本文系作者授权腾讯云开发者社区发表，未经许可，不得转载。
如有侵权，请联系 cloudcommunity@tencent.com 删除。
评论
登录后参与评论
暂无评论
推荐阅读
![Docker教程（超全总结）[通俗易懂]](https://ask.qcloudimg.com/http-save/yehe-8223537/4e9a552898745d2a8a0d040916398e96.png)
推荐阅读
相关推荐
快速入门Docker(4)——commit镜像|容器数据卷
更多 >腾讯云开发者
