Author: Bill House
Posted: 2012-05-02 09:13:35 Link
Hello all,
This is a generic question, but I am trying to learn enough about Dabo
to use it and I need to understand how things work before I can
proceed. I am using postgresql 9.1 and psycopg2 2.4.5. and python 2.7
on OpenSUSE 64 bit.
The only language I consider myself proficient in is dbase (not even
foxpro).
But python feels comfortable and there is a lot of 1:1 equivalency
between dbase server commands and SQL commands and it looks like Ed has
already gone down this road many years ago.
What I am unsure of is the actual technique to accomplish with
SQL/client tools what is so simple in dbase. For example:
Given a dbase table called
files
with a structure of
md5sum char 30
path char 450
file_name char 220
and indexed (or sorted) in the order of
md5sum + path + file_name
run the following script
<CODE>
********************** mark_del.prg **********************
*mark all records with identical md5sums as deleted
*except for last one
*initialize some variables
store "" to m_md5sum
store 0 to m_hit_cntr
store 0 to m_place_holder
*open the table for reading and writing
use files
*begin to process the table
*iterate until end of table
do while .not. eof()
*save the md5sum for comparison
store md5sum to m_md5sum
*save position in table if needed to come back here
*recno() is probably like oid in postgresql (unless one has defined
one's own sequence)
store recno() to m_place_holder
*initialize the counter for duplicate md5sums
store 0 to m_hit_cntr
*look for instances of duplication by counting identical md5sums
*iterate until a change in md5sum
do while md5sum = m_md5sum .and. .not. eof()
*increment the counter
store m_hit_cntr + 1 to m_hit_cntr
*scroll one record
skip
enddo
*Are there duplicates?
if m_hit_cntr > 1
*go back to starting point
goto m_place_holder
*decrement the counter by the one record to leave unmarked
store m_hit_cntr -1 to m_hit_cntr
*go down the list again and mark records as deleted
*iterate until counter = 0
do while m_hit_cntr > 0 .and. .not. eof()
*mark record as deleted
delete
*decrement the counter
store m_hit_cntr -1 to m_hit_cntr
*scroll one record
skip
enddo
*scroll past the record to retain
skip
endif
*scroll to next record to begin the sequence again
skip
enddo
*close the table
use
*all done now
</CODE>
The code above is untested, but those of you with dbase experience
should get the idea.
This is a real task by the way (I'm cleaning up my hard disk). There
are 1,100,000 records in the table about 47% of them duplicates.
My SQL table is identical to the table above with the addition of two
more fields:
del boolian
recno long long int (sequence unique)
I can mimic the above operations in postgresql/python except for one thing.
I can connect to the database and table
I can iterate through the table
I can scroll up and down the table
I can update the field del in the table
Because of the size of the table, I think a named cursor is appropriate.
The problem I am having is that each update requires a commit statement
and a commit statement closes the named cursor. So one loses his place.
I understand that psycopg2 named cursors now have an attribute
withhold
and if that is set to true, then a commit will not close the named
cursor (unless a rollback is done), but I have not been able to make
this work yet.
While studying this, I thought of an approach to do what I am trying to
do. This approach involves the use of two cursors that I call a
"walking" cursor and a "working" cursor.
This seems so messy to me so I wanted to see how Dabo did it.
I have been reading the Dabo code and I notice the use of what is called an
AuxCursor
Which is described at
line 439 of dCursorMixin.py
Where it says:
...because it won't harm the contents of the main cursor....
So (finally) my questions are:
Is the use of an AuxCursor the only way to do what I want to do (unless
I can get the withhold attribute to behave)?
If there is another way, where is the best description of it so I can
read about it?
Thanks in advance,
Bill House
_______________________________________________
Post Messages to: Dabo-users@leafe.com
Subscription Maintenance: http://leafe.com/mailman/listinfo/dabo-users
Searchable Archives: http://leafe.com/archives/search/dabo-users
This message: http://leafe.com/archives/byMID/4FA1410F.7010604@house-grp.net
Author: John Fabiani
Posted: 2012-05-02 09:37:47 Link
On Wednesday, May 02, 2012 09:13:35 AM Bill House wrote:
> Hello all,
>
> This is a generic question, but I am trying to learn enough about Dabo
> to use it and I need to understand how things work before I can
> proceed. I am using postgresql 9.1 and psycopg2 2.4.5. and python 2.7
> on OpenSUSE 64 bit.
>
> The only language I consider myself proficient in is dbase (not even
> foxpro).
>
> But python feels comfortable and there is a lot of 1:1 equivalency
> between dbase server commands and SQL commands and it looks like Ed has
> already gone down this road many years ago.
>
> What I am unsure of is the actual technique to accomplish with
> SQL/client tools what is so simple in dbase. For example:
>
> Given a dbase table called
>
> files
>
> with a structure of
>
> md5sum char 30
> path char 450
> file_name char 220
>
> and indexed (or sorted) in the order of
>
> md5sum + path + file_name
>
> run the following script
>
> <CODE>
> ********************** mark_del.prg **********************
> *mark all records with identical md5sums as deleted
> *except for last one
>
> *initialize some variables
> store "" to m_md5sum
> store 0 to m_hit_cntr
> store 0 to m_place_holder
>
> *open the table for reading and writing
> use files
>
> *begin to process the table
> *iterate until end of table
> do while .not. eof()
> *save the md5sum for comparison
> store md5sum to m_md5sum
>
> *save position in table if needed to come back here
> *recno() is probably like oid in postgresql (unless one has defined
> one's own sequence)
> store recno() to m_place_holder
>
> *initialize the counter for duplicate md5sums
> store 0 to m_hit_cntr
>
> *look for instances of duplication by counting identical md5sums
> *iterate until a change in md5sum
> do while md5sum = m_md5sum .and. .not. eof()
> *increment the counter
> store m_hit_cntr + 1 to m_hit_cntr
> *scroll one record
> skip
> enddo
>
> *Are there duplicates?
> if m_hit_cntr > 1
> *go back to starting point
> goto m_place_holder
>
> *decrement the counter by the one record to leave unmarked
> store m_hit_cntr -1 to m_hit_cntr
>
> *go down the list again and mark records as deleted
> *iterate until counter = 0
> do while m_hit_cntr > 0 .and. .not. eof()
> *mark record as deleted
> delete
> *decrement the counter
> store m_hit_cntr -1 to m_hit_cntr
> *scroll one record
> skip
> enddo
>
> *scroll past the record to retain
> skip
> endif
>
> *scroll to next record to begin the sequence again
> skip
> enddo
>
> *close the table
> use
>
> *all done now
>
> </CODE>
>
> The code above is untested, but those of you with dbase experience
> should get the idea.
>
> This is a real task by the way (I'm cleaning up my hard disk). There
> are 1,100,000 records in the table about 47% of them duplicates.
>
> My SQL table is identical to the table above with the addition of two
> more fields:
>
> del boolian
> recno long long int (sequence unique)
>
> I can mimic the above operations in postgresql/python except for one thing.
>
> I can connect to the database and table
> I can iterate through the table
> I can scroll up and down the table
> I can update the field del in the table
>
> Because of the size of the table, I think a named cursor is appropriate.
>
> The problem I am having is that each update requires a commit statement
> and a commit statement closes the named cursor. So one loses his place.
>
> I understand that psycopg2 named cursors now have an attribute
>
> withhold
>
> and if that is set to true, then a commit will not close the named
> cursor (unless a rollback is done), but I have not been able to make
> this work yet.
>
> While studying this, I thought of an approach to do what I am trying to
> do. This approach involves the use of two cursors that I call a
> "walking" cursor and a "working" cursor.
>
> This seems so messy to me so I wanted to see how Dabo did it.
>
> I have been reading the Dabo code and I notice the use of what is called an
>
> AuxCursor
>
> Which is described at
>
> line 439 of dCursorMixin.py
>
> Where it says:
>
> ...because it won't harm the contents of the main cursor....
>
> So (finally) my questions are:
>
> Is the use of an AuxCursor the only way to do what I want to do (unless
> I can get the withhold attribute to behave)?
>
> If there is another way, where is the best description of it so I can
> read about it?
>
> Thanks in advance,
>
> Bill House
My first bit of advise is
SQL <> dbaseIII
There are many ways to skin this cat but you first need to review how SQL
works and how it is different from dbase.
But in general
you DO NOT commit after each update instead for your project:
pseudo code
begin
loop
update a row (you are looping over all your rows)
commit
or
rollback
Note that you do everything you need to do and then you either commit or
rollback. These are called "Transactions"
http://www.postgresql.org/docs/8.3/static/tutorial-transactions.html
Once you understand how the SQL works then I believe you will better
understand what Dabo is doing.
pseudo Dabo code
for rec in DataSet:
# set the value
rec['fieldName'] = someVal
bizobj.save()
Johnf
_______________________________________________
Post Messages to: Dabo-users@leafe.com
Subscription Maintenance: http://leafe.com/mailman/listinfo/dabo-users
Searchable Archives: http://leafe.com/archives/search/dabo-users
This message: http://leafe.com/archives/byMID/1809914.3MFPlIcBki@linux-12
Author: Dennis Lee Bieber
Posted: 2012-05-02 13:43:17 Link
On Wed, 02 May 2012 09:13:35 -0500, Bill House
<wch-tech@house-grp.net> declaimed the
following in gmane.comp.python.dabo.user:
> Given a dbase table called
>
> files
>
> with a structure of
>
> md5sum char 30
> path char 450
> file_name char 220
>
> and indexed (or sorted) in the order of
>
> md5sum + path + file_name
>
> run the following script
>
> <CODE>
> ********************** mark_del.prg **********************
> *mark all records with identical md5sums as deleted
> *except for last one
You apparently have no primary key or other means of identifying
order... Otherwise I'd suggest ordering IN REVERSE so the first
appearance of an md5sum value is the one to keep, and any subsequent
identical records are to be deleted. Oh well... Hypothetical is that the
one to keep is the one whose path/name sorts last...
#pseudo code including raw database operations
csr = con.cursor() #get a cursor for the database connection
csr.execute("""select md5sum, path, file_name
from files
order by md5sum desc,
path desc,
file_name desc""")
lastMD5 = None
for rec in csr.fetchall(): #I'm not sure if you can issue a new
#command on the cursor before
#reading all the data
if rec[0] == lastMD5:
#substitute the appropriate placeholder;
#MySQLdb uses %s, SQLite3 uses ?
csr.execute("""delete from files where
md5sum = %s and
path = %s and
file_name = %s""",
(rec[0], rec[1], rec[2]))
else:
lastMD5 = rec[0]
csr.close()
con.commit() #all the deletes now happen
--
Wulfraed Dennis Lee Bieber AF6VN
wlfraed@ix.netcom.com HTTP://wlfraed.home.netcom.com/
_______________________________________________
Post Messages to: Dabo-users@leafe.com
Subscription Maintenance: http://leafe.com/mailman/listinfo/dabo-users
Searchable Archives: http://leafe.com/archives/search/dabo-users
This message: http://leafe.com/archives/byMID/jnrv83$a9$1@dough.gmane.org
Author: Ricardo Araoz
Posted: 2012-05-03 05:15:46 Link
El 02/05/12 11:37, John Fabiani escribió:
> On Wednesday, May 02, 2012 09:13:35 AM Bill House wrote:
>> Hello all,
>>
>> This is a generic question, but I am trying to learn enough about Dabo
>> to use it and I need to understand how things work before I can
>> proceed. I am using postgresql 9.1 and psycopg2 2.4.5. and python 2.7
>> on OpenSUSE 64 bit.
>>
>> The only language I consider myself proficient in is dbase (not even
>> foxpro).
>>
>> But python feels comfortable and there is a lot of 1:1 equivalency
>> between dbase server commands and SQL commands and it looks like Ed has
>> already gone down this road many years ago.
>>
>> What I am unsure of is the actual technique to accomplish with
>> SQL/client tools what is so simple in dbase. For example:
>>
>> Given a dbase table called
>>
>> files
>>
>> with a structure of
>>
>> md5sum char 30
>> path char 450
>> file_name char 220
>>
>> and indexed (or sorted) in the order of
>>
>> md5sum + path + file_name
>>
>> run the following script
>>
>> <CODE>
>> ********************** mark_del.prg **********************
>> *mark all records with identical md5sums as deleted
>> *except for last one
>>
>> *initialize some variables
>> store "" to m_md5sum
>> store 0 to m_hit_cntr
>> store 0 to m_place_holder
>>
>> *open the table for reading and writing
>> use files
>>
>> *begin to process the table
>> *iterate until end of table
>> do while .not. eof()
>> *save the md5sum for comparison
>> store md5sum to m_md5sum
>>
>> *save position in table if needed to come back here
>> *recno() is probably like oid in postgresql (unless one has defined
>> one's own sequence)
>> store recno() to m_place_holder
>>
>> *initialize the counter for duplicate md5sums
>> store 0 to m_hit_cntr
>>
>> *look for instances of duplication by counting identical md5sums
>> *iterate until a change in md5sum
>> do while md5sum = m_md5sum .and. .not. eof()
>> *increment the counter
>> store m_hit_cntr + 1 to m_hit_cntr
>> *scroll one record
>> skip
>> enddo
>>
>> *Are there duplicates?
>> if m_hit_cntr > 1
>> *go back to starting point
>> goto m_place_holder
>>
>> *decrement the counter by the one record to leave unmarked
>> store m_hit_cntr -1 to m_hit_cntr
>>
>> *go down the list again and mark records as deleted
>> *iterate until counter = 0
>> do while m_hit_cntr > 0 .and. .not. eof()
>> *mark record as deleted
>> delete
>> *decrement the counter
>> store m_hit_cntr -1 to m_hit_cntr
>> *scroll one record
>> skip
>> enddo
>>
>> *scroll past the record to retain
>> skip
>> endif
>>
>> *scroll to next record to begin the sequence again
>> skip
>> enddo
>>
>> *close the table
>> use
>>
>> *all done now
>>
>> </CODE>
>>
>> The code above is untested, but those of you with dbase experience
>> should get the idea.
>>
>> This is a real task by the way (I'm cleaning up my hard disk). There
>> are 1,100,000 records in the table about 47% of them duplicates.
>>
>> My SQL table is identical to the table above with the addition of two
>> more fields:
>>
>> del boolian
>> recno long long int (sequence unique)
>>
>> I can mimic the above operations in postgresql/python except for one thing.
>>
>> I can connect to the database and table
>> I can iterate through the table
>> I can scroll up and down the table
>> I can update the field del in the table
>>
>> Because of the size of the table, I think a named cursor is appropriate.
>>
>> The problem I am having is that each update requires a commit statement
>> and a commit statement closes the named cursor. So one loses his place.
>>
>> I understand that psycopg2 named cursors now have an attribute
>>
>> withhold
>>
>> and if that is set to true, then a commit will not close the named
>> cursor (unless a rollback is done), but I have not been able to make
>> this work yet.
>>
>> While studying this, I thought of an approach to do what I am trying to
>> do. This approach involves the use of two cursors that I call a
>> "walking" cursor and a "working" cursor.
>>
>> This seems so messy to me so I wanted to see how Dabo did it.
>>
>> I have been reading the Dabo code and I notice the use of what is called an
>>
>> AuxCursor
>>
>> Which is described at
>>
>> line 439 of dCursorMixin.py
>>
>> Where it says:
>>
>> ...because it won't harm the contents of the main cursor....
>>
>> So (finally) my questions are:
>>
>> Is the use of an AuxCursor the only way to do what I want to do (unless
>> I can get the withhold attribute to behave)?
>>
>> If there is another way, where is the best description of it so I can
>> read about it?
>>
>> Thanks in advance,
>>
>> Bill House
> My first bit of advise is
> SQL <> dbaseIII
>
> There are many ways to skin this cat but you first need to review how SQL
> works and how it is different from dbase.
>
> But in general
> you DO NOT commit after each update instead for your project:
> pseudo code
>
> begin
> loop
> update a row (you are looping over all your rows)
> commit
> or
> rollback
>
> Note that you do everything you need to do and then you either commit or
> rollback. These are called "Transactions"
>
> http://www.postgresql.org/docs/8.3/static/tutorial-transactions.html
>
> Once you understand how the SQL works then I believe you will better
> understand what Dabo is doing.
>
> pseudo Dabo code
>
> for rec in DataSet:
> # set the value
> rec['fieldName'] = someVal
> bizobj.save()
>
> Johnf
>
Don't know if I understood what you want. I think you want to get rid of
duplicate records.
It it is so, then in SQL is really simple :
select distinct * from TheTable
T t t t that's all folks.
_______________________________________________
Post Messages to: Dabo-users@leafe.com
Subscription Maintenance: http://leafe.com/mailman/listinfo/dabo-users
Searchable Archives: http://leafe.com/archives/search/dabo-users
This message: http://leafe.com/archives/byMID/4FA25AD2.9060601@gmail.com
Author: John Fabiani
Posted: 2012-05-03 08:05:59 Link
On Thursday, May 03, 2012 07:15:46 AM Ricardo Araoz wrote:
> El 02/05/12 11:37, John Fabiani escribió:
> > On Wednesday, May 02, 2012 09:13:35 AM Bill House wrote:
> >> Hello all,
> >>
> >> This is a generic question, but I am trying to learn enough about Dabo
> >> to use it and I need to understand how things work before I can
> >> proceed. I am using postgresql 9.1 and psycopg2 2.4.5. and python 2.7
> >> on OpenSUSE 64 bit.
> >>
> >> The only language I consider myself proficient in is dbase (not even
> >> foxpro).
> >>
> >> But python feels comfortable and there is a lot of 1:1 equivalency
> >> between dbase server commands and SQL commands and it looks like Ed
> >> has
> >> already gone down this road many years ago.
> >>
> >> What I am unsure of is the actual technique to accomplish with
> >> SQL/client tools what is so simple in dbase. For example:
> >>
> >> Given a dbase table called
> >>
> >> files
> >>
> >> with a structure of
> >>
> >> md5sum char 30
> >> path char 450
> >> file_name char 220
> >>
> >> and indexed (or sorted) in the order of
> >>
> >> md5sum + path + file_name
> >>
> >> run the following script
> >>
> >> <CODE>
> >> ********************** mark_del.prg **********************
> >> *mark all records with identical md5sums as deleted
> >> *except for last one
> >>
> >> *initialize some variables
> >> store "" to m_md5sum
> >> store 0 to m_hit_cntr
> >> store 0 to m_place_holder
> >>
> >> *open the table for reading and writing
> >> use files
> >>
> >> *begin to process the table
> >> *iterate until end of table
> >> do while .not. eof()
> >>
> >> *save the md5sum for comparison
> >> store md5sum to m_md5sum
> >>
> >> *save position in table if needed to come back here
> >> *recno() is probably like oid in postgresql (unless one has
> >> defined
> >>
> >> one's own sequence)
> >>
> >> store recno() to m_place_holder
> >>
> >> *initialize the counter for duplicate md5sums
> >> store 0 to m_hit_cntr
> >>
> >> *look for instances of duplication by counting identical md5sums
> >> *iterate until a change in md5sum
> >> do while md5sum = m_md5sum .and. .not. eof()
> >>
> >> *increment the counter
> >> store m_hit_cntr + 1 to m_hit_cntr
> >> *scroll one record
> >> skip
> >>
> >> enddo
> >>
> >> *Are there duplicates?
> >>
> >> if m_hit_cntr > 1
> >>
> >> *go back to starting point
> >> goto m_place_holder
> >>
> >> *decrement the counter by the one record to leave
> >> unmarked
> >>
> >> store m_hit_cntr -1 to m_hit_cntr
> >>
> >> *go down the list again and mark records as deleted
> >> *iterate until counter = 0
> >> do while m_hit_cntr > 0 .and. .not. eof()
> >>
> >> *mark record as deleted
> >> delete
> >>
> >> *decrement the counter
> >> store m_hit_cntr -1 to m_hit_cntr
> >>
> >> *scroll one record
> >> skip
> >>
> >> enddo
> >>
> >> *scroll past the record to retain
> >> skip
> >>
> >> endif
> >>
> >> *scroll to next record to begin the sequence again
> >> skip
> >>
> >> enddo
> >>
> >> *close the table
> >> use
> >>
> >> *all done now
> >>
> >> </CODE>
> >>
> >> The code above is untested, but those of you with dbase experience
> >> should get the idea.
> >>
> >> This is a real task by the way (I'm cleaning up my hard disk). There
> >> are 1,100,000 records in the table about 47% of them duplicates.
> >>
> >> My SQL table is identical to the table above with the addition of two
> >>
> >> more fields:
> >> del boolian
> >> recno long long int (sequence unique)
> >>
> >> I can mimic the above operations in postgresql/python except for one
> >> thing.
> >>
> >> I can connect to the database and table
> >> I can iterate through the table
> >> I can scroll up and down the table
> >> I can update the field del in the table
> >>
> >> Because of the size of the table, I think a named cursor is
> >> appropriate.
> >>
> >> The problem I am having is that each update requires a commit
> >> statement
> >> and a commit statement closes the named cursor. So one loses his
> >> place.
> >>
> >> I understand that psycopg2 named cursors now have an attribute
> >>
> >> withhold
> >>
> >> and if that is set to true, then a commit will not close the named
> >> cursor (unless a rollback is done), but I have not been able to make
> >> this work yet.
> >>
> >> While studying this, I thought of an approach to do what I am trying
> >> to
> >> do. This approach involves the use of two cursors that I call a
> >> "walking" cursor and a "working" cursor.
> >>
> >> This seems so messy to me so I wanted to see how Dabo did it.
> >>
> >> I have been reading the Dabo code and I notice the use of what is
> >> called an>>
> >> AuxCursor
> >>
> >> Which is described at
> >>
> >> line 439 of dCursorMixin.py
> >>
> >> Where it says:
> >> ...because it won't harm the contents of the main cursor....
> >>
> >> So (finally) my questions are:
> >>
> >> Is the use of an AuxCursor the only way to do what I want to do
> >> (unless
> >> I can get the withhold attribute to behave)?
> >>
> >> If there is another way, where is the best description of it so I can
> >> read about it?
> >>
> >> Thanks in advance,
> >>
> >> Bill House
> >
> > My first bit of advise is
> > SQL <> dbaseIII
> >
> > There are many ways to skin this cat but you first need to review how
> > SQL
> > works and how it is different from dbase.
> >
> > But in general
> > you DO NOT commit after each update instead for your project:
> > pseudo code
> >
> > begin
> > loop
> >
> > update a row (you are looping over all your rows)
> >
> > commit
> > or
> > rollback
> >
> > Note that you do everything you need to do and then you either commit or
> > rollback. These are called "Transactions"
> >
> > http://www.postgresql.org/docs/8.3/static/tutorial-transactions.html
> >
> > Once you understand how the SQL works then I believe you will better
> > understand what Dabo is doing.
> >
> > pseudo Dabo code
> >
> > for rec in DataSet:
> > # set the value
> > rec['fieldName'] = someVal
> >
> > bizobj.save()
> >
> > Johnf
>
> Don't know if I understood what you want. I think you want to get rid of
> duplicate records.
> It it is so, then in SQL is really simple :
>
> select distinct * from TheTable
>
> T t t t that's all folks.
He wants all the dups removed but the last one.
Johnf
_______________________________________________
Post Messages to: Dabo-users@leafe.com
Subscription Maintenance: http://leafe.com/mailman/listinfo/dabo-users
Searchable Archives: http://leafe.com/archives/search/dabo-users
This message: http://leafe.com/archives/byMID/2178129.uNiTdTEeE4@linux-12
Author: Ricardo Araoz
Posted: 2012-05-03 19:05:38 Link
El 03/05/12 10:05, John Fabiani escribió:
> On Thursday, May 03, 2012 07:15:46 AM Ricardo Araoz wrote:
>>
>> Don't know if I understood what you want. I think you want to get rid of
>> duplicate records.
>> It it is so, then in SQL is really simple :
>>
>> select distinct * from TheTable
>>
>> T t t t that's all folks.
>
> He wants all the dups removed but the last one.
>
> Johnf
>
So I was right (the last one is the same as any other dup) the "select
distinct" will do it easily.
_______________________________________________
Post Messages to: Dabo-users@leafe.com
Subscription Maintenance: http://leafe.com/mailman/listinfo/dabo-users
Searchable Archives: http://leafe.com/archives/search/dabo-users
This message: http://leafe.com/archives/byMID/4FA31D52.3020200@gmail.com
Author: John Fabiani
Posted: 2012-05-03 19:14:51 Link
On Thursday, May 03, 2012 09:05:38 PM Ricardo Araoz wrote:
> El 03/05/12 10:05, John Fabiani escribió:
> > On Thursday, May 03, 2012 07:15:46 AM Ricardo Araoz wrote:
> >> Don't know if I understood what you want. I think you want to get rid
> >> of
> >> duplicate records.
> >> It it is so, then in SQL is really simple :
> >>
> >> select distinct * from TheTable
> >>
> >> T t t t that's all folks.
> >
> > He wants all the dups removed but the last one.
> >
> > Johnf
>
> So I was right (the last one is the same as any other dup) the "select
> distinct" will do it easily.
As I understand it all but the last one - using distinct does not insure the
LAST ONE is saved. How he finds the last I'm not sure.
Johnf
_______________________________________________
Post Messages to: Dabo-users@leafe.com
Subscription Maintenance: http://leafe.com/mailman/listinfo/dabo-users
Searchable Archives: http://leafe.com/archives/search/dabo-users
This message: http://leafe.com/archives/byMID/1935844.tM7pu1bqSp@linux-12
Author: Bill House
Posted: 2012-05-03 20:07:00 Link
Thanks for taking a look at this guys.
The file is sorted(indexed) md5sum + path + file_name
Therefore the longer path name (and more deeply nested) will be the last
one and therefore the one retained.
(That was my plan anyway.)
No file removal will be done at this stage, the Boolean field "del" will
be marked as true and then on another pass (after the results are
inspected), the files belonging to the records marked for deletion will
be moved (not deleted) to another place where another inspection will
take place and after the unmoved files are backed up the moved files
will be deleted.
So to traverse this file and update it without loosing my place I am
supposing I need one cursor to maintain my position and another
(disposable one) to update the table when necessary. I wanted to know
if I had misunderstood what I had read.
My original questions were:
Is the use of an AuxCursor the only way to do what I want to do (unless
I can get the withhold attribute to behave)(psycopg2)?
If there is another way, where is the best description of it so I can
read about it?
Thanks,
Bill House
On 05/03/2012 07:14 PM, John Fabiani wrote:
> On Thursday, May 03, 2012 09:05:38 PM Ricardo Araoz wrote:
>> El 03/05/12 10:05, John Fabiani escribió:
>>> On Thursday, May 03, 2012 07:15:46 AM Ricardo Araoz wrote:
>>>> Don't know if I understood what you want. I think you want to get rid
>>>> of
>>>> duplicate records.
>>>> It it is so, then in SQL is really simple :
>>>>
>>>> select distinct * from TheTable
>>>>
>>>> T t t t that's all folks.
>>> He wants all the dups removed but the last one.
>>>
>>> Johnf
>> So I was right (the last one is the same as any other dup) the "select
>> distinct" will do it easily.
> As I understand it all but the last one - using distinct does not insure the
> LAST ONE is saved. How he finds the last I'm not sure.
>
> Johnf
> _______________________________________________
> Post Messages to: Dabo-users@leafe.com
> Subscription Maintenance: http://leafe.com/mailman/listinfo/dabo-users
> Searchable Archives: http://leafe.com/archives/search/dabo-users
> This message: http://leafe.com/archives/byMID/1935844.tM7pu1bqSp@linux-12
>
>
_______________________________________________
Post Messages to: Dabo-users@leafe.com
Subscription Maintenance: http://leafe.com/mailman/listinfo/dabo-users
Searchable Archives: http://leafe.com/archives/search/dabo-users
This message: http://leafe.com/archives/byMID/4FA32BB4.50303@house-grp.net
Author: Dennis Lee Bieber
Posted: 2012-05-03 21:26:52 Link
On Thu, 03 May 2012 21:05:38 -0300, Ricardo Araoz
<ricaraoz@gmail.com> declaimed the
following in gmane.comp.python.dabo.user:
> El 03/05/12 10:05, John Fabiani escribió:
> > On Thursday, May 03, 2012 07:15:46 AM Ricardo Araoz wrote:
> >>
> >> Don't know if I understood what you want. I think you want to get rid of
> >> duplicate records.
> >> It it is so, then in SQL is really simple :
> >>
> >> select distinct * from TheTable
> >>
> >> T t t t that's all folks.
> >
> > He wants all the dups removed but the last one.
> >
> > Johnf
> >
>
> So I was right (the last one is the same as any other dup) the "select
> distinct" will do it easily.
Not quite... The specification was that the ordering was
MD5-path-file, but duplicates were only determined by MD5.
--
Wulfraed Dennis Lee Bieber AF6VN
wlfraed@ix.netcom.com HTTP://wlfraed.home.netcom.com/
_______________________________________________
Post Messages to: Dabo-users@leafe.com
Subscription Maintenance: http://leafe.com/mailman/listinfo/dabo-users
Searchable Archives: http://leafe.com/archives/search/dabo-users
This message: http://leafe.com/archives/byMID/ihf6q75ms9i45kdeq6djidlihs3bv0b6na@invalid.netcom.com
Author: Dennis Lee Bieber
Posted: 2012-05-03 21:32:57 Link
On Thu, 03 May 2012 20:07:00 -0500, Bill House
<wch-tech@house-grp.net> declaimed the
following in gmane.comp.python.dabo.user:
> Thanks for taking a look at this guys.
>
> The file is sorted(indexed) md5sum + path + file_name
>
> Therefore the longer path name (and more deeply nested) will be the last
> one and therefore the one retained.
> (That was my plan anyway.)
>
> No file removal will be done at this stage, the Boolean field "del" will
> be marked as true and then on another pass (after the results are
> inspected), the files belonging to the records marked for deletion will
> be moved (not deleted) to another place where another inspection will
> take place and after the unmoved files are backed up the moved files
> will be deleted.
>
So go back to my example in which the duplicate /records/ were
deleted from the database, and change it to perform an update...
#pseudo code including raw database operations
csr = con.cursor() #get a cursor for the database connection
csr.execute("""select md5sum, path, file_name
from files
order by md5sum desc,
path desc,
file_name desc""")
lastMD5 = None
for (md5sum, path, file_name) in csr.fetchall():
#I'm not sure if you can issue a new
#command on the cursor before
#reading all the data, so fetchall() reads all
if md5sum == lastMD5:
#substitute the appropriate placeholder;
#MySQLdb uses %s, SQLite3 uses ?
csr.execute("""update files set
flag = True
where
md5sum = %s and
path = %s and
file_name = %s""",
(md5sum, path, file_name))
else:
lastMD5 = md5sum
csr.close()
con.commit() #all the flag updates now happen
--
Wulfraed Dennis Lee Bieber AF6VN
wlfraed@ix.netcom.com HTTP://wlfraed.home.netcom.com/
_______________________________________________
Post Messages to: Dabo-users@leafe.com
Subscription Maintenance: http://leafe.com/mailman/listinfo/dabo-users
Searchable Archives: http://leafe.com/archives/search/dabo-users
This message: http://leafe.com/archives/byMID/rkf6q7pi57ckvtnrajrap7g4sh5t1p52du@invalid.netcom.com