Path: csiph.com!v102.xanadu-bbs.net!xanadu-bbs.net!feeder.erje.net!eu.feeder.erje.net!newsfeed.freenet.ag!news2.euro.net!newsgate.cistron.nl!newsgate.news.xs4all.nl!post.news.xs4all.nl!not-for-mail
To: python-list@python.org
From: Dennis Lee Bieber <wlfraed@ix.netcom.com>
Subject: Re: mysql solution
Date: Thu, 24 Jan 2013 15:43:59 -0500
Organization: > Bestiaria Support Staff <
References: <88306c73-dfa2-44e1-ab0c-d90dba05be1c@googlegroups.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Precedence: list
Newsgroups: comp.lang.python
Message-ID: <mailman.1012.1359060248.2939.python-list@python.org>
Lines: 119
NNTP-Posting-Host: 2001:888:2000:d::a6
Xref: csiph.com comp.lang.python:37629

On Thu, 24 Jan 2013 03:04:46 -0800 (PST), Ferrous Cranus
<nikos.gr33k@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"


	
-- 
	Wulfraed                 Dennis Lee Bieber         AF6VN
        wlfraed@ix.netcom.com    HTTP://wlfraed.home.netcom.com/