# #!/usr/bin/env python  
# # -*- coding: iso-8859-1 -*-  
# # This program is free software; you can redistribute it and/or modify  
# #it under the terms of the GNU General Public License as published by  
# #the Free Software Foundation; version 2 of the License.  
# # This program is distributed in the hope that it will be useful,  
# #but WITHOUT ANY WARRANTY; without even the implied warranty of  
# #MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the  
# #GNU General Public License for more details.  
# #  
# #author: Marlon Petry  
# #Date: 2008/06/5  
# #Function: Mapeamento ORM Primeria Parte  
# http://petryx.blogrs.com.br/

from sqlalchemy import *
from sqlalchemy.orm import *

class Cliente(object):
	
	def __init__(self,nome,rg,cpf):
		self.nome = nome
		self.rg = rg
		self.cpf = cpf
	
	def __repr__(self):
		return "<Cliente('%s','%s', '%s')>" % (self.nome,self.rg,self.cpf)	
	
	
db = create_engine('sqlite:///:memory:', echo=False)

metadata = MetaData()	
	
	
#Cria a tabela Cliente

ClienteTable = Table('cliente', metadata,
	Column('id', Integer, primary_key=True),
	Column('nome', String(40)),
	Column('rg', String(10)),
	Column('cpf', String(12))
)

metadata.create_all(db) 

#mapeando
mapper(Cliente,ClienteTable)

cliente1 = Cliente('Marlon','1012123302','12345678901')
cliente2 = Cliente('Paulo','3414423302','12345678901')
cliente3 = Cliente('Joao','8765123302','12345678901')
cliente4 = Cliente('Jose','3212123302','12345678901')
cliente5 = Cliente('Mauro','3456123302','12345678901')

#criando a sessao

Session = sessionmaker(bind=db, autoflush=True, transactional=True)

session = Session()

session.save(cliente1)
session.save(cliente2)
session.save(cliente3)
session.save(cliente4)
session.save(cliente5)

session.commit()


#consultando
	
query = session.query(Cliente)

print 'Lista todos e mostra o nome\n'
for cliente in session.query(Cliente):
	print cliente.nome
	
	

print '\nLIMIT e OFFSET\n'
	
for c in session.query(Cliente)[1:3]: 
	print c

print '\nFiltrando por Nome\n'
	
for cliente in session.query(Cliente).filter_by(nome='Marlon'):
	print cliente
	
print '\n Usando like\n'
	
query = session.query(Cliente).filter(Cliente.nome.like('%Ma%'))
query.all()