python之ORM

pymysql

python操作数据库的基本步骤：

导入相应的python模块；
使用connect函数连接数据库，并返回一个connection对象；
通过connection对象的cursor方法，返回一个cursor对象；
通过cursor对象的execute方法执行SQL语句；
如果执行的是查询语句，通过cursor对象的fetchall语句获取返回结果；
调用cursor对象的close方法关闭cursor;
调用connection对象的close方法关闭数据库连接。

 1 import pymysql
 2 
 3 conn = pymysql.Connect(host='127.0.0.1',user='admin',passwd='admin',db='test_py')
 4 cur = conn.cursor()
 5 
 6 reCount = cur.execute('select * from student')
 7 print(cur.fetchall())    # ((1, 'gareth', 22, datetime.date(2011, 9, 1)),)
 8 
 9 cur.close()
10 conn.close()

connection类成员

begin: 开始事务
commit：提交事务
rollback: 回滚事务
cursor：返回一个cursor对象
autocommit：设置是否事务自动提交
set_character_set：设置字符集编码
get_server_info：获取数据库版本信息

注：一般不直接调用begin，commit和roolback函数，而是通过上下文管理器实现事务的提交与回滚操作。

cursor类成员对象：cursor对象表示数据库游标，用于执行SQL语句并获取SQL语句的执行结果。

execute：执行SQL语句
close：关闭游标
fetchall：获取SQL语句的所有记录
fetchmany：获取SQL语句的多条记录
fetchone：获取SQL语句的一条记录
owncount：常量，表示SQL语句的结果集中返回了多少条记录
arraysize：变量，保存了当前获取纪录的下标
lastrowid：获取最新自增ID

注：在fetch数据时按照顺序进行，可以使用cursor.scroll(num,mode)来移动游标位置，如：

cursor.scroll(1,mode='relative') # 相对当前位置移动
cursor.scroll(2,mode='absolute') # 相对绝对位置移动

默认获取的数据是元祖类型，如果想要或者字典类型的数据，即：

 1 conn = pymysql.Connect(host='127.0.0.1',user='admin',passwd='admin',db='test_py')
 2 cur = conn.cursor(cursor=pymysql.cursors.DictCursor)
 3 
 4 reCount = cur.execute('select * from student')
 5 print(cur.fetchall())
 6 """
 7 [{'stu_id': 1, 'name': 'gareth', 'age': 22, 'register_data': datetime.date(2011, 9, 1)}, 
 8  {'stu_id': 3, 'name': 'Bob', 'age': 19, 'register_data': datetime.date(2012, 2, 3)}, 
 9  {'stu_id': 4, 'name': 'Bob', 'age': 19, 'register_data': datetime.date(2012, 2, 3)}, 
10  {'stu_id': 5, 'name': 'Mary', 'age': 18, 'register_data': datetime.date(2013, 1, 2)}]
11 """
12 
13 cur.close()
14 conn.close()

使用上下文管理管理数据库:

 1 import pymysql
 2 import os
 3 
 4 
 5 def get_conn(**kwargs):
 6     if os.getenv('DB','MYSQL') == 'MYSQL':
 7         return pymysql.connect(host=kwargs.get('host','localhost'),
 8                                user=kwargs.get('user'),
 9                                passwd=kwargs.get('passwd'),
10                                port=kwargs.get('port',3306),
11                                db=kwargs.get('db'))
12 
13 def execute_sql(conn, sql):
14     with conn as cur:
15         cur.execute(sql)
16 
17 def insert_data(conn,sname,sage,sregister):
18     INSERT_FORMAT = """insert into student (name,age,register_data) values('{0}','{1}','{2}')"""
19     sql = INSERT_FORMAT.format(sname,sage,sregister)
20     execute_sql(conn,sql)
21 
22 def main():
23     conn = get_conn(host='127.0.0.1',
24                     user='admin',
25                     passwd='admin',
26                     port=3306,
27                     db='test_py')
28 
29     try:
30         insert_data(conn,'Bob',19,'2012-02-03')
31         insert_data(conn,'Mary',18,'2013-01-02')
32 
33         with conn as cur:
34             cur.execute('select * from student')
35             rows = cur.fetchall()
36             for row in rows:
37                 print(row)
38     finally:
39         if conn:
40             conn.close()
41 
42 if __name__ == '__main__':
43     main()

View Code

上面例子中如果values('{0}','{1}','{2}')的引号去掉，则会报错：pymysql.err.InternalError: (1054, "Unknown column 'jack' in 'field list'")

1 cur.execute("insert into student (name,age,register_data) values('jack',12,'2012-02-03')")
2 
3 INSERT_FORMAT = """insert into student (name,age,register_data) values('{0}','{1}','{2}')"""

批量插入：

1 cur = conn.cursor()
2 
3 cur.executemany("insert into student (name,age,register_data) values(%s,%s,%s)",
4             [('jack',12,'2012-02-03'),('Bob',12,'2012-02-03')]  )

ORM

orm英文全称object relational mapping，即对象映射关系程序，简单来说我们类似python这种面向对象的程序来说一切皆对象，但是我们使用的数据库却都是关系型的，为了保证一致的使用习惯，通过orm将编程语言的对象模型和数据库的关系模型建立映射关系，这样我们在使用编程语言对数据库进行操作的时候可以直接使用编程语言的对象模型进行操作就可以了，而不用直接使用sql语言。

优点：

隐藏了数据访问细节，“封闭”的通用数据库交互，ORM的核心。他使得我们的通用数据库交互变得简单易行，并且完全不用考虑该死的SQL语句。快速开发，由此而来。
ORM使我们构造固化数据结构变得简单易行。

缺点：

自动化意味着映射和关联管理，代价是牺牲性能

sqlalchemy

SQLAlchemy是Python编程语言下的一款ORM框架，该框架建立在数据库API之上，使用关系对象映射进行数据库操作，简言之便是：将对象转换成SQL，然后使用数据API执行SQL并获取执行结果。

SQLAlchemy本身无法操作数据库，其必须依赖pymsql等第三方插件，Dialect用于和数据API进行交流，根据配置文件的不同调用不同的数据库API，从而实现对数据库的操作，如：

 1 MySQL-Python
 2     mysql+mysqldb://<user>:<password>@<host>[:<port>]/<dbname>
 3    
 4 pymysql
 5     mysql+pymysql://<username>:<password>@<host>/<dbname>[?<options>]
 6    
 7 MySQL-Connector
 8     mysql+mysqlconnector://<user>:<password>@<host>[:<port>]/<dbname>
 9    
10 cx_Oracle
11     oracle+cx_oracle://user:pass@host:port/dbname[?key=value&key=value...]
12    
13 更多详见：http://docs.sqlalchemy.org/en/latest/dialects/index.html

dialect

使用 Engine/ConnectionPooling/Dialect 进行数据库操作，Engine使用ConnectionPooling连接数据库，然后再通过Dialect执行SQL语句。

 1 from sqlalchemy import create_engine
 2 
 3 engine = create_engine('mysql+pymysql://user_account:password@127.0.0.1/test', max_overflow=5)
 4 
 5 cur = engine.execute("insert into student(name,age,register_date) values('Jack',20,'2018-01-02')")
 6 print(cur.lastrowid)
 7 # 1
 8 
 9 cur = engine.execute("insert into student(name,age,register_date) values(%s,%s,%s)",
10                      [('Gareth',18,'2018-07-02'),('Mar',17,'2017-12-02')])
11 
12 cur = engine.execute("insert into student(name,age,register_date) values(%(name)s,%(age)s,%(register_date)s)",
13                      name = 'Ker',age = 21,register_date='2016-09-01')
14 cur = engine.execute('select * from student')
15 print(cur.fetchone())
16 # (1, 'Jack', 20, datetime.date(2018, 1, 2))
17 print(cur.fetchmany(2))
18 # [(2, 'Gareth', 18, datetime.date(2018, 7, 2)), (3, 'Mar', 17, datetime.date(2017, 12, 2))]
19 print(cur.fetchall())
20 # [(4, 'Ker', 21, datetime.date(2016, 9, 1))]

ORM使用

使用 Engine/ConnectionPooling/Dialect 进行数据库操作，Engine使用ConnectionPooling连接数据库，然后再通过Dialect执行SQL语句。

创建表：

 1 from sqlalchemy.ext.declarative import declarative_base
 2 from sqlalchemy import Column,Integer,String,ForeignKey, UniqueConstraint,Index
 3 from sqlalchemy.orm import sessionmaker, relationship
 4 from sqlalchemy import create_engine
 5 
 6 
 7 engine = create_engine("mysql+pymysql://user_account:password@127.0.0.1:3306/test", max_overflow = 5)
 8 Base = declarative_base()  # 生成一个基类
 9 
10 class Classes(Base):
11     __tablename__='classes'
12     id = Column(Integer, primary_key=True)
13     name = Column(String(32))
14 
15 Base.metadata.create_all(engine) #创建表结构

创建与数据库的会话session:

1 session_class = sessionmaker(bind=engine)  # 创建与数据库的会话session class ,注意,这里返回给session的是个class,不是实例
2 session = session_class()                  # 生成session实例
3 
4 cla_obj = Classes(name='python099')  #生成你要创建的数据对象
5 
6 session.add(cla_obj)  # 把要创建的数据对象添加到这个session里， 一会统一创建
7 
8 session.commit()      # 现此才统一提交，创建数据

查询

sqlalchemy把返回的数据映射成一个对象，调用每个字段可以像调用对象属性一样。

 1 session_class = sessionmaker(bind=engine)  # 创建与数据库的会话session class ,注意,这里返回给session的是个class,不是实例
 2 session = session_class()                  # 生成session实例
 3 
 4 
 5 my_classes = session.query(Classes)
 6 print(my_classes)
 7 #  "SELECT classes.id AS classes_id, classes.name AS classes_name FROM classes"
 8 
 9 my_classes = session.query(Classes).first()
10 print(my_classes)                                  # 返回内存对象地址
11     # <__main__.Classes object at 0x000001832CE1A4E0>
12 
13 my_classes = session.query(Classes).first()
14 print('id: ',my_classes.id,'name:',my_classes.name)
15     # id:  1 name: python011
16 
17 my_classes = session.query(Classes)
18 for classes in my_classes:
19     # print('id: ',classes.id,'name:',classes.name)
20     # id: 1   name: python011
21     # id: 2   name: python016
22     # id: 3   name: linux

把内存对象地址转化为可读性数据，除了调用字段还可以使用__repr__(self) 函数。

 1 def __repr__(self):
 2     return "Classes(id=%s,name=%s)"%(self.id, self.name)
 3 
 4 my_classes = session.query(Classes)
 5 print(my_classes)     # SELECT classes.id AS classes_id, classes.name AS classes_name FROM classes
 6 
 7 print(my_classes[1])  # Classes(id=2,name=python016)
 8 
 9 for classes in my_classes:
10     print(classes)
11 # Classes(id=1,name=python011)
12 # Classes(id=2,name=python016)
13 # Classes(id=3,name=linux)

过滤

1 print(session.query(Classes).filter(Classes.name.in_(['mysql','python_sqlalchemy'])).all())
2 
3 session.query(Classes).filter_by(name='python011').first()

多条件查询：相当于id > 1 and id <4

1 obj = session.query(Classes).filter(Classes.id>1).filter(Classes.id<4).all()
2 print(obj)  
3 # [Classes(id=2,name=python016), Classes(id=3,name=linux)]

获取所有数据

1 print(session.query(Classes).all())
2 # [Classes(id=1,name=python_mysql), Classes(id=2,name=python016), Classes(id=3,name=linux)]
3 print(session.query(Classes.id,Classes.name).all())
4 #  [(1, 'python_mysql'), (2, 'python016'), (3, 'linux')]

查询语法

 1 Common Filter Operators
 2 
 3 Here’s a rundown of some of the most common operators used in filter():
 4 
 5 equals:
 6 
 7      query.filter(User.name == 'ed')
 8 not equals:
 9 
10      query.filter(User.name != 'ed')
11 LIKE:
12 
13 query.filter(User.name.like('%ed%'))
14 
15 IN:
16 
17 NOT IN:
18 query.filter(~User.name.in_(['ed', 'wendy', 'jack']))
19 
20 IS NULL:
21 
22 IS NOT NULL:
23 
24 AND:
25 2.1. ObjectRelationalTutorial 17
26 
27 query.filter(User.name.in_(['ed', 'wendy', 'jack']))
28 # works with query objects too:
29 query.filter(User.name.in_( session.query(User.name).filter(User.name.like('%ed%'))
30 
31 ))
32 
33 query.filter(User.name == None)
34 # alternatively, if pep8/linters are a concern
35 query.filter(User.name.is_(None))
36 query.filter(User.name != None)
37 # alternatively, if pep8/linters are a concern
38 query.filter(User.name.isnot(None))
39 SQLAlchemy Documentation, Release 1.1.0b1
40 
41 # use and_()
42 
43 from sqlalchemy import and_
44 query.filter(and_(User.name == 'ed', User.fullname == 'Ed Jones'))
45 
46 # or send multiple expressions to .filter()
47 query.filter(User.name == 'ed', User.fullname == 'Ed Jones')
48 # or chain multiple filter()/filter_by() calls
49 query.filter(User.name == 'ed').filter(User.fullname == 'Ed Jones')
50 Note: Makesureyouuseand_()andnotthePythonandoperator! • OR:
51 
52 Note: Makesureyouuseor_()andnotthePythonoroperator! • MATCH:
53 
54 query.filter(User.name.match('wendy'))
55 Note: match() uses a database-specific MATCH or CONTAINS f

View Code

分组和统计

 1 print(session.query(Classes).filter(Classes.name.like('p%')).count())
 2 # 2
 3 print(session.query(Classes).count())
 4 # 2
 5 
 6 from sqlalchemy import func
 7 print(session.query(Classes).all())
 8 # [Classes(id=1,name=python_mysql), Classes(id=2,name=python016), Classes(id=3,name=linux), Classes(id=5,name=linux)]
 9 
10 print(session.query(func.count(Classes.name),Classes.name).group_by(Classes.name).all())
11 #[(1, 'python_mysql'), (1, 'python016'), (2, 'linux')]

修改

使用修改，可以加个判断，如果没有查询到需要修改的信息则跳过，否则容易出现异常 AttributeError 。

 1 my_classes = session.query(Classes).filter_by(name='python011').first()
 2 
 3 my_classes.name = 'python_mysql022'
 4 session.commit()
 5 my_classes = session.query(Classes)
 6 for classes in my_classes:
 7     print(classes)
 8 
 9 # Classes(id=1,name=python_mysql)
10 # Classes(id=2,name=python016)
11 # Classes(id=3,name=linux)

异常：

1 Traceback (most recent call last):
2   File "C:/D/personal_data/workspace/eleven/mysql_study/class_study/orm_01.py", line 64, in <module>
3     my_classes.name = 'python_mysql022'
4 AttributeError: 'NoneType' object has no attribute 'name'

回滚

 1 my_classes = session.query(Classes).filter_by(id=2).first()
 2 my_classes.name = 'python_sqlalchemy'
 3 
 4 fake_classes = Classes(name='mysql')
 5 session.add(fake_classes)
 6 
 7 print(session.query(Classes).filter(Classes.name.in_(['mysql','python_sqlalchemy'])).all())
 8 #[Classes(id=2,name=python_sqlalchemy), Classes(id=4,name=mysql)]
 9 session.rollback()
10 
11 print(session.query(Classes).filter(Classes.name.in_(['mysql','python_sqlalchemy'])).all())
12 # []

程序：

  1 from sqlalchemy.ext.declarative import declarative_base
  2 from sqlalchemy import Column,Integer,String,ForeignKey, UniqueConstraint,Index
  3 from sqlalchemy.orm import sessionmaker, relationship
  4 from sqlalchemy import create_engine
  5 
  6 
  7 engine = create_engine("mysql+pymysql://jiawenyx:intel@3117@127.0.0.1:3306/test", max_overflow = 5)
  8 Base = declarative_base()
  9 
 10 class Classes(Base):
 11     __tablename__='classes'
 12     id = Column(Integer, primary_key=True)
 13     name = Column(String(32))
 14 
 15     def __repr__(self):
 16         return "Classes(id=%s,name=%s)"%(self.id, self.name)
 17 
 18 Base.metadata.create_all(engine) #创建表结构
 19 
 20 session_class = sessionmaker(bind=engine)  # 创建与数据库的会话session class ,注意,这里返回给session的是个class,不是实例
 21 session = session_class()                  # 生成session实例
 22 #
 23 # cla_obj = Classes(name='python016')  #生成你要创建的数据对象
 24 # cla_obj2 = Classes(name='linux')
 25 #
 26 # session.add(cla_obj)  # 把要创建的数据对象添加到这个session里， 一会统一创建
 27 # session.add(cla_obj2)
 28 # session.commit()      # 现此才统一提交，创建数据
 29 
 30 
 31 # ********************************   查询  ***************************************
 32 
 33 my_classes = session.query(Classes)
 34 # print(my_classes)
 35 #  "SELECT classes.id AS classes_id, classes.name AS classes_name FROM classes"
 36 my_classes = session.query(Classes).first()
 37 # print(my_classes)
 38 # <__main__.Classes object at 0x000001832CE1A4E0>
 39 my_classes = session.query(Classes).first()
 40 # print('id: ',my_classes.id,'name:',my_classes.name)
 41 # id:  1 name: python011
 42 
 43 my_classes = session.query(Classes)
 44 # for classes in my_classes:
 45     # print('id: ',classes.id,'name:',classes.name)
 46     # id: 1   name: python011
 47     # id: 2   name: python016
 48     # id: 3   name: linux
 49 
 50 
 51 # my_classes = session.query(Classes)
 52 # print(my_classes)     # SELECT classes.id AS classes_id, classes.name AS classes_name FROM classes
 53 # print(my_classes[1])  # Classes(id=2,name=python016)
 54 # for classes in my_classes:
 55 #     print(classes)
 56 # # Classes(id=1,name=python011)
 57 # # Classes(id=2,name=python016)
 58 # # Classes(id=3,name=linux)
 59 
 60 #******************************************  修改  ********************************************
 61 
 62 my_classes = session.query(Classes).filter_by(name='python011').first()
 63 
 64 # my_classes.name = 'python_mysql022'
 65 # session.commit()
 66 # my_classes = session.query(Classes)
 67 # for classes in my_classes:
 68 #     print(classes)
 69 
 70 # Classes(id=1,name=python_mysql)
 71 # Classes(id=2,name=python016)
 72 # Classes(id=3,name=linux)
 73 
 74 # *******************************  回滚  ********************************
 75 
 76 my_classes = session.query(Classes).filter_by(id=2).first()
 77 my_classes.name = 'python_sqlalchemy'
 78 
 79 fake_classes = Classes(name='mysql')
 80 session.add(fake_classes)
 81 
 82 # print(session.query(Classes).filter(Classes.name.in_(['mysql','python_sqlalchemy'])).all())
 83 #[Classes(id=2,name=python_sqlalchemy), Classes(id=4,name=mysql)]
 84 session.rollback()
 85 
 86 # print(session.query(Classes).filter(Classes.name.in_(['mysql','python_sqlalchemy'])).all())
 87 
 88 
 89 # **********************获取所有数据*****************************
 90 
 91 # print(session.query(Classes).all())
 92 # # [Classes(id=1,name=python_mysql), Classes(id=2,name=python016), Classes(id=3,name=linux)]
 93 # print(session.query(Classes.id,Classes.name).all())
 94 # #  [(1, 'python_mysql'), (2, 'python016'), (3, 'linux')]
 95 #
 96 # # ********************** 多条件查询 ****************************
 97 #
 98 # obj = session.query(Classes).filter(Classes.id>1).filter(Classes.id<4).all()
 99 # print(obj)
100 # # [Classes(id=2,name=python016), Classes(id=3,name=linux)]
101 #
102 # # ********************** 统计和分组 ****************************
103 #
104 print(session.query(Classes).filter(Classes.name.like('p%')).count())
105 # 2
106 print(session.query(Classes).count())
107 # 2
108 
109 from sqlalchemy import func
110 print(session.query(Classes).all())
111 # [Classes(id=1,name=python_mysql), Classes(id=2,name=python016), Classes(id=3,name=linux), Classes(id=5,name=linux)]
112 print(session.query(func.count(Classes.name),Classes.name).group_by(Classes.name).all())
113 #[(1, 'python_mysql'), (1, 'python016'), (2, 'linux')]

程序

外键

 1 from sqlalchemy import Column,Integer,String
 2 from sqlalchemy import ForeignKey
 3 from sqlalchemy.orm import relationship
 4 from sqlalchemy.ext.declarative import declarative_base
 5 from sqlalchemy import create_engine
 6 from sqlalchemy.orm import sessionmaker
 7 
 8 engine = create_engine("mysql+pymysql://user:password@127.0.0.1:3306/test", max_overflow = 5)
 9 
10 Base = declarative_base()
11 
12 class Customer(Base):
13     __tablename__='customer'
14     id = Column(Integer,primary_key=True)
15     name = Column(String(32))
16     billing_address_id = Column(Integer,ForeignKey('address.id'))
17     shipping_address_id = Column(Integer)
18     #shipping_address_id = Column(Integer,ForeignKey('address.id'))
19     address = relationship('Address', backref='customer')
20   #  shipping_address = relationship('Address')
21     def __repr__(self):
22         return "id:%s,name:%s,billing_address_id:%s,shipping_address_id:%s"
23                %(self.id,self.name,self.billing_address_id,self.shipping_address_id)
24 
25 class Address(Base):
26     __tablename__ = 'address'
27     id = Column(Integer,primary_key=True)
28     stress = Column(String(64))
29     city = Column(String(64))
30     state = Column(String(64))
31 
32     def __repr__(self):
33         return 'id:%s,stress:%s,city:%s,state:%s' %(self.id,self.stress,self.city,self.state)
34 
35 Base.metadata.create_all(engine) #创建表结构
36 
37 session_class = sessionmaker(engine)
38 session = session_class()
39 
40 # cus1 = Customer(name='gareth',billing_address_id=1, shipping_address_id = 2)
41 # cus2 = Customer(name = 'Jack',billing_address_id=2, shipping_address_id = 3)
42 # cus3 = Customer(name ='Mar',billing_address_id=3, shipping_address_id = 3)
43 #
44 # add_obj = Address(stress = 'shuangxing', city = 'shunyi', state= 'Beijing')
45 # add_obj1 = Address(stress = 'tiantongyuan', city = 'changping', state = 'Beijing')
46 # add_obj2 = Address(stress = 'bayiqiao', city = 'nanchang', state = 'jiangxi')
47 # session.add_all([add_obj,add_obj1,add_obj2,cus1,cus2,cus3])
48 #
49 # session.commit()
50 
51 obj = session.query(Address).first()
52 # print(obj)      # id:1,stress:shuangxing,city:shunyi,state:Beijing
53 # print(obj.customer)  # [id:1,name:gareth,billing_address_id:1,shipping_address_id:2]
54 # for i in obj.customer:
55 #     print(i)  # id:1,name:gareth,billing_address_id:1,shipping_address_id:2
56 
57 obj = session.query(Customer).first()
58 # print(obj)    # id:1,name:gareth,billing_address_id:1,shipping_address_id:2
59 # print(obj.address)  # id:1,stress:shuangxing,city:shunyi,state:Beijing

外键

注：

定义外键： billing_address_id = Column(Integer,ForeignKey('address.id'))

relationship：address = relationship('Address', backref='customer')，通过Address表可以反查customer信息，可以在Customer表中通过address字段，查找Adress表中信息；从而两张表互查。

1 obj = session.query(Customer).first()
2 print(obj)          # id:1,name:gareth,billing_address_id:1,shipping_address_id:2
3 print(obj.address)  # id:1,stress:shuangxing,city:shunyi,state:Beijing

1 obj = session.query(Address).first()
2 print(obj)      # id:1,stress:shuangxing,city:shunyi,state:Beijing
3 print(obj.customer)  # [id:1,name:gareth,billing_address_id:1,shipping_address_id:2]
4 for i in obj.customer:
5     print(i)  # id:1,name:gareth,billing_address_id:1,shipping_address_id:2

多外键关联

 1 from sqlalchemy import Column,Integer,String
 2 from sqlalchemy import ForeignKey
 3 from sqlalchemy.orm import relationship
 4 from sqlalchemy.ext.declarative import declarative_base
 5 from sqlalchemy import create_engine
 6 from sqlalchemy.orm import sessionmaker
 7 
 8 engine = create_engine("mysql+pymysql://user:password@127.0.0.1:3306/test", max_overflow = 5)
 9 
10 Base = declarative_base()
11 
12 class Customer(Base):
13     __tablename__='customer'
14     id = Column(Integer,primary_key=True)
15     name = Column(String(32))
16     billing_address_id = Column(Integer,ForeignKey('address.id'))
17     shipping_address_id = Column(Integer,ForeignKey('address.id'))
18     address = relationship('Address')
19     shipping_address = relationship('Address')
20     def __repr__(self):
21         return "id:%s,name:%s,billing_address_id:%s,shipping_address_id:%s"
22                %(self.id,self.name,self.billing_address_id,self.shipping_address_id)
23 
24 class Address(Base):
25     __tablename__ = 'address'
26     id = Column(Integer,primary_key=True)
27     stress = Column(String(64))
28     city = Column(String(64))
29     state = Column(String(64))
30 
31     def __repr__(self):
32         return 'id:%s,stress:%s,city:%s,state:%s' %(self.id,self.stress,self.city,self.state)
33 
34 Base.metadata.create_all(engine) #创建表结构
35 
36 session_class = sessionmaker(engine)
37 session = session_class()

创建表结构OK，但Address表中插入数据时会报下面的错

sqlalchemy.exc.AmbiguousForeignKeysError: Could not determine join condition between parent/child tables on relationship Customer.address - 
there are multiple foreign key paths linking the tables.  Specify the 'foreign_keys' argument, providing a list of those columns which should be counted as containing a foreign key reference to the parent table.

修改方法：

1 class Customer(Base):
2     __tablename__='customer'
3     id = Column(Integer,primary_key=True)
4     name = Column(String(32))
5     billing_address_id = Column(Integer,ForeignKey('address.id'))
6     shipping_address_id = Column(Integer,ForeignKey('address.id'))
7     billing_address = relationship('Address',foreign_keys=[billing_address_id])
8     shipping_address = relationship('Address',foreign_keys=[shipping_address_id])

处理中文

sqlalchemy设置编码字符集一定要在数据库访问的URL上增加charset=utf8，否则数据库的连接就不是utf8的编码格式

1 eng = create_engine('mysql://root:root@localhost:3306/test2?charset=utf8',echo=True)

多对多关系

ORM_API

 1 from sqlalchemy import Column,Integer,String,Date,Table
 2 from sqlalchemy import ForeignKey
 3 from sqlalchemy.orm import relationship
 4 from sqlalchemy.ext.declarative import declarative_base
 5 from sqlalchemy import create_engine
 6 from sqlalchemy.orm import sessionmaker
 7 
 8 engine = create_engine("mysql+pymysql://admin:admin@localhost:3306/test_py?charset=utf8", max_overflow = 5)
 9 
10 Base = declarative_base()
11 
12 book_m2m_author = Table('book_m2m_author', Base.metadata,
13                         Column('book_id',Integer,ForeignKey('books.id')),
14                         Column('author_id',Integer,ForeignKey('authors.id')),
15                         )
16 class Book(Base):
17     __tablename__='books'
18     id = Column(Integer,primary_key=True)
19     name = Column(String(64))
20     pub_data = Column(Date)
21     authors = relationship('Author',secondary=book_m2m_author, backref='books')
22 
23     def __repr__(self):
24         return self.name
25 
26 class Author(Base):
27     __tablename__='authors'
28     id = Column(Integer,primary_key=True)
29     name = Column(String(32))
30 
31     def __repr__(self):
32         return self.name
33 
34 Base.metadata.create_all(engine) #创建表结构

session:

 1 from sqlalchemy.orm import sessionmaker
 2 import book_orm
 3 
 4 
 5 Session = sessionmaker(book_orm.engine)
 6 session = Session()
 7 
 8 b1 = book_orm.Book(name='三国演义')
 9 b2 = book_orm.Book(name='水浒传')
10 b3 = book_orm.Book(name='红楼梦')
11 b4 = book_orm.Book(name='西游记')
12 
13 a1 = book_orm.Author(name='罗贯中')
14 a2= book_orm.Author(name='曹雪芹')
15 a3= book_orm.Author(name='吴承恩')
16 a4= book_orm.Author(name='施耐庵')
17 a5= book_orm.Author(name='Gareth')
18 a6= book_orm.Author(name='Yu')
19 a7= book_orm.Author(name='caven')
20 
21 b1.authors = [a1,a6]
22 b2.authors = [a4,a5,a7]
23 b3.authors = [a2]
24 b4.authors = [a3]
25 
26 session.add_all([a1,a2,a3,a4,a5,a6,a7,b1,b2,b3,b4])
27 session.commit()

table:

 1 mysql> show tables;
 2 +-------------------+
 3 | Tables_in_test_py |
 4 +-------------------+
 5 | authors           |
 6 | book_m2m_author   |
 7 | books             |
 8 | classes           |
 9 | student           |
10 | user              |
11 +-------------------+
12 6 rows in set (0.03 sec)
13 
14 
15 mysql> select * from authors;
16 +----+--------+
17 | id | name   |
18 +----+--------+
19 |  1 | 罗贯中 |
20 |  2 | Yu     |
21 |  3 | 曹雪芹 |
22 |  4 | 吴承恩 |
23 |  5 | 施耐庵 |
24 |  6 | Gareth |
25 |  7 | caven  |
26 +----+--------+
27 7 rows in set (0.03 sec)
28 
29 
30 mysql> select * from authors;
31 +----+--------+
32 | id | name   |
33 +----+--------+
34 |  1 | 罗贯中 |
35 |  2 | Yu     |
36 |  3 | 曹雪芹 |
37 |  4 | 吴承恩 |
38 |  5 | 施耐庵 |
39 |  6 | Gareth |
40 |  7 | caven  |
41 +----+--------+
42 7 rows in set (0.03 sec)
43 
44 
45 mysql> select * from book_m2m_author;
46 +---------+-----------+
47 | book_id | author_id |
48 +---------+-----------+
49 |       4 |         5 |
50 |       4 |         6 |
51 |       1 |         1 |
52 |       1 |         2 |
53 |       2 |         3 |
54 |       4 |         7 |
55 |       3 |         4 |
56 +---------+-----------+
57 7 rows in set (0.00 sec)

tables

查询：

1 print("通过book table查询关联的作者")
2 book_obj = session.query(book_orm.Book).filter_by(name="三国演义").all()
3 print(book_obj)   # [三国演义]
4 book_obj = session.query(book_orm.Book).filter_by(name="三国演义").first()
5 print(book_obj.name, book_obj.authors)
6 #三国演义 [罗贯中, 曹雪芹]
7 print("通过author table查询关联的书")
8 author_obj = session.query(book_orm.Author).filter_by(name="曹雪芹").first()
9 print(author_obj.name, author_obj.books)

直接删除作者：

 1 author_obj = session.query(book_orm.Author).filter(book_orm.Author.id>7).all()
 2 print(author_obj)
 3 
 4 for i in author_obj:
 5     session.delete(i)
 6 session.commit()
 7 print(session.query(book_orm.Author).all())
 8 
 9 #[罗贯中, 曹雪芹, 施耐庵, Yu, 罗贯中, 曹雪芹, 施耐庵, Yu, 罗贯中, 曹雪芹, 施耐庵, Yu, 罗贯中, 曹雪芹, 施耐庵, Yu, 罗贯中, 罗贯中, 曹雪芹, 罗贯中, 曹雪芹]
10 #[罗贯中, Yu, 曹雪芹, 吴承恩, 施耐庵, Gareth, caven]

通过书删除作者：

1 author_obj = session.query(book_orm.Author).filter_by(name="曹雪芹").first()
2 book_obj = session.query(book_orm.Book).filter_by(name="红楼梦").first()
3 book_obj.authors.remove(author_obj)
4 session.commit()

删除数据时不用管boo_m2m_authors　， sqlalchemy会自动帮你把对应的数据删除。