#! /usr/bin/python
import sqlalchemy as sa
sa.__version__  # '0.9.4' for me

# Sample DB connection string
# engine = create_engine('postgresql://scott:tiger@localhost/mydatabase')
# engine = create_engine('postgresql+psycopg2://scott:tiger@localhost/mydatabase')
# engine = create_engine('postgresql+pg8000://scott:tiger@localhost/mydatabase')
# engine = create_engine('mysql://scott:tiger@localhost/foo')
# engine = create_engine('mysql+mysqldb://scott:tiger@localhost/foo')
# engine = create_engine('mysql+mysqlconnector://scott:tiger@localhost/foo')
# engine = create_engine('mysql+oursql://scott:tiger@localhost/foo')


##################################################
# 'traditional' database definition / manipulation by SQL
import sqlalchemy as sa
engine = sa.create_engine('sqlite:///sqlite.db', echo=True)
conn = engine.connect()

# you got OperationalError if SQL is incorrect or table exists
result = conn.execute("""
    CREATE TABLE user (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        first_name TEXT,
        last_name TEXT,
        job TEXT
    );
""")

# result.rowcount will be 1
result = conn.execute("""
    INSERT INTO user (first_name, last_name, job) values ('Kenial', 'Lee', 'SE');
""")
# due to stupid sqlite3_excute API ... it can just run one statement per one call
result = conn.execute("""
    INSERT INTO user (first_name, last_name, job) values ('Jon', 'Skeet', 'Author');
""")

result = conn.execute("""
    SELECT * FROM user;
""")

print result.rowcount   # shoule be 2, but -1. due to stupid sqlite3 API ...

# Iterate them all
row = result.fetchone()
while row:
    print row
    row = result.fetchone()
# Otherwise, you got [(1, u'Kenial', u'Lee', u'SE'), (2, u'Jon', u'Skeet', u'Author')]
print result.fetchall()

# result.rowcount will be 2
result = conn.execute("""
    DELETE FROM user;
""")
conn.close()

######################################
# Semi - statement mapping ways
import sqlalchemy as sa
engine = sa.create_engine('sqlite:///sqlite.db', echo=True)
metadata = sa.MetaData(bind=engine)
users = sa.Table(
    'user', 
    metadata,
    sa.Column('id', sa.Integer, primary_key=True, autoincrement=True),
    sa.Column('first_name', sa.String),
    sa.Column('last_name', sa.String),
    sa.Column('job', sa.String),
)
metadata.create_all()     # create table - it's safe to run many times

# insert(), select() methods return Insert, Class object, which represents
# SQL statement template and bahaviors like Command, Template class of 
# traditional DB library. 
users.insert(values={
    "first_name": "Kenial",
    "last_name": "Lee",
    "job": "SE",
}).execute()
result = users.select().execute()

# you got [(1, u'Kenial', u'Lee', u'SE')]
print result.fetchall()

######################################
# ORM ways here
import sqlalchemy as sa
import sqlalchemy.orm as orm
from sqlalchemy.ext.declarative import declarative_base

engine = sa.create_engine('sqlite:///sqlite.db', echo=True)
metadata = sa.MetaData(bind=engine)
db_session = orm.scoped_session(orm.sessionmaker(
    autocommit=False,
    autoflush=False,
    bind=engine
))

class User(object):
    id = sa.Column(sa.Integer, primary_key=True)
    first_name = sa.Column(sa.String)
    last_name = sa.Column(sa.String)
    job = sa.Column(sa.String)
    #
    def __init__(self, first_name=None, last_name=None, job=None):
        self.first_name = first_name
        self.last_name = last_name
        self.job = job
    #
    def __repr__(self):
        return '<User %r>' % (self.name)
    #
    query = sa.Table(
        'user', 
        metadata,
        sa.Column('id', sa.Integer, primary_key=True, autoincrement=True),
        sa.Column('first_name', sa.String),
        sa.Column('last_name', sa.String),
        sa.Column('job', sa.String),
    )


def init_db():
    metadata.create_all(bind=engine)


# set up db and schema
init_db()
orm.mapper(User, User.query)

u = User("Kenial", "Lee", "SE")
db_session.add(User("Kenial", "Lee", "SE"))
db_session.add_all([
    User("Kenial", "Lee", "SE"),
    User("Jon", "Skeet", "Author"),
])
db_session.commit()

result = User.query.select(User.id == 1).execute()
# you got [(1, u'Kenial', u'Lee', u'SE')]
print result.fetchall()