SQLAlchemy （一）

概述

SQLAlchemy 库用于与各种数据库交互，你可使用一种类似于 Python 类和语句的方式创建数据模型和查询，SQLAlchemy 可用于连接大多数常见的数据库，比如 Postgres、MySQL、SQLite、Oracle 等：

SQLAlchemy 提供了两种主要的使用模式——SQLAlchemy Core和SQLAlchemy ORM，这为我们使用SQLAlchemy提供了很大的灵活性，两种方式可以单独使用也可以组合使用。

SQLAlchemy Core 和 SQLAlchemy ORM

SQLAlchemy Core 提供了连接数据库，操作数据的相关组件，其中SQL Expression Language是最为核心的组件，该组件允许我们使用sql语句和表达式实现对数据库进行操作:

engine = create_engine("[db]+[driver]://[username]:[password]@[host]:[port]/[database]")
with engine.connect() as con:
    sql = "SELECT * FROM {table_name};"
    con.execute(sql)

SQLAlchemy ORM类似于对象关系映射（ORM）。通过使用python中的类与数据库中的表相关关联，并在此基础使用类似于python方法的方式来实现对于数据的操作：

class User(db.Model):
    __tableName__ = 'dc_user'
    id = db.Column(db.Integer, autoincrement=True, primary_key=True)
    name = db.Column(db.String(16))
    password = db.Column(db.String(16))
    
User.query.get(id)
engine.session.add(User(id,name,password))

安装

SQLAlchemy 可以支持python2.7，python3.6+的python环境

直接从PyPI下载并安装

pip install SQLAlchemy

SQLAlchemy连接数据库需要和数据库对应的Python驱动程序的支持，SQLAlchemy默认直接支持SQLite3，不需要再安装额外的驱动程序，但是再连接其他数据库时候需要安装想要的数据库驱动程序，并且该程序需要支持DBAPI规范：

数据库	驱动
Oracle	cs_Oracle
Mysql	mysqlclient, pymysql, MySQL-Connector …
PostgreSQL	psycopg2

SQLAlchemy 还支持连接 Drizzle、Firebird、Sybase 和 Microsoft SQL Server。

具体详情可以查看 SQLAlchemy DBAPI

SQLAlchmey Core 连接数据库

• 首先需要创建对应数据的Engine对象，Engine对象会根据数据库的连接参数为数据库连接池分配空间，并维持SQLAlchmey与数据库的连接，Engine对象通常是针对一种数据库而创建的全局对象。

  from sqlalchemy import create_engine
  """
  需要指明连接数据库所用的驱动 例如连接mysql使用的是pymysql 则需要在creat_engine中的url的[db]+[driver]字段中指明mysql+pymysql
  echo 是否将执行信息打印
  future 是否使用2.0版本中的 Engine 对象来创建
  同时creat_engine方法中也提供了很多参数来用于设置连接数据库，具体可以查看相关源码
  """
  engine = create_engine("[db]+[driver]://[username]:[password]@[host]:[port]/[table/database]", echo=True, future=True)
  

  当创建好engine时，实际上并没有真的去连接数据库，只是将连接的资源所需要进行分配，只有在第一次执行任务的时候才会真正连接到数据库中

• 再获得Engine对象之后我们就可以连接到数据库中

  with engine.connect() as conn:
  	result = conn.execute("{sql}")
  	print(result.all())
  # 使用with语句来管理Engine对象创建的Connection对象，保证在连接完成后可以自动释放资源，此时execute()执行后SQLAlchmey默认事务仍在执行中并不会自动的提交，当作用域结束释放连接之后会发生回滚，sql语句并不会修改数据库中的数据这时候就需要主动提交事务
  	conn.commit()
  

  同时还可使用一下方式来连接数据库并执行：

  with engine.begin() as conn:
      conn.execute(
      text("INSERT INTO some_table (x, y) VALUES (:x, :y)"),
      [{"x": 6, "y": 8}, {"x": 9, "y": 10}]
      )
  #这种方式直接声明当前的连接块为事务块，会在资源回收后自动的commit
  

• 在执行sql语句时，一般先写好sql语句的statement之后再将相关的值注入sql语句中，SQLAlchemy Core中也提供相应的实现，我们可以使用text（）方法来预先构建sql statement，对于需要注入值的变量，需要用:来标明，之后在execte()时传入对应的字典或者字典数组

  with engine.connect() as conn:
  	sql = text("INSERT INTO some_table (x, y) VALUES (:x, :y)")
      # 插入单条数据
      conn.execute(
  		sql,
  		{"x": 11, "y": 12}
  	)
      # 插入多条数据
  	conn.execute(
  		sql,
  		[{"x": 11, "y": 12}, {"x": 13, "y": 14}]
  	)
  	conn.commit()
  

• 在连接并执行相关sql语句之后我们就需要对数据中的查询结果进行处理，SQLAlchemy Core中提供了一个Result的类，在一个Result的类对象中保存执行的结果，Result中提供了很多用于转换和读取的方法：

  # 使用元组的方式访问result中的数据
  result = conn.execute(text("select x, y from some_table"))
  for x, y in result:
      pass
  
  # 使用索引的方式
  result = conn.execute(text("select x, y from some_table"))
  for row in result:
        x = row[0]
          
  # 使用访问其中属性的方式       
  result = conn.execute(text("select x, y from some_table"))
  for row in result:
      y = row.y
      print(f"Row: {row.x} {y}")
  
  # 同时Result中还提供将结果转换成字典的方法 Result.mappings()
  result = conn.execute(text("select x, y from some_table"))
  for dict_row in result.mappings():
      x = dict_row['x']
      y = dict_row['y']