you're welcome. since I'm dealing with tables without primarykeys in legacy is there some web2py "standard" behaviour for this case? do you think that returning None instead of an id upon a table insert is appropriate? is it going to break something else?
On 5 Feb, 15:42, mdipierro <[email protected]> wrote: > 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.
