python中的sqlalchemy ORM学习测试代码！

学习及测试过程中参考了一个博客，将源博客中的代码部分拿出来进行了测试一下！

 

#coding=utf-8

"""创建一个连接"""
from sqlalchemy import create_engine

engine = create_engine('mysql://root:123456@localhost/test', echo=True)



"""创建类与表之间的映射"""
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String
  
  
Base = declarative_base()
  
class User(Base):
     __tablename__ = 'users'
   
     id = Column(Integer, primary_key=True)
     name = Column(String)
     fullname = Column(String)
     password = Column(String)
   
     def __init__(self, name, fullname, password):
         self.name = name
         self.fullname = fullname
         self.password = password
   
     def __repr__(self):
        return "<User('%s','%s', '%s')>" % (self.name, self.fullname, self.password)
Base.metadata.create_all(engine) 



"""创建对象实例"""
ed_user = User('ed', 'Ed Jones', 'edspassword')

print ed_user.name
print ed_user.id


"""获取访问数据库的session"""
from sqlalchemy.orm import sessionmaker
Session = sessionmaker()
Session.configure(bind=engine)
session = Session()


"""session的简单操作"""
session.add(ed_user)

our_user = session.query(User).filter_by(name='ed').first()
print our_user

print ed_user is our_user


session.add_all([User('wendy', 'Wendy Williams', 'foobar'),
                 User('mary', 'Mary Contrary', 'xxg527'),
                 User('fred', 'Fred Flinstone', 'blah')])


ed_user.password = 'f8s7ccs'

session.dirty

session.new

session.commit()


"""回滚测试"""
ed_user.name = 'Edwardo'
fake_user = User('fakeuser', 'Invalid', '12345')
session.add(fake_user)
session.query(User).filter(User.name.in_(['Edwardo', 'fakeuser'])).all() 
session.rollback()
print ed_user.name



"""query操作"""
# 基本操作
for instance in session.query(User).order_by(User.id): 
    print instance.name, instance.fullname
# 返回元组
for name, fullname in session.query(User.name, User.fullname): 
    print name, fullname
# 返回对象
for row in session.query(User, User.name).all(): 
    print row.User, row.name
# 对返回的对象字段重名名
for row in session.query(User.name.label('name_label')).all(): 
    print(row.name_label)
# 数据库映射重命名
from sqlalchemy.orm import aliased
user_alias = aliased(User, name='user_alias')

for row in session.query(user_alias, user_alias.name).all(): 
    print row.user_alias

# 返回结果分片
for u in session.query(User).order_by(User.id)[1:3]: 
    print u

# 使用过滤
for name, in session.query(User.name).filter_by(fullname='Ed Jones'): 
    print name
    
for name, in session.query(User.name).filter(User.fullname=='Ed Jones'): 
    print name

for user in session.query(User).filter(User.name=='ed').filter(User.fullname=='Ed Jones'):
    print user

# 分条获取返回记录
query = session.query(User).filter(User.name.like('%ed')).order_by(User.id)

query.all()
query.first()
from sqlalchemy.orm.exc import MultipleResultsFound
try: 
    user = query.one()
except MultipleResultsFound, e:
    print e


# 返回记录计数
session.query(User).filter(User.name.like('%ed')).count()

from sqlalchemy import func
session.query(func.count(User.name), User.name).group_by(User.name).all()

session.query(func.count(User.id)).scalar()

 

参考的源博客地址：http://wangye.org/blog/archives/category/web-development/network-programming-and-database/

基本是对官方文档(http://docs.sqlalchemy.org/en/rel_0_8/orm/tutorial.html#create-an-instance-of-the-mapped-class)的一个完整翻译!