2.8.4SQLAlchemy

总目录：https://www.jianshu.com/p/e406a9bc93a9

Python-后端 - 子目录：https://www.jianshu.com/p/29d6539d6554

武沛齐的sqlalchemy：https://www.cnblogs.com/wupeiqi/articles/8259356.html

官方文档：https://www.osgeo.cn/sqlalchemy/

SQLAlchemy

    ORM（关系映射）框架

        类对应数据库的表

        对象对应数据库的行

    作用：帮助我们使用类和对象快速实现数据库操作


操作数据库的库

      -原生：

        PyMySql（支持Python3和Python2）

        MySqlDB（不支持Python3）

      -ORM框架：

        SQLAlchemy


使用

创建引擎

        engine = create_engine(

            "mysql+pymysql://root:123456@127.0.0.1:3306/python_ech?charsct=utf8",

            max_overflow = 0,  //超过连接池大小外最多创建的连接

            pool_size = 5,    //连接池大小

            pool_timeout = 30, //线程最多等待时间

            pool_recycle = 1  //多久后进行回收（-1不回收）

        )

设置映射

Base = declarative_base()

class Users（Base）：

    __tablename__ = 'users'

    id = Column(Integer,primary_key=True)

    name = Column(String(32),index=True,nullable=False)

    age = Column(Integer,index=True,nullable=False)

    banji = Column(Integer,index=True,nullable=False)

启动

Base.metadata.create_all(engine)     //新建

Base.metadata.drop(engine)             //删除

增删改查

创建连接

SessionFactory = sessionmaker(bind = engine)

session = SessionFactory()

增

obj1 = Users(name="赵谦")

obj2 = Users(name="孙莉")


session.add(obj1)

session.add(obj2)  //插入一个对象


session.add_all([

Users(name="赵谦"),

Users(name="孙莉")

])            //插入多个对象


session.commit()      //执行

session.close()    //关闭连接

查

result = session.query(Users).all()  //取出Users表内所有的数据

for row in result:

    print(row.id,row.name,row.age) //遍历显示


result1 = session.query(Users).filter(Users.id>3)  //取出Users表内id大于3的数据

for row in result:

    print(row.id,row.name,row.age) //遍历显示


result1 = session.query(Users).filter(Users.id>3).first()  //取出Users表内id大于3中的第一条数据

print(result.id,result.name,result.age)

删

session.query(Users).filter(Users.id < 3).delete()  //将id小于3的全部删除

session.commit()

改

session.query(Users).filter(Users.id == 3).updata({Users.name:"周武",Users.age:27}) //将id等于3的name改为周武,age改为27

session.query(Users).filter(Users.id == 3).updata({'name':Users.name+"郑旺"},syschronize_session=False)

session.commit()

-常用操作

1.实例化数据（可以使用索引）

result = session.query(Users.id,Users.name,Users.age).all() 

for item in result:

    print(item[0],item.id,item.name)

2.label别名

result = session.query(Users.id,Users.name.label('xm'),Users.age).all() 

3.查看SQL语句

result = session.query(Users.id,Users.name,Users.age)

print(result)

4.查询默认条件是and

session.query(Users).filter(Users.id > 1,Users.name == '王五').all() 

5.between用于限制范围

session.query(Users).filter(Users.id.between(1,3),Users.name == '王五').all()  

6.in_用于限制范围  in可以是另一条语句  ~Users.id.in_([1,3,4])则为not

session.query(Users).filter(Users.id.in_([1,3,4]),Users.name == '王五').all()    

7.使用in_实现子查询

session.query(Users).filter(Users.id.in_(session.query(Users.id).filter(Users.name != "李四")),Users.name == '王五').all()  

8.or_操作符

session.filter(or_(Users.id>3,Users.name == "张三")).all()

9. filter_by传递的是参数，其他与filter相同

 session.query(Users).filter_by(name = "张三").all() 

10.通配符的使用  %：若干各字符  _:一个字符

session.query(Users).filter(User.name.like('张%')).all() 

11.切片

session.query(Users)[1:3]  

12. 降序排序   asc升序   User.age.desc(),User.id.desc()先按照age排序，若age相同，按照id排序

session.query(Users).order_by(User.age.desc()).all()  

13. 分组  可以使用func聚合条件

session.query(Users).group_by(User.banji).all() 

14.使用func聚合条件

session.query(func.max(Users.id)).group_by(User.banji).all()  

15.组合

q1.union(q2).all()  q1.union_all(q2).all()  

上面是我第一次学习时的笔记。

下面开始说这次复习：

安装

语法

python -m pip install sqlalchemy

连接数据库

from sqlalchemy import create_engine


#数据库类型+数据库引擎://用户名：密码@IP地址:端口号/数据库名?charset=使用的编码

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

print(engine)

Engine(mysql+pymysql://root:***@127.0.0.1:3306/sql_py?charset=utf8)


建表

from sqlalchemy import create_engine

from sqlalchemy.ext.declarative import declarative_base

from sqlalchemy import Column,String,Integer

#数据库类型+数据库引擎://用户名：密码@IP地址:端口号/数据库名?charset=使用的编码

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


#生成orm基类

Base = declarative_base() 


#继承生成的orm基类

class Student(Base):

    __tablename__ ="student"                             #表名

    id = Column(Integer,primary_key=True)        #int类型，设置主键

    name = Column(String(32))                            #字符串类型，32位长度

    banji = Column(String(32))


#创建表

Base.metadata.create_all(engine)



插入数据

from sqlalchemy import create_engine

from sqlalchemy.ext.declarative import declarative_base

from sqlalchemy import Column,String,Integer

from sqlalchemy.orm import sessionmaker


#数据库类型+数据库引擎://用户名：密码@IP地址:端口号/数据库名?charset=使用的编码

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

#生成orm基类

Base = declarative_base()


class Student(Base):#继承生成的orm基类

    __tablename__ ="student"            #表名

    id = Column(Integer,primary_key=True)#int类型，设置主键

    name = Column(String(32))#字符串类型，32位长度

    banji = Column(String(32))


#创建与数据库的会话，Session_Class为一个类

Session_Class = sessionmaker(bind=engine)

#实例化与数据库的会话

Session = Session_Class()


#插入一行数据

t1=Student(name="小明",banji="三班")

Session.add(t1)


#插入多行数据

Session.add_all([

Student(name="小红",banji="二班"),

    Student(name="小刚",banji="一班")

])


#必须要提交

Session.commit()

#关闭连接

Session.close()


为了更好的进行下面的操作，在数据库中插入了二十条数据。

查询数据 

普通查询

from sqlalchemy import create_engine

from sqlalchemy.ext.declarative import declarative_base

from sqlalchemy import Column,String,Integer

from sqlalchemy.orm import sessionmaker


#数据库类型+数据库引擎://用户名：密码@IP地址:端口号/数据库名?charset=使用的编码

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


#生成orm基类

Base = declarative_base()

class Student(Base):#继承生成的orm基类

    __tablename__ ="student"            #表名

    id = Column(Integer,primary_key=True)#int类型，设置主键

    name = Column(String(32))#字符串类型，32位长度

    banji = Column(String(32))


#创建与数据库的会话，Session_Class为一个类

Session_Class = sessionmaker(bind=engine)

#实例化与数据库的会话

Session = Session_Class()


#取出Student表内所有的数据

result = Session.query(Student).all()

for rowin result:

    print(row.id, row.name, row.banji)


#关闭连接

Session.close()

运行结果

条件查询（filter）

#取出Student表中id大于15的记录

result = Session.query(Student).filter(Student.id >=15)

for rowin result:

    print(row.id, row.name, row.banji)

运行结果

first()

如果只想要第一条数据就可以用first()。

#取出Student表中id大于15的记录中的第一条

result = Session.query(Student).filter(Student.id >=15).first()

print(result.id,result.name,result.banji)

运行结果


排序查询

#取出Student表中数据，按照banji降序排序 默认升序排序

result = Session.query(Student).order_by(Student.banji.desc()).all()

for rowin result:

    print(row.id, row.name, row.banji)


运行结果

双排序

#取出Student表中数据，外部按照banji降序排序，内部按照id升序排序

result = Session.query(Student).order_by(Student.banji.desc(),Student.id).all()

for rowin result:

    print(row.id, row.name, row.banji)

运行结果

分组查询

#取出Student表中数据，按照banji分组。

result = Session.query(Student).group_by(Student.banji).all()

for rowin result:

    print(row.id, row.name, row.banji)

运行结果

分组查询一般都是结合聚合函数。

注 需要导入func函数   

from sqlalchemy.sql import func

#取出Student表中数据，按照banji分组，查询每班有多少人。

result = Session.query(func.count(Student.id)).group_by(Student.banji).all()

for rowin result:

    print(row)

运行结果是元组类型

运行结果

分组查询当然有分组条件了

#取出Student表中数据，按照banji分组，查询班级人数是偶数的班级人数。

result = Session.query(func.count(Student.id)).group_by(Student.banji).having(func.count(Student.id)%2 ==0).all()  

for rowin result:

    print(row)

运行结果


删除数据

删除数据建立在查询数据之上，查询出来数据，删除。

from sqlalchemy import create_engine

from sqlalchemy.ext.declarative import declarative_base

from sqlalchemy import Column,String,Integer

from sqlalchemy.orm import sessionmaker


#数据库类型+数据库引擎://用户名：密码@IP地址:端口号/数据库名?charset=使用的编码

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


#生成orm基类

Base = declarative_base()

class Student(Base):#继承生成的orm基类

    __tablename__ ="student"            #表名

    id = Column(Integer,primary_key=True)#int类型，设置主键

    name = Column(String(32))#字符串类型，32位长度

    banji = Column(String(32))


#创建与数据库的会话，Session_Class为一个类

Session_Class = sessionmaker(bind=engine)

#实例化与数据库的会话

Session = Session_Class()


#删除id大于15的信息

result = Session.query(Student).filter(Student.id >=15).delete()

#提交修改

Session.commit()

#关闭连接

Session.close()

运行结果


修改数据

#一些常见的修改操作

Session.query(Student).filter(Student.id ==1).update({Student.banji:'五班'})

Session.query(Student).filter(Student.id ==2).update({'name':'王红'})

Session.query(Student).filter(Student.id ==3).update({'name':Student.name+"（尖子生）"},synchronize_session=False)

synchronize_session用于query在进行delete or update操作时，对session的同步策略。

也就是修改是否要在当前session中生效。

运行结果


事务

实例：郑旺要和冯晨换班。

#换班

try:

    Session.query(Student).filter(Student.id ==7).update({Student.banji:'二班'})

    raise Exception  #在这里抛出错误  让操作中断

    Session.query(Student).filter(Student.id ==8).update({Student.banji:'三班'})

    Session.commit()

except Exception as e:

    #收到错误，回滚，结束事务。

    Session.rollback()

    Session.commit()

换班失败


其他常用操作

别名

#label 设置别名

result = Session.query(Student.id.label("学号")).filter(Student.id <=5)

for i in result:

    print(i.学号)

运行结果


查看SQL语句

#查看sql语句

result = Session.query(Student.id.label("学号")).filter(Student.id <=5)

print(result)

运行结果


操作符

操作符有  in_  和 ~in_  两种

#使用in_ 操作符 找到符合的id

result = Session.query(Student).filter(Student.id.in_([1,3,4,5])).all()

for i in result:

    print(i.id,i.name,i.banji)

运行结果

#~in_ 会输出和 in_ 相反的结果

result = Session.query(Student).filter(~Student.id.in_([1,3,4,5])).all()

for i in result:

    print(i.id,i.name,i.banji)

运行结果


逻辑运算符（and_ in or_）

不适用的逻辑运算符：

print("----------普通查询----------")

#普通查询

result = Session.query(Student).filter(Student.banji =='一班',Student.id >=8).all()

for i in result:

    print(i.id,i.name,i.banji)

运行结果

使用逻辑运算符：

from sqlalchemy import and_, or_


print("----------and_----------")

#使用and_

result = Session.query(Student).filter(and_(Student.banji =='一班',Student.id >=8)).all()

for i in result:

    print(i.id,i.name,i.banji)


print("----------or_----------")

#使用or_

result = Session.query(Student).filter(or_(Student.banji =='一班',Student.id >=8)).all()

for i in result:

    print(i.id,i.name,i.banji)


print("----------or_(and_)----------")

#and_  和 or_  可以嵌套使用  也可以并行使用

result = Session.query(Student).filter(

    or_(

        Student.banji =='一班',

        and_(Student.id >=8,Student.id <=12)

    )

).all()

for i in result:

    print(i.id,i.name,i.banji)

运行结果

通配符

#使用通配符

result = Session.query(Student).filter(Student.name.like('小%')).all()

for i in result:

    print(i.id,i.name,i.banji)


print("----------使用反向通配符---------")

#同样也有反向的

result = Session.query(Student).filter(~Student.name.like('小%')).all()

for i in result:

        print(i.id,i.name,i.banji)

运行结果

联合查询

Union：对两个结果集进行并集操作，不包括重复行，同时进行默认规则的排序；

union_all：对两个结果集进行并集操作，包括重复行，不进行排序；

我们建立另一个表

s1 = Session.query(Student.name).filter(Student.id >=1)

s2 = Session.query(Student1.name).filter(Student1.id >=1)

result=s1.union(s2).all()

for iin result:

    print(i.name)


print("---")


s1 = Session.query(Student.name).filter(Student.id >=1)

s2 = Session.query(Student1.name).filter(Student1.id >=1)

result=s1.union_all(s2).all()

for iin result:

    print(i.name)

union运行结果
union_all运行结果
最后编辑于 ：
©著作权归作者所有,转载或内容合作请联系作者
  • 人面猴
    序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
    沈念sama阅读 205,386评论 6赞 479
  • 死咒
    序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
    沈念sama阅读 87,939评论 2赞 381
  • 救了他两次的神仙让他今天三更去死
    文/潘晓璐 我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
    开封第一讲书人阅读 151,851评论 0赞 341
  • 道士缉凶录：失踪的卖姜人
    文/不坏的土叔 我叫张陵，是天一观的道长。 经常有香客问我，道长，这世上最难降的妖魔是什么？ 我笑而不...
    开封第一讲书人阅读 54,953评论 1赞 278
  • 港岛之恋（遗憾婚礼）
    正文 为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
    茶点故事阅读 63,971评论 5赞 369
  • 恶毒庶女顶嫁案：这布局不是一般人想出来的
    文/花漫 我一把揭开白布。 她就那样静静地躺着，像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上，一...
    开封第一讲书人阅读 48,784评论 1赞 283
  • 城市分裂传说
    那天，我揣着相机与录音，去河边找鬼。 笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。 我是一名探鬼主播，决...
    沈念sama阅读 38,126评论 3赞 399
  • 双鸳鸯连环套：你想象不到人心有多黑
    文/苍兰香墨 我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
    开封第一讲书人阅读 36,765评论 0赞 258
  • 万荣杀人案实录
    序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
    沈念sama阅读 43,148评论 1赞 300
  • 护林员之死
    正文 独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 35,744评论 2赞 323
  • 白月光启示录
    正文 我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 37,858评论 1赞 333
  • 活死人
    序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
    沈念sama阅读 33,479评论 4赞 322
  • 日本核电站爆炸内幕
    正文 年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
    茶点故事阅读 39,080评论 3赞 307
  • 男人毒药：我在死后第九天来索命
    文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 30,053评论 0赞 19
  • 一桩弑父案，背后竟有这般阴谋
    文/苍兰香墨 我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 31,278评论 1赞 260
  • 情欲美人皮
    我被黑心中介骗来泰国打工， 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。 一个月前我还...
    沈念sama阅读 45,245评论 2赞 352
  • 代替公主和亲
    正文 我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
    茶点故事阅读 42,590评论 2赞 343

推荐阅读更多精彩内容

  • pySpark 中文API (2)
    pyspark.sql模块 模块上下文 Spark SQL和DataFrames的重要类： pyspark.sql...
    mpro阅读 9,446评论 0赞 13
  • [转]SQLAlchemy Introduce | 绿萝间
    转载，觉得这篇写 SQLAlchemy Core，写得非常不错。不过后续他没写SQLAlchemy ORM... ...
    非梦nj阅读 5,371评论 1赞 14
  • Java面试
    一. Java基础部分.................................................
    wy_sure阅读 3,789评论 0赞 11
  • sqlalchemy操作手册
    orm操作是所有完整软件中后端处理最重要的一部分，主要完成了后端程序和数据库之间的数据同步和持久化的操作，本文基于...
    大熊_7d48阅读 4,993评论 1赞 13
  • 9.8作业
    8月22日-----字符串相关 2-3 个性化消息: 将用户的姓名存到一个变量中，并向该用户显示一条消息。显示的消...
    future_d180阅读 957评论 0赞 1