[orm-devel] Re: ORM

[Mike Watkins]

I have a work around that uses pg_types table in Postgres, so you can use 
psycopg.

Attached...

Mike

#!/usr/bin/python
# generate_orm.py
# (c) 2002 Mike Watkins
# Anyone can use this for whatever they desire.
# Generates (mostly) a complete class definition from the SQL tables. 
one2one, one2many etc relations
# have to be hand inserted into the output of this. Requires that one row 
at least be in the table.
# Example of a finished class:
#
# class subject(dbclass):
#     columns = { "id": serial(),   # generate_orm will simply call this an 
integer() unless its labelled 'id'
#                 "name": varchar(),
#                 "detail": varchar() ,
#                 "category_id": one2one(category), # generate_orm will 
simply call this an integer()
#                 "create_date": timestamp(),
#                 "modified_date": timestamp() }
#




import sys
# psycopg doesn't return the column types in cursor.description, pyPgSQL does
# get it from http://pypgsql.sourceforge.net/
# from pyPgSQL import PgSQL
# 2002-11-25 switched this to use psycopg, drawing the type info from pg_types

import psycopg
def output(table):
     pg_types = {}
     cursor.execute('select oid, typname from pg_type')
     result = cursor.fetchall()
     for r in result:
         pg_types[r[0]] = r[1]

     colname = colclass = ''
     classcomment = '# column(s): %s are candidates for one2one, one2many, 
etc, update manually'
     candidates = []

     # get a row, colnames are in cursor.description
     select = "SELECT * FROM %s" % (table,)
     cursor.execute(select)
     row = cursor.fetchone()

     # print the basic class definition
     print """class %s(dbclass):
     columns = { """ % (table.title(),)

     first = True
     for column in cursor.description:
         if first:
             first = False
         else:
             print ","

         colname = column[0]
         colclass = pg_types[column[1]]

         if colname.find('_id') > 0:
             candidates.append(colname)

         # this mapping of classes and types to ORM objects (defined in 
relationships.py) is by no
         # means complete. I just don't use many different data types.
         if colname == 'id':
             colclass = 'serial'
         if colclass == 'bool':
             colclass = 'boolean'
         if colclass == 'int4':
             colclass = 'integer'

         print """       "%s" :%s%s()""" % (colname, (25 - len(colname) ) * 
' ', colclass),
     print "\n       }",
     if candidates:
         print classcomment % candidates,
     print "\n"

if __name__ == "__main__":
     # only interested in one table? Specify it here or leave as None
     table = None

     # feel free to hard code your DSN in here
     DSN = 'YOUR OWN DSN HERE'
     print "# ORM unfinished classes automagically generated by 
orm_generate.py"
     print "#    Cut and paste these into your code and update as required."
     print "#    Opening connection using DSN: %s\n\n" % (DSN,)

     db = psycopg.connect(DSN)
     cursor = db.cursor()

     tables = []
     if not table:
         cursor.execute("""select tablename from pg_tables where tablename 
not like 'pg_%' order by tablename""")
         result = cursor.fetchall()
         for r in result:
             tables.append(r[0])
     else:
         tables.append(table)

     for table in tables:
         output(table)





At 04:21 PM 12/7/2002 +0000, you wrote:
>Had a quick look. Does anyone know why psycopg doesn't import column
>types in cursor.description? I'll ask them on their list as well.
>
>I haven't tested this, as I don't have pyPgSQL, but if the following
>lines are modified it would be easier to spot what requires attention
>for relationships in the generated code.