ORM简介

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

SQLAlchemy简介

SQLAlchemy是Python编程语言下的一款ORM框架，该框架建立在数据库API之上，使用关系对象映射进行数据库操作，简言之便是：将对象转换成SQL，然后使用数据API执行SQL并获取执行结果。SQLAlchemy本身无法操作数据库，其必须以来pymsql等第三方插件，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...]

详见：官方文档

SQLAlchemy安装&使用

Python下通过easy_install或者pip安装：

$ pip3/easy_install install SQLAlchemy

使用指南

1. postgres建表

CREATE TABLE user (
    id INTEGER NOT NULL AUTO_INCREMENT,
    name VARCHAR(32),
    password VARCHAR(64),
    PRIMARY KEY (id)
)

2. sqlalchemy建表

from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String


connect = create_engine("mysql+pymysql://root:123456@localhost:3306/mysql",
                        encoding="utf-8",
                        echo=True)  # 连接数据库，echo=True =>把所有的信息都打印出来


Base = declarative_base()  # 生成ORM基类


class User(Base):
    __tablename__ = "hello_word"  # 表名
    id = Column(Integer, primary_key=True)
    name = Column(String(32))
    password = Column(String(64))

Base.metadata.create_all(connect)   # 创建表结构

3. 建立与postgres的Session会话连接，便可进行增删查改等操作

session_class = sessionmaker(bind=connect)  # 创建与数据库的会话session class ,这里返回给session的是个class,不是实例
session = session_class()   # 生成session实例

4. 新增数据

#原生sql：insert  into mysql.hello_word(name,password) values("test2","1234")；
obj = User(name="test", password="1234")  #生成你要创建的数据对象
session.add(obj) #把要创建的数据对象添加到这个session里， 一会统一创建
session.commit() #统一提交，创建数据，在此之前数据库是不会有新增数据的

#新增多条数据
#原生sql:insert  into mysql.hello_word(name,password) values("test2","1234"),("test3","123");
obj = User(name="test", password="1234")
obj1 = User(name="test", password="1234")
session.add_all([obj,obj1])
session.commit() #回滚，在session.add()之后，在session.commit()之前，想把添加至session缓存中的数据清除，使用rollback()函数回滚即可<br>Session.rollback()

5. 删除数据

#原生sql:mysql.hello_word where id > 5;
session.query(User).filter(User.id > 5).delete() #通过session查询User类，然后过滤出id>5的进行删除
session.commit() #提交

6. 更新数据

#①第一种方式
data = Session.query(User).filter_by(name="test1").first()  #获取数据
data.name = "test"  #修改数据
Session.commit()   #提交

#②第二种方式，通过查找表，过滤条件，然后更新对应参数
session.query(User).filter(User.id > 15).update({"name": "test"})
session.query(User).filter(User.id == 18).update({User.name: "hello"}, synchronize_session=False)
session.query(User).filter_by(name="test1").update({User.password: User.name}, synchronize_session="evaluate")
session.commit()

#③synchronize_session解释，用于query在进行delete or update操作时，对session的同步策略：
#1、synchronize_session=False，不对session进行同步，直接进行delete or update操作。
#2、synchronize_session="evaluate"，在delete or update操作之前，用query中的条件直接对session的identity_map中的objects进行eval操作，将符合条件的记录下来，　在delete or update操作之后，将符合条件的记录删除或更新。

7. 查询数据

#原生sql:select * from mysql.hello_word;
ret = session.query(User).all()  #查询所有
#也可以这样写：
ret = Session.query(User.name,User.id).all()

#原生slq:select name,password from mysql.hello_word;
ret = session.query(User.name, User.extra).all()  #只查询name和extra字段所以所有数据

#原生sql:select * from mysql.hello_word where name="test1";
ret = session.query(User).filter_by(name='test1').all() #查询name='alex'的所有数据
ret = session.query(User).filter_by(name='test1').first()#查询name='alex'的第一条数据

#查询id>5的name字段内容，且以id大小排序
#原生sql；select name from mysql.hello_word where id >5 order by id；
ret = session.query(User).filter(text("id>:value and name=:name")).params(value=5, name='test2').order_by(User.id).all()

#根据原生sql查询数据
ret = session.query(User).from_statement(text("SELECT * FROM hello_word where name=:name")).params(name='test1').all()

Notes：filter和filter_by使用的区别

#filter用于sql表达式查询过滤，如>，<, ==,等表达式
session.query(MyClass).filter(MyClass.name == 'some name')
#filter_by用于关键字查询过滤，如id=value,name=value
session.query(MyClass).filter_by(name = 'some name')

8. 其他操作

#多条件查询
#原生sql:select * from mysql.hello_word where id >2 and id < 19
data = session.query(User).filter(Use.id>2).filter(Use.id<19).all()

<br>#通配符
#原生sql:select * from mysql.hello_word where name like "test%" #"test_"、%test%
data = session.query(User).filter(User.name.like('test%')).all() #匹配以test开头，而后跟多个字符
data = session.query(User).filter(User.name.like('test_')).all() #匹配以test开头，而后跟一个字符
data = session.query(User).filter(~User.name.like('e%')).all() #加~后，忽略like(),直接匹配所有
#原生sql select count(name) from mysql.hello_word where name like "%test%"
data = session.query(User).filter(User.name.like("%qigao%")).count() # 模糊匹配并计数
<br>
#分组
from sqlalchemy import func #导入func 进行函数操作
#原生sql:select count(name),name from mysql.hello_word group by name
data =session.query(func.count(User.name),User.name).group_by(User.name).all()  #根据User.name分组
#原生sql:select max(id),sum(id),min(id) from mysql.hello_word group by name  #根据name 分组
data =session.query(func.max(User.id),func.sum(User.id),func.min(User.id)).group_by(User.name).all()
#原生sql:select max(id),sum(id),min(id) from mysql.hello_word group by name having min(id > 2) # 根据name分组且id>2
data = session.query(func.max(User.id),func.sum(User.id),func.min(User.id)).group_by(User.name).having(func.min(User.id) >2).all()
<br><br>#排序
#原生sql:select * from mysql.hello_word  order by id asc
data = session.query(User).order_by(User.id.asc()).all() #将所有数据根据 “列” 从小到大排列
#原生sql:select * from mysql.hello_word  order by id desc, id asc
data = session.query(User).order_by(User.id.desc(), User.id.asc()).all()#将所有数据根据 “列1” 从大到小排列,如果相同则按照“列2”由小到大排列


#条件表达式 in、between、 and 、or
data = session.query(User).filter_by(name='test').all()
data = session.query(User).filter(User.id > 1, Users.name == 'test').all()
data = session.query(User).filter(User.id.between(1, 3), Users.name == 'test').all()
data = session.query(User).filter(User.id.in_([1,3,4])).all()
data = session.query(User).filter(~User.id.in_([1,3,4])).all()
data = session.query(User).filter(Users.id.in_(session.query(User.id).filter_by(name='test'))).all()

from sqlalchemy import and_, or_
data = session.query(User).filter(and_(User.id > 3, Users.name == 'test')).all()
data = session.query(User).filter(or_(User.id < 2, Users.name == 'test')).all()
data = session.query(User).filter(or_(User.id < 2,and_(User.name == 'test',User.id > 3),User.password != "")).all()

9. 实践案例（Done）

from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String
from sqlalchemy.orm import sessionmaker

# 连接数据库，echo=True =>把所有的信息都打印出来
connect = create_engine('postgresql+psycopg2://postgres:system123@localhost:5432/PBC', encoding="utf-8", echo=True)

Base = declarative_base() # 生成ORM基类

class User(Base):
    __tablename__ = "hello_word" # 表名
    id = Column(Integer, primary_key=True)
    name = Column(String(32))
    password = Column(String(64))

Base.metadata.create_all(connect) # 创建表结构

session_class = sessionmaker(bind=connect) # 创建与数据库的会话session class ,这里返回给session的是个class,不是实例
session = session_class() # 生成session实例

obj = User(name="test", password="1234") #生成你要创建的数据对象
session.add(obj) #把要创建的数据对象添加到这个session里， 一会统一创建
session.commit() #统一提交，创建数据，在此之前数据库是不会有新增数据的


# import data from csv file to progressql
conn2 = connect.raw_connection()    
cursor = conn2.cursor()

SQL_STATEMENT = """
    COPY %s FROM STDIN WITH
        CSV
        HEADER
        DELIMITER AS ','
    """
my_file = open("data.csv") #可用绝对路径"C:\\Users\\mzhu\\Desktop\\data.csv"

def import_csv_file(conn, table_name, file_object):
    cursor = conn.cursor()
    cursor.copy_expert(sql=SQL_STATEMENT % table_name, file=file_object)
    conn.commit()
    cursor.close()

try:
    import_csv_file(conn2, 'test_table', my_file) #表已建好
finally:
    conn2.close()