SQLAlchemy

在使用 django 操作数据库时。会使用django提供的ORM，在使用其他框架时。没有自带的ORM，这个时候就出现了一个替代品 -- SQLAlchemy

SQLAlchemy是 Python 编程语言下的一款开源软件。提供了SQL工具包及对象关系映射（ORM）工具

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

SQLAlchemy 的一个目标是提供能兼容众多数据库（如 SQLite、MySQL、Postgres、Oracle、MS-SQL、SQLServer 和 Firebird）的企业级持久性模型

单表操作

创建表

# 导入模块
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.engine import create_engine

from sqlalchemy import Column
from sqlalchemy import String
from sqlalchemy import Integer

# 实例化，建立基础类 -- 关系映射类
model = declarative_base()

# 创建引擎。用于指定库
engine = create_engine('mysql+pymysql://root:123@127.0.0.1:3306/test?charset=utf8')


class Users(model):
    '''
    Users : 类名，对象
    user : 真实的表名
    Column : 列  数据库中的字段所在位置
    Integer ：当前字段的数据类型，看写参数控制长度
    String  ：当前字段的数据类型，可写参数控制长度
    '''
    __tablename__ = 'user'
    id = Column(Integer,primary_key=True,autoincrement=True)
    name = Column(String(32),nullable=False)

# 检索所有继承model的对象，并在engine指向的库中创建表
model.metadata.create_all(engine)

需要注意的时。在创建表的时候。需要把字段写入。不写字段会报错

增加数据

from sqlalchemy.orm import sessionmaker
from create_table import engine,Users

# 新建查询窗口
session = sessionmaker(engine)

# 打开查询窗口
db_session = session()

info = Users(name='略略略') # 新建SQL语句 -- insert into
db_session.add(info) # 将SQL添加到查询窗口
db_session.commit() # 执行窗口内SQL语句
db_session.close() # 关闭窗口

from sqlalchemy.orm import sessionmaker
from create_table import engine,Users
# 新建查询窗口
session = sessionmaker(engine)

# 打开查询窗口
db_session = session()

info_list = [Users(name='大G'),Users(name='小G')]
db_session.add_all(info_list) # 将SQL添加到查询窗口
db_session.commit() # 执行窗口内SQL语句
db_session.close()

 查询数据

from sqlalchemy.orm import sessionmaker
from create_table import engine,Users
# 新建查询窗口
session = sessionmaker(engine)

# 打开查询窗口
db_session = session()

# 查询出所有数据
res = db_session.query(Users).all()
print(res)
for i in res:
    print(i.name)
'''
[
 <__main__.Users object at 0x000002955002C4E0>,
 <__main__.Users object at 0x000002955002C128>, 
 <__main__.Users object at 0x000002955002C2B0>
 ]
 略略略
 大G
 小G
'''
# 查询出所有数据中的第一条
ret = db_session.query(Users).first()
print(ret)
print(ret.name)
'''
<__main__.Users object at 0x000002955002C4E0>
略略略
'''

# 简单的待条件查询
res = db_session.query(Users).filter(Users.id < 2).all()
print(res)
print(res[0].name)
'''
[<__main__.Users object at 0x0000022A71C2B320>]
略略略
'''

# 并列条件查询
res = db_session.query(Users).filter(Users.id<4,Users.name=="略略略").all()
print(res)
print(res[0].id)
'''
[<__main__.Users object at 0x000001E378B2B4E0>]
1
'''

更新数据

from sqlalchemy.orm import sessionmaker
from create_table import engine,Users
# 新建查询窗口
session = sessionmaker(engine)

# 打开查询窗口
db_session = session()

db_session.query(Users).filter(Users.id==1).update({'name':'哈哈哈'})
db_session.commit()

删除数据

from sqlalchemy.orm import sessionmaker
from create_table import engine,Users
# 新建查询窗口
session = sessionmaker(engine)

# 打开查询窗口
db_session = session()

db_session.query(Users).filter(Users.id==1).delete()
db_session.commit()

 

多表操作

一对多

创建表

# 导入模块
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.engine import create_engine
from sqlalchemy.orm import relationship #用于建立关系 # relationship ORM精髓所在
from sqlalchemy import Column
from sqlalchemy import String
from sqlalchemy import Integer
from sqlalchemy import ForeignKey

# 实例化，建立基础类 -- 关系映射类
model = declarative_base()

# 创建引擎。用于指定库
engine = create_engine('mysql+pymysql://root:123@127.0.0.1:3306/test?charset=utf8')

class Student(model):
    __tablename__ = "student"
    id = Column(Integer, primary_key=True)
    name = Column(String(32), nullable=False)
    sch_id = Column(Integer, ForeignKey("school.id"))  # 多对一关系存储列
    stu2sch = relationship("School", backref="sch2stu")


class School(model):
    __tablename__ = "school"
    id = Column(Integer, primary_key=True)
    name = Column(String(32), nullable=False)

model.metadata.create_all(engine)

增加数据

建议先后给外键所在表添加数据

from sqlalchemy.orm import sessionmaker
from create_table_ForeignKey import engine

session = sessionmaker(engine)
db_session = session()

sc = School(name='哈佛大学')
db_session.add(sc)
db_session.commit()
db_session.colse()


# 在给外键所在的表添加数据

from sqlalchemy.orm import sessionmaker
from create_table_ForeignKey import engine

session = sessionmaker(engine)
db_session = session()
school_id = db_session.query(School).filter(School.name=='哈佛大学').first()
print(school_id)
stu = Student(name='大熊',sch_id=school_id.id)
db_session.add(stu)
db_session.commit()

from sqlalchemy.orm import sessionmaker
from create_table_ForeignKey import engine
from create_table_ForeignKey import Student,School

session = sessionmaker(engine)
db_session = session()

stu = Student(name='静香',stu2sch=School(name='剑桥大学')) 
db_session.add(stu)
db_session.commit()

from sqlalchemy.orm import sessionmaker
from s1.create_table_ForeignKey import engine
from s1.create_table_ForeignKey import Student,School

session = sessionmaker(engine)
db_session = session()

sch = School(name='牛津大学')
sch.sch2stu = [
    Student(name='胖虎'),
    Student(name='樱桃小丸子')
]
db_session.add(sch)
db_session.commit()

查询数据

from sqlalchemy.orm import sessionmaker
from create_table_ForeignKey import engine
from create_table_ForeignKey import Student,School

session = sessionmaker(engine)
db_session = session()

res = db_session.query(Student).all()
for sch in res:
    print(sch.stu2sch.name)

from sqlalchemy.orm import sessionmaker
from create_table_ForeignKey import engine
from create_table_ForeignKey import Student,School

session = sessionmaker(engine)
db_session = session()

res = db_session.query(School).all()
for sch in res:
    for s in sch.sch2stu:
        print(s.name)

更新数据

from sqlalchemy.orm import sessionmaker
from create_table import engine,Users
# 新建查询窗口
session = sessionmaker(engine)

# 打开查询窗口
db_session = session()


sch = db_session.query(School).filter(School.name=='哈佛大学').first()
stu = db_session.query(Student).filter(Student.id==sch.id).update({'name':'胖小虎'})
db_session.commit()

 删除数据

from sqlalchemy.orm import sessionmaker
from create_table import engine,Users
# 新建查询窗口
session = sessionmaker(engine)

# 打开查询窗口
db_session = session()

sch = db_session.query(School).filter(School.name=='哈佛大学').first()
stu = db_session.query(Student).filter(Student.id==sch.id).delete()
db_session.commit()

多对多操作

创建表

在多对多中。SQLAlchemy不会为我们自动创建出第三张表，需要手动创建

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column,Integer,String,ForeignKey
from sqlalchemy.orm import relationship
from sqlalchemy.engine import create_engine

Model = declarative_base()
engine = create_engine("mysql+pymysql://root:123@127.0.0.1:3306/test?charset=utf8")

class Girls(Model):
    __tablename__ = "girl"
    id = Column(Integer,primary_key=True)
    name = Column(String(32),nullable=False)

    g2b = relationship("Boys",backref="b2g",secondary="hotel")

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


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


Model.metadata.create_all(engine)

增加数据

from sqlalchemy.orm import sessionmaker
from create_table_M2M import engine
from create_table_M2M import Girls,Boys

s = sessionmaker(engine)
db_session = s()

g = Girls(name='玛丽莲',g2b=[Boys(name='美国队长'),Boys(name='闪电侠')])
db_session.add(g)
db_session.commit()

from sqlalchemy.orm import sessionmaker
from create_table_M2M import engine
from create_table_M2M import Girls,Boys

s = sessionmaker(engine)
db_session = s()


b = Boys(name='钢铁侠')
b.b2g = [
    Girls(name='蜘蛛精'),
    Girls(name='白骨精')
]
db_session.add(b)
db_session.commit()

查询数据

from sqlalchemy.orm import sessionmaker
from create_table_M2M import engine
from create_table_M2M import Girls,Boys

session = sessionmaker(engine)
db_session = session()

g_list = db_session.query(Girls).all()
for g in g_list:
    for boy in g.g2b:
        print(boy.name)

from sqlalchemy.orm import sessionmaker
from create_table_M2M import engine
from create_table_M2M import Girls,Boys

session = sessionmaker(engine)
db_session = session()

b_list = db_session.query(Boys).all()
for b in b_list:
    for girl in b.b2g:
        print(girl.name)