Thank you Andrea! these will definitively go in. Massimo
On Feb 5, 5:58 am, kralin <[email protected]> wrote: > 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.
