sqlalchemy test

README.md

curl -ks https://gist.github.com/nicerobot/5259643/raw/run.sh | bash -

Python code swiped from this stackoverflow.

run.sh

#!/bin/bash
[ -r sq.sqlite ] || {
	curl -ks https://gist.github.com/nicerobot/5259643/raw/sq.sql | sqlite3 sq.sqlite || exit ${LINENO}
}
[ -r sq.py ] || {
	curl -ksO https://gist.github.com/nicerobot/5259643/raw/sq.py || exit ${LINENO}
}
python sq.py sq.sqlite

sq.py

import sys, getopt
from sqlalchemy import *
from sqlalchemy.orm import create_session
from sqlalchemy.ext.declarative import declarative_base

class Usage(Exception):
	def __init__(self, msg):
		self.msg = msg

def dbmain(db='sq.sqlite'):
	#Create and engine and get the metadata
	Base = declarative_base()
	engine = create_engine('sqlite:///%s' %db, echo=True)

	metadata = MetaData(bind=engine)

	#Reflect each database table we need to use, using metadata
	class Tests(Base):
		__table__ = Table('another', metadata, autoload=True)
		def __repr__(self):
			return "%s\n%s\t%s\n" %(self.__table__,self.a,self.b)

	class Users(Base):
	  __table__ = Table('user', metadata, autoload=True)
	  def __repr__(self):
	  	return "%s\n%s\t%s\t%s\t%s\t%s\n" %(self.__table__,self.a,self.b,self.c,self.d,self.e)

	#Create a session to use the tables    
	session = create_session(bind=engine)

	#Here I will just query some data using my foreign key relation,  as you would
	#normally do if you had created a declarative data mode.
	#Note that not all test records have an author so I need to accomodate for Null records
	userlist = session.query(Users).all()

	for user in userlist:
		print user
		for a in session.query(Tests).filter_by(b=user.a):
			print a

def main(argv=None):
	if argv is None:
		argv = sys.argv
	try:
		try:
			opts, args = getopt.getopt(argv[1:], "h", ["help"])
		except getopt.error, msg:
			raise Usage(msg)
	except Usage, err:
		print >>sys.stderr, err.msg
		print >>sys.stderr, "for help use --help"
		return 2

	dbmain(argv[1])

if __name__ == "__main__":
	sys.exit(main())

sq.sql

CREATE TABLE "user" (
	"a" INTEGER PRIMARY KEY AUTOINCREMENT,
	"b" REAL,
	"c" TEXT,
	"d" BLOB,
	"e" INTEGER
);
INSERT INTO "user" VALUES (1, 1.2, 'okc', 'okd', 'oke');

CREATE INDEX "e" ON "user" ("e");

CREATE TABLE "another" (
	"a" INTEGER PRIMARY KEY,
	"b" INTEGER REFERENCES "user"("a")
);
INSERT INTO "another" VALUES (1, 1);
INSERT INTO "another" VALUES (2, 1);
INSERT INTO "another" VALUES (3, 1);
INSERT INTO "another" VALUES (4, 1);