sqlalchemy 使用

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column,Integer,String
from sqlalchemy import Sequence
from sqlalchemy.orm import sessionmaker
Base=declarative_base()
from sqlalchemy import create_engine
engine=create_engine('sqlite:///:memory:',echo=True)
class User(Base):
    __tablename__='users'
    id=Column(Integer,Sequence('user_id_seq'),primary_key=True)
    name=Column(String(50))
    fullname=Column(String(50))
    password=Column(String(12))
    def __init__(self,name,fullname,password):
        self.name=name
        self.fullname=fullname
        self.password=password

    def __repr__(self):
        return '' %(self.name,self.fullname,self.password)
Base.metadata.create_all(engine)
Session=sessionmaker(bind=engine)
session=Session()
#Add on user
ed_user=User('ed','Ed Jones','edpassword')
session.add(ed_user)
#Retrive saved ed_user
our_user=session.query(User).filter_by(name='ed').first()
print 'our_user is:',our_user
print 'our_user id is:',our_user.id
print 'our_user is ed_user',our_user==ed_user

#Add multiple object
session.add_all(
    [
        User('Wendy','Wendy Williams','foobar'),
        User('Marry','Marry Contrary','xxg527'),
        User('Fred','Fred Flinstone','blah')
    ]
)
#Detective the dirty data
ed_user.password='f8x902'
print 'Dirty data',session.dirty
#Detective the new data
print 'New data',session.new
#Commit data
session.commit()
#========Querying===============
#Querying user instance
for instance in session.query(User).order_by(User.id):
    print instance.name,instance.fullname
#Querying by ORM-instrument
for name,fullname in session.query(User.name,User.fullname):
    print name,fullname
#Querying as a Python object
for row in session.query(User,User.name).all():
    print row.User,row.name

#Querying labeled
for row in session.query(User.name.label('name_label')).all():
    print (row.name_label)
#Querying with aliased
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
#Querying with limit and offset
for u in session.query(User).order_by(User.id)[1:3]:
    print u
#Qyering with filter by
for name, in session.query(User.name).filter_by(fullname='Ed Jones'):
    print name
#Querying with filter
for name, in session.query(User.name).filter(User.fullname=='Ed Jones'):
    print name
#Querying with fully generative
for name, in session.query(User.name).filter(User.fullname=='Ed Jones').filter(User.name=='ed'):
    print name