一、SQLAlchemy

      SQLAlchemy 是python 操作数据库的一个库，能够进行 orm 映射。

1. orm

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

python如何映射EXCEL表头和与数据库数据表字段_数据库

2. 使用SQLAlchemy操作MySQL

  • 创建连接
  • 声明映射文件
  • 创建模式
  • 初始化映射类实例
  • 创建回话
  • 持久化实例对象

 3. SQLAlchemy创建表

    首先通过create_engine创建一个连接引擎，echo=true可以打印出后台操作的SQL语句，在SQLAlchemy中通过Declarative完成类与表的映射，通过declarative_base()创建一个基类，然后程序可以基于这个基类创建用户的自定义类，在自定义类中定义表结构（表名、列名、列的类型等）。

1 #创建连接引擎
 2 engine = create_engine('mysql+pymysql://root:123456@127.0.0.1/py14',encoding = 'utf-8',echo = True)
 3 #创建表基类
 4 Base = declarative_base()
 5 
 6 class User(Base):
 7     __tablename__ = 'user'#表名
 8     #表中的列名
 9     id = Column(Integer,primary_key=True)
10     name = Column(String(32))
11     password = Column(String(64))
12 
13     def __repr__(self):
14         return '<%s name:%s>'%(self.id,self.name)

此时数据库中还没有创建user表，需要调用Metadata。create_all()方法，完成数据表的创建，将上面创建的连接引擎作为参数传入

Base.metadata.create_all(engine)

session是ORM与数据库连接的入口，将session与engine绑定，就可以调用session的方法对数据库表进行增删改查等操作了

#创建回话
Session_class = sessionmaker(bind = engine)
session = Session_class()
user_obj = User(name = 'jim',password = '123')
print(user_obj.name,user_obj.id)
session.add(user_obj)
session.commit()

 4. SQLAlchemy对表进行增删改查

(1)查询

执行查询可以使用Session的query函数,all()函数返回查询的所有元组的list,first()返回至多一条记录。filter或filter_by函数用来对筛选查询条件，filter_by只适用于精确查询，filter适用于模糊查询。

1 users = session.query(User).all()
2 user1 = session.query(User).filter_by(name = 'jim').first()
3 
4 print(users)
5 print(user1.name,user1.password)
6 
7 session.commit()

 (2)修改数据

可以直接通过实例名称.属性名=  更新值

user1.name = 'rain'
user1.password = '1234'

（3）删除数据通过Session的delete（）函数

session.delete(user4)
session.commit()

 二、 多表关联

       由于关系数据库的多个表还可以用外键实现一对多、多对多等关联，相应地，ORM框架也可以提供两个对象之间的一对多、多对多等功能。

1. 一对多关联

1 class Student(Base):
 2     __tablename__ = "student"
 3     id = Column(Integer, primary_key=True)
 4     name = Column(String(32),nullable=False)
 5     register_date = Column(DATE,nullable=False)
 6 
 7     def __repr__(self):
 8         return "<%s name:%s>" % (self.id, self.name)
 9 
10 class StudyRecord(Base):
11     __tablename__ = "study_record"
12     id = Column(Integer, primary_key=True)
13     day = Column(Integer,nullable=False)
14     status = Column(String(32),nullable=False)
15     stu_id = Column(Integer,ForeignKey("student.id"))
16     #study_obj = query(id=1)
17     #student = query(Student).filter(Student.id == stu_obj.stu_id).first()
18     student = relationship("Student", backref="my_study_record") #允许你在student表里通过backref字段反向查出所有它在study_record表里的关联项
19 
20     def __repr__(self):
21         return "<%s day:%s status:%s>" % (self.student.name, self.day,self.status)
22 
23 Base.metadata.create_all(engine)  # 创建表结构

1 stu_obj = session.query(Student).filter(Student.name=="alex").first()
2 print(stu_obj.my_study_record)
3 print('----------------------------------')
4 sr = session.query(StudyRecord).filter(StudyRecord.day == 1).all()
5 print(sr)
6 session.commit()

ORM通过relationship()函数和foreignkey将student和study_record关联起来，在Study_Record中定义student属性，可以通过Study_Record.student属性查询对应的所有student对象，同样也在Student中建立了my_study_record属性，可以通过Student.my_sudy_record查询Student对应的所有Study_Record集合。

2. 多对多关联

例如有两张表分别是作者表和书籍表，一个作者可以写好多书，一本书可以好多作者。此时需要创建第三张表来存储两者的关联关系，这张表是未映射的（也就是没有一个Python类与之对应）

1 Base = declarative_base()  # 生成orm基类
 2 book_m2m_author = Table('book_m2m_author', Base.metadata,
 3                         Column('book_id', Integer, ForeignKey('books.id')),
 4                         Column('author_id', Integer, ForeignKey('authors.id')),
 5                         )
 6 
 7 
 8 class Book(Base):
 9     __tablename__ = 'books'
10     id = Column(Integer, primary_key=True)
11     name = Column(String(64))
12     pub_date = Column(DATE)
13     authors = relationship('Author', secondary=book_m2m_author, backref='books')
14 
15     def __repr__(self):
16         return self.name
17 
18 
19 class Author(Base):
20     __tablename__ = 'authors'
21     id = Column(Integer, primary_key=True)
22     name = Column(String(32))
23 
24     def __repr__(self):
25         return self.name
26 
27 
28 Session_class = sessionmaker(bind=engine)  # 创建与数据库的会话session class ,注意,这里返回给session的是个class,不是实例
29 s = Session_class()  # 生成session实例
30 
31 b1 = Book(name="跟Alex学Python")
32 b2 = Book(name="跟Alex学java")
33 b3 = Book(name="跟Alex学linux")
34 b4 = Book(name="跟Rain学mysql")
35 
36 a1 = Author(name="Alex")
37 a2 = Author(name="Jack")
38 a3 = Author(name="Rain")
39 
40 b1.authors = [a1, a2]
41 b2.authors = [a1, a2, a3]
42 
43 s.add_all([b1, b2, b3, b4, a1, a2, a3])
44 
45 s.commit()

在relationship（）函数中多了secondary参数，这个参数指向了中间表(原文为associated table)。这个中间表只包含了指向多对多关系两侧的表的主键的列。

1 aut = s.query(Author).filter(Author.name =='Alex').first()
2 print(aut.name,aut.books)