SQLAlchemy--For Python

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

2. 底层处理

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

# -*- coding:utf-8 -*-
from sqlalchemy import create_engine

engine = create_engine("mysql+pymysql://root:123@127.0.0.1:3306/t1", max_overflow=5)

# 执行SQL
cur = engine.execute(
    "insert into hosts (host, color_id) values ('1.1.1.0', 3)"
)

# 新插入行自增ID
cur.lastrowid

# 执行SQL
cur = engine.execute(
    "insert into hosts (host, color_id) values(%s, %s)", [('1.1.1.0', 3), ('1.1.1.1', 3),]
)

# 执行SQL
cur = engine.execute("select * from hosts")
# 获取第一行数据
cur.fetchone()
# 获取第n行数据
cur.fetchmany(3)
# 获取所有数据
cur.fetchall()

3. 基本使用

1）单表操作

# -*- coding:utf-8 -*-
import sqlalchemy
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String
from sqlalchemy.orm import sessionmaker
print sqlalchemy.__version__

engine = create_engine("mysql+pymysql://root:root@127.0.0.1/test.db", echo=True)

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


class User(Base):
    __tablename__ = "users"

    id = Column(Integer, primary_key=True, autoincrement=True)
    name = Column(String(40))
    fullname = Column(String(80))
    password = Column(String(40))

    # def __repr__(self):
    #     return "<User(name='%s', fullname='%s', password='%s')>" % (
    #         self.name, self.fullname, self.password
    #     )


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

ed_user = User(name="Apple", fullname="Apple Newton", password="tree")

print(ed_user)

# 这两行触发sessionmaker类下的__call__方法，return得到Session实例，赋给变量session
MySession = sessionmaker(bind=engine)
session = MySession()

# 添加数据
session.add(ed_user)

# our_user = session.query(User).filter_by(name='Apple').first()
# select * from user where name='Apple' limit 1;
# session.query(User).all()
# for row in session.query(User).order_by(User.id):
#     print row
# for row in session.query(User).filter(User.name.in_(["Alex", "Bob", "Cathy"])):
#     print row
# print session.query(User).filter(User.name=='Bob').count()
from sqlalchemy import and_, or_
for row in session.query(User).filter(and_(User.name=="Bob", User.fullname=="Bob Cooper")):
    print row



# 添加多条数据
session.add_all(
    User(name="Avy", fullname="Avy Nash", password="123"),
    User(name="Elly", fullname="Elly Johnson", password="123"),
    User(name="Bob", fullname="Bob Cooper", password="123"),
)

# 提交
session.commit()