sqlalchemy学习笔记

SQLAlchemy是python的一个数据库ORM工具，提供了强大的对象模型间的转换，可以满足绝大多数数据库操作的需求，并且支持多种数据库引擎（sqlite，mysql，postgres, mongodb等），在这里记录基本用法和学习笔记

一、安装

通过pip安装

$ pip install SQLAlchemy

二、使用

首先是连接到数据库，SQLALchemy支持多个数据库引擎，不同的数据库引擎连接字符串不一样，常用的有

mysql://username:password@hostname/database
postgresql://username:password@hostname/database
sqlite:absolute/path/to/database
sqlite:///c:/absolute/path/to/database

更多连接字符串的介绍参见这里

下面是连接和使用sqlite数据库的例子

1. connection

使用传统的connection的方式连接和操作数据库

from sqlalchemy import create_engine

# 数据库连接字符串
DB_CONNECT_STRING = 'sqlite:///:memory:'

# 创建数据库引擎,echo为True,会打印所有的sql语句
engine = create_engine(DB_CONNECT_STRING, echo=True)

# 创建一个connection，这里的使用方式与python自带的sqlite的使用方式类似
with engine.connect() as con:
    # 执行sql语句，如果是增删改，则直接生效，不需要commit
    rs = con.execute('SELECT 5')
    data = rs.fetchone()[0]
    print "Data: %s" % data

与python自带的sqlite不同，这里不需要Cursor光标，执行sql语句不需要commit

2. connection事务

使用事务可以进行批量提交和回滚

from sqlalchemy import create_engine

# 数据库连接字符串
DB_CONNECT_STRING = 'sqlite:Users/zhengxiankai/Desktop/Document/db.sqlite'
engine = create_engine(DB_CONNECT_STRING, echo=True)

with engine.connect() as connection:
    trans = connection.begin()
    try:
        r1 = connection.execute("select * from User")
        r2 = connection.execute("insert into User(name, age) values(?, ?)", 'bomo', 24)
        trans.commit()
    except:
        trans.rollback()
        raise

3. session

connection是一般使用数据库的方式，sqlalchemy还提供了另一种操作数据库的方式，通过session对象，session可以记录和跟踪数据的改变，在适当的时候提交，并且支持强大的ORM的功能，下面是基本使用

from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker

# 数据库连接字符串
DB_CONNECT_STRING = 'sqlite:Users/zhengxiankai/Desktop/Document/db.sqlite'

# 创建数据库引擎,echo为True,会打印所有的sql语句
engine = create_engine(DB_CONNECT_STRING, echo=True)

# 创建会话类
DB_Session = sessionmaker(bind=engine)

# 创建会话对象
session = DB_Session()

# dosomething with session

# 用完记得关闭，也可以用with
session.close()

上面创建了一个session对象，接下来可以操作数据库了，session也支持通过sql语句操作数据库

session.execute('select * from User')
session.execute("insert into User(name, age) values('bomo', 13)")
session.execute("insert into User(name, age) values(:name, :age)", {'name': 'bomo', 'age':12})

# 如果是增删改，需要commit
session.commit()

注意参数使用dict，并在sql语句中使用:key占位

4. ORM

上面简单介绍了sql的简单用法，既然是ORM框架，我们先定义两个模型类User和Role，sqlalchemy的模型类继承自一个由declarative_base()方法生成的类，我们先定义一个模块Models.py生成Base类

from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

User.py

from sqlalchemy import Column, Integer, String
from Models import Base

class User(Base):
    __tablename__ = 'User'
    id = Column('id', Integer, primary_key=True, autoincrement=True)
    name = Column('name', String(50))
    age = Column('age', Integer)

Role.py

from sqlalchemy import Column, Integer, String
from Models import Base

class Role(Base):
    __tablename__ = 'Role'
    id = Column('id', Integer, primary_key=True, autoincrement