Re: sync databse table based on current directory data without losign previous values

Newsgroups	comp.lang.python
Date	2013-03-06 00:57 -0800
References	<390f0dc5-5750-4849-9433-a19d90cc8566@googlegroups.com> <mailman.2928.1362557959.2939.python-list@python.org>
Subject	Re: sync databse table based on current directory data without losign previous values
From	Νίκος Γκρ33κ <nikos.gr33k@gmail.com>
Message-ID	<mailman.2930.1362560945.2939.python-list@python.org> (permalink)

Show all headers | View raw

Τη Τετάρτη, 6 Μαρτίου 2013 10:19:06 π.μ. UTC+2, ο χρήστης Lele Gaifax έγραψε:
> Νίκος Γκρ33κ <nikos.gr33k@gmail.com> writes:
> 
> 
> 
> > How can i update  the databse to only contain the existing filenames without losing the previous stored data?
> 
> 
> 
> Basically you need to keep a list (or better, a set) containing all
> 
> current filenames that you are going to insert, and finally do another
> 
> "inverse" loop where you scan all the records and delete those that are
> 
> not present anymore.
> 
> 
> 
> Of course, this assume you have a "bidirectional" identity between the
> 
> filenames you are loading and the records you are inserting, which is
> 
> not the case in the code you show:
> 
> 
> 
> > #read the containing folder and insert new filenames
> 
> > for result in os.walk(path):
> 
> > 	for filename in result[2]:
> 
> 
> 
> Here "filename" is just that, not the full path: this could result in
> 
> collisions, if your are actually loading a *tree* instead of a flat
> 
> directory, that is multiple source files are squeezed into a single
> 
> record in your database (imagine "/foo/index.html" and
> 
> "/foo/subdir/index.html").
> 
> 
> 
> With that in mind, I would do something like the following:
> 
> 
> 
>   # Compute a set of current fullpaths
> 
>   current_fullpaths = set()
> 
>   for root, dirs, files in os.walk(path):
> 
>     for fullpath in files:
> 
>       current_fullpaths.add(os.path.join(root, file))
> 
> 
> 
>   # Load'em
> 
>   for fullpath in current_fullpaths:
> 
>     
> 
>     try:
> 
>       #find the needed counter for the page URL
> 
>       cur.execute('''SELECT URL FROM files WHERE URL = %s''', (fullpath,) ) 
> 
>       data = cur.fetchone()        #URL is unique, so should only be one
> 
> 
> 
>       if not data:
> 
>         #first time for file; primary key is automatic, hit is defaulted
> 
>         cur.execute('''INSERT INTO files (URL, host, lastvisit) VALUES (%s, %s, %s)''', (fullpath, host, date) )
> 
>     except MySQLdb.Error, e:
> 
>       print ( "Query Error: ", sys.exc_info()[1].excepinfo()[2] )
> 
> 
> 
>   # Delete spurious
> 
>   cur.execute('''SELECT url FROM files''')  
> 
>   for rec in cur:
> 
>     fullpath = rec[0]
> 
>     if fullpath not in current_fullpaths:
> 
>       other_cur.execute('''DELETE FROM files WHERE url = %s''', (fullpath,))
> 
> 
> 
> Of course here I am assuming a lot (a typical thing we do to answer your
> 
> questions :-), in particular that the "url" field content matches the
> 
> filesystem layout, which may not be the case. Adapt it to your usecase.
> 
> 
> 
> hope this helps,
> 
> ciao, lele.
> 
> -- 
> 
> nickname: Lele Gaifax | Quando vivrò di quello che ho pensato ieri
> 
> real: Emanuele Gaifas | comincerò ad aver paura di chi mi copia.
> 
> lele@metapensiero.it  |                 -- Fortunato Depero, 1929.

You are fantastic! Your straightforward logic amazes me!

Thank you very much for making things clear to me!!

But there is a slight problem when iam trying to run the code iam presenting this error ehre you can see its output here:

http://superhost.gr/cgi-bin/files.py

Thread

sync databse table based on current directory data without losign previous values Νίκος Γκρ33κ <nikos.gr33k@gmail.com> - 2013-03-05 23:45 -0800
  Re: sync databse table based on current directory data without losign previous values Lele Gaifax <lele@metapensiero.it> - 2013-03-06 09:19 +0100
    Re: sync databse table based on current directory data without losign previous values Νίκος Γκρ33κ <nikos.gr33k@gmail.com> - 2013-03-06 00:57 -0800
      Re: sync databse table based on current directory data without losign previous values Lele Gaifax <lele@metapensiero.it> - 2013-03-06 10:24 +0100
        Re: sync databse table based on current directory data without losign previous values Νίκος Γκρ33κ <nikos.gr33k@gmail.com> - 2013-03-06 01:41 -0800
          Re: sync databse table based on current directory data without losign previous values Νίκος Γκρ33κ <nikos.gr33k@gmail.com> - 2013-03-06 01:43 -0800
            Re: sync databse table based on current directory data without losign previous values Bryan Devaney <bryan.devaney@gmail.com> - 2013-03-06 02:15 -0800
            Re: sync databse table based on current directory data without losign previous values Bryan Devaney <bryan.devaney@gmail.com> - 2013-03-06 02:15 -0800
          Re: sync databse table based on current directory data without losign previous values Νίκος Γκρ33κ <nikos.gr33k@gmail.com> - 2013-03-06 01:43 -0800
          Re: sync databse table based on current directory data without losign previous values Lele Gaifax <lele@metapensiero.it> - 2013-03-06 11:27 +0100
          Re: sync databse table based on current directory data without losign previous values Dave Angel <davea@davea.name> - 2013-03-06 08:31 -0500
          Re: sync databse table based on current directory data without losign previous values Lele Gaifax <lele@metapensiero.it> - 2013-03-06 15:16 +0100
        Re: sync databse table based on current directory data without losign previous values Νίκος Γκρ33κ <nikos.gr33k@gmail.com> - 2013-03-06 01:41 -0800
    Re: sync databse table based on current directory data without losign previous values Νίκος Γκρ33κ <nikos.gr33k@gmail.com> - 2013-03-06 00:57 -0800
  Set x to  to None and del x doesn't release memory in python 2.7.1 (HPUX 11.23, ia64) Wong Wah Meng-R32813 <r32813@freescale.com> - 2013-03-06 10:11 +0000
    Re: Set x to  to None and del x doesn't release memory in python 2.7.1 (HPUX 11.23, ia64) Bryan Devaney <bryan.devaney@gmail.com> - 2013-03-06 02:25 -0800
      RE: Set x to  to None and del x doesn't release memory in python 2.7.1 (HPUX 11.23, ia64) Wong Wah Meng-R32813 <r32813@freescale.com> - 2013-03-06 12:31 +0000
      Re: Set x to  to None and del x doesn't release memory in python 2.7.1 (HPUX 11.23, ia64) Dave Angel <davea@davea.name> - 2013-03-06 08:18 -0500
      Re: Set x to  to None and del x doesn't release memory in python 2.7.1 (HPUX 11.23, ia64) Dave Angel <davea@davea.name> - 2013-03-06 08:25 -0500
    Re: Set x to  to None and del x doesn't release memory in python 2.7.1 (HPUX 11.23, ia64) Bryan Devaney <bryan.devaney@gmail.com> - 2013-03-06 02:25 -0800
    Re: Set x to  to None and del x doesn't release memory in python 2.7.1 (HPUX 11.23, ia64) Steven D'Aprano <steve+comp.lang.python@pearwood.info> - 2013-03-06 23:34 +0000
      RE: Set x to  to None and del x doesn't release memory in python 2.7.1 (HPUX 11.23, ia64) Wong Wah Meng-R32813 <r32813@freescale.com> - 2013-03-07 06:33 +0000
      Re: Set x to to None and del x doesn't release memory in python 2.7.1 (HPUX 11.23, ia64) Chris Angelico <rosuav@gmail.com> - 2013-03-07 18:19 +1100
      RE: Set x to to None and del x doesn't release memory in python 2.7.1 (HPUX 11.23, ia64) Wong Wah Meng-R32813 <r32813@freescale.com> - 2013-03-08 09:08 +0000
      Re: Set x to to None and del x doesn't release memory in python 2.7.1 (HPUX 11.23, ia64) Dennis Lee Bieber <wlfraed@ix.netcom.com> - 2013-03-08 19:40 -0500
      RE: Set x to to None and del x doesn't release memory in python 2.7.1 (HPUX 11.23, ia64) Wong Wah Meng-R32813 <r32813@freescale.com> - 2013-03-09 08:07 +0000
        Re: Set x to to None and del x doesn't release memory in python 2.7.1 (HPUX 11.23, ia64) Grant Edwards <invalid@invalid.invalid> - 2013-03-09 19:18 +0000
          Re: Set x to to None and del x doesn't release memory in python 2.7.1 (HPUX 11.23, ia64) Roy Smith <roy@panix.com> - 2013-03-09 15:04 -0500
            Re: Set x to to None and del x doesn't release memory in python 2.7.1 (HPUX 11.23, ia64) Grant Edwards <invalid@invalid.invalid> - 2013-03-09 20:35 +0000
              Re: Set x to to None and del x doesn't release memory in python 2.7.1 (HPUX 11.23, ia64) Roy Smith <roy@panix.com> - 2013-03-09 16:44 -0500
                Re: Set x to to None and del x doesn't release memory in python 2.7.1 (HPUX 11.23, ia64) Grant Edwards <invalid@invalid.invalid> - 2013-03-11 14:27 +0000
      Re: Set x to to None and del x doesn't release memory in python 2.7.1 (HPUX 11.23, ia64) Dave Angel <davea@davea.name> - 2013-03-09 06:02 -0500
      Re: Set x to to None and del x doesn't release memory in python 2.7.1 (HPUX 11.23, ia64) Isaac To <isaac.to@gmail.com> - 2013-03-09 23:02 +0800
  Re: Set x to  to None and del x doesn't release memory in python 2.7.1 (HPUX 11.23, ia64) Terry Reedy <tjreedy@udel.edu> - 2013-03-06 05:59 -0500
  Re: sync databse table based on current directory data without losign previous values Mark Lawrence <breamoreboy@yahoo.co.uk> - 2013-03-06 11:52 +0000
  RE: Set x to  to None and del x doesn't release memory in python 2.7.1 (HPUX 11.23, ia64) Wong Wah Meng-R32813 <r32813@freescale.com> - 2013-03-06 12:36 +0000
  Re: sync databse table based on current directory data without losign previous values Chris Angelico <rosuav@gmail.com> - 2013-03-07 00:40 +1100
  Re: sync databse table based on current directory data without losign previous values "Michael Ross" <gmx@ross.cx> - 2013-03-06 15:04 +0100
    Re: sync databse table based on current directory data without losign previous values nagia.retsina@gmail.com - 2013-03-06 08:09 -0800
    Re: sync databse table based on current directory data without losign previous values nagia.retsina@gmail.com - 2013-03-06 08:09 -0800

csiph-web