[orm-devel] Re: ORM
Mike Watkins
orm-devel@mailman.tux4web.de
Sat, 07 Dec 2002 08:15:01 -0800
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.