Path: csiph.com!newsfeed.hal-mli.net!feeder3.hal-mli.net!newsfeed.hal-mli.net!feeder2.hal-mli.net!newsfeed.xs4all.nl!newsfeed2.news.xs4all.nl!xs4all!post.news.xs4all.nl!not-for-mail
Newsgroups: comp.lang.python
Date: Fri, 25 Jan 2013 07:43:53 -0800 (PST)
In-Reply-To: <mailman.1012.1359060248.2939.python-list@python.org>
Complaints-To: groups-abuse@google.com
Injection-Info: glegroupsg2000goo.googlegroups.com; posting-host=94.68.70.179; posting-account=DYJQ-woAAACEPH85Au2BhUVfFTfSfVa4
References: <88306c73-dfa2-44e1-ab0c-d90dba05be1c@googlegroups.com> <mailman.1012.1359060248.2939.python-list@python.org>
User-Agent: G2/1.0
MIME-Version: 1.0
Subject: Re: mysql solution
From: Ferrous Cranus <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.1043.1359128642.2939.python-list@python.org>
Lines: 248
NNTP-Posting-Host: 2001:888:2000:d::a6
Xref: csiph.com comp.lang.python:37676

=CE=A4=CE=B7 =CE=A0=CE=AD=CE=BC=CF=80=CF=84=CE=B7, 24 =CE=99=CE=B1=CE=BD=CE=
=BF=CF=85=CE=B1=CF=81=CE=AF=CE=BF=CF=85 2013 10:43:59 =CE=BC.=CE=BC. UTC+2,=
 =CE=BF =CF=87=CF=81=CE=AE=CF=83=CF=84=CE=B7=CF=82 Dennis Lee Bieber =CE=AD=
=CE=B3=CF=81=CE=B1=CF=88=CE=B5:
> On Thu, 24 Jan 2013 03:04:46 -0800 (PST), Ferrous Cranus
>=20
> <nikos.gr33k@gmail.com> declaimed the following in
>=20
> gmane.comp.python.general:
>=20
>=20
>=20
> > # insert new page record in table counters or update it if already exis=
ts
>=20
> > 	try:
>=20
> > 		cursor.execute( '''INSERT INTO counters(page, hits) VALUES(%s, %s)=20
>=20
> > 								ON DUPLICATE KEY UPDATE hits =3D hits + 1''', (htmlpage, 1) )
>=20
> > 	except MySQLdb.Error, e:
>=20
> > 		print ( "Query Error: ", sys.exc_info()[1].excepinfo()[2] )
>=20
> > 	=09
>=20
> > 	# update existing visitor record if same pin and same host found
>=20
> > 	try:
>=20
> > 		cursor.execute( '''UPDATE visitors SET hits =3D hits + 1, useros =3D =
%s, browser =3D %s, date =3D %s WHERE pin =3D %s AND host =3D %s''', (usero=
s, browser, date, pin, host))
>=20
> > 	except MySQLdb.Error, e:
>=20
> > 		print ( "Error %d: %s" % (e.args[0], e.args[1]) )
>=20
> >
>=20
> > 	# insert new visitor record if above update did not affect a row
>=20
> > 	if cursor.rowcount =3D=3D 0:
>=20
> > 		cursor.execute( '''INSERT INTO visitors(hits, host, useros, browser, =
date) VALUES(%s, %s, %s, %s, %s)''', (1, host, useros, browser, date) )
>=20
> >=20
>=20
>=20
>=20
> 	Seeing the database schema would help. At present I have no idea
>=20
> what is defined as a key, what may be a foreign key, etc.
>=20
>=20
>=20
> 	For example: you show a "counters" table in which you are saving
>=20
> "hits" per page (I presume the URL is being saved). But the very next
>=20
> thing you are doing is something with a hit count in a "visitors" table
>=20
> which appears to be keyed by the combination of "host" and "pin" -- but
>=20
> you've failed to provide "pin" on the INSERT.
>=20
>=20
>=20
> 	Furthermore, your "visitors" table is only saving the most recent
>=20
> "useros" and "browser" data... Is that what you really want -- or do you
>=20
> want to log ALL users that visit the page.
>=20
>=20
>=20
> 	Making presumptions, I'd probably have something like:
>=20
>=20
>=20
> SCHEMA:
>=20
>=20
>=20
> create table counters
>=20
> (
>=20
>   ID integer not null auto_increment primary key,
>=20
>   URL varchar(255) not null,
>=20
>   hits integer not null default 1,
>=20
>   unique index (URL)
>=20
> );
>=20
>=20
>=20
> create table visitors
>=20
> (
>=20
>   ID integer not null auto_increment primary key,
>=20
>   counterID integer not null,
>=20
>   host varchar(255) not null,
>=20
>   userOS varchar(255) not null,
>=20
>   browser varchar(255) not null,
>=20
>   hits integer not null default 1,
>=20
>   lastVisit datetime not null,
>=20
>   foreign key (counterID) references counters (ID),
>=20
>   unique index (counterID, host)
>=20
> );
>=20
>=20
>=20
> -=3D-=3D-=3D-
>=20
>=20
>=20
> con =3D db.connection()
>=20
>=20
>=20
> cur =3D con.cursor()
>=20
>=20
>=20
> try:
>=20
> 	#find the needed counter for the page URL
>=20
> 	cur.execute("select ID from counters where URL =3D %s", (htmlpage, ) )
>=20
> 	data =3D cur.fetchone()	#URL is unique, so should only be one
>=20
> 	if not data:
>=20
> 		#first time for page; primary key is automatic, hit is defaulted
>=20
> 		cur.execute("insert into counters (URL) values (%s)",
>=20
> 						(htmlpage,) )
>=20
> 		cID =3D cur.lastrowid	#get the primary key value of the new record
>=20
> 	else:
>=20
> 		#found the page, save primary key and use it to issue hit update
>=20
> 		cID =3D data[0]
>=20
> 		cur.execute("update counters set hits =3D hits + 1 where ID =3D %s",
>=20
> 						(cID,) )
>=20
>=20
>=20
> 	#find the visitor record for the (saved) cID and current host
>=20
> 	cur.execute("""select ID from visitors=20
>=20
> 					where counterID =3D %s
>=20
> 						and host =3D %s""",
>=20
> 					(cID, host) )
>=20
> 	data =3D cur.fetchone()	#cID&host are unique
>=20
> 	if not data:
>=20
> 		#first time for this host on this page, create new record
>=20
> 		cur.execute("""insert into visitors=20
>=20
> 						(counterID, host, userOS, browser, lastVisit)
>=20
> 						values (%s, %s, %s, %s, %s)""",
>=20
> 						(cID, host, useros, browser, date) )
>=20
> 		#primary key and hits are defaulted, don't care about key
>=20
> 	else:
>=20
> 		#found the page, save its primary key for later use
>=20
> 		vID =3D data[0]
>=20
> 		#update record using retrieved vID
>=20
> 		cur.execute("""update visitors set
>=20
> 							userOS =3D %s,
>=20
> 							browser =3D %s,
>=20
> 							lastVisit =3D %s,
>=20
> 							hits =3D hits + 1
>=20
> 						where ID =3D %s""",
>=20
> 						(useros, browser, date, vID) )
>=20
>=20
>=20
> 	con.commit()	#if we made it here, the transaction is complete
>=20
>=20
>=20
> except:		#blind excepts aren't "good", but you get the idea
>=20
> 			#ANY exception needs to rollback the above sequence
>=20
> 	con.rollback()	#something failed, rollback the entire transaction
>=20
> 	print "ERROR DURING hit counter update sequence"
>=20

It worked like a charm! Thank you very much!

what do you mean by that?

 " Furthermore, your "visitors" table is only saving the most recent=20
"useros" and "browser" data... Is that what you really want -- or do you=20
want to log ALL users that visit the page. "

If the same hostname  visits my webpage multiple times i only update the us=
erOS, bwoswer, date information.=20

What do you mean?

And also: why does the table 'visitors' ahs to have an auto increment colum=
n ID what for?