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