Hi all,
I'm using PostgreSQL with schemas w/ web2py 1.97. The patch shown here from
2 years ago made that work.
I just downloaded v1.99.7 and noticed that this patch is not implemented in
/gluon/dal.py PostgreSQLAdapter.lastrowid().
So I'm wondering what the outcome of this discussion was -- was some other
change made? Or will this be added in the future?
Thanks,
Bruce
On Friday, February 5, 2010 4:58:56 AM UTC-7, kralin 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
>
>
On Friday, February 5, 2010 4:58:56 AM UTC-7, kralin 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
>
>
On Friday, February 5, 2010 4:58:56 AM UTC-7, kralin 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
>
>
On Friday, February 5, 2010 4:58:56 AM UTC-7, kralin 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
>
>
