Hi Karra, > Further, in the actual use case, I cannot assume that the records will be > inserted in the serial order of day. A patient can call and make an > appointment for a later date, and after that a patient might walk in for > the same day. Hm... > > -Karra
Since you want more flexible system, I recommend that you add more columns-- apt date, reservation date, actual visit date and *time* of consultation. To maintain our own sanity when records are accidentally updated, this will help to trace the problem. Not sure if you want the 'consultation-day-id' as a handy parameter for reference *after* the consultation or even before the consultation. If it is for before-use, then you can generate the 'priority-of-the-day-id' by writing a query which depends on the apt-date. If it is for later-use, you can require that the consultation-time be unique, (data-entry operator can probably re-enter the record to adjust the minute or even seconds to make the record unique). You can derive the consultation-id from the consultation time either by post bulk processing or generating it on the fly. I have not used SQLAlchemy, but almost all database systems require you that there is only one built-in auto-increment column per table. If you want more, you will have to simulate it. One alternative is to use just another 'auxilary-table' just for the purpose of generating auto-increment value which you can use it to explicitly insert on to other table-- not recommended, but there is a way. In a nutshell, it seems to me that the real information you need in there is the actual-consultation-time to be precise. Ignoring that and adding numerous other data and trying to keep them consistent may not help. Just some initial thoughts and impressions from me. Regards, -thava On Wed, Feb 6, 2013 at 11:50 AM, <bangpypers-requ...@python.org> wrote: > Send BangPypers mailing list submissions to > bangpypers@python.org > > To subscribe or unsubscribe via the World Wide Web, visit > http://mail.python.org/mailman/listinfo/bangpypers > or, via email, send a message with subject or body 'help' to > bangpypers-requ...@python.org > > You can reach the person managing the list at > bangpypers-ow...@python.org > > When replying, please edit your Subject line so it is more specific > than "Re: Contents of BangPypers digest..." > > > Today's Topics: > > 1. SQLAlchemy and 'non-trivial' default values for a column > (Sriram Karra) > 2. Re: SQLAlchemy and 'non-trivial' default values for a column > (Vinod Kumar Narasimhaiah) > 3. Re: SQLAlchemy and 'non-trivial' default values for a column > (Dhruv Baldawa) > 4. Re: SQLAlchemy and 'non-trivial' default values for a column > (Sriram Karra) > 5. Re: SQLAlchemy and 'non-trivial' default values for a column > (Sriram Karra) > > > ---------------------------------------------------------------------- > > Message: 1 > Date: Tue, 5 Feb 2013 21:58:36 +0530 > From: Sriram Karra <karra....@gmail.com> > To: Bangalore Python Users Group - India <BangPypers@python.org> > Subject: [BangPypers] SQLAlchemy and 'non-trivial' default values for > a column > Message-ID: > <CAFkt3UNFaEK+0Tjh=jifu3s9nc8zjzmw7yyu6vuzexf1v9p...@mail.gmail.com> > Content-Type: text/plain; charset=ISO-8859-1 > > I have a Declarative table defined as follows: > > class Consultation(Base): > __tablename__ = 'consultation' > > id = Column(Integer, primary_key=True) > patient_id = Column(Integer, ForeignKey('patient.id')) > doctor_id = Column(Integer, ForeignKey('doctor.id')) > date = Column(Date(), default=MyT.today()) > > Each row in this table represents a single consultation instance of a > patient seeing a doctor on a given day. > > I would like an additional attribute called "cid" that should be an > auto-incrementing value representing how many-th consultation it was in > that day. Basically it is an auto-incrementing counter, which gets reset to > 0 at the start of a day (hence not unique, whereas the id will be unique). > No row is ever deleted. > > How do I do achieve this with the least amount of additional database > space? It is trivial to have another table with one column for date and > another column for the total consultations thus far. > > Any help? > > -Karra > > P.S. This is for PRS - an open source patient record system for small > clinics I am developing, and available at: https://github.com/skarra/PRS > > > ------------------------------ > > Message: 2 > Date: Wed, 6 Feb 2013 11:15:39 +0530 > From: Vinod Kumar Narasimhaiah <vinod.narasimha...@gmail.com> > To: Bangalore Python Users Group - India <bangpypers@python.org> > Subject: Re: [BangPypers] SQLAlchemy and 'non-trivial' default values > for a column > Message-ID: > <capaifdfrvt5uk4fydw-xjn39xx3pf7bjfcf1fc-zwdptd3q...@mail.gmail.com> > Content-Type: text/plain; charset=ISO-8859-1 > > I am not a python programmer, but how about this logic? > > add new field called "consultation_count" to the same table > > before every insert to the table: > > Check if the date field on the last record (you might want to add a > timestamp field to get the last record easily) = today's date > > If yes, then it's the same day- > take the consultation count from the last record, increment it by one > and create the new record. > If No, then it's a start of the new day- > set the counter to 1 and create the new record. > > > > On Tue, Feb 5, 2013 at 9:58 PM, Sriram Karra <karra....@gmail.com> wrote: > >> I have a Declarative table defined as follows: >> >> class Consultation(Base): >> __tablename__ = 'consultation' >> >> id = Column(Integer, primary_key=True) >> patient_id = Column(Integer, ForeignKey('patient.id')) >> doctor_id = Column(Integer, ForeignKey('doctor.id')) >> date = Column(Date(), default=MyT.today()) >> >> Each row in this table represents a single consultation instance of a >> patient seeing a doctor on a given day. >> >> I would like an additional attribute called "cid" that should be an >> auto-incrementing value representing how many-th consultation it was in >> that day. Basically it is an auto-incrementing counter, which gets reset to >> 0 at the start of a day (hence not unique, whereas the id will be unique). >> No row is ever deleted. >> >> How do I do achieve this with the least amount of additional database >> space? It is trivial to have another table with one column for date and >> another column for the total consultations thus far. >> >> Any help? >> >> -Karra >> >> P.S. This is for PRS - an open source patient record system for small >> clinics I am developing, and available at: https://github.com/skarra/PRS >> _______________________________________________ >> BangPypers mailing list >> BangPypers@python.org >> http://mail.python.org/mailman/listinfo/bangpypers >> > > > ------------------------------ > > Message: 3 > Date: Wed, 6 Feb 2013 11:32:45 +0530 > From: Dhruv Baldawa <dhruvbald...@gmail.com> > To: Bangalore Python Users Group - India <bangpypers@python.org> > Subject: Re: [BangPypers] SQLAlchemy and 'non-trivial' default values > for a column > Message-ID: > <CAC5Hnjgga6bq1n63yR3AUx4mOLTTJJvz=9--2=romjkwlnf...@mail.gmail.com> > Content-Type: text/plain; charset=UTF-8 > > I would do a: > > class Consultation(Base): > __tablename__ = 'consultation' > > id = Column(Integer, primary_key=True) > patient_id = Column(Integer, ForeignKey('patient.id')) > doctor_id = Column(Integer, ForeignKey('doctor.id')) > date = Column(Date(), default=MyT.today()) > > @property > def consultation_count(self): > ''' returns the consultation count for current date ''' > return self.query.filter_by(date=self.date).count() # the syntax > might not be correct > > c = Consultation.query.get(1) > print c.consultation_count > > This way its computed on the fly and you dont need to store it. > > -- > Dhruv Baldawa > (http://www.dhruvb.com) > > > On Wed, Feb 6, 2013 at 11:15 AM, Vinod Kumar Narasimhaiah < > vinod.narasimha...@gmail.com> wrote: > >> I am not a python programmer, but how about this logic? >> >> add new field called "consultation_count" to the same table >> >> before every insert to the table: >> >> Check if the date field on the last record (you might want to add a >> timestamp field to get the last record easily) = today's date >> >> If yes, then it's the same day- >> take the consultation count from the last record, increment it by one >> and create the new record. >> If No, then it's a start of the new day- >> set the counter to 1 and create the new record. >> >> >> >> On Tue, Feb 5, 2013 at 9:58 PM, Sriram Karra <karra....@gmail.com> wrote: >> >> > I have a Declarative table defined as follows: >> > >> > class Consultation(Base): >> > __tablename__ = 'consultation' >> > >> > id = Column(Integer, primary_key=True) >> > patient_id = Column(Integer, ForeignKey('patient.id')) >> > doctor_id = Column(Integer, ForeignKey('doctor.id')) >> > date = Column(Date(), default=MyT.today()) >> > >> > Each row in this table represents a single consultation instance of a >> > patient seeing a doctor on a given day. >> > >> > I would like an additional attribute called "cid" that should be an >> > auto-incrementing value representing how many-th consultation it was in >> > that day. Basically it is an auto-incrementing counter, which gets reset >> to >> > 0 at the start of a day (hence not unique, whereas the id will be >> unique). >> > No row is ever deleted. >> > >> > How do I do achieve this with the least amount of additional database >> > space? It is trivial to have another table with one column for date and >> > another column for the total consultations thus far. >> > >> > Any help? >> > >> > -Karra >> > >> > P.S. This is for PRS - an open source patient record system for small >> > clinics I am developing, and available at: https://github.com/skarra/PRS >> > _______________________________________________ >> > BangPypers mailing list >> > BangPypers@python.org >> > http://mail.python.org/mailman/listinfo/bangpypers >> > >> _______________________________________________ >> BangPypers mailing list >> BangPypers@python.org >> http://mail.python.org/mailman/listinfo/bangpypers >> > > > ------------------------------ > > Message: 4 > Date: Wed, 6 Feb 2013 11:47:52 +0530 > From: Sriram Karra <karra....@gmail.com> > To: Bangalore Python Users Group - India <bangpypers@python.org> > Subject: Re: [BangPypers] SQLAlchemy and 'non-trivial' default values > for a column > Message-ID: > <cafkt3umvbc_tnaupfxpzzk+fgntg_gsd0-fag1jnryxv9w7...@mail.gmail.com> > Content-Type: text/plain; charset=ISO-8859-1 > > On Wed, Feb 6, 2013 at 11:32 AM, Dhruv Baldawa <dhruvbald...@gmail.com>wrote: > >> I would do a: >> >> class Consultation(Base): >> __tablename__ = 'consultation' >> >> id = Column(Integer, primary_key=True) >> patient_id = Column(Integer, ForeignKey('patient.id')) >> doctor_id = Column(Integer, ForeignKey('doctor.id')) >> date = Column(Date(), default=MyT.today()) >> >> @property >> def consultation_count(self): >> ''' returns the consultation count for current date ''' >> return self.query.filter_by(date=self.date).count() # the syntax >> might not be correct >> >> c = Consultation.query.get(1) >> print c.consultation_count >> >> This way its computed on the fly and you dont need to store it. >> > > If I read your code correctly this would mean: > > (a) the consultation_count property for all the consutlations that happened > on a given day will be the same value > (b) for a given row this value will keep changing as we insert more records > into the table. > > So this will not work, no? > > > ------------------------------ > > Message: 5 > Date: Wed, 6 Feb 2013 11:50:13 +0530 > From: Sriram Karra <karra....@gmail.com> > To: Bangalore Python Users Group - India <bangpypers@python.org> > Subject: Re: [BangPypers] SQLAlchemy and 'non-trivial' default values > for a column > Message-ID: > <cafkt3unsihrfki+ckxy5mm3fk_b4vktw6oravgrwzqrixvx...@mail.gmail.com> > Content-Type: text/plain; charset=ISO-8859-1 > > Vinod, thanks for these inputs. Part of hte problem is the SQLAlchemy > syntax itself and getting it to work! > > Further, in the actual use case, I cannot assume that the records will be > inserted in the serial order of day. A patient can call and make an > appointment for a later date, and after that a patient might walk in for > the same day. Hm... > > -Karra > > > On Wed, Feb 6, 2013 at 11:15 AM, Vinod Kumar Narasimhaiah < > vinod.narasimha...@gmail.com> wrote: > >> I am not a python programmer, but how about this logic? >> >> add new field called "consultation_count" to the same table >> >> before every insert to the table: >> >> Check if the date field on the last record (you might want to add a >> timestamp field to get the last record easily) = today's date >> >> If yes, then it's the same day- >> take the consultation count from the last record, increment it by one >> and create the new record. >> If No, then it's a start of the new day- >> set the counter to 1 and create the new record. >> >> >> >> On Tue, Feb 5, 2013 at 9:58 PM, Sriram Karra <karra....@gmail.com> wrote: >> >> > I have a Declarative table defined as follows: >> > >> > class Consultation(Base): >> > __tablename__ = 'consultation' >> > >> > id = Column(Integer, primary_key=True) >> > patient_id = Column(Integer, ForeignKey('patient.id')) >> > doctor_id = Column(Integer, ForeignKey('doctor.id')) >> > date = Column(Date(), default=MyT.today()) >> > >> > Each row in this table represents a single consultation instance of a >> > patient seeing a doctor on a given day. >> > >> > I would like an additional attribute called "cid" that should be an >> > auto-incrementing value representing how many-th consultation it was in >> > that day. Basically it is an auto-incrementing counter, which gets reset >> to >> > 0 at the start of a day (hence not unique, whereas the id will be >> unique). >> > No row is ever deleted. >> > >> > How do I do achieve this with the least amount of additional database >> > space? It is trivial to have another table with one column for date and >> > another column for the total consultations thus far. >> > >> > Any help? >> > >> > -Karra >> > >> > P.S. This is for PRS - an open source patient record system for small >> > clinics I am developing, and available at: https://github.com/skarra/PRS >> > _______________________________________________ >> > BangPypers mailing list >> > BangPypers@python.org >> > http://mail.python.org/mailman/listinfo/bangpypers >> > >> _______________________________________________ >> BangPypers mailing list >> BangPypers@python.org >> http://mail.python.org/mailman/listinfo/bangpypers >> > > > ------------------------------ > > Subject: Digest Footer > > _______________________________________________ > BangPypers mailing list > BangPypers@python.org > http://mail.python.org/mailman/listinfo/bangpypers > > > ------------------------------ > > End of BangPypers Digest, Vol 66, Issue 4 > ***************************************** _______________________________________________ BangPypers mailing list BangPypers@python.org http://mail.python.org/mailman/listinfo/bangpypers
