Re: mysql solution

Newsgroups	comp.lang.python
Date	2013-01-25 07:43 -0800
References	<88306c73-dfa2-44e1-ab0c-d90dba05be1c@googlegroups.com> <mailman.1012.1359060248.2939.python-list@python.org>
Subject	Re: mysql solution
From	Ferrous Cranus <nikos.gr33k@gmail.com>
Message-ID	<mailman.1043.1359128642.2939.python-list@python.org> (permalink)

Show all headers | View raw

Τη Πέμπτη, 24 Ιανουαρίου 2013 10:43:59 μ.μ. UTC+2, ο χρήστης Dennis Lee Bieber έγραψε:
> 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"
> 

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?

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

Thread

mysql solution Ferrous Cranus <nikos.gr33k@gmail.com> - 2013-01-24 03:04 -0800
  Re: mysql solution Chris Angelico <rosuav@gmail.com> - 2013-01-24 22:16 +1100
    Re: mysql solution Ferrous Cranus <nikos.gr33k@gmail.com> - 2013-01-24 05:31 -0800
      Re: mysql solution Chris Angelico <rosuav@gmail.com> - 2013-01-25 01:46 +1100
    Re: mysql solution Ferrous Cranus <nikos.gr33k@gmail.com> - 2013-01-24 05:31 -0800
  Re: mysql solution Lele Gaifax <lele@metapensiero.it> - 2013-01-24 12:25 +0100
    Re: mysql solution Ferrous Cranus <nikos.gr33k@gmail.com> - 2013-01-24 04:01 -0800
      Re: mysql solution Lele Gaifax <lele@metapensiero.it> - 2013-01-24 13:22 +0100
        Re: mysql solution Ferrous Cranus <nikos.gr33k@gmail.com> - 2013-01-24 05:24 -0800
          Re: mysql solution Lele Gaifax <lele@metapensiero.it> - 2013-01-24 14:37 +0100
            Re: mysql solution Ferrous Cranus <nikos.gr33k@gmail.com> - 2013-01-24 06:35 -0800
            Re: mysql solution Ferrous Cranus <nikos.gr33k@gmail.com> - 2013-01-24 06:35 -0800
              Re: mysql solution Duncan Booth <duncan.booth@invalid.invalid> - 2013-01-24 15:19 +0000
                Re: mysql solution Chris Angelico <rosuav@gmail.com> - 2013-01-25 02:27 +1100
                Re: mysql solution Lele Gaifax <lele@metapensiero.it> - 2013-01-24 16:39 +0100
                Re: mysql solution Ferrous Cranus <nikos.gr33k@gmail.com> - 2013-01-24 10:22 -0800
                Re: mysql solution Ferrous Cranus <nikos.gr33k@gmail.com> - 2013-01-24 10:22 -0800
        Re: mysql solution Ferrous Cranus <nikos.gr33k@gmail.com> - 2013-01-24 05:24 -0800
    Re: mysql solution Ferrous Cranus <nikos.gr33k@gmail.com> - 2013-01-24 04:01 -0800
  Re: mysql solution Chris Angelico <rosuav@gmail.com> - 2013-01-24 22:29 +1100
  Re: mysql solution Dennis Lee Bieber <wlfraed@ix.netcom.com> - 2013-01-24 15:43 -0500
    Re: mysql solution Ferrous Cranus <nikos.gr33k@gmail.com> - 2013-01-25 07:43 -0800
      Re: mysql solution Dennis Lee Bieber <wlfraed@ix.netcom.com> - 2013-01-25 16:56 -0500
        Re: mysql solution Ferrous Cranus <nikos.gr33k@gmail.com> - 2013-01-26 02:35 -0800
        Re: mysql solution Ferrous Cranus <nikos.gr33k@gmail.com> - 2013-01-26 02:35 -0800
    Re: mysql solution Ferrous Cranus <nikos.gr33k@gmail.com> - 2013-01-25 07:43 -0800

csiph-web