gistfile1.py

#An example of using sqlalchemy ( svn ) from python 3.1 using pg8000

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

engine = create_engine("postgresql+pg8000://postgres@localhost:15432/pg8000")
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()")))

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])
c.close()