python模块整理28SQLAlchemy模块

参考：http://hi.baidu.com/uybdifnwhtbhqsd/item/72177cb0ef74e59618469771
SQLAlchemy是python里面最好的ORM框架
一、安装
easy_install SQLAlchemy
二、使用-模型表相关
建表，建class，再mapper
1、连接数据库
sqlite:
engine=create_engine('sqlite:///%s' % os.path.join(os.getcwd(),'inventory.db'))
mysql:
engine=create_engine('mysql://user:pass@localhost/test',encoding='utf8',echo=True)
#metadata=BoundMetaData(db)
metadata=MetaData()
2、定义表
os_table=Table('inventory_operatingsystem',metadata,
Column('id',Integer,primary_key=True),
Column('name',VARCHAR(50)),
Column('description',Text()),
)
3、创建表
users.create()
4、定义class
class OperatingSystem(object):
def __init__(self,name,description):
self.name = name
self.description = description

def __repr__(self):
return "<OperatingSystem('%s','%s')>" % (self.name,self.description)

__str__ = __repr__
4、关联table和class
mapper(OperatingSystem,os_table)
#Session = sessionmaker(bind=engine,autoflush=True,transactional=True)
Session = sessionmaker(bind=engine,autoflush=True)
session = Session()

6、完整
#!/bin/env python
# -*- coding: utf-8 -*-
import os
from sqlalchemy import create_engine
from sqlalchemy import Table,Column,Integer,Text,VARCHAR,MetaData
from sqlalchemy.orm import mapper
from sqlalchemy.orm import sessionmaker

engine = create_engine('mysql://root:pass@localhost/djangodb ',encoding='utf8',echo=True)

#metadata = MetaData()
metadata = MetaData(engine)

os_table = Table('inventory_operatingsystem',metadata,
Column('id',Integer,primary_key=True),
Column('name',VARCHAR(50)),
Column('description',Text()),
)

os_table.create()

class OperatingSystem(object):
def __init__(self,name,description):
self.name = name
self.description = description

def __repr__(self):
return "<OperatingSystem('%s','%s')>" % (self.name,self.description)

__str__ = __repr__

mapper(OperatingSystem,os_table)
#Session = sessionmaker(bind=engine,autoflush=True,transactional=True)
Session = sessionmaker(bind=engine,autoflush=True)
session = Session()

运行报错
sqlalchemy.exc.UnboundExecutionError: The Table 'inventory_operatingsystem' is not bound to an Engine or Connection. Execution can not proceed without a database to execute against. Either execute with an explicit connection or assign this Table's .metadata.bind to enable implicit execution.
原因：'inventory_operatingsystem' is not bound to an Engine or Connection.
metadata=MetaData(engine)

6、简化
将定义表,定义class,mapper简化
class OperatingSystem(object):
__tablename__ = 'inventory_operatingsystem'
id = Column(Integer, primary_key = True)
name = Column(VARCHAR(50))
description = Column(Text())

def __repr__(self):
return "<OperatingSystem('%s','%s')>" % (name,description)

__str__ = __repr__

数据引擎和session简化
Session = sessionmaker(bind=create_engine('mysql://root:pass@localhost/djangodb ',encoding='utf8',echo=True),autoflush=True)
session = Session()

简化后 不知道怎么创建表？??

三、使用-插入，查询，修改
1、插入
把上面的代码os_table.create()注释
table.insert().values(name='foo')
i = os_table.insert()
i.execute(name='test1',description='desc1')
插入多个，参数多个字典即可
i.execute({'name': 'test2', 'description': 'desc2'},{'name': 'test3', 'description': 'desc3'})
2、修改
table.update().where(table.c.id==7).values(name='foo')
u = os_table.update().where(os_table.c.id==1)
u.execute(name='test)
3、删除
table.delete().where(table.c.id==7)
d = os_table.delete().where(os_table.c.id==3)
d.execute()
4、选择 查询
s = os_table.select()
rs=s.execute()
for i in rs:
print i
(1L, 'test', 'desc1')
(2L, 'test2', 'desc2')
(4L, 'test3', 'desc3')
(5L, 'test4', 'desc4')
一个select语句对象调用execute()将会返回一个结果集，
它有fetchone()和fetchall()两个方法。正如你期望的，fetchone()返回一个记录行，
fetchall()返回一个记录行列表。
s = os_table.select()
rs=s.execute()
print rs.fetchone()
print rs.fetchall()
(1L, 'test', 'desc1')
[(2L, 'test2', 'desc2'), (4L, 'test3', 'desc3'), (5L, 'test4', 'desc4')]

遍历打印
s = os_table.select()
rs=s.execute()
for i in rs:
print 'Id:', i[0]
print 'Name:', i[1]
print 'DESC:', i[2]

四、SQLAlchemy数据建模过程的改进
http://www.2cto.com/kf/201108/101549.html