On Thu, 24 Jan 2013 03:04:46 -0800 (PST), Ferrous Cranus
<
[email protected]> declaimed the following in
gmane.comp.python.general:
# insert new page record in table counters or update it if already exists
cursor.execute( '''INSERT INTO counters(page, hits) VALUES(%s, %s)
ON DUPLICATE KEY UPDATE hits = hits + 1''', (htmlpage, 1) )
print ( "Query Error: ", sys.exc_info()[1].excepinfo()[2] )
# update existing visitor record if same pin and same host found
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))
print ( "Error %d: %s" % (e.args[0], e.args[1]) )
# insert new visitor record if above update did not affect a row
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"
