SQLAlchemy Notes

Summarized From 
http://www.sqlalchemy.org/docs/core/tutorial.html


This covers the basics of the Non-orm usage of SQLAlchemy

# Define connection engine
from sqlalchemy import create_engine
engine = create_engine('sqlite:///:memory:', echo=True)

# Create some database tables 
from sqlalchemy import Table, Column, Integer, String, MetaData, ForeignKey
metadata = MetaData()
users = Table('users', metadata,
    Column('id', Integer, primary_key=True),
    Column('name', String),
    Column('fullname', String),
)

addresses = Table('addresses', metadata,
  Column('id', Integer, primary_key=True),
  Column('user_id', None, ForeignKey('users.id')),
  Column('email_address', String, nullable=False)
)

metadata.create_all(engine)

# Insert into the tables
ins = users.insert()
ins.values(name='jack', fullname='Jack the Pumpkin king')

conn = engine.connect()
result = conn.execute(ins)
result.inserted_primary_key

# Multiple insert into the tables
conn.execute(addresses.insert(), [
   {'user_id': 1, 'email_address' : 'jack@yahoo.com'},
   {'user_id': 1, 'email_address' : 'jack@msn.com'},
   {'user_id': 2, 'email_address' : 'www@www.org'},
   {'user_id': 2, 'email_address' : 'wendy@aol.com'},
])

result = engine.execute(users.insert(), name='fred', fullname="Fred Flintstone")

# Select from the tables
from sqlalchemy.sql import select

# Select all fields in user table
s = select([users])
result = conn.execute(s)
list(result)

# Select name and fullname columns from user table
s = select([users.c.name, users.c.fullname])

# Join two user and address tables
#  Cartesian product 
list(conn.execute(select([users, addresses])))
# Join on id columns
list(conn.execute(select([users, addresses], users.c.id==addresses.c.user_id)))

# create conditions for the where clause
from sqlalchemy.sql import and_, or_, not_
print and_(users.c.name.like('j%'), users.c.id==addresses.c.user_id, )


s = select([(users.c.fullname + "--" + addresses.c.email_address).label('title')])
print conn.execute(s).fetchall()

print users.join(addresses)
print users.join(addresses, addresses.c.email_address.like(users.c.name + '%'))


s = select([users.c.fullname], from_obj=[
   users.join(addresses, addresses.c.email_address.like(users.c.name + '%'))
   ])
print conn.execute(s).fetchall()

# Delete records based on a condition
conn.execute(users.delete().where(users.c.name > 'm'))

Postgresql Sequences

Create a new sequence:
CREATE SEQUENCE mytable_myid_seq;

Add the sequence as the incrementor for a table column:
ALTER TABLE mytable
ALTER COLUMN myid
SET DEFAULT NEXTVAL('mytable_myid_seq');

Update existing rows in table with the sequence:
UPDATE mytable
SET myid = NEXTVAL('mytable_myid_seq');

Display the sequence information:
Select * from mytable_myid_seq;

Update a sequence to be one greater than a table's id column:
select setval('mytable_myid_seq', (select max(id) + 1 from mytable));
http://railspikes.com/2009/3/6/duplicate-key-violates-unique-constraint (thanks Louis)
http://pointbeing.net/weblog/2008/03/mysql-versus-postgresql-adding-an-auto-increment-column-to-a-table.html