SQLAlchemy使用汇总

　　之前零碎写了一下SQLAlchemy的使用，现将之前的博客汇总一下。

更多复杂查询参考这2篇博客

https://www.cnblogs.com/echeng192/p/7791984.html

https://www.cnblogs.com/open-yang/p/11278093.html

单表操作

　　前提：注意在使用SQLAlchemy之前需要自己创建一个数据库 —— 我这里用的是MySQL（用户名为root密码为123），创建了名为t1的数据库。

创建表create_table.py　　

# -*- coding:utf-8 -*-
from sqlalchemy.ext.declarative import declarative_base

BaseModel = declarative_base()


### ORM —— 对象关系映射
##Class —— Table —— 通过class 操作 数据库的表;class的属性对应表中的一个字段

### 创建Class/Table

from sqlalchemy import Column,INT,INTEGER,Integer,CHAR,NCHAR,VARCHAR,NVARCHAR,String
# 看源码：INT,INTEGER,Integer其实时一样的～～符合所有程序员的命名习惯
# 看源码：CHAR,NCHAR,VARCHAR,NVARCHAR,String也是一样的～～

class User(BaseModel):
    __tablename__ = 'user' # 创建table时的名字

    # 定义数据列
    id =  Column(INT,primary_key=True,autoincrement=True)
    # 不能为空，索引，唯一(知道有这个设置，其实索引就是限定为唯一了)
    # 注意～以name为索引的话，数据库中的数据是按照name排序的～id就乱了～～～～～～
    name = Column(String(32),nullable=False,index=True,unique=True)

### 利用User去数据库中创建user Table

## 数据库引擎的创建
from sqlalchemy.engine import create_engine
# 注意charset那里必须写utf8，不能写utf-8！！！
engine = create_engine("mysql+pymysql://root:123@127.0.0.1:3306/t1?charset=utf8") # 数据库连接驱动语句

## 找到继承BaseModel的类
BaseModel.metadata.create_all(engine) # 数据库引擎

往表中插入数据insert_data.py

# -*- coding:utf-8 -*-

# 1、选择数据库
# from create_table import engine
from sqlalchemy.engine import create_engine
engine = create_engine("mysql+pymysql://root:123@127.0.0.1:3306/t1?charset=utf8")

# 2、选择表
from create_table import User

# 3、创建查询对象
from sqlalchemy.orm import sessionmaker
select_db = sessionmaker(engine) # 选中数据库
db_session = select_db()  # 打开查询窗口

# 4、写入SQL语句
# 插入单条数据
# user_add_sql = User(name='Naruto') # insert into user('name') value ('wanghw')

# 插入多条数据
user_list = [User(name='www1'),User(name='Sasuke1')]


# 5、放入查询窗口
# 插入单条数据
# db_session.add(user_add_sql)

# 插入多条数据
db_session.add_all(user_list)

# 6、提交
db_session.commit()

# 7、关闭
db_session.close()

查数据search_data.py

# -*- coding:utf-8 -*-
from create_table import User

from sqlalchemy.engine import create_engine
engine = create_engine("mysql+pymysql://root:123@127.0.0.1:3306/t1?charset=utf8")

from sqlalchemy.orm import sessionmaker
select_db = sessionmaker(engine) # 选中数据库
db_session = select_db()  # 打开查询窗口


# 简单无条件查询
res = db_session.query(User).all() # 查询符合条件的所有对象
print(res)
print(res[0].id,res[0].name)

ret = db_session.query(User).first() # 查询符合条件的第一条对象
print(ret.id,ret.name)


# 简单条件查询
ret2 =db_session.query(User).filter(User.id==3).all()
print(ret2)
print(ret2[0].id,ret2[0].name)

### 复杂条件查询，且的关系用逗号
ret3 = db_session.query(User).filter(User.id==3,User.name=='www1').all()
print(ret3)

### 复杂条件查询 —— 注意条件之间不能直接用and与or～ 
# —— 注意"且"的逻辑用逗号！！！and 与 or是逻辑表达式，得出的是一个结果，并不是SQL中且跟或的关系！！！
ret3_1 = db_session.query(User).filter(User.id==3 or User.name=='www1').all()
print('ret4:',ret3_1) # []  ### 查出的结果是错误的

ret3_2 = db_session.query(User).filter(User.id==3 and User.name=='www1').all()
print('ret5:',ret3_2) # [<create_table.User object at 0x1110c87b8>]  ### 查出的结果是错误的


### 复杂条件查询 —— 多条件组合的正确写法！
from sqlalchemy import and_,or_

ret4 = db_session.query(User).filter(
    or_(
        and_(User.id==3,User.name=='www'),
        and_(User.id==5,User.name=='wanghw')
    )
).all()
for i in ret4:
    print(i.id,i.name)

修改数据update_data.py

# -*- coding:utf-8 -*-

from create_table import User

from sqlalchemy.engine import create_engine
engine = create_engine("mysql+pymysql://root:123@127.0.0.1:3306/t1?charset=utf8")

from sqlalchemy.orm import sessionmaker
select_db = sessionmaker(engine) # 选中数据库
db_session = select_db()  # 打开查询窗口



##### 修改数据
ret6 = db_session.query(User).filter(User.id==2).update({'name':'超级赛亚人'})
print(ret6) # 1代表"要修改的数据量"
# 一定要提交一下
db_session.commit()
# 关闭
db_session.close()

删除数据delete_data.py

# -*- coding:utf-8 -*-
from create_table import User

from sqlalchemy.engine import create_engine
engine = create_engine("mysql+pymysql://root:123@127.0.0.1:3306/t1?charset=utf8")

from sqlalchemy.orm import sessionmaker
select_db = sessionmaker(engine) # 选中数据库
db_session = select_db()  # 打开查询窗口



## 删除数据
res = db_session.query(User).filter(User.id==1).delete()
# 提交
db_session.commit()
# 关闭
db_session.close()

外键关系的表的操作

创建有外键关联的两张表create_foreign_key.py

# -*- coding:utf-8 -*-
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column,Integer,String,ForeignKey
from sqlalchemy.engine import create_engine

# ORM精髓
from sqlalchemy.orm import relationship


engine = create_engine('mysql+pymysql://root:123@127.0.0.1:3306/t1?charset=utf8')


BaseModel = declarative_base()


# 一对多
class School(BaseModel):
    # 注意两边都有下划线
    __tablename__ = 'school'
    id = Column(Integer,primary_key=True)
    name = Column(String(32),nullable=False)

# 一对多，把外键字段放在多的那张表中
class Student(BaseModel):
    # 注意两边都有下划线
    __tablename__ = 'student'
    id = Column(Integer, primary_key=True)
    name = Column(String(32), nullable=False)
    # 建立外键关联
    sch_id = Column(Integer,ForeignKey("school.id"))
    # 关系映射
    # backref是反向查询用的
    stu2sch = relationship('School',backref='sch2stu')


BaseModel.metadata.create_all(engine)

插入数据insert_foreignkey.py

# -*- coding:utf-8 -*-
from sqlalchemy.orm import sessionmaker

from create_foreign_key import engine
from create_foreign_key import Student,School


select_db = sessionmaker(engine)
db_session = select_db()

# 一种思路是：# 先建立一个学校 再查询这个学校的id 利用这个ID 再去创建学生添加sch_id
# 但是这种思路很麻烦～～


### 正向添加数据
s1 = Student(name='Naruto',stu2sch=School(name='木叶忍者村'))

db_session.add(s1)
db_session.commit()
db_session.close()

### 反向添加数据
school = School(name="一乐拉面馆")
school.sch2stu = [
    Student(name='Sasuke'),
    Student(name='Sakurua'),
    Student(name='Maitogai'),
]

db_session.add(school)
db_session.commit()
db_session.close()

外键关联的表查询数据search_foreignkey.py——用到默认字典

# -*- coding:utf-8 -*-
from collections import defaultdict
from sqlalchemy.orm import sessionmaker

from create_foreign_key import engine,School,Student


select_db = sessionmaker(engine)
db_session = select_db()


### 正向查询
res = db_session.query(Student).all()
for stu in res:
    # 正向查询 —— 根据学生对象查询这个学生所在的学校
    print(stu.name,stu.stu2sch.name)

### 反向查询
ret = db_session.query(School).all()

dic = defaultdict(list)

for sch in ret:
    # 反向查询，根据学校对象查询这个学校名称以及里面的学生
    # 默认字典
    dic[sch.name]
    dic[sch.name].extend([i.name for i in  sch.sch2stu]) # sch.sch2stu得到的是一个存放着符合条件的对象的列表

print(dict(dic)) # {'木叶忍者村': ['Naruto'], '一乐拉面馆': ['Sasuke', 'Sakurua', 'Maitogai']}

多对多关系的表的操作

创建多对多关系的表create_m2m.py

# -*- coding:utf-8 -*-
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column,Integer,String,ForeignKey
from sqlalchemy.engine import create_engine
from sqlalchemy.orm import relationship


BaseModel = declarative_base()
engine = create_engine('mysql+pymysql://root:123@127.0.0.1:3306/t1?charset=utf8')

class Book(BaseModel):
    __tablename__ = 'book'
    id = Column(Integer,primary_key=True)
    name = Column(String(32),nullable=False)

    # 建立ORM多对多的关系 创建在Book类中，book差author就是正向的
    # secondary是数据库层面的，添加数据时自动往第三张表中插入数据
    # backref是反向查询用的～～
    book2author = relationship('Author',backref = 'author2book',secondary='book_author')

class Author(BaseModel):
    __tablename__ = 'author'
    id = Column(Integer,primary_key=True)
    name = Column(String(32),nullable=False)

# 第三张表
class Book_Author(BaseModel):
    __tablename__ = 'book_author'
    id = Column(Integer, primary_key=True)
    name = Column(String(32), nullable=False)
    # 创建外键
    book_id = Column(Integer,ForeignKey('book.id'))
    author_id = Column(Integer,ForeignKey('author.id'))


# 创建一次
BaseModel.metadata.create_all(engine)

多对多关系表添加数据

# -*- coding:utf-8 -*-
from sqlalchemy.orm import sessionmaker

from create_m2m import engine,Author,Book


select_db = sessionmaker(engine)
db_session = select_db()

# ### 正向添加数据
insert1 = Book(name='三国蔬菜传奇',book2author=[Author(name='Naruto'),Author(name='Sasuke')])
db_session.add(insert1)
db_session.commit()
db_session.close()


### 反向添加数据
author1 = Author(name='wanghw')
author1.author2book = [
    Book(name='一个和尚水很多'),
    Book(name='三个和尚没水喝'),
]

db_session.add(author1)
db_session.commit()
db_session.close()

多对多关系表的查询search_m2m.py——用到默认字典处理数据

# -*- coding:utf-8 -*-
from collections import defaultdict
from sqlalchemy.orm import sessionmaker

from create_m2m import engine,Author,Book


select_db = sessionmaker(engine) # 选中数据库
db_session = select_db()  # 打开查询窗口

dic_book2author = defaultdict(list)
dic_author2book = defaultdict(list)

### 正向查询 —— 查询每本书对应的作者
ret = db_session.query(Book).all()
print(ret)
for obj in ret:
    print(obj.name)
    # 创建以书名为key，值为一个空列表的默认字典
    dic_book2author[obj.name]
    dic_book2author[obj.name].extend([i.name for i in obj.book2author])

print(dict(dic_book2author)) # {'三国蔬菜传奇': ['Naruto', 'Sasuke'], '一个和尚水很多': ['wanghw'], '三个和尚没水喝': ['wanghw']}


### 反向查询 —— 查询每个作者出版的书
res = db_session.query(Author).all()
print(res)
for obj in res:
    print(obj.name)
    # 创建以作者名为key，值为一个空列表的默认字典
    dic_author2book[obj.name]
    dic_author2book[obj.name].extend([i.name for i in obj.author2book])

print(dict(dic_author2book)) # {'Naruto': ['三国蔬菜传奇'], 'Sasuke': ['三国蔬菜传奇'], 'wanghw': ['一个和尚水很多', '三个和尚没水喝']}

～～～