sqlachemy知识点

mysql语句

1、GROUP BY基本语法格式:

GROUP BY关键字可以将查询结果按照某个字段或多个字段进行分组。字段中值相等的为一组。基本的语法格式如下：

GROUP BY 属性名 [HAVING 条件表达式] [WITH ROLLUP]

属性名：是指按照该字段的值进行分组。

HAVING 条件表达式：用来限制分组后的显示，符合条件表达式的结果将被显示。

WITH ROLLUP：将会在所有记录的最后加上一条记录。加上的这一条记录是上面所有记录的总和。

2、GROUP BY联合函数使用：

1）GROUP BY关键字可以和GROUP_CONCAT()函数一起使用。

2）GROUP_CONCAT()函数会把每个分组中指定的字段值都显示出来。

3）同时，GROUP BY关键字通常与集合函数一起使用。集合函数包括COUNT()函数、SUM()函数、AVG()函数、MAX()函数和MIN()函数等。

4）注意：如果GROUP BY不与上述函数一起使用，那么查询结果就是字段取值的分组情况。字段中取值相同的记录为一组，但是只显示该组的第一条记录。

GROUP BY关键字只显示每个分组的一条记录。这说明，GROUP BY关键字单独使用时，只能查询出每个分组的一条记录，这样做的意义不大。

因此，一般在使用集合函数时才使用GROUP BY关键字。

group_concat( [DISTINCT] 要连接的字段 [Order BY 排序字段 ASC/DESC] [Separator '分隔符'] )

GROUP_CONCAT函数返回一个字符串结果，该结果由分组中的值连接组合而成

1.2 GROUP BY关键字与GROUP_CONCAT()函数一起使用

GROUP BY关键字与GROUP_CONCAT()函数一起使用时，每个分组中指定的字段值会全部显示出来。

1.3 GROUP BY关键字与集合函数一起使用

GROUP BY关键字与集合函数一起使用时，可以通过集合函数计算分组中的总记录、最大值、最小值等。----这些函数是对每个分组进行计算的

1.4 GROUP BY关键字与HAVING一起使用

使用GROUP BY关键字时，如果加上“HAVING 条件表达式”，则可以限制输出的结果。只有符合条件表达式的结果才会显示，这个having表达式是对

每个分组进行过滤筛选的

1.5按照多个字段进行分组

在MySQL中，还可以按照多个字段进行分组，对多个字段进行分组的话，例如按照a,b,c进行分组，那么先按照a进行分组，遇到多个a相同的时候，

再按照b进行分组，遇到多个b相同的时候，再安装c进行分组，这样一层一层的进行下去

左联：首先取出a表中所有数据,然后再加上与a,b匹配的的数据

内联：两个表a,b相连接,要取出id相同的字段

右联：指的是首先取出b表中所有数据,然后再加上与a,b匹配的的数据

二、Mysql联表查询

2.1 内联结和外联结的含义及区别

1.内联结：将两个表中存在联结关系的字段符合联结关系的那些记录形成记录集的联结。

2.外联结：分为外左联结和外右联结。

说明：

1）内外联结的区别是内联结将去除所有不符合条件的记录，而外联结则保留其中部分。

2) 外左联结与外右联结的区别在于如果用A左联结B则A中所有记录都会保留在结果中，此时B中只有符合联结条件的记录，而右联结相反。

1）内联结：

Select A.Name B.Hobby from A, B where A.id = B.id

它的作用和：

Select A.Name from A INNER JOIN B ON A.id = B.id

是一样的。

2）外左联结

Select A.Name from A Left JOIN B ON A.id = B.id

这样查询得到的结果将会是保留所有A表中联结字段的记录，若无与其相对应的B表中的字段记录则留空

2）外右联结

Select A.Name from A Right JOIN B ON A.id = B.id

此时B表中的全部记录都打印了，但是A表没有显示完整记录，只是显示了跟B表相关联的记录。

2.3、联表查询中用到的一些参数

1.USING (column_list)：

其作用是为了方便书写联结的多对应关系，大部分情况下USING语句可以用ON语句来代替，如下面例子：

a LEFT JOIN b USING (c1,c2,c3)，其作用相当于

a LEFT JOIN b ON a.c1=b.c1 AND a.c2=b.c2 AND a.c3=b.c3

2.STRAIGHT_JOIN：

由于默认情况下MySQL在进行表的联结的时候会先读入左表，当使用了这个参数后MySQL将会先读入右表，这是个MySQL的内置优化参数，

大家应该在特定情况下使用，譬如已经确认右表中的记录数量少，在筛选后能大大提高查询速度。

=====================

!/usr/bin/env python

-- coding:utf-8 --

from sqlalchemy.ext.declarative import declarative_base

from sqlalchemy import Column, Integer, String, ForeignKey, UniqueConstraint, Index

from sqlalchemy.orm import sessionmaker, relationship

from sqlalchemy import create_engine

engine = create_engine("mysql+pymysql://root:123@127.0.0.1:3306/t1", max_overflow=5) 建立数据库连接

Base = declarative_base() # 创建base module

创建单表

class Users(Base): 创建类，要继承Base

tablename = 'users' 定义的表名

id = Column(Integer, primary_key=True) # 下面是创建了三列数据

name = Column(String(32))

extra = Column(String(16))

__table_args__ = (

UniqueConstraint('id', 'name', name='uix_id_name'),    加了联合索引，上面一行是唯一，下面一行是加索引。

    Index('ix_id_name', 'name', 'extra'),

)

一对多

class Favor(Base):

tablename = 'favor'

nid = Column(Integer, primary_key=True)

caption = Column(String(50), default='red', unique=True)

class Person(Base):

tablename = 'person'

nid = Column(Integer, primary_key=True)

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

favor_id = Column(Integer, ForeignKey("favor.nid")) 和favor表的id做了外键关联

多对多

class Group(Base):

tablename = 'group'

id = Column(Integer, primary_key=True)

name = Column(String(64), unique=True, nullable=False)

class Server(Base):

tablename = 'server'

id = Column(Integer, primary_key=True, autoincrement=True)

hostname = Column(String(64), unique=True, nullable=False)

port = Column(Integer, default=22)

class ServerToGroup(Base): 创建多对多关系，得创建第三张表。

tablename = 'servertogroup'

nid = Column(Integer, primary_key=True, autoincrement=True)

server_id = Column(Integer, ForeignKey('server.id')) # 外键到server表的id

group_id = Column(Integer, ForeignKey('group.id')) # 外键到group表的id

def init_db():

Base.metadata.create_all(engine) 只要执行这一句，就会自动找base所有的子类，根据这些子类把表批量创建出来。

def drop_db():

Base.metadata.drop_all(engine) 表示批量删除所有base的子类创建的表

2、操作表

创建session会话。通过session来操作数据库。如果想往表里添加数据的话，用session.add()或者session.addall()就可以往从某个表里加了数据

Session = sessionmaker(bind=engine)

session = Session()

obj = Users(name="qiaomei", extra='qm') 创建一个记录，就是创建一个对象

session.add(obj) #把对象加到session里

session.add_all([

Users(name="qiaomei1", extra='qm'),

Users(name="qiaomei2", extra='qm'),

])

session.commit()

如果是多个条件，且的话，就在filter里加上逗号，添加多个条件。

session.query(Users).filter(Users.id > 2,Users.name='qiaomei').delete()

session.commit()

增

obj = Users(name="qiaomei0", extra='qm')

session.add(obj)

session.add_all([

Users(name="qiaomei1", extra='qm'),

Users(name="qiaomei2", extra='qm'),

])添加多个对象的话，需要把这些对象存放到一个列表里面

session.commit()

删

session.query(Users).filter(Users.id>2,Users.name=='xiaowei5').delete() 记住这里用的是等号，不是赋值语句

session.commit()

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

False

不对session进行同步，直接进行delete or update操作。

fetch

'在delete or update操作之前，先发一条sql到数据库获取符合条件的记录

在delete or update操作之后，将session的identity_map与前一步获取到的记录进行match，符合条件的就从session中删掉或更新

evaluate'

在delete or update操作之前，用query中的条件直接对session的identity_map中的objects进行eval操作，将符合条件的记录下来。

改---注意这里面是用键值对进行更改

session.query(Users).filter(Users.id > 2).update({"name" : "099"})

session.query(Users).filter(Users.id > 2).update({Users.name: Users.name + "099"}, synchronize_session=False)

session.query(Users).filter(Users.id > 2).update({"num": Users.num + 1}, synchronize_session="evaluate")

session.commit()

查

ret = session.query(Users).all()-----从数据库中读取记录信息，这些信息，以对象的方式，从在一个列表里面，类的一个实例

print(type(ret[0])) # <class 'main.Users'>

print ret

ret = session.query(Users.name, Users.extra).all()---以元组的形式进行呈现

ret = session.query(Users).filter_by(name='qiaomei').all()

ret = session.query(Users).filter_by(name='qiaomei').first()

session.commit()

<class 'main.Users'>

[<main.Users object at 0x2259b10>, <main.Users object at 0x2259e90>, <main.Users object at 0x2259f10>]

如果想查看生成的sql语句是什么，就不加all()

q = session.query(Users) -----如果不加all的话，那么打印出来的结果是sql语句，不是查询到的结果

print(q) # SELECT users.id AS users_id, users.name AS users_name, users.extra AS users_extra FROM users

all函数已列表的形式，打印出来内容

其他内容

条件

ret = session.query(Users).filter_by(name='alex').all()

ret = session.query(Users).filter(Users.id > 1, Users.name == 'eric').all() # filtwr里有逗号，条件是并且的关系

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

ret = session.query(Users).filter(Users.id.in_([1,3,4])).all() # id是1,3,4其中一个

ret = session.query(Users).filter(~Users.id.in_([1,3,4])).all() # id不在，~是否的意思

ret = session.query(Users).filter(Users.id.in_(session.query(Users.id).filter_by(name='eric'))).all() # id在一个数组中，但是要先执行里面的sql

from sqlalchemy import and_, or_

ret = session.query(Users).filter(and_(Users.id > 3, Users.name == 'eric')).all() # and 表示都是并且关系

ret = session.query(Users).filter(or_(Users.id < 2, Users.name == 'eric')).all()

ret = session.query(Users).filter( # 内部是and连接，得出一个布尔值，然后外面再用or

or_(

Users.id < 2,

and_(Users.name == 'eric', Users.id > 3),

Users.extra != ""

)).all()

通配符

ret = session.query(Users).filter(Users.name.like('e%')).all() # %表示通配符

ret = session.query(Users).filter(~Users.name.like('e%')).all() # ~表示否

限制

ret = session.query(Users)[1:2]

排序

ret = session.query(Users).order_by(Users.name.desc()).all()

ret = session.query(Users).order_by(Users.name.desc(), Users.id.asc()).all() # 首先按照User表倒序排列，如果有相同，再排序

分组

from sqlalchemy.sql import func

ret = session.query(Users).group_by(Users.extra).all()

ret = session.query(

func.max(Users.id),

func.sum(Users.id),

func.min(Users.id)).group_by(Users.name).all() # 按照name分组，并且取其他列（id列）的最大，总数，最小值。

ret = session.query(

func.max(Users.id),

func.sum(Users.id),

func.min(Users.id)).group_by(Users.name).having(func.min(Users.id) >2).all() # having是group_by的条件过滤

连表

ret = session.query(Users, Favor).filter(Users.id == Favor.nid).all() 直接让两个表进行连接

直接让两个表联合。这里join默认是innerjoin，这里没有写他们的对应关系，它们在内部自己找。

它是怎么找的呢，在创建表的时候，有类型是foreignkey，是根据它来找的。

ret = session.query(Person).join(Favor).all()

ret = session.query(Person).join(Favor, isouter=True).all() # isouter=True表示leftjoin。没有rightjoin，如果想rightjoin，替换表写的位置。

组合

q1 = session.query(Users.name).filter(Users.id > 2)

q2 = session.query(Favor.caption).filter(Favor.nid < 2)

ret = q1.union(q2).all() # 把q1.q2两个联合的全部取到，union会帮你去重

q1 = session.query(Users.name).filter(Users.id > 2)

q2 = session.query(Favor.caption).filter(Favor.nid < 2)

ret = q1.union_all(q2).all() # union_all不会帮你去重

7.3 sqlalchemy单表查询

1）repr 指定打印对象显示内容

def repr 通过这样定义，print Users对象的时候，就打印出需要显示的内容。

如果是django,如果是python2.7，就是__unicode__方式，如果是python3，就是__str__方式。

ret = session.query(Users).filter_by(name='qiaomei').all()

print ret 这时候显示出的是__repr__打印的结果组成的数组。

__repr__对数据库查询，一点作用都没有，只是print对象有用。

在类中没有增加__repr__方法的输出结果

[root@RGCC01 chenwei]# python sqlacchemy.py

<class 'main.Users'>

[<main.Users object at 0x157eb10>, <main.Users object at 0x157ee90>, <main.Users object at 0x157ef10>]

099099

SELECT users.name AS users_name, users.extra AS users_extra

FROM users

[]

在类中增加了__repr__方法的输出结果

<class 'main.Users'>

[4-099099, 1-xiaowei, 2-xiaowei1]

099099

SELECT users.name AS users_name, users.extra AS users_extra

FROM users

[]

None

两者结果对比可以得出一个结论，sqlalchemy的查询输出结果形式，是由类里面的__repr__方法进行格式化的，每一个表，即表对应的类，都可以单独的

重新构造这个方法，这个方法是对查询到的整条记录，为对象输出时，格式化（个人认为）

2）获取属性

ret = session.query(Users).filter_by(name='qiaomei').all()

print ret[0].name就是获取名字。

这种方式是直接获取属性值

q1 = session.query(Users.name).filter(Users.id > 2)

print q1

打印：[('qiaomei1',),('qiaomei2',)]

7.5 联表查询-一对多-推荐方式

1）正向查询

正向查询指的是：多对一，多的那端开始查，也就是foreignkey写在哪里，从哪里查。

使用上面的方法非常麻烦，我们用更高效的方法。

只要在表里加上这一句话：

favor = relationship("Favor", backref='pers')

2）反向查询

反向查询指的是：多对一，从一的那端开始查，也就是从没写foreignkey的表里反查。

多对一，从一的那端反查。

Person和Favor是多对一，假如查询喜欢蓝色的所有人。Favor的caption为blue的所有对应的Person

传统方式，反向查询：

ret3 = session.query(Person.name,Favor.caption).join(Favor,isouter=True).filter(Favor.caption == 'blue').all()

Person表里，写了backref='pers',就相当于在favor表里加了个字段pers

favor = relationship("Favor", backref='pers')

3）总结

Foreignkey和relationship要成对写在一个表里。

class Person(Base):

tablename = 'person'

nid = Column(Integer, primary_key=True)

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

favor_id = Column(Integer, ForeignKey("favor.nid"))

# 与生成表结构无关，仅用于查询方便

favor = relationship("Favor", backref='pers')

Person对Favor 是多对一的关系，foreignkey加在了多的那端（Person表）。

Person对象.favor.favor的字段：叫做正向查找

Favor对象.pers.person的字段：反向查找

==============

实战操作

session.add() 是建立在数据库中已经有对应table的基础上了。

所以你需要先对数据库进行表的建立:Base.metadata.create_all(engine)

巴特西

sqlachemy知识点

最新文章

热门文章