SQLAlchemy是Python编程语言下的一款ORM框架，该框架建立在数据库API之上，使用关系对象映射进行数据库操作，简言之便是：将对象转换成SQL，然后使用数据API执行SQL并获取执行结果
Dialect用于和数据API进行交流，根据配置文件的不同调用不同的数据库API，从而实现对数据库的操作，如：
MySQL-Python mysql+mysqldb://<user>:<password>@<host>[:<port>]/<dbname> pymysql mysql+pymysql://<username>:<password>@<host>/<dbname>[?<options>] MySQL-Connector mysql+mysqlconnector://<user>:<password>@<host>[:<port>]/<dbname> cx_Oracle oracle+cx_oracle://user:pass@host:port/dbname[?key=value&key=value...] 更多详见：http://docs.sqlalchemy.org/en/latest/dialects/index.html
步骤一：
使用 Engine/ConnectionPooling/Dialect 进行数据库操作,执行原生的SQL，Engine使用ConnectionPooling连接数据库，然后再通过Dialect执行SQL语句
#!/usr/bin/env python # -*- coding:utf-8 -*- from sqlalchemy import create_engine engine = create_engine("mysql+mysqldb://root:123@127.0.0.1:3306/s11", max_overflow=5) engine.execute( "INSERT INTO ts_test (a, b) VALUES ('2', 'v1')" ) engine.execute( "INSERT INTO ts_test (a, b) VALUES (%s, %s)", ((555, "v1"),(666, "v1"),) ) engine.execute( "INSERT INTO ts_test (a, b) VALUES (%(id)s, %(name)s)", id=999, name="v1" ) result = engine.execute('select * from ts_test') result.fetchall()
步骤二：
使用 Schema Type/SQL Expression Language/Engine/ConnectionPooling/Dialect 进行数据库操作。Engine使用Schema Type创建一个特定的结构对象，之后通过SQL Expression Language将该对象转换成SQL语句，然后通过 ConnectionPooling 连接数据库，再然后通过 Dialect 执行SQL，并获取结果。
#!/usr/bin/env python # -*- coding:utf-8 -*- from sqlalchemy import create_engine, Table, Column, Integer, String, MetaData, ForeignKey metadata = MetaData() # metadata 作用：绑定创建的表对象，进行create_all创建 user = Table('user', metadata, Column('id', Integer, primary_key=True), Column('name', String(20)), ) color = Table('color', metadata, Column('id', Integer, primary_key=True), Column('name', String(20)), ) engine = create_engine("mysql+mysqldb://root@localhost:3306/test", max_overflow=5) # max_overflow 指定最大连接数 metadata.create_all(engine)
一个完整的创建库和插入数据的例子
from sqlalchemy import create_engine,and_,or_ from sqlalchemy.ext.declarative import declarative_base from sqlalchemy import Column, Integer, String from sqlalchemy.orm import sessionmaker Base = declarative_base() #生成一个SqlORM 基类 engine = create_engine("mysql+mysqldb://root@localhost:3306/test",echo=False) class Host(Base): __tablename__ = 'hosts' id = Column(Integer,primary_key=True,autoincrement=True) hostname = Column(String(64),unique=True,nullable=False) ip_addr = Column(String(128),unique=True,nullable=False) port = Column(Integer,default=22) Base.metadata.create_all(engine) #创建所有表结构 if __name__ == '__main__': SessionCls = sessionmaker(bind=engine) #创建与数据库的会话session class ,注意,这里返回给session的是个class,不是实例 session = SessionCls() #h1 = Host(hostname='localhost',ip_addr='127.0.0.1') #h2 = Host(hostname='ubuntu',ip_addr='192.168.2.243',port=20000) #h3 = Host(hostname='ubuntu2',ip_addr='192.168.2.244',port=20000) #session.add(h3) #session.add_all( [h1,h2])
#session.add_all([
# Host(hostname="aa",ip_addr="192.168.1.11"),
# Host(hostname="bb",ip_addr="192.168.1.12")]
# )
# session.commit()
#h2.hostname = 'ubuntu_test' #只要没提交,此时修改也没问题 #session.rollback() #session.commit() #提交
注意：sqlalchemy 存在一些问题，当你新增一个字段时，sqlalchemy 无法自动增加字段，因为表已经存在了 他就不会再做操作，解决办法1：删除表，重新创建（sqlalchemy有一个开源的工具可以自动增加字段，需要单独安装）。。 解决办法2：使用原生的sql进行操作，
查询
#查询 #简单查询 res = session.query(Host).filter(Host.hostname=="ubuntu").first()#取一条数据 print(res.hostname) # session.delete(res) #查询的结果可以删除 # res.hostname="ubuntu2" #也可以修改 #session.commit() res = session.query(Host).filter(Host.id > 1).all()#取多条数据 for i in res:print i.hostname #高级查询 # ret=session.query(Users).filter(Users.id > 2).first() # print(ret.groupname)#指定为first显示为这样显示 # ret=session.query(Users).filter(Users.id > 2).all() # for i in ret:print(i.groupname) # 指定为返回的是一个列表，列表里包含类 # ret=session.query(Users).filter_by(groupname="g1").all()#过滤器类型为filter_by时,可以过滤一些关键字 # print(ret.groupname)# # for i in ret:print(i.groupname) # ret = session.query(Users).filter(Users.groupname.in_(["g1","g2"])).all()#.in_表示包含在后面的列表里的值 # for i in ret:print(i.groupname) # ret = session.query(Users.groupname.label("wocao")).all()#label表示将表中的字段起一个别名 # print (ret,type(ret)) # ret = session.query(Users).order_by(Users.groupname).all()# order_by 排序，在此表示对Users下的groupname字段排序 # for i in ret:print(i.groupname) # ret = session.query(Users).filter(Users.id > 1).order_by(Users.groupname).all()#order_by 排序，在此表示对Users下的id字段大于2的groupname排序 # for i in ret:print(i.groupname) # ret = session.query(Users).filter(Users.id > 1).order_by(Users.groupname)[1:3]#order_by 排序，在此表示对Users下的id字段大于2的groupname排序,进行切片操作，获取第1个和第2个的值 # for i in ret:print(i.groupname) # use and_() 需要导入and_模块 ret = session.query(Host).filter(and_(Host.hostname.like("ub%"),Host.port >20)).all() for i in ret:print(i.hostname) # or_() from sqlalchemy import or_ ret = session.query(Host).filter(or_(Host.hostname=='ubunt',Host.port==22)).all() for i in ret:print(i.hostname)
修改
#修改 session.query(Host).filter(Host.id > 4).update({"groupname":"g1"}) session.commit()
删除
#删除 session.query(Host).filter(Host.id >4).delete() session.commit()
外键关联
A one to many relationship places a foreign key on the child table referencing the parent.relationship() is then specified on the parent, as referencing a collection of items represented by the child
from sqlalchemy import Table, Column, Integer, ForeignKey
from sqlalchemy.orm import relationship
from sqlalchemy.ext.declarative import declarative_base
首先来看看什么是外键，为什么要有外键
实例： 一对多
import pymysql from sqlalchemy import create_engine,MetaData,ForeignKey,and_,or_ from sqlalchemy.ext.declarative import declarative_base from sqlalchemy import Column, Integer, String from sqlalchemy.orm import sessionmaker,relationship # 生成一个sqlorm基类 Base = declarative_base() #创建一个引擎 engine = create_engine("mysql+pymysql://root:chen27@localhost:3306/chen",echo=False) class Hosts(Base): __tablename__='hosts' id = Column(Integer,primary_key=True) hostname = Column(String(64),unique=True,nullable=False) ip_addr = Column(String(128),unique=True,nullable=False) port = Column(Integer,default=22) group_id = Column(Integer,ForeignKey('groups.id')) # 外键关联groups下的id group = relationship("Groups") # 关联查询时候用，表示在Hosts表里通过什么字段(group)可以调用groups表（这里是表的类名Groups）里的字段信息 【其实就相当于反射，把Groups类的内存地址反射给group，从而获取到Groups下的所有字段，这个过程等同于实例化】 class Groups(Base): __tablename__='groups' id = Column(Integer,primary_key=True) groupname = Column(String(64),unique=True,nullable=False) Base.metadata.create_all(engine) if __name__ == "__main__": sess = sessionmaker(bind=engine) session = sess() # g1 = Groups(groupname = "g1") # g2 = Groups(groupname = "g2") # g3 = Groups(groupname = "g3") # h1 = Hosts(hostname="h1",ip_addr="192.168.1.10",group_id="1") # h2 = Hosts(hostname="h2",ip_addr="192.168.1.11",group_id="2") # # session.add_all([h1,h2,g1,g2,g3]) # session.commit() h1 = session.query(Hosts).filter(Hosts.hostname=="h1").first() # 查询主机对应的组 print(h1.group.groupname) # 查询的时候group必须与字段指定的group字段一致 才能找到Groups类，获取到相对应的字段信息
上面的例子加入了关联查询的实例，通过主机查询对应的组， 那我们如果想要通过组获取组下对应的主机呢？上面肯定是没法实现的，那么来看下面（在Groups下面也添加一个关联字段）
import pymysql from sqlalchemy import create_engine,MetaData,ForeignKey,and_,or_ from sqlalchemy.ext.declarative import declarative_base from sqlalchemy import Column, Integer, String from sqlalchemy.orm import sessionmaker,relationship # 生成一个sqlorm基类 Base = declarative_base() #创建一个引擎 engine = create_engine("mysql+pymysql://root:chen27@localhost:3306/chen",echo=False) class Hosts(Base): __tablename__='hosts' id = Column(Integer,primary_key=True) hostname = Column(String(64),unique=True,nullable=False) ip_addr = Column(String(128),unique=True,nullable=False) port = Column(Integer,default=22) group_id = Column(Integer,ForeignKey('groups.id')) # 外键关联groups下的id group = relationship("Groups") # 关联查询时候用，表示在Hosts表里通过什么字段(group)可以调用groups表（这里是表的类名Groups）里的字段信息 class Groups(Base): __tablename__='groups' id = Column(Integer,primary_key=True) groupname = Column(String(64),unique=True,nullable=False) hosts = relationship("Hosts") # Groups也添加一个关联查询的字段 Base.metadata.create_all(engine) if __name__ == "__main__": sess = sessionmaker(bind=engine) session = sess() # g1 = Groups(groupname = "g1") # g2 = Groups(groupname = "g2") # g3 = Groups(groupname = "g3") # h1 = Hosts(hostname="h1",ip_addr="192.168.1.10",group_id="1") # h2 = Hosts(hostname="h2",ip_addr="192.168.1.11",group_id="2") # # session.add_all([h1,h2,g1,g2,g3]) # session.commit() # h1 = session.query(Hosts).filter(Hosts.hostname=="h1").first() # print(h1.group.groupname) # h3 = Hosts(hostname="h3",ip_addr="192.168.1.12",group_id="1") # 新加了一条主机h3属于g1组 # session.add(h3) # session.commit() g1 = session.query(Groups).filter(Groups.groupname=="g1").first() # 查询主机组下面包含了几个主机， print(g1.hosts) #[<__main__.Hosts object at 0x7ffb945af5f8>, <__main__.Hosts object at 0x7ffb945af668>]
上面又在groups下面添加了一个relationship，对应Hosts类，还有一个比较简单的方法，直接在Host类下面做一个反向关联，需要额外导入一个模块backref
import pymysql from sqlalchemy import create_engine,MetaData,ForeignKey,and_,or_ from sqlalchemy.ext.declarative import declarative_base from sqlalchemy import Column, Integer, String from sqlalchemy.orm import sessionmaker,relationship,backref#反向关联模块 # 生成一个sqlorm基类 Base = declarative_base() #创建一个引擎 engine = create_engine("mysql+pymysql://root:chen27@localhost:3306/chen",echo=False) class Hosts(Base): __tablename__='hosts' id = Column(Integer,primary_key=True) hostname = Column(String(64),unique=True,nullable=False) ip_addr = Column(String(128),unique=True,nullable=False) port = Column(Integer,default=22) group_id = Column(Integer,ForeignKey('groups.id')) # 外键关联groups下的id group = relationship("Groups",backref='hosts_list') # backref表示反向关联hosts_list可以指定任意字符串，但是你调用的时候必须一致 class Groups(Base): __tablename__='groups' id = Column(Integer,primary_key=True) groupname = Column(String(64),unique=True,nullable=False) # hosts = relationship("Hosts") Base.metadata.create_all(engine) if __name__ == "__main__": sess = sessionmaker(bind=engine) session = sess() # g1 = Groups(groupname = "g1") # g2 = Groups(groupname = "g2") # g3 = Groups(groupname = "g3") # h1 = Hosts(hostname="h1",ip_addr="192.168.1.10",group_id="1") # h2 = Hosts(hostname="h2",ip_addr="192.168.1.11",group_id="2") # # session.add_all([h1,h2,g1,g2,g3]) # session.commit() # h1 = session.query(Hosts).filter(Hosts.hostname=="h1").first() # print(h1.group.groupname) # h3 = Hosts(hostname="h3",ip_addr="192.168.1.12",group_id="1") # session.add(h3) # session.commit() g1 = session.query(Groups).filter(Groups.groupname=="g1").first() print(g1.hosts_list)#这里的hosts_list必须与刚才反向关联的一致
for i in g1.hosts_list:print(i.hostname) #[<__main__.Hosts object at 0x7f8553ca2668>, <__main__.Hosts object at 0x7f8553ca26d8>]
# h1 h3
附，原生sql join查询
几个Join的区别 http://stackoverflow.com/questions/38549/difference-between-inner-and-outer-joins
- INNER JOIN: Returns all rows when there is at least one match in BOTH tables
- LEFT JOIN: Return all rows from the left table, and the matched rows from the right table
- RIGHT JOIN: Return all rows from the right table, and the matched rows from the left table
inner join sql
select * from hosts inner join groups on hosts.group_id = groups.id; +----+----------+--------------+------+----------+----+-----------+ | id | hostname | ip_addr | port | group_id | id | groupname | +----+----------+--------------+------+----------+----+-----------+ | 2 | h1 | 192.168.1.10 | 22 | 1 | 1 | g1 | | 3 | h2 | 192.168.1.11 | 22 | 2 | 2 | g2 | | 4 | h3 | 192.168.1.12 | 22 | 1 | 1 | g1 | +----+----------+--------------+------+----------+----+-----------+
inner join sqlalchemy
ret = session.query(Hosts).join(Hosts.group).filter(Groups.groupname=="g1").all() # query(表的类) .join(关联查询的字段) for i in ret:print(i.hostname) print(len(ret)) ''' h1 h3 2 '''
left join sql
#首先我们先插入一个数据 以便看到效果 mysql> insert into hosts(hostname,ip_addr) values("h4","192.168.1.13"); Query OK, 1 row affected (0.01 sec) mysql> select * from hosts; +----+----------+--------------+------+----------+ | id | hostname | ip_addr | port | group_id | +----+----------+--------------+------+----------+ | 2 | h1 | 192.168.1.10 | 22 | 1 | | 3 | h2 | 192.168.1.11 | 22 | 2 | | 4 | h3 | 192.168.1.12 | 22 | 1 | | 6 | h4 | 192.168.1.13 | NULL | NULL | +----+----------+--------------+------+----------+ #left joiin sql mysql> select * from hosts left join groups on hosts.group_id = groups.id; +----+----------+--------------+------+----------+------+-----------+ | id | hostname | ip_addr | port | group_id | id | groupname | +----+----------+--------------+------+----------+------+-----------+ | 2 | h1 | 192.168.1.10 | 22 | 1 | 1 | g1 | | 3 | h2 | 192.168.1.11 | 22 | 2 | 2 | g2 | | 4 | h3 | 192.168.1.12 | 22 | 1 | 1 | g1 | | 6 | h4 | 192.168.1.13 | NULL | NULL | NULL | NULL | +----+----------+--------------+------+----------+------+-----------+ 4 rows in set (0.00 sec)
right join sql
mysql> select * from hosts right join groups on hosts.group_id = groups.id; +------+----------+--------------+------+----------+----+-----------+ | id | hostname | ip_addr | port | group_id | id | groupname | +------+----------+--------------+------+----------+----+-----------+ | 2 | h1 | 192.168.1.10 | 22 | 1 | 1 | g1 | | 3 | h2 | 192.168.1.11 | 22 | 2 | 2 | g2 | | 4 | h3 | 192.168.1.12 | 22 | 1 | 1 | g1 | | NULL | NULL | NULL | NULL | NULL | 3 | g3 | +------+----------+--------------+------+----------+----+-----------+
full join （因mysql不支持full join 只能通过union的替代方法实现）
mysql> select * from hosts left join groups on hosts.group_id = groups.id union select * from hosts right join groups on hosts.group_id = groups.id; +------+----------+--------------+------+----------+------+-----------+ | id | hostname | ip_addr | port | group_id | id | groupname | +------+----------+--------------+------+----------+------+-----------+ | 2 | h1 | 192.168.1.10 | 22 | 1 | 1 | g1 | | 3 | h2 | 192.168.1.11 | 22 | 2 | 2 | g2 | | 4 | h3 | 192.168.1.12 | 22 | 1 | 1 | g1 | | 6 | h4 | 192.168.1.13 | NULL | NULL | NULL | NULL | | NULL | NULL | NULL | NULL | NULL | 3 | g3 | +------+----------+--------------+------+----------+------+-----------+
group by sql过滤重复的字段（分类聚合）
mysql> select * from hosts; +----+----------+--------------+------+----------+ | id | hostname | ip_addr | port | group_id | +----+----------+--------------+------+----------+ | 1 | h1 | 192.168.1.10 | 22 | 1 | | 2 | h2 | 192.168.1.11 | 22 | 2 | | 3 | h3 | 192.168.1.12 | 22 | 1 | +----+----------+--------------+------+----------+ 3 rows in set (0.00 sec) mysql> select * from hosts group by group_id; +----+----------+--------------+------+----------+ | id | hostname | ip_addr | port | group_id | +----+----------+--------------+------+----------+ | 1 | h1 | 192.168.1.10 | 22 | 1 | | 2 | h2 | 192.168.1.11 | 22 | 2 | +----+----------+--------------+------+----------+ 2 rows in set (0.00 sec) ------------------------------------------------------------------------ mysql> select * from hosts right join groups on hosts.group_id = groups.id;
+------+----------+--------------+------+----------+----+-----------+ | id | hostname | ip_addr | port | group_id | id | groupname | +------+----------+--------------+------+----------+----+-----------+ | 1 | h1 | 192.168.1.10 | 22 | 1 | 1 | g1 | | 2 | h2 | 192.168.1.11 | 22 | 2 | 2 | g2 | | 3 | h3 | 192.168.1.12 | 22 | 1 | 1 | g1 | | NULL | NULL | NULL | NULL | NULL | 3 | g3 | +------+----------+--------------+------+----------+----+-----------+ 4 rows in set (0.00 sec) mysql> select * from hosts right join groups on hosts.group_id = groups.id group by groupname; +------+----------+--------------+------+----------+----+-----------+ | id | hostname | ip_addr | port | group_id | id | groupname | +------+----------+--------------+------+----------+----+-----------+ | 1 | h1 | 192.168.1.10 | 22 | 1 | 1 | g1 | | 2 | h2 | 192.168.1.11 | 22 | 2 | 2 | g2 | | NULL | NULL | NULL | NULL | NULL | 3 | g3 | +------+----------+--------------+------+----------+----+-----------+ 3 rows in set (0.00 sec)
mysql> select count(*),groups.groupname from hosts inner join groups on hosts.group_id = groups.id group by groups.groupname;
+----------+-----------+
| count(*) | groupname |
+----------+-----------+
| 2 | g1 |
| 1 | g2 |
+----------+-----------+
2 rows in set (0.00 sec)
group by sqlalchemy
ret = session.query(Hosts,func.count(Groups.groupname)).join(Hosts.group).group_by(Groups.groupname).all() print(ret)
上述的情况是一个组对应多个主机， 那如果一个主机想要对应多个组呢？上述的表结构就无法实现了
many to many 多对多
1、
# 多对多 # 1、创建另一个表 与hosts和groups表相关联 Hosts_2_Groups = Table('Hosts_2_Groups',Base.metadata, # Column('id',nullable=False,autoincrement=True), Column('host_id',ForeignKey('hosts.id'),primary_key=True), Column('group_id',ForeignKey('groups.id'),primary_key=True), ) class Hosts(Base): __tablename__='hosts' id = Column(Integer,primary_key=True) hostname = Column(String(64),unique=True,nullable=False) ip_addr = Column(String(128),unique=True,nullable=False) port = Column(Integer,default=22) # group_id = Column(Integer,ForeignKey('groups.id')) #这里外键关联到groups就取消了， group = relationship("Groups",backref="hosts_list",secondary=Hosts_2_Groups) #secondary指定中间表的实例 def __repr__(self): # 返回一些信息 return "<id=%s,hostname=%s,ip_addr=%s>" %(self.id, self.hostname, self.ip_addr) class Groups(Base): __tablename__='groups' id = Column(Integer,primary_key=True) groupname = Column(String(64),unique=True,nullable=False) def __repr__(self): return "id=%s,groupname=%s"%(self.id,self.groupname) Base.metadata.create_all(engine)
2、插入数据
sess = sessionmaker(bind=engine) session = sess() # g1 = Groups(groupname = "g1") # g2 = Groups(groupname = "g2") # g3 = Groups(groupname = "g3") # g4 = Groups(groupname = "g4") # h1 = Hosts(hostname="h1",ip_addr="192.168.1.10") # h2 = Hosts(hostname="h2",ip_addr="192.168.1.11") # h3 = Hosts(hostname="h3",ip_addr="192.168.1.12") # # # session.add_all([h1,h2,h3,g1,g2,g3,g4]) # session.commit()
3、关联第三张表（你也可以直接在插入数据的时候指定组，例如：
''' g1 = Groups(groupname = "g1" h1 = Hosts(hostname="h1",ip_addr="192.168.1.10" h1.group = [g1,g2,g3...]) ) ） '''
# h1 = session.query(Hosts).filter(Hosts.hostname=="h2").first() # groups = session.query(Groups).all() # h1.group= groups # session.commit()
4、查询
# 查询组对应的主机 g2 = session.query(Groups).filter(Groups.groupname=="g2").first() print(g2.hosts_list) >>>[<id=1,hostname=h1,ip_addr=192.168.1.10>, <id=2,hostname=h2,ip_addr=192.168.1.11>] #查询主机对应的组 h1 = session.query(Hosts).filter(Hosts.hostname=="h1").first() print(h1.group) >>> [id=1,groupname=g1, id=2,groupname=g2, id=3,groupname=g3]
更多ORM内容点击这里 http://119.90.25.39/files.cnblogs.com/files/wupeiqi/sqlalchemy.pdf.zip