Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.lang.python > #37629

Re: mysql solution

Show all headers | View raw

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/

Back to comp.lang.python | Previous | Next — Previous in thread | Next 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