gistfile1.py

from sqlalchemy import create_engine, Table, Column, Integer, String, DateTime,MetaData
from sqlalchemy.sql.expression import text
from sqlalchemy.orm import mapper, sessionmaker
import logging
logging.basicConfig(filename = "sqlalchemypy3.log")
logging.getLogger('sqlalchemy.engine').setLevel(logging.INFO)

class Test(object):
        pass

engine = create_engine("postgresql+pg8000://postgres@localhost:15432/pg8000")

Session = sessionmaker( bind = engine )
session = Session()

c = engine.connect()
metadata = MetaData()
test_table = Table("test", metadata,
                Column("id", Integer, primary_key = True),
                Column("name", String(100)),
                Column("when", DateTime, server_default=text("NOW()")))

mapper( Test, test_table)

metadata.drop_all(engine)
metadata.create_all(engine)

words = "first look at sqlalchemy from python3 using pg8000 module"
aWords = ["('{0}')".format(x) for x in words.split()]

sql = "insert into test(name) values {0} returning id,name".format(",".join(aWords))
[ print(row["id"], row["name"]) for row in c.execute(sql) ]

print("\nNow the tuples ordered by name ( the plain sql way)\n")
sql = "select id,name from test order by name"
for row in c.execute(sql):
        print(row["id"], row["name"])

metadata.bind = engine
morewords = "more words to include in the table in case you want to see lots of tuples"
aWords = ["('{0}')".format(x) for x in morewords.split()]
sql = "insert into test(name) values {0} returning id,name".format(",".join(aWords))
[ print(row["id"], row["name"]) for row in c.execute(sql) ]

print("\nNow the tuples ordered by name\n")
for row in test_table.select(order_by = test_table.c.name).execute():
        print( row[test_table.c.id], row[test_table.c.name], row[test_table.c.when])

print("\nNow listing table using orm's session\n")
for row in session.query(Test).order_by(Test.id).all():
        print( row.id, row.name, row.when )
c.close()