Τη Πέμπτη, 24 Ιανουαρίου 2013 10:43:59 μ.μ. UTC+2, ο χρήστης Dennis Lee Bieber
έγραψε:
> On Thu, 24 Jan 2013 03:04:46 -0800 (PST), Ferrous Cranus
>
> <nikos.gr...@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?
--
http://mail.python.org/mailman/listinfo/python-list
