I have decided to take your advice.
I wasn't able to fit those 'lists' of mine into MySQL's varchar()
datatype after converting them to long strings and that sads me.
My implementation is like the following.
I do not use an extra table of downlaods that i asoociate with table
visitors with a foreing key but decided to add an additional 'download'
column into the existant visitors table:
Here it is:
=================================================================================================================
# ~ DATABASE INSERTS ~
=================================================================================================================
try:
# if first time for webpage; create new record( primary key is
automatic, hit is defaulted ), if page exists then update record
cur.execute('''INSERT INTO counters (url) VALUES (%s) ON DUPLICATE KEY
UPDATE hits = hits + 1''', page )
# get the primary key value of the new added record
cID = cur.lastrowid
# add this visitor entry into database (hits && downloads are
defaulted)
cur.execute('''INSERT INTO visitors (counterID, refs, host, city,
useros, browser, visits) VALUES (%s, %s, %s, %s, %s, %s, %s)''',
(cID, ref, host, city, useros,
browser, lastvisit) )
con.commit()
except pymysql.ProgrammingError as e:
print( repr(e) )
con.rollback()
sys.exit(0)
=================================================================================================================
=================================================================================================================
# ~ Presentation Time
=================================================================================================================
def coalesce( data ):
newdata = []
seen = {}
for host, refs, city, useros, browser, visits, hits, downloads
in data:
# Here i have to decide how to group the rows together
# I want an html row for every unique combination of (host) and that
hits should be summed together
key = host
if key not in seen:
newdata.append( [ host, [refs], city, useros, browser, [visits],
hits, [downloads] ] )
seen[key] = len( newdata ) - 1 # Save
index (for 'newdata') of this row
else: # This row is a duplicate row with a different referrer &&
visit time && torrent download
rowindex = seen[key]
newdata[rowindex][1].append( refs )
newdata[rowindex][5].append( visits )
newdata[rowindex][6] += hits
newdata[rowindex][7].append( downloads )
return newdata
cur.execute( '''SELECT host, refs, city, useros, browser, visits, hits,
downloads FROM visitors
WHERE counterID = (SELECT ID FROM counters WHERE url = %s) ORDER BY
visits DESC''', page )
data = cur.fetchall()
newdata = coalesce( data )
for row in newdata:
(host, refs, city, useros, browser, visits, hits, downloads) =
row
# Note that 'refs' && 'visits' && 'downloads' are now lists
print( '<tr>' )
print( '<td><center><b><font color=white> %s </td>' % host )
print( '<td><select>' )
for ref in refs:
print( '<option> %s </option>' % ref )
print( '</select></td>' )
for item in (city, useros, browser):
print( '<td><center><b><font color=cyan> %s </td>' %
item )
print( '<td><select>' )
for visit in visits:
visittime = visit.strftime('%A %e %b, %H:%M')
print( '<option> %s </option>' % visittime )
print( '</select></td>' )
print( '<td><center><b><font color=yellow size=4> %s </td>' %
hits )
# populate torrent list
torrents = []
for download in downloads:
if download:
torrents.append( download )
# present visitor's movie picks if any
if torrents:
print( '<td><select>' )
for torrent in torrents:
print( '<option> %s </option>' % torrent )
print( '</select></td>' )
else:
print( '<td><center><b><font color=orange> Δεν πραγματοποίηθηκαν
ακόμη! </td>' )
break
print( '</tr>' )
sys.exit(0)
=================================================================================================================
At least my webpage http://superhost.gr is working now, but i look into
by lookinto into phpmyadmin whats into the database and what is being
presented somethign is worng.
This is a screenshot of my database visit sicne last night which i
decided to use your method: http://i.imgur.com/yquXO7u.png
and this is what is being presented:
http://superhost.gr/?show=log&page=index.html
In my database they are clearly shown lots of entries with counterID = 1
( 1 is related to index.html) but when i ask them to be presented it
only shows 1 hostname.
Where is the rest hostnames having counterID == 1?
Also the counterID values should have been of 1 or 2 or 3 but in
screenshot i see values of 6, 1-, 11, 12
Is there something wrong with the code i provided?
I decided to use your logic and i ask for your help
--
https://mail.python.org/mailman/listinfo/python-list
