Postgres

''
Created on Apr 6, 2009
http://www.sqlalchemy.org/docs/05/dbengine.html#supported-dbapis
Sorce code from: http://www.ibm.com/developerworks/aix/library/au-sqlalchemy/
@author:
'''

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Table, Column, Integer, String, MetaData, ForeignKey
from sqlalchemy.orm import sessionmaker
from sqlalchemy import create_engine

Base = declarative_base()

#Table class: path as primary key
class Filesystem(Base):
__tablename__ = 'filesystem'

path = Column(String, primary_key=True)
name = Column(String)

def __init__(self, path,name):
self.path = path
self.name = name

def __repr__(self):
return "" % (self.path,self.name)


#psycopg2 is needed for postgres connection, use synaptic pakage manager
#The actual table will not be created until you run code to create a table,
#and you also need to define what database engine SQLAlchemy will use
#
engine = create_engine('postgres://tulay:tulay@localhost:5432/testdb',echo=True)

Base.metadata.create_all(engine)
#Base.metadata.drop_all(engine)

#At this point, we know enough to create a SQLAlchemy project and control the database from the SQLAlchemy API.
#The only other major item to tackle before getting into a real-life example is the concept of a session.
#The SQLAlchemy "official" documentation describes the session as the handle to the database.
#In practical use, it allows for distinct, transaction-based, connections to occur from a pool of connections
#that SQLAlchemy has waiting. Inside of a session it is typical to add data to the database, perform queries, or delete data.
#
#In order to create a session, perform these sequential steps:

#establish Session type, only need to be done once for all sessions
Session = sessionmaker(bind=engine)
#create record object
create_record = Filesystem("/tmp/foo.txt", "foo.txt")
#make a unique session
session = Session()
#do atomic in session. We are inserting a record here
session.add(create_record)
#commit the transaction
session.commit()

#$pysql testdb

#select * from filesystem;

# path | name
# --------------+---------
# /tmp/foo.txt | foo.txt
#(1 row)