[DB-SIG] Records as an addition to the api
John Shafaee
pshafaee@hostway.com
Thu, 05 Apr 2001 12:33:24 -0500
--------------82905410A4F0311E6D4CD3FC
Content-Type: text/plain; charset=EUC-KR
Content-Transfer-Encoding: 7bit
I agree. Records (my terminology for mapping names to returned filed data) should be a
"bolt-on" to the lower level db api. They are certainly convenient and very useful in
certain programming paradigms.
In response to the difficulties with mappings, I do agree that there are some design and
implementation details that still need to be worked out, however, I don't see any of them
as being insurmountable. Most of the issues can be resolved at the API level and in some
cases may require special handling based on the RDBMs.
For example, as pointed out earlier when performing a SELECT * type query there is the
possibility of multiple return fields with the same field name as a result of a join
operation. However, at the SQL level, each joined column (I am referring to the where
clause) must be qualified with a table name, otherwise the operation is considered
ambiguous. Since the table names are provided, all that we need to do is use the fully
qualified field name for the returned results.
In my Record implementation I do this exact logic when the resulting field names are not
unique. Otherwise, I store the results with the field's common name.
Here is a code sample to better illustrate the idea.
db = DBFactory.getMySQLDataAccess( DB_CONF_FILE )
db.execute( """select * from Transaction, Domain where Transaction.Id = Domain.Id """ )
results = db.getRecords()
print results[0]
Results in:
<Record>
<Field name="Domain.Cycle" value="3"/>
<Field name="Transaction.Id" value="1"/>
[many other fields, here but I have truncated them]
<Field name="Domain.Id" value="1"/>
</Record>
It would really be convenient to have dictionary based lookups as a standard feature (an
addition) to the API. It would also make the API that much more powerful as it provides a
number of ways to access and work with SQL results.
Best,
John Shafaee
>
>
> Subject: Re: [DB-SIG] Use of record sets
> Date: Thu, 5 Apr 2001 08:24:51 -0400
> From: "Brad Clements" <bkc@murkworks.com>
> Organization: MurkWorks, Incorporated.
> To: db-sig@python.org
>
> On 3 Apr 2001, at 21:57, John Shafaee wrote:
>
> > The DBFactory module returns query results as a sequence of Records.
> > Records are Python UserDict objects that index field data by the
> > field(column) name. This is very convenient when interpolating results in a
> > string. The above example code would look like the following if written in
> > terms of lists of Records:
> >
> > print "Found id '%s', first name '%s' and last name
> > '%s'"%( result['id'], result['first_name'], result['last_name'] )
> >
> > I was wondering if this is something that you have considered. If not, I
> > was wondering what were the proper channels that I would need to go through
> > to make this suggestion open to the rest of the Python DB API community.
>
> Look for the module SQLDict.py which does exactly this (and much more)
>
> Much of my Zope/Python work is done with either SQLDict, or at the base db-api level. I
> use the db-api level when I need the maximum performance.
>
> I think dict-base (userdicts, whatever) should be a bolt-on above db-api.. Not a
> replacement for it.
>
> Brad Clements, bkc@murkworks.com (315)268-1000
> http://www.murkworks.com (315)268-9812 Fax
> netmeeting: ils://ils.murkworks.com AOL-IM: BKClements
--------------82905410A4F0311E6D4CD3FC
Content-Type: text/html; charset=EUC-KR
Content-Transfer-Encoding: 7bit
<!doctype html public "-//w3c//dtd html 4.0 transitional//en">
<html>
I agree. Records (my terminology for mapping names to returned filed
data) should be a "bolt-on" to the lower level db api. They are certainly
convenient and very useful in certain programming paradigms.
<p>In response to the difficulties with mappings, I do agree that there
are some design and implementation details that still need to be worked
out, however, I don't see any of them as being insurmountable. Most
of the issues can be resolved at the API level and in some cases may require
special handling based on the RDBMs.
<p>For example, as pointed out earlier when performing a SELECT *
type query there is the possibility of multiple return fields with the
same field name as a result of a join operation. However, at the SQL level,
each joined column (I am referring to the where clause) must be qualified
with a table name, otherwise the operation is considered ambiguous. Since
the table names are provided, all that we need to do is use the fully qualified
field name for the returned results.
<p>In my Record implementation I do this exact logic when the resulting
field names are not unique. Otherwise, I store the results with the
field's common name.
<p>Here is a code sample to better illustrate the idea.
<p><tt>db = DBFactory.getMySQLDataAccess( DB_CONF_FILE )</tt>
<br><tt>db.execute( """select * from Transaction, Domain where Transaction.Id
= Domain.Id """ )</tt>
<br><tt>results = db.getRecords()</tt>
<br><tt>print results[0]</tt>
<p>Results in:
<br><tt><Record></tt>
<br><tt> <Field name="Domain.Cycle" value="3"/></tt>
<br><tt> <Field name="Transaction.Id" value="1"/></tt>
<br><tt> [many other fields, here but I have truncated them]</tt>
<br><tt> <Field name="Domain.Id" value="1"/></tt>
<br><tt></Record></tt>
<p>It would really be convenient to have dictionary based lookups as a
standard feature (an addition) to the API. It would also make the
API that much more powerful as it provides a number of ways to access
and work with SQL results.
<p>Best,
<br>John Shafaee
<br>
<blockquote TYPE=CITE>
<p>Subject: Re: [DB-SIG] Use of record sets
<br>Date: Thu, 5 Apr 2001 08:24:51 -0400
<br>From: "Brad Clements" <bkc@murkworks.com>
<br>Organization: MurkWorks, Incorporated.
<br>To: db-sig@python.org
<p>On 3 Apr 2001, at 21:57, John Shafaee wrote:
<p>> The DBFactory module returns query results as a sequence of Records.
<br>> Records are Python UserDict objects that index field data by the
<br>> field(column) name. This is very convenient when interpolating results
in a
<br>> string. The above example code would look like the following if written
in
<br>> terms of lists of Records:
<br>>
<br>> print "Found id '%s', first name '%s' and last name
<br>> '%s'"%( result['id'], result['first_name'], result['last_name'] )
<br>>
<br>> I was wondering if this is something that you have considered. If
not, I
<br>> was wondering what were the proper channels that I would need to
go through
<br>> to make this suggestion open to the rest of the Python DB API community.
<p>Look for the module SQLDict.py which does exactly this (and much more)
<p>Much of my Zope/Python work is done with either SQLDict, or at the base
db-api level. I
<br>use the db-api level when I need the maximum performance.
<p>I think dict-base (userdicts, whatever) should be a bolt-on above db-api..
Not a
<br>replacement for it.
<p>Brad Clements,
bkc@murkworks.com (315)268-1000
<br><a href="http://www.murkworks.com">http://www.murkworks.com</a>
(315)268-9812 Fax
<br>netmeeting: ils://ils.murkworks.com
AOL-IM: BKClements</blockquote>
</html>
--------------82905410A4F0311E6D4CD3FC--