sqlAlchemy

1,SQLAlchemy是Python知名的ORM工具之一,为高效和高性能的数据库访问设计,实现了完整的企业级持久模型.SQLAlchemy的理念是SQL数据库的量级和性能重要于集合对象,而对象集合的抽象有重要于表和行.因此SQLAlchemy采用了类似于Hibernate的数据映射模型,而不是其他ORM框架采用的Active Record不过，Elixir和declarative等可选插件可以让用户使用声明语法。

2,安装SQLAlchemy.

pip install sqlalchemy

• 如果安装SQLAlchemy报错,那是因为SQLAlchemy依赖pymysql

3,创建表,并连接数据库

# ORM
# 1,Class - Obj
# 创建数据库引擎
# 将所有的Class序列化成数据表
# ORM操作 - CRUD (增删改查)


# 1,创建一个class(表)
from sqlalchemy.ext.declarative import declarative_base

# 实例化一个declaraive_base对象
Base = declarative_base()
# Base 是ORM模型 基类
# ORM模型 - Obj里面的属性 == table中创建的字段
#   - Obj定义table的操作方式和属性

from sqlalchemy import Column, Integer, String


class User(Base):
    __tablename__ = "user"
    id = Column(Integer, primary_key=True, autoincrement=True)
    name = Column(String(32), index=True)


# 2,创建数据库引擎
from sqlalchemy import create_engine  # 创建数据库引擎
engine = create_engine("mysql+pymysql://root:123456@127.0.0.1:3306/sqlalchemy?charset=utf8")

# 3,将所有的继承Base的Class序列化数据表
# Base.metadata.create_all(engine)
Base.metadata.create_all(engine)

参数说明:

• __tablename__指定在MySql表中的名字
• 类中的Column代表数据库中的一列
• Integer代表数值型
• String代表字符串

4.1,单标的增加:

# 1,选中数据库 - 创建数据库引擎, 导入数据库引擎
# 2,创建查询窗口, 必须是选中数据库的查询窗口
# 3,创建sql语句
# 4,点击运行

# 1,选中数据库 - 创建数据库引擎,导入数据库引擎
from Single_table.create_table import engine, User

# 2,创建查询窗口,必须是选中数据库的查询窗口
from sqlalchemy.orm import sessionmaker
Session_window = sessionmaker(engine)

# 打开查询窗口
db_session = Session_window()  # 实例化查询窗口对象

# 1,增加数据 - 创建sql语句
user_obj = User(name="帅爆太阳的男人")  # 创建sql语句
db_session.add(user_obj)  # 将sql语句粘贴到查询窗口中


# 点击运行
db_session.commit()  # 执行全部的sql语句
db_session.close()  # 关闭会话窗口


# 2,创建多条数据
user_obj_list = [User(name="雪雪"), User(name="小雪")]
db_session.add_all(user_obj_list)  # 将多个创建的值粘贴到查询窗口
db_session.commit()  # 提交要执行的sql语句
db_session.close()

 

4.2,单标的查

# 创建窗口
from Single_table.create_table import engine, User
from sqlalchemy.orm import sessionmaker
Session = sessionmaker(engine)  # 常见查询窗口
db_session = Session()  # 打开查询窗口

# 1,查询单条数据
user_obj = db_session.query(User).first()  # 从查询窗口中查询数据
print(user_obj.id, user_obj.name)

# 查询多条数据
user_obj_list = db_session.query(User).all()
print(user_obj_list)
for row in user_obj_list:
    print(row.id, row.name)


# 带条件的查询
user_obj_list = db_session.query(User).filter(User.id <= 2, User.name == "雪雪").all()
print(user_obj_list)
for row in user_obj_list:
    print(row.id, row.name)


user_obj_list = db_session.query(User).filter_by(id=2, name="雪雪").all()
print(user_obj_list)
for row in user_obj_list:
    print(row.id, row.name)

# 3 小雪
# [<Single_table.create_table.User object at 0x000002C0D560EAC8>, <Single_table.create_table.User object at 0x000002C0D560EEF0>, <Single_table.create_table.User object at 0x000002C0D560EE80>]
# 3 小雪
# 1 帅爆太阳的男人
# 2 雪雪
# [<Single_table.create_table.User object at 0x000002C0D560EE80>]
# 2 雪雪
# [<Single_table.create_table.User object at 0x000002C0D560EE80>]
# 2 雪雪

4.3,单标的更新

# 创建窗口
from Single_table.create_table import User, engine
from sqlalchemy.orm import sessionmaker  # 会话窗口

Session = sessionmaker(engine)  # 创建数据查询窗口
db_session = Session()  # 打开数据查询窗口

# 1,修改一条数据
user_obj = db_session.query(User).filter(User.id == 1).update({"name": "帅爆太阳那人"})
db_session.commit()  # 提交sql语句

# 2,修改多条数据
user_obj - db_session.query(User).filter(User.id >= 2).update({"name": "miss"})
db_session.commit()  # 提交sql语句

4.4,单标的删除

from sqlalchemy.orm import sessionmaker
from Single_table.create_table import engine, User

# 1,创建数据窗口
Session = sessionmaker(engine)  # 库好里放的参数是要对那个数据库进行操作
db_session = Session()  # 打开数据查询窗口


# 1,删除数据
res = db_session.query(User).filter(User.id == 1).delete()  # 直接queryset对象就可以直接删除
db_session.commit()


# 2,删除多条数据
res = db_session.query(User).filter(User.id >=1).delete()
db_session.commit()  # 提交sql语句

5,Foreignkey(一对多)

• 5.1创建表和连接

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, INT, VARCHAR, ForeignKey
from sqlalchemy.orm import relationship

# 实例化基类
Base = declarative_base()


class Student(Base):
    __tablename__ = "student"
    id = Column(INT, primary_key=True)
    name = Column(VARCHAR(32))
    school_id = Column(INT, ForeignKey("school.id"))
    stu2sch = relationship("School", backref="sch2stu")  # backref反向查询的用到的字段


class School(Base):
    __tablename__ = "school"
    id = Column(INT, primary_key=True)
    name = Column(VARCHAR(32))


from sqlalchemy import create_engine
engine = create_engine("mysql+pymysql://root:123456@127.0.0.1:3306/sqlalchemy?charset=utf8")  # utf8, 不要写成utf-8
print(engine)
Base.metadata.create_all(engine)

student和school多对一,relationship指定外键关系

• 5.2外键关系的增加

from sqlalchemy.orm import sessionmaker
from foreignKey.create_table import Student, School, engine
Session = sessionmaker(engine)  # 创建数据查询窗口
db_session = Session()  # 打开数据查询窗口

# 1增加数据
sch_obj = School(name="OldBoyBeijing")
db_session.add(sch_obj)  # 把要执行的orm操作语句
db_session.commit()  # 提交成要操作的sql语句

sch_obj = db_session.query(School).filter(School.name=="OldBoyBeijing").first()

stu_obj = Student(name="帅爆太阳的男人", school_id=sch_obj.id)  # 通过学校,查找该学校所有的学生

db_session.add(stu_obj)  # 把orm的语句放到数据查询窗口
db_session.commit()  # 提交sql语句

# 2,Relationship 添加数据操作 ---正向
stu_obj = Student(name="帅爆太阳的男人", stu2sch=School(name="OldBoyBeijing"))  # 直接通过relationship直接定位到学校
db_session.add(stu_obj)
db_session.commit()


# Relationship - 反向
sch_obj = School(name="OldBoyBeijing")  # 先查到学校对象
sch_obj.sch2stu = [Student(name="雪雪"), Student(name="小雪")]  # 在根据relationship查找到这个学校的所有学生
db_session.add(sch_obj)
db_session.commit()

• 5.2外键关系的查询

from sqlalchemy.orm import sessionmaker
from foreignKey.create_table import engine, Student, School

Session = sessionmaker(engine)  # 创建数据查询窗口
db_session = Session()

# 1,查询
sch_obj = db_session.query(School).filter(School.name=="OldBoyBeijing").first()
beijing_stu_obj = db_session.query(Student).filter(Student.school_id==sch_obj.id).first()
print(beijing_stu_obj.name, beijing_stu_obj.id)  # 帅爆太阳的男人 1

# 2,relatership 正向查询
stu_obj = db_session.query(Student).filter(Student.name == "帅爆太阳的男人").first()
print(stu_obj.name, stu_obj.stu2sch.name)  # 帅爆太阳的男人 OldBoyBeijing


# 3,relationship   反向查询
sch_obj_list = db_session.query(School).all()
for row in sch_obj_list:
    for stu in row.sch2stu:
        print(row.name, stu.name)
# OldBoyBeijing 帅爆太阳的男人
# OldBoyBeijing 帅爆太阳的男人
# OldBoyBeijing 雪雪
# OldBoyBeijing 小雪

6,多对多关系的表

• 6.1,多对多表的创建和连接

from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()  # 实例化declarative_base

from sqlalchemy import Column, Integer, String, ForeignKey
from sqlalchemy.orm import relationship


class Girls(Base):
    __tablename__ = "girl"
    id = Column(Integer, primary_key=True)
    name = Column(String(32))
    g2b = relationship("Boys", backref="b2g", secondary="hotel")  # backref是反向查询的字段


class Boys(Base):
    __tablename__ = "boy"
    id = Column(Integer, primary_key=True)
    name = Column(String(32))


class Hotel(Base):
    __tablename__ = "hotel"
    id = Column(Integer, primary_key=True)
    boy_id = Column(Integer, ForeignKey("boy.id"))
    girl_id = Column(Integer, ForeignKey("girl.id"))


from sqlalchemy import create_engine
engine = create_engine("mysql+pymysql://root:123456@127.0.0.1:3306/sqlalchemy")
print(engine)
Base.metadata.create_all(engine)

• 6.2多对多表的插入数据

from Many_to_Many.create_table_M2M import engine, Girls, Boys, Hotel
from sqlalchemy.orm import sessionmaker
Session = sessionmaker(engine)  # 创建一个会话窗口
db_session = Session()  # 打开一个 会话窗口

# 1增加数据  -- relationship正向
girl_obj = Girls(name="雪雪")
girl_obj.g2b = [Boys(name="帅爆太阳的男人")]
db_session.add(girl_obj)  # 把增加的数据窗口
db_session.commit()  # 将存放于窗口的数据提交到数据库中


# 2增加数据 - relationship 反向
boy_obj = Boys(name="胡夏")
boy_obj.b2g = [Girls(name="郁可唯"), Girls(name="谢大脚")]
db_session.add(boy_obj)
db_session.commit()  # 提交到数据库

• 6.3多对多表的查询

from Many_to_Many.create_table_M2M import engine, Girls, Boys
from sqlalchemy.orm import sessionmaker
Session = sessionmaker(engine)  # 创建会话窗口
db_session = Session()  # 打开会话窗口


# 1查询数据 - relationship  正向
girl_obj_list = db_session.query(Girls).all()  # 查询所有的女生
print(girl_obj_list)
for girl in girl_obj_list:
    print(girl)  # <Many_to_Many.create_table_M2M.Girls object at 0x000001F6E5344908>
    for boy in girl.g2b:  # 通过外键拿到
        print(girl.name,boy.name)

# 2查询数据 - relationship  - 反向
boy_obj_list = db_session.query(Boys).all()
for boy in boy_obj_list:
    for girl in boy.b2g:
        print(girl.name, boy.name)

7,SQLAlchemy的进阶操作

• 7.1创建表和建立连接

# ORM
# 1,class - obj
# 2,将所有的class序列化成数据
# 3,将所有的class序列化成数据表
# 4,ORM操作 -CRUD


# 1创建一个表class
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
# Base 是ORM的模型类, 基类
# ORM模型 -obj里面的属性 == table中创建的字段
# -obj定义table的操作方式和属性

from sqlalchemy import Column, Integer, String


class People(Base):
    __tablename__ = "people"
    id = Column(Integer, primary_key=True, autoincrement=True)
    name = Column(String(32), index=True)
    age = Column(Integer)
    gender = Column(String(32))
    
    
# 2创建数据库引擎
from sqlalchemy import create_engine
engine = create_engine("mysql+pymysql://root:123456@127.0.0.1:3306/sqlalchemy")
# engine = create_engine("mysql+pymysql://root:123456@127.0.0.1:3306/sqlalchemy")

# 将所有的继承Base的Class序列化成数据表
Base.metadata.create_all(engine)

• 7.2进阶操作的增加

from sqlalchemy.orm import sessionmaker
from advanced.create_table_advanced import engine, People
Session_window = sessionmaker(engine)
# 打开查询窗口
db_session = Session_window()


# 增加多条数据
user_obj_list = [People(name="小雪", age=18, gender="女"), People(name="雪人", age=23, gender="女"), People(name="刘夏", age=22, gender="女")]
db_session.add_all(user_obj_list)
print(db_session)
db_session.commit()
db_session.close()

• 7.3进阶操作的增加

from sqlalchemy.orm import sessionmaker
from advanced.create_table_advanced import engine, People
Session_window = sessionmaker(engine)
# 打开查询窗口
db_session = Session_window()


# 增加多条数据
user_obj_list = [People(name="小雪", age=18, gender="女"), People(name="雪人", age=23, gender="女"), People(name="刘夏", age=22, gender="女")]
db_session.add_all(user_obj_list)
print(db_session)
db_session.commit()
db_session.close()

• 7.4,进阶操作的

from advanced.create_table_advanced import engine, People
from sqlalchemy.orm import sessionmaker
Session = sessionmaker(engine)
db_session = Session()

# 引用增加, 批量曹组,也可以筛选出来做批量操作
res = db_session.query(People).update({People.age:People.age+20}, synchronize_session=False)
print(res)  # 显示操作成功几个

db_session.query(People).filter(People.id>0).update({"age":People.age+30}, synchronize_session="evaluate")
db_session.commit()