[BangPypers] SQLAlchemy and 'non-trivial' default values for a column
Thava Alagu
thavamuni at gmail.com
Wed Feb 6 09:07:12 CET 2013
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-request at python.org> wrote:
> Send BangPypers mailing list submissions to
> bangpypers at 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-request at python.org
>
> You can reach the person managing the list at
> bangpypers-owner at 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.etc at gmail.com>
> To: Bangalore Python Users Group - India <BangPypers at python.org>
> Subject: [BangPypers] SQLAlchemy and 'non-trivial' default values for
> a column
> Message-ID:
> <CAFkt3UNFaEK+0Tjh=JiFU3S9Nc8zJZMW7YYU6Vuzexf1V9P3sA at 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.narasimhaiah at gmail.com>
> To: Bangalore Python Users Group - India <bangpypers at python.org>
> Subject: Re: [BangPypers] SQLAlchemy and 'non-trivial' default values
> for a column
> Message-ID:
> <CAPaifDfRvT5Uk4Fydw-XjN39Xx3pf7bjfCF1Fc-ZwdpTd3Q8xw at 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.etc at 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 at python.org
>> http://mail.python.org/mailman/listinfo/bangpypers
>>
>
>
> ------------------------------
>
> Message: 3
> Date: Wed, 6 Feb 2013 11:32:45 +0530
> From: Dhruv Baldawa <dhruvbaldawa at gmail.com>
> To: Bangalore Python Users Group - India <bangpypers at python.org>
> Subject: Re: [BangPypers] SQLAlchemy and 'non-trivial' default values
> for a column
> Message-ID:
> <CAC5Hnjgga6bq1n63yR3AUx4mOLTTJJvz=9--2=roMJkWLNfB5A at 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.narasimhaiah at 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.etc at 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 at python.org
>> > http://mail.python.org/mailman/listinfo/bangpypers
>> >
>> _______________________________________________
>> BangPypers mailing list
>> BangPypers at python.org
>> http://mail.python.org/mailman/listinfo/bangpypers
>>
>
>
> ------------------------------
>
> Message: 4
> Date: Wed, 6 Feb 2013 11:47:52 +0530
> From: Sriram Karra <karra.etc at gmail.com>
> To: Bangalore Python Users Group - India <bangpypers at python.org>
> Subject: Re: [BangPypers] SQLAlchemy and 'non-trivial' default values
> for a column
> Message-ID:
> <CAFkt3UMVbC_TNauPFXPZzk+FgNtG_GSD0-FAg1jNrYxV9w7d4g at mail.gmail.com>
> Content-Type: text/plain; charset=ISO-8859-1
>
> On Wed, Feb 6, 2013 at 11:32 AM, Dhruv Baldawa <dhruvbaldawa at 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.etc at gmail.com>
> To: Bangalore Python Users Group - India <bangpypers at python.org>
> Subject: Re: [BangPypers] SQLAlchemy and 'non-trivial' default values
> for a column
> Message-ID:
> <CAFkt3UNSihRfKi+ckxy5Mm3fk_b4vktw6OrAVGRWZQrixvXNdw at 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.narasimhaiah at 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.etc at 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 at python.org
>> > http://mail.python.org/mailman/listinfo/bangpypers
>> >
>> _______________________________________________
>> BangPypers mailing list
>> BangPypers at python.org
>> http://mail.python.org/mailman/listinfo/bangpypers
>>
>
>
> ------------------------------
>
> Subject: Digest Footer
>
> _______________________________________________
> BangPypers mailing list
> BangPypers at python.org
> http://mail.python.org/mailman/listinfo/bangpypers
>
>
> ------------------------------
>
> End of BangPypers Digest, Vol 66, Issue 4
> *****************************************
More information about the BangPypers
mailing list