[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