Path: csiph.com!usenet.pasdenom.info!gegeweb.org!usenet-fr.net!nerim.net!novso.com!newsfeed.xs4all.nl!newsfeed3.news.xs4all.nl!xs4all!post.news.xs4all.nl!not-for-mail
Newsgroups: comp.lang.python
Date: Wed, 6 Mar 2013 00:57:26 -0800 (PST)
In-Reply-To: <mailman.2928.1362557959.2939.python-list@python.org>
Complaints-To: groups-abuse@google.com
Injection-Info: glegroupsg2000goo.googlegroups.com; posting-host=94.68.127.128; posting-account=DYJQ-woAAACEPH85Au2BhUVfFTfSfVa4
References: <390f0dc5-5750-4849-9433-a19d90cc8566@googlegroups.com> <mailman.2928.1362557959.2939.python-list@python.org>
User-Agent: G2/1.0
MIME-Version: 1.0
Subject: Re: sync databse table based on current directory data without losign previous values
From: =?ISO-8859-7?B?zd/q7/Igw+rxMzPq?= <nikos.gr33k@gmail.com>
To: comp.lang.python@googlegroups.com
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: quoted-printable
Cc: python-list@python.org
Precedence: list
Message-ID: <mailman.2930.1362560945.2939.python-list@python.org>
Lines: 142
NNTP-Posting-Host: 2001:888:2000:d::a6
Xref: csiph.com comp.lang.python:40615

=CE=A4=CE=B7 =CE=A4=CE=B5=CF=84=CE=AC=CF=81=CF=84=CE=B7, 6 =CE=9C=CE=B1=CF=
=81=CF=84=CE=AF=CE=BF=CF=85 2013 10:19:06 =CF=80.=CE=BC. UTC+2, =CE=BF =CF=
=87=CF=81=CE=AE=CF=83=CF=84=CE=B7=CF=82 Lele Gaifax =CE=AD=CE=B3=CF=81=CE=
=B1=CF=88=CE=B5:
> =CE=9D=CE=AF=CE=BA=CE=BF=CF=82 =CE=93=CE=BA=CF=8133=CE=BA <nikos.gr33k@gm=
ail.com> writes:
>=20
>=20
>=20
> > How can i update  the databse to only contain the existing filenames wi=
thout losing the previous stored data?
>=20
>=20
>=20
> Basically you need to keep a list (or better, a set) containing all
>=20
> current filenames that you are going to insert, and finally do another
>=20
> "inverse" loop where you scan all the records and delete those that are
>=20
> not present anymore.
>=20
>=20
>=20
> Of course, this assume you have a "bidirectional" identity between the
>=20
> filenames you are loading and the records you are inserting, which is
>=20
> not the case in the code you show:
>=20
>=20
>=20
> > #read the containing folder and insert new filenames
>=20
> > for result in os.walk(path):
>=20
> > 	for filename in result[2]:
>=20
>=20
>=20
> Here "filename" is just that, not the full path: this could result in
>=20
> collisions, if your are actually loading a *tree* instead of a flat
>=20
> directory, that is multiple source files are squeezed into a single
>=20
> record in your database (imagine "/foo/index.html" and
>=20
> "/foo/subdir/index.html").
>=20
>=20
>=20
> With that in mind, I would do something like the following:
>=20
>=20
>=20
>   # Compute a set of current fullpaths
>=20
>   current_fullpaths =3D set()
>=20
>   for root, dirs, files in os.walk(path):
>=20
>     for fullpath in files:
>=20
>       current_fullpaths.add(os.path.join(root, file))
>=20
>=20
>=20
>   # Load'em
>=20
>   for fullpath in current_fullpaths:
>=20
>    =20
>=20
>     try:
>=20
>       #find the needed counter for the page URL
>=20
>       cur.execute('''SELECT URL FROM files WHERE URL =3D %s''', (fullpath=
,) )=20
>=20
>       data =3D cur.fetchone()        #URL is unique, so should only be on=
e
>=20
>=20
>=20
>       if not data:
>=20
>         #first time for file; primary key is automatic, hit is defaulted
>=20
>         cur.execute('''INSERT INTO files (URL, host, lastvisit) VALUES (%=
s, %s, %s)''', (fullpath, host, date) )
>=20
>     except MySQLdb.Error, e:
>=20
>       print ( "Query Error: ", sys.exc_info()[1].excepinfo()[2] )
>=20
>=20
>=20
>   # Delete spurious
>=20
>   cur.execute('''SELECT url FROM files''') =20
>=20
>   for rec in cur:
>=20
>     fullpath =3D rec[0]
>=20
>     if fullpath not in current_fullpaths:
>=20
>       other_cur.execute('''DELETE FROM files WHERE url =3D %s''', (fullpa=
th,))
>=20
>=20
>=20
> Of course here I am assuming a lot (a typical thing we do to answer your
>=20
> questions :-), in particular that the "url" field content matches the
>=20
> filesystem layout, which may not be the case. Adapt it to your usecase.
>=20
>=20
>=20
> hope this helps,
>=20
> ciao, lele.
>=20
> --=20
>=20
> nickname: Lele Gaifax | Quando vivr=C3=B2 di quello che ho pensato ieri
>=20
> real: Emanuele Gaifas | comincer=C3=B2 ad aver paura di chi mi copia.
>=20
> 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 presentin=
g this error ehre you can see its output here:

http://superhost.gr/cgi-bin/files.py