Python基础11 SQLAlchemy入门使用

一、简介

SQLAlchemy是构建在WSGI规范下的，Python企业级持久性模型，支持ORM（Object Relational Mappers，ORM）。

二、安装

环境：CentOS6.5

pip install sqlalchemy
yum install python-devel mysql-devel zlib-devel openssl-devel  -y
pip install mysql-python
pip install --egg mysql-connector-python-rf

如果切换源，使用 -i 参数，如：

 -i https://pypi.tuna.tsinghua.edu.cn/simple

其中-i是切换源，附几个国内源

• pypi.douban.com
• pypi.hustunique.com
• pypi.gocept.com
• pypi.tuna.tsinghua.edu.cn

也可以到下面地址下载安装：

• https://pypi.python.org/pypi/MySQL-python/1.2.5#downloads
• http://www.codegood.com/download/11/
• 官网

如果上面安装的某个包有问题，比较容易的方式是去搜索其whl包，下载以后在对应目录直接使用命令：pip install 文件名 安装。

另外需要mysqlclient到 https://dev.mysql.com/downloads/connector/python/ 下载

三、准备数据库、表

/etc/my.ini
注释掉 #skip-name-resolve

建表
在test数据库建user表，字段：id,name

CREATE TABLE IF NOT EXISTS `user` (
  `id` int(11) NOT NULL,
  `name` varchar(32) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

操作语句

#!/usr/bin/python
#-*-coding:utf-8-*-

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

# 创建对象的基类:
Base = declarative_base()

# 定义User对象:
class User(Base):
    # 表的名字:
    __tablename__ = 'user'

    # 表的结构:
    id = Column(String(20), primary_key=True)
    name = Column(String(20))

# 初始化数据库连接:
engine = create_engine('mysql+mysqlconnector://test:test@localhost:3306/test')
# 创建DBSession类型:
DBSession = sessionmaker(bind=engine)

# 创建session对象:
session = DBSession()
# 创建新User对象:
new_user = User(id='5', name='Bob')
# 添加到session:
session.add(new_user)
# 提交即保存到数据库:
session.commit()
# 关闭session:
session.close()

建表示例

# -*- coding: utf-8 -*-

from flask import Flask
#from flask.ext.sqlalchemy import SQLAlchemy
from flask_sqlalchemy import SQLAlchemy
from sqlalchemy import create_engine, Table, Column, Integer, String, MetaData, ForeignKey
# 连接数据库 
engine = create_engine("mysql+mysqlconnector://adspy:adspy1$$3456@localhost:3306/test?charset=utf8",encoding="utf-8", echo=True)
# 获取元数据
metadata = MetaData()
# 定义表
user = Table('user1', metadata,
    Column('id', Integer, primary_key=True),
    Column('name', String(20)),
    Column('fullname', String(40)),
  )
 
address = Table('address1', metadata,
    Column('id', Integer, primary_key=True),
    Column('user_id', None, ForeignKey('user1.id')),
    Column('email', String(60), nullable=False)
  )
# 创建数据表，如果数据表存在，则忽视
metadata.create_all(engine)
# 获取数据库连接
conn = engine.connect()

建表与插入

# -*- coding: utf-8 -*-

from flask import Flask
from sqlalchemy import create_engine, Table, Column, Integer, String, MetaData, ForeignKey
# 连接数据库 
engine = create_engine("mysql+mysqlconnector://user:pass@localhost:3306/test?charset=utf8",encoding="utf-8", echo=True)
# 获取元数据
metadata = MetaData()
# 定义表
user = Table('user1', metadata,
    Column('id', Integer, primary_key=True),
    Column('name', String(20)),
    Column('fullname', String(40)),
  )
 
address = Table('address1', metadata,
    Column('id', Integer, primary_key=True),
    Column('user_id', None, ForeignKey('user1.id')),
    Column('email', String(60), nullable=False)
  )
# 创建数据表，如果数据表存在，则忽视
metadata.create_all(engine)
# 获取数据库连接
conn = engine.connect()

i = user.insert()
u = dict(name='jack',fullname='jackJone')
r = conn.execute(i,**u)
r.inserted_primary_key

i = address.insert()
addresses = [{'user_id':1,'email':'abc@sohu.com'}]
r = conn.execute(i,addresses)

print(r.rowcount)

i = user.insert().values(name='tom',fullname='tom jim')
i.compile()
print(i.compile())
# INSERT INTO user1 (name, fullname) VALUES (:name, :fullname)

print(i.compile().params)
# {'fullname': 'tom jim', 'name': 'tom'}

r = conn.execute(i)
print(r.rowcount)

查询

# -*- coding: utf-8 -*-

from flask import Flask
from sqlalchemy import create_engine, Table,select, Column, Integer, String, MetaData, ForeignKey
# 连接数据库 
engine = create_engine("mysql+mysqlconnector://adspy:adspy1$$3456@localhost:3306/test?charset=utf8",encoding="utf-8", echo=True)
# 获取元数据
metadata = MetaData()
# 定义表
user = Table('user1', metadata,
    Column('id', Integer, primary_key=True),
    Column('name', String(20)),
    Column('fullname', String(40)),
  )
 
address = Table('address1', metadata,
    Column('id', Integer, primary_key=True),
    Column('user_id', None, ForeignKey('user1.id')),
    Column('email', String(60), nullable=False)
  )
# 获取数据库连接
conn = engine.connect()


s = select([user])
print(s)
r = conn.execute(s)
ru = r.fetchall() 
print("查询结果")
print(ru)
r.closed  # 只要 r.fetchall() 之后，就会自动关闭 ResultProxy 对象

# 表的字段
print("表的字段")
print(user.c)

参考：
http://www.jb51.net/article/86018.htm

部分内容：
http://www.liaoxuefeng.com/wiki/001374738125095c955c1e6d8bb493182103fac9270762a000/0014021031294178f993c85204e4d1b81ab032070641ce5000