Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.lang.python > #40612

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

Show all headers | View raw

Νίκος Γκρ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.

Back to comp.lang.python | Previous | Next — Previous in thread | Next in thread | Find similar | Unroll thread

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