since in the same schema there are many to many tables with no primary
key,
a further improvement to the one described above can permitt the DAL
to write also this table
with the side effect of returning None on an insert instead of the
inserted record id (since there is no one)
this write to all tables:
def lastrowid(self,tablename):
self.execute("""SELECT column_default from
information_schema.columns where table_name='%s'; """%tablename)
column_default=self.cursor.fetchone()
if column_default[0]:#if there is no sequence in the table
first column
get_currval_string ='select ' +
column_default[0].replace('nextval', 'currval')
self.execute(get_currval_string)
return int(self.cursor.fetchone()[0])
else:
return None #or the appropriate value
On 5 Feb, 12:22, kralin <[email protected]> wrote:
> Hi,
> In the last days I've been trying to use the web2py DAL to access a
> public DB shema widely used in bioinformatics (BioSQL,www.biosql.org).
>
> this schema does not follow web2py constraints, however most of the
> tables have a primarykey that can be set as the 'id' fields.
>
> howver web2py DAL as it is is only able to read data, but fails to
> write because it assumes that in the postgresql db, each sequence for
> the 'id' field is named
>
> ''%s_id_Seq''%tablename
>
> while this is the default postgresql behaviour, and works most of the
> time, it fails if the sequence has a different name in this case:
>
> ''%s_pk_Seq''%tablename
>
> so after a lot o googleing I end up to made the following modification
> to the PostgreSQLAdapter class in gluon.dal
>
> orginal:
>
> def lastrowid(self,tablename):
> self.execute("select currval('%s_id_Seq')" % tablename)
> return int(self.cursor.fetchone()[0])
>
> improved:
>
> def lastrowid(self,tablename):
> self.execute("""select column_default from
> information_schema.columns where table_name='%s'; """%tablename)
> get_currval_string ='select ' + self.cursor.fetchone()
> [0].replace('nextval', 'currval')
> self.execute(get_currval_string)
> return int(self.cursor.fetchone()[0])
>
> this way, the sequence name is dynamically retrieved from the db, and
> should work on any situation.
>
> Hope this help!
>
> Andrea
--
You received this message because you are subscribed to the Google Groups
"web2py-users" group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/web2py?hl=en.
