#!/usr/bin/env python

import os
from sqlalchemy import *

os.remove( './tutorial.db' )
db = create_engine( 'sqlite:///tutorial.db' )

metadata = BoundMetaData(db)
metadata.engine.echo = True

ut = Table( 'users', metadata,
            Column( 'uid', Integer, primary_key=True ),
            Column( 'uname', String(40) ),
            Column( 'passwd', String(20) )   )

ut.create()

xt = Table( 'users', metadata, autoload=True )

print list(xt.columns)

i = xt.insert()
print i

i.execute( uname='Monkey', passwd='new' )

i.execute( [{'uname':name} for name in 'a b c'.split()] )

s = ut.select()
print s

r = s.execute()
print r.fetchone()
print r.fetchall()

for row in select([ut.c.uid,ut.c.uname]).execute():
  print row
  print row.keys()

et = Table('email_addresses', metadata,
        Column('address_id', Integer, primary_key=True),
        Column('email_address', String(100), nullable=False),
        Column('uid', Integer, ForeignKey('users.uid')))
et.create()

et.insert().execute( {'email_address':'c@d', 'uid':2},{'email_address':'a@b','uid':1} )

for row in ut.join(et).select( order_by= ut.c.uid ).execute():
  print row

print join(ut,et, and_(ut.c.uid == et.c.uid, ut.c.passwd != et.c.email_address) )


from sqlalchemy.orm import *

class User(object):
  def __repr__(self):
    return "%s(%r,%r)" % (self.__class__.__name__, self.uname, self.passwd)

class Address(object):
  def __init__(self, email_address):
    self.email_address = email_address

  def __repr__(self):
    return "%s(%r)" % (self.__class__.__name__, self.email_address) 


mapper(User,ut)

u = User()
print u.uname

session = create_session()
query = session.query(User)
print query.select_by(uname='Monkey')
print query.select( User.c.uid==3 )
print query.get(2)
print query.get_by( uname='c' )
print query.selectfirst(User.c.passwd==None)
print query.count()

test = User()
test.uname = 'Test'
test.passwd = 'tpw'
session.save( test )

session.delete( query.selectfirst(User.c.passwd==None) )

print test in session

session.flush()

clear_mappers()
mapper(Address,et)
mapper( User, ut, properties={'addresses':relation(Address)} )

session.clear()
uu = query.selectfirst()
uu.addresses.append( Address('c@cc') )
session.flush()

tr = session.create_transaction()
try:
  (m,b,c) = session.query(User).select( User.c.uname.in_( 'Monkey','b','c' ), order_by=User.c.uname )
  del m.addresses[1]
  b.addresses.append( Address('test@app') )
  session.flush()
  print 'FLUSH!'
  f = User()
  f.uname = 'f'
  f.addresses.append( Address('m@ca' ) )
  session.save(f)
  tr.commit()
except:
  tr.rollback()
  raise