python-sqlalchemy

官方教程： http://docs.sqlalchemy.org/en/rel_1_0/core/engines.html
参考：https://www.jianshu.com/p/2f07258ffc98
Flask-SQLAlchemy： http://python.jobbole.com/86797/

SQLAlchemy 是python的一款开源软件，提供了SQL工具包及对象关系映射(ORM)工具。(需要安装第三方库), 避免写繁复的sql语句.（隐藏数据库，良好的数据接口，动态的数据映射，引入缓存）

Database_urls:
'数据库类型+数据库驱动名称://用户名:口令@机器地址:端口号/数据库名'
dialect+driver://username:[email protected]:port/database

mysql://root:[email protected]:3306/test
postgresql://scott:[email protected]/mydatabase
oracle://scott:[email protected]:1521/sidname
sqlite:///foo.db

# Connecting
from sqlalchemy import create_engine
engine = create_engine('sqlite:///test.db', echo=True)  # sqlite:///:memory:  内存数据库
# 返回Engine仓库的实例, echo - SQLAlchemy logging  打印出sql语句

# Define and Create Tables¶

from sqlalchemy import Table, Column, Integer, String, MetaData, ForeignKey
metadata = MetaData()

# 数据table的信息
users = Table('users', metadata,
        Column('id', Integer, primary_key=True),   # 主键
        Column('name', String),
        Column('fullname', String),
              )

addresses = Table('addresses', metadata,
        Column('id', Integer, primary_key=True),     # 主键
        Column('user_id', None, ForeignKey('users.id')),
        Column('email_address', String, nullable=False)
                  )

metadata.create_all(engine)
# 创建两个数据表分别为users 和 address


# insert
ins = users.insert()
print str(ins)
ins = users.insert().values(name='jack', fullname='Jack Jones')
print str(ins)
print ins.compile().params

# Executing

conn = engine.connect()
result = conn.execute(ins)
print result
ins = users.insert()
conn.execute(ins, id=20, name='wendy', fullname='Wendy Williams')


conn.execute(addresses.insert(), [
    {'user_id': 1, 'email_address' : '[email protected]'},
    {'user_id': 1, 'email_address' : '[email protected]'},
   {'user_id': 2, 'email_address' : '[email protected]'},
   {'user_id': 2, 'email_address' : '[email protected]'},
])

# Selecting

from sqlalchemy.sql import select
s = select([users])
result = conn.execute(s)
row = result.fetchone()
print row
for row in result:
    print(row)
result.close()

# 创建表的其它方式
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()  # 基类
from sqlalchemy import Column, Integer, String

class User(Base):
    __tablename__ = 'users'
    id = Column(Integer, primary_key=True)
    name = Column(String(10))
    password = Column(String(10))
    def __repr__(self):
        return "<User(name='%s', name='%s', password='%s')>" % (self.name, self.name, self.password)

User.metadata.create_all(engine)


# 创建会话
from sqlalchemy.orm import sessionmaker
Session = sessionmaker(bind=engine)
session = Session()

# 持久化实例对象, 插入
ed_user = User(id=1, name='xiexiaolu', password='dianwo')
session.add(ed_user)
session.commit()

# 在建立的会话基础上执行sql语句
session.execute('insert into users values(2,"Bob","budian")')
session.commit()


# 查询操作
user=session.query(User).filter(User.id ==1).one()
print(user.name)
print(user.password)

users = session.query(User).all()    # 返回数据表所有数据

session.execute('update addresses set user_id = 1 where id = 2')
session.commit()
session.query(User).filter(User.id == 2).update({"id": 1})