[Tutor] Question on a select statement with ODBC
Al Bull
a.bull at pubdmgroup.com
Wed Oct 22 17:06:32 CEST 2014
-----Original Message-----
From: Tutor [mailto:tutor-bounces+a.bull=pubdmgroup.com at python.org] On
Behalf Of Alan Gauld
Sent: Tuesday, October 21, 2014 6:42 PM
To: tutor at python.org
Subject: Re: [Tutor] Question on a select statement with ODBC
On 21/10/14 19:57, Al Bull wrote:
> have multiple records per ord_dbasub. Is there a way I can structure
> the select statement to retrieve only the most current record (based
> on ord_date)?
Yes, the cursor can be told to only retrieve N records, in your case 1.
SELECT ord_dbasub, ord_pub,ord_date,ord_service,
...
FROM ord
WHERE ord_pub='QWKFIL'
ORDER BY ord_dbasub, ord_date
LIMIT 1
If the sort order is wrong you can specify ASC or DESC to reverse it as
needed.
> ord_rows = cursor.execute("select ord_dbasub, ord_pub,
> ord_date,ord_service,"
> "ord_agency, ord_woa, ord_status,"
> "ord_channel, ord_source, ord_giftcomp,"
> "ord_cnreason "
> "from ord "
> "Where ord_pub='QWKFIL'"
> "order by ord_dbasub, ord_date").fetchall()
Rather than all those quotes you can use triple quotes:
ord_rows = cursor.execute('''select ord_dbasub, ord_pub,
ord_date,ord_service,
ord_agency, ord_woa, ord_status, etc...
order by ord_dbasub, ord_date
limit 1''').fetchall()
> for row in ord_rows:
> print (row.ord_dbasub, row.ord_date, row.ord_pub)
> # Add code here to find the most recent order per DBASUB and
> delete other orders
If it's in order you could just access the first row using an index.
print (ord_rows[0])
> I have to admit that the concept of tuples & dictionaries has me a little
> bit confused. I'm used to working with arrays and arrays of structures.
tuples are just read-only lists, which, in turn, are arrays that can hold
any data type.
tuples are also like records without named fields. You can use a named tuple
from the collections module which is even more like a record.
--
Alan G
Author of the Learn to Program web site
http://www.alan-g.me.uk/
http://www.flickr.com/photos/alangauldphotos
_______________________________________________
Tutor maillist - Tutor at python.org
To unsubscribe or change subscription options:
https://mail.python.org/mailman/listinfo/tutor
[Al Bull]
I don't think I explained the problem properly. I have several hundred
thousand records in the ORD table. There are many instances of records with
identical ORD_DBASUB values. Where duplicates exist, I only want to keep
the most current record. There could be 1-20 or more records with the same
ORD_DBASUB value. I am close to having this working. I added the
following statement:
ord_rows.reverse()
to reverse the order of the table. Now for each ORD_DBASUB, the most
current record will be the first one. I then added this code:
savedbasub = 0
for row in ord_rows:
if savedbasub == row.ord_dbasub:
ord_rows.remove(row)
delcount += 1
else:
savedbasub = row.ord_dbasub
This code works except in very specific cases. Take the following example:
ORD_DBASUB DATE
1) 100000360 2004-11-02
2) 100000360 2004-09-03
3) 100000334 2004-04-05
4) 100000334 2004-03-08
Record #1 is saved, as it should be. Record #2 is correctly removed.
Record #3 is correctly saved, but record #4 is not removed. It appears
that ROW is being moved to the next entry after the ord_rows.remove
statement, then being moved again at the top of the loop causing me to drop
down into the else clause for record #4.
Al Bull, Chief Technology Officer/Owner
Publishers Data Management Group
a.bull at pubdmgroup.com
815-732-5297
More information about the Tutor
mailing list