mysql solution

[Ferrous Cranus]

Τη Πέμπτη, 24 Ιανουαρίου 2013 10:43:59 μ.μ. UTC+2, ο χρήστης Dennis Lee Bieber έγραψε:
> On Thu, 24 Jan 2013 03:04:46 -0800 (PST), Ferrous Cranus
> 
> <nikos.gr33k at gmail.com> declaimed the following in
> 
> gmane.comp.python.general:
> 
> 
> 
> > # insert new page record in table counters or update it if already exists
> 
> > 	try:
> 
> > 		cursor.execute( '''INSERT INTO counters(page, hits) VALUES(%s, %s) 
> 
> > 								ON DUPLICATE KEY UPDATE hits = hits + 1''', (htmlpage, 1) )
> 
> > 	except MySQLdb.Error, e:
> 
> > 		print ( "Query Error: ", sys.exc_info()[1].excepinfo()[2] )
> 
> > 		
> 
> > 	# update existing visitor record if same pin and same host found
> 
> > 	try:
> 
> > 		cursor.execute( '''UPDATE visitors SET hits = hits + 1, useros = %s, browser = %s, date = %s WHERE pin = %s AND host = %s''', (useros, browser, date, pin, host))
> 
> > 	except MySQLdb.Error, e:
> 
> > 		print ( "Error %d: %s" % (e.args[0], e.args[1]) )
> 
> >
> 
> > 	# insert new visitor record if above update did not affect a row
> 
> > 	if cursor.rowcount == 0:
> 
> > 		cursor.execute( '''INSERT INTO visitors(hits, host, useros, browser, date) VALUES(%s, %s, %s, %s, %s)''', (1, host, useros, browser, date) )
> 
> > 
> 
> 
> 
> 	Seeing the database schema would help. At present I have no idea
> 
> what is defined as a key, what may be a foreign key, etc.
> 
> 
> 
> 	For example: you show a "counters" table in which you are saving
> 
> "hits" per page (I presume the URL is being saved). But the very next
> 
> thing you are doing is something with a hit count in a "visitors" table
> 
> which appears to be keyed by the combination of "host" and "pin" -- but
> 
> you've failed to provide "pin" on the INSERT.
> 
> 
> 
> 	Furthermore, your "visitors" table is only saving the most recent
> 
> "useros" and "browser" data... Is that what you really want -- or do you
> 
> want to log ALL users that visit the page.
> 
> 
> 
> 	Making presumptions, I'd probably have something like:
> 
> 
> 
> SCHEMA:
> 
> 
> 
> create table counters
> 
> (
> 
>   ID integer not null auto_increment primary key,
> 
>   URL varchar(255) not null,
> 
>   hits integer not null default 1,
> 
>   unique index (URL)
> 
> );
> 
> 
> 
> create table visitors
> 
> (
> 
>   ID integer not null auto_increment primary key,
> 
>   counterID integer not null,
> 
>   host varchar(255) not null,
> 
>   userOS varchar(255) not null,
> 
>   browser varchar(255) not null,
> 
>   hits integer not null default 1,
> 
>   lastVisit datetime not null,
> 
>   foreign key (counterID) references counters (ID),
> 
>   unique index (counterID, host)
> 
> );
> 
> 
> 
> -=-=-=-
> 
> 
> 
> con = db.connection()
> 
> 
> 
> cur = con.cursor()
> 
> 
> 
> try:
> 
> 	#find the needed counter for the page URL
> 
> 	cur.execute("select ID from counters where URL = %s", (htmlpage, ) )
> 
> 	data = cur.fetchone()	#URL is unique, so should only be one
> 
> 	if not data:
> 
> 		#first time for page; primary key is automatic, hit is defaulted
> 
> 		cur.execute("insert into counters (URL) values (%s)",
> 
> 						(htmlpage,) )
> 
> 		cID = cur.lastrowid	#get the primary key value of the new record
> 
> 	else:
> 
> 		#found the page, save primary key and use it to issue hit update
> 
> 		cID = data[0]
> 
> 		cur.execute("update counters set hits = hits + 1 where ID = %s",
> 
> 						(cID,) )
> 
> 
> 
> 	#find the visitor record for the (saved) cID and current host
> 
> 	cur.execute("""select ID from visitors 
> 
> 					where counterID = %s
> 
> 						and host = %s""",
> 
> 					(cID, host) )
> 
> 	data = cur.fetchone()	#cID&host are unique
> 
> 	if not data:
> 
> 		#first time for this host on this page, create new record
> 
> 		cur.execute("""insert into visitors 
> 
> 						(counterID, host, userOS, browser, lastVisit)
> 
> 						values (%s, %s, %s, %s, %s)""",
> 
> 						(cID, host, useros, browser, date) )
> 
> 		#primary key and hits are defaulted, don't care about key
> 
> 	else:
> 
> 		#found the page, save its primary key for later use
> 
> 		vID = data[0]
> 
> 		#update record using retrieved vID
> 
> 		cur.execute("""update visitors set
> 
> 							userOS = %s,
> 
> 							browser = %s,
> 
> 							lastVisit = %s,
> 
> 							hits = hits + 1
> 
> 						where ID = %s""",
> 
> 						(useros, browser, date, vID) )
> 
> 
> 
> 	con.commit()	#if we made it here, the transaction is complete
> 
> 
> 
> except:		#blind excepts aren't "good", but you get the idea
> 
> 			#ANY exception needs to rollback the above sequence
> 
> 	con.rollback()	#something failed, rollback the entire transaction
> 
> 	print "ERROR DURING hit counter update sequence"
> 

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 
"useros" and "browser" data... Is that what you really want -- or do you 
want to log ALL users that visit the page. "

If the same hostname  visits my webpage multiple times i only update the userOS, bwoswer, date information. 

What do you mean?

And also: why does the table 'visitors' ahs to have an auto increment column ID what for?