SQLAlchemy

SQLAlchemy简介

SQLAlchemy是Python的一个ORM框架，该框架建立在数据库的API上，使用关系映射进行了数据库操作。

ORM就是类和对象，SQL Expression负责把对象转化为SQL语句，而Engine引擎负责与ConnectionPooling连接池进行交互，Connection负责连接数据库，Dialect有各种数据库使用的Python 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

通过底层引擎执行SQL

#!/usr/bin/env python
# -*- coding:utf-8 -*-
from sqlalchemy import create_engine

# 创建引擎
engine = create_engine("mysql+pymysql://root:424826@47.91.228.125:3306/testpymysql", max_overflow = 5)
# max_overflow = 5 最大溢出数

# 执行SQL
curson = engine.execute("CREATE TABLE tb2 ("
                        "id int not null auto_increment primary key, "
                        "name varchar(16) null, "
                        "password varchar(16) null "
                        ") ENGINE=InnoDB DEFAULT CHARSET=utf8"
                        )

可以看到在数据库创建的表。

mysql> show tables;
+-----------------------+
| Tables_in_testpymysql |
+-----------------------+
| tb1                   |
| tb2                   |
+-----------------------+
2 rows in set (0.00 sec)

其他功能，包括fatchone，fatchmany，fatchall等方法与pymysql一致。

执行事务

事务也需要通过底层引擎来执行

#!/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)

# 事务操作
with engine.begin() as conn:
    conn.execute("insert into table (x, y, z) values (1, 2, 3)")
    conn.execute("my_special_procedure(5)")

conn = engine.connect()
# 事务操作 
with conn.begin():
       conn.execute("some statement", {'x':5, 'y':10})

通过ORM来实现

创建表

#!/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:424826@47.91.228.125:3306/testpymysql", max_overflow = 5)

# 生成一个SQLORM的基类
Base = declarative_base()

# 通过基类创建表
class User(Base):

    # 表名
    __tablename__  = 'users'
    # 列         数据类型，主键，自增（默认也是自增的）
    id = Column(Integer, primary_key=True, autoincrement=True)
    name = Column(String(16))
    password = Column(String(16), index=True)

    # 参数，可以创建联合索引
    __table_args__ = (
    UniqueConstraint('id', 'name', name='unique_index_id_name'),
    Index('index_name_password',name, password),
    )

    # 返回数据对象前的操作
    def __repr__(self):
        return self.name

# 创建所有表结构
Base.metadata.create_all(engine)

如果需要插入中文需要制定字符集。

# 创建引擎
engine = create_engine("mysql+pymysql://root:424826@47.91.228.125:3306/testpymysql?charset=utf8", max_overflow = 5)

可以看到创建的表

mysql> show tables;
+-----------------------+
| Tables_in_testpymysql |
+-----------------------+
| tb1                   |
| tb2                   |
| users                 |
+-----------------------+
3 rows in set (0.00 sec)

mysql> show create table users \G;
*************************** 1. row ***************************
       Table: users
Create Table: CREATE TABLE `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(16) DEFAULT NULL,
  `password` varchar(16) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_index_id_name` (`id`,`name`),
  KEY `index_name_password` (`name`,`password`),
  KEY `ix_users_password` (`password`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

ERROR: 
No query specified


mysql> show index from users \G;
*************************** 1. row ***************************
        Table: users
   Non_unique: 0
     Key_name: PRIMARY
 Seq_in_index: 1
  Column_name: id
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null: 
   Index_type: BTREE
      Comment: 
Index_comment: 
*************************** 2. row ***************************
        Table: users
   Non_unique: 0
     Key_name: unique_index_id_name
 Seq_in_index: 1
  Column_name: id
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null: 
   Index_type: BTREE
      Comment: 
Index_comment: 
*************************** 3. row ***************************
        Table: users
   Non_unique: 0
     Key_name: unique_index_id_name
 Seq_in_index: 2
  Column_name: name
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment: 
Index_comment: 
*************************** 4. row ***************************
        Table: users
   Non_unique: 1
     Key_name: index_name_password
 Seq_in_index: 1
  Column_name: name
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment: 
Index_comment: 
*************************** 5. row ***************************
        Table: users
   Non_unique: 1
     Key_name: index_name_password
 Seq_in_index: 2
  Column_name: password
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment: 
Index_comment: 
*************************** 6. row ***************************
        Table: users
   Non_unique: 1
     Key_name: ix_users_password
 Seq_in_index: 1
  Column_name: password
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment: 
Index_comment: 
6 rows in set (0.00 sec)

ERROR: 
No query specified

删除表

删除所有表结构

Base.metadata.drop_all(engine)

数据对象返回结果

返回数据对象前的操作

ed_user = User(name='why', password='123456')
print ed_user

返回结果

why

这是根据类中定义的def __repr__(self)实现的、

创建一对多关系表

#!/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:424826@47.91.228.125:3306/testpymysql", max_overflow = 5)

# 生成一个SQLORM的基类
Base = declarative_base()

# 通过基类创建表
class Favor(Base):
    __tablename__ = 'favor'
    nid = Column(Integer, primary_key=True)
    caption = Column(String(50), default='red', unique=True)

    def __repr__(self):
        return "%s, %s" %(self.nid, self.caption)


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')

relationshi方法是sqlarchemy对关系之间提供的一种便利的调用方式，backref是用来提供反向引用的声明。新版本还有back_populates，不过必须是成对的存在的，实现的功能和backref一样。

查看建表语句

mysql> show create table person \G;
*************************** 1. row ***************************
       Table: person
Create Table: CREATE TABLE `person` (
  `nid` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(32) DEFAULT NULL,
  `favor_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`nid`),
  KEY `favor_id` (`favor_id`),
  KEY `ix_person_name` (`name`),
  CONSTRAINT `person_ibfk_1` FOREIGN KEY (`favor_id`) REFERENCES `favor` (`nid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

ERROR: 
No query specified

mysql> show create table favor \G;
*************************** 1. row ***************************
       Table: favor
Create Table: CREATE TABLE `favor` (
  `nid` int(11) NOT NULL AUTO_INCREMENT,
  `caption` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`nid`),
  UNIQUE KEY `caption` (`caption`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

ERROR: 
No query specified

一对一关系

只需要在一对多的外键中指定unique=True即可。

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

创建多对多关系

#!/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:424826@47.91.228.125:3306/testpymysql", max_overflow = 5)

# 生成一个SQLORM的基类
Base = declarative_base()

# 通过基类创建表
class ServerToGroup(Base):
    __tablename__ = 'servertogroup'
    nid = Column(Integer, primary_key=True, autoincrement=True)
    server_id = Column(Integer, ForeignKey('server.id'))
    group_id = Column(Integer, ForeignKey('servergroup.id'))
    group = relationship("Group", backref='s2g')
    server = relationship("Server", backref='s2g')

class Group(Base):
    __tablename__ = 'servergroup'
    id = Column(Integer, primary_key=True)
    name = Column(String(64), unique=True, nullable=False)
    port = Column(Integer, default=22)

class Server(Base):
    __tablename__ = 'server'

    id = Column(Integer, primary_key=True, autoincrement=True)
    hostname = Column(String(64), unique=True, nullable=False)

# 创建所有表结构
Base.metadata.create_all(engine)

注意用于关联两张表的ServerToGroup一定要放在最上边，否则会找不到

查看建表语句

mysql> show create table server \G;
*************************** 1. row ***************************
       Table: server
Create Table: CREATE TABLE `server` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `hostname` varchar(64) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `hostname` (`hostname`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

ERROR: 
No query specified

mysql> show create table servergroup \G;
*************************** 1. row ***************************
       Table: servergroup
Create Table: CREATE TABLE `servergroup` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(64) NOT NULL,
  `port` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

ERROR: 
No query specified

mysql> show create table servertogroup \G;
*************************** 1. row ***************************
       Table: servertogroup
Create Table: CREATE TABLE `servertogroup` (
  `nid` int(11) NOT NULL AUTO_INCREMENT,
  `server_id` int(11) DEFAULT NULL,
  `group_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`nid`),
  KEY `server_id` (`server_id`),
  KEY `group_id` (`group_id`),
  CONSTRAINT `servertogroup_ibfk_1` FOREIGN KEY (`server_id`) REFERENCES `server` (`id`),
  CONSTRAINT `servertogroup_ibfk_2` FOREIGN KEY (`group_id`) REFERENCES `servergroup` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

ERROR: 
No query specified

sqlarchemy中并不提供修改表字段的功能。

数据操作

提交数据

单条添加

#!/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:424826@47.91.228.125:3306/testpymysql", max_overflow = 5)

# 生成一个SQLORM的基类
Base = declarative_base()

# 通过基类创建表
class User(Base):

    # 表名
    __tablename__  = 'users'
    # 列         数据类型，主键，自增（默认也是自增的）
    id = Column(Integer, primary_key=True, autoincrement=True)
    name = Column(String(16))
    password = Column(String(16), index=True)

    # 参数，可以创建联合索引
    __table_args__ = (
    UniqueConstraint('id', 'name', name='unique_index_id_name'),
    Index('index_name_password',name, password),
    )

    # 返回数据对象前的操作
    def __repr__(self):
        return self.name

MySession = sessionmaker(bind=engine)
# 激活类的call方法
session = MySession()

session.add(ed_user)
# 提交
session.commit()

查看表中数据

mysql> select * from users;
+----+------+----------+
| id | name | password |
+----+------+----------+
|  1 | why  | 123456   |
+----+------+----------+
1 row in set (0.00 sec)

批量添加

session.add_all([
    User(name='mabiao', password='234567'),
    User(name='yanwei', password='345678')])

查看表中数据

mysql> select * from users;
+----+--------+----------+
| id | name   | password |
+----+--------+----------+
|  2 | mabiao | 234567   |
|  1 | why    | 123456   |
|  3 | yanwei | 345678   |
+----+--------+----------+
3 rows in set (0.00 sec)

对于一对多，和多对多需要提交依赖的外键表的数据先进行提交

查询数据

基本查询

# 查询User对象表中所有数据的返回值
print session.query(User).all()
# 查询User对象表中所有name列和password列的数据
print session.query(User.name, User.password).all()
# 查询User对象表中name为why的所有password列的数据
print session.query(User.password).filter_by(name='why').all()
# 查询User对象表中name为why的所有password列的第一行数据
print session.query(User.password).filter_by(name='why').first()

执行结果

[mabiao, why, yanwei]
[('mabiao', '234567'), ('why', '123456'), ('yanwei', '345678')]
[('123456',)]
('123456',)

注意，如果是在query参数中指定表，例如session.query(User)进行first操作返回的是一个对象，可以通过ret.name进行获取

删除数据

session.query(User).filter(User.id > 2).delete()
session.commit()
print session.query(User.id, User.name).all()

修改数据

session.query(User).filter(User.id < 2).update({"name" : "wanghongyu"})
session.commit()
print session.query(User.id, User.name).all()

执行结果

[(1, 'wanghongyu'), (2, 'mabiao')]

session.query(User).filter(User.id < 2).update({User.password : User.password + 'why'}, synchronize_session=False)
session.commit()
print session.query(User.id, User.name, User.password).all()

执行结果

[(2, 'mabiao', '234567'), (1, 'wanghongyu', '123456why')]

session.query(User).filter(User.id == 2).update({User.id : User.id + 1}, synchronize_session='evaluate')
session.commit()
print session.query(User.id, User.name, User.password).all()

执行结果

[(3, 'mabiao', '234567'), (1, 'wanghongyu', '123456why')]

synchronize_session参数

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

默认值为evaluate，以下参考源码参数解释

        :param synchronize_session: chooses the strategy for the removal of
            matched objects from the session. Valid values are:

            ``False`` - don't synchronize the session. This option is the most
            efficient and is reliable once the session is expired, which
            typically occurs after a commit(), or explicitly using
            expire_all(). Before the expiration, objects may still remain in
            the session which were in fact deleted which can lead to confusing
            results if they are accessed via get() or already loaded
            collections.

            ``'fetch'`` - performs a select query before the delete to find
            objects that are matched by the delete query and need to be
            removed from the session. Matched objects are removed from the
            session.

            ``'evaluate'`` - Evaluate the query's criteria in Python straight
            on the objects in the session. If evaluation of the criteria isn't
            implemented, an error is raised.

            The expression evaluator currently doesn't account for differing
            string collations between the database and Python.

• false，不对session进行同步，直接执行delete或update操作，在session过期后提交最有效的操作，不过在session过期之前，更新的对象可能仍在session中保留旧的属性，可能会导致查询结果混乱;
• fetch，在delete或update操作之前，先发一条SQL到数据库查询符合条件的记录，在delete或者update操作之后，将session的identity_map与前一步取到的记录进行match，符合就从session中删除;
• evaluate，在delete或update操作之前，用query中的条件直接对session的identity_map中的对象进行eval操作，将符合条件的记录下来，在delete或者update之后将符合条件的记录删掉，否则会抛出异常。

其他操作

条件

对于判断条件的都用filter，而键值对的用filter_by。

ret = session.query(Users).filter_by(name='alex').all()
ret = session.query(Users).filter(Users.id > 1, Users.name == 'eric').all()
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()
ret = session.query(Users).filter(~Users.id.in_([1,3,4])).all()
ret = session.query(Users).filter(Users.id.in_(session.query(Users.id).filter_by(name='eric'))).all()
from sqlalchemy import and_, or_
ret = session.query(Users).filter(and_(Users.id > 3, Users.name == 'eric')).all()
ret = session.query(Users).filter(or_(Users.id < 2, Users.name == 'eric')).all()
ret = session.query(Users).filter(
    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()

分组

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()

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()

连表

ret = session.query(Users, Favor).filter(Users.id == Favor.nid).all()
ret = session.query(Person).join(Favor).all()
ret = session.query(Person).join(Favor, isouter=True).all()

组合

q1 = session.query(Users.name).filter(Users.id > 2)
q2 = session.query(Favor.caption).filter(Favor.nid < 2)
ret = q1.union(q2).all()

q1 = session.query(Users.name).filter(Users.id > 2)
q2 = session.query(Favor.caption).filter(Favor.nid < 2)
ret = q1.union_all(q2).all()

一对多操作

创建一对多

#!/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:424826@47.91.228.125:3306/testpymysql", max_overflow = 5)

# 生成一个SQLORM的基类
Base = declarative_base()

# 通过基类创建表
class Favor(Base):
    __tablename__ = 'favor'
    nid = Column(Integer, primary_key=True)
    caption = Column(String(50), default='red', unique=True)
    person = relationship("Person")

    def __repr__(self):
        return "%s, %s" %(self.nid, self.caption)


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")

MySession = sessionmaker(bind=engine)
session = MySession()

f1 = Favor(caption='python')
session.add(f1)
session.commit()

p1 = Person(name='why', favor_id = '1')
p2 = Person(name='mabiao', favor_id = '1')
session.add_all([p1, p2])
session.commit()

查询数据

mysql> select * from favor;
+-----+---------+
| nid | caption |
+-----+---------+
|   1 | python  |
+-----+---------+
1 row in set (0.00 sec)

mysql> select * from person;
+-----+--------+----------+
| nid | name   | favor_id |
+-----+--------+----------+
|   1 | why    |        1 |
|   2 | mabiao |        1 |
+-----+--------+----------+
2 rows in set (0.00 sec)

连表操作

通过join

代码部分

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

执行结果

SELECT favor.nid AS favor_nid, favor.caption AS favor_caption 
FROM favor INNER JOIN person ON favor.nid = person.favor_id

可以看到返回的SQL语句，内部也可看到join操作。

执行获取结果

ret = session.query(Favor.caption, Person.name).join(Person).all()
print ret

获取结果

[('python', 'why'), ('python', 'mabiao')]

在查询的时候可以起别名的形式，防止子查询的时候名称重复，实例代码

ret = session.query(Favor.caption.label('FFF'), Person.name.label('PPP')).join(Person)
print ret

返回的SQL语句

SELECT favor.caption AS `FFF`, person.name AS `PPP` 
FROM favor INNER JOIN person ON favor.nid = person.favor_id

通过relationship

首先条件语句查询的结果

ret = session.query(Favor).filter_by(nid=1).all()
print type(ret)

可以看到返回值是一个列表

<type 'list'>

是一个怎么样的列表呢

ret = session.query(Favor).all()
print ret

可以看到内部并没有结构

[2, java, 1, python]

对于first获取的唯一值

ret = session.query(Favor).filter_by(nid=1).first()
print type(ret)

返回的是一个对象

<class '__main__.Favor'>

通过relationship获取值

ret = session.query(Favor).filter_by(nid=1).first()
print ret.person
for i in ret.person:
    print i.name

直接通过返回对象的person，这个ret.person的person就是relationship定义的字段

why
mabiao

同理在person这边也能反查favor

ret = session.query(Person).filter_by(nid=1).first()
print ret.favor.caption

返回结果

python

内部也是进行join，只不过是SQLAlchemy做了封装

对于relationship的字段不会在表中生成列，可以创建了表后再在类中添加对应关系

在上述我们添加了两个relationship，也可以写成一个，例如Person类中favor = relationship("Favor", backref='person')，另一边Favor类不需要写了。

通过relationship进行提交

p3 = Person(name='panqiutong')
f1 = session.query(Favor).filter_by(nid=1).first()
f1.person.append(p3)

session.add(f1)
session.commit()

在创建p3的时候，对于f1.son就是一个列表，把p3添加到列表中，提交的时候只需要提交f1即可。

执行结果

mysql> select * from person;
+-----+------------+----------+
| nid | name       | favor_id |
+-----+------------+----------+
|   1 | why        |        1 |
|   2 | mabiao     |        1 |
|   3 | panqiutong |        1 |
+-----+------------+----------+
3 rows in set (0.00 sec)

多对多操作

创建多对多表结构

#!/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:424826@47.91.228.125:3306/testpymysql", max_overflow = 5)

# 生成一个SQLORM的基类
Base = declarative_base()

# 通过基类创建表
class ServerToGroup(Base):
    __tablename__ = 'servertogroup'
    nid = Column(Integer, primary_key=True, autoincrement=True)
    server_id = Column(Integer, ForeignKey('server.id'))
    group_id = Column(Integer, ForeignKey('servergroup.id'))

class Group(Base):
    __tablename__ = 'servergroup'
    id = Column(Integer, primary_key=True)
    name = Column(String(64), unique=True, nullable=False)
    port = Column(Integer, default=22)

class Server(Base):
    __tablename__ = 'server'
    id = Column(Integer, primary_key=True, autoincrement=True)
    hostname = Column(String(64), unique=True, nullable=False)
    group = relationship('Group',secondary=ServerToGroup.__tablename__,backref='server')

# Base.metadata.create_all(engine)

MySession = sessionmaker(bind=engine)
session = MySession()

g1 = Group(name='ops',port='4399')
g2 = Group(name='dev',port='4398')

h1 = Server(hostname='dev01')
h2 = Server(hostname='dev02')

session.add_all([g1, g2, h1, h2])
session.commit()

s2g1 = ServerToGroup(server_id='1',group_id='1')

session.add(s2g1)
session.commit()

创建的好的表和数据

mysql> select * from server;
+----+----------+
| id | hostname |
+----+----------+
|  1 | dev01    |
|  2 | dev02    |
+----+----------+
2 rows in set (0.00 sec)

mysql> select * from servergroup;
+----+------+------+
| id | name | port |
+----+------+------+
|  1 | ops  | 4399 |
|  2 | dev  | 4398 |
+----+------+------+
2 rows in set (0.00 sec)

mysql> select * from servertogroup;
+-----+-----------+----------+
| nid | server_id | group_id |
+-----+-----------+----------+
|   1 |         1 |        1 |
+-----+-----------+----------+
1 row in set (0.00 sec)

也可以通过一对多的操作进行提交

获取group的id为2的对象，将查询出来的所有server列表加入到g1中，提交g1

g1 = session.query(Group).filter_by(id='2').first()
h1 = session.query(Server).all()
g1.server = h1

session.add(g1)
session.commit()

数据库查询到的结果

mysql> select * from servertogroup;
+-----+-----------+----------+
| nid | server_id | group_id |
+-----+-----------+----------+
|   1 |         1 |        1 |
|   2 |         1 |        2 |
|   3 |         2 |        2 |
+-----+-----------+----------+
3 rows in set (0.00 sec)