Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]
Groups > comp.lang.python > #37561 > unrolled thread
| Started by | Ferrous Cranus <nikos.gr33k@gmail.com> |
|---|---|
| First post | 2013-01-24 03:04 -0800 |
| Last post | 2013-01-25 07:43 -0800 |
| Articles | 6 on this page of 26 — 5 participants |
Back to article view | Back to comp.lang.python
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
Page 2 of 2 — ← Prev page 1 [2]
| From | Dennis Lee Bieber <wlfraed@ix.netcom.com> |
|---|---|
| Date | 2013-01-24 15:43 -0500 |
| Message-ID | <mailman.1012.1359060248.2939.python-list@python.org> |
| In reply to | #37561 |
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/
[toc] | [prev] | [next] | [standalone]
| From | Ferrous Cranus <nikos.gr33k@gmail.com> |
|---|---|
| Date | 2013-01-25 07:43 -0800 |
| Message-ID | <b96ad10a-783a-416e-adc1-abdc404c1e92@googlegroups.com> |
| In reply to | #37629 |
Τη Πέμπτη, 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?
[toc] | [prev] | [next] | [standalone]
| From | Dennis Lee Bieber <wlfraed@ix.netcom.com> |
|---|---|
| Date | 2013-01-25 16:56 -0500 |
| Message-ID | <mailman.1056.1359151015.2939.python-list@python.org> |
| In reply to | #37675 |
On Fri, 25 Jan 2013 07:43:53 -0800 (PST), Ferrous Cranus
<nikos.gr33k@gmail.com> declaimed the following in
gmane.comp.python.general:
>
> 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.
>
And what use will you make of that? Consider that "hostname" is not
something unique to a user. Heck, unless on pays for a fixed domain, a
DHCP system "hostname" can change from one user to another every day
(common DHCP "lease" is 24 hours; every 24 hours a DSL connection
renegotiates an IP number, and many ISPs embed that IP number into a DNS
name).
E:\UserData\Wulfraed\My Documents>tracert 76.249.16.247
Tracing route to adsl-76-249-16-247.dsl.klmzmi.sbcglobal.net
[76.249.16.247]
over a maximum of 30 hops:
1 2 ms <1 ms <1 ms 192.168.2.1
2 1 ms 1 ms 1 ms
adsl-76-249-16-247.dsl.klmzmi.sbcglobal.net [76.249.16.247]
Trace complete.
If I reboot my DSL modem, I'll probably get a different IP number,
and hence a different host name (lovely, I pay for Earthlink DSL in
Grand Rapids, but they apparently are hosted on old AT&T [SBC] servers
located in Kalamazoo). Note how the IP number is in the name (I checked
my DSL modem to find the IP number assigned to me).
Also, with NAT, I could start up my two laptops and my Nook HD+
browsers -- giving three different operating systems, three browser
versions, etc. and all will appear to be from the same hostname...
To me, the result is meaningless -- it only shows the most recent
access but no history.
> What do you mean?
>
> And also: why does the table 'visitors' ahs to have an auto increment column ID what for?
Habbit -- I /always/ create an auto-increment primary key for a
table; after all, maybe next year you expand the system and now need a
way to reference the older table. Better to have a unique primary key
that is not dependent upon data fields available for use.
--
Wulfraed Dennis Lee Bieber AF6VN
wlfraed@ix.netcom.com HTTP://wlfraed.home.netcom.com/
[toc] | [prev] | [next] | [standalone]
| From | Ferrous Cranus <nikos.gr33k@gmail.com> |
|---|---|
| Date | 2013-01-26 02:35 -0800 |
| Message-ID | <3f50eac6-ea27-46d7-be6b-5efb7f435b07@googlegroups.com> |
| In reply to | #37695 |
Τη Παρασκευή, 25 Ιανουαρίου 2013 11:56:44 μ.μ. UTC+2, ο χρήστης Dennis Lee Bieber έγραψε:
=====================
#find the visitor record for the (saved) cID and current host
cur.execute('''SELECT * 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) )
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, hits = hits + 1, lastvisit = %s
WHERE counterID = %s and host = %s''', (useros, browser, date, vID, host) )
=======================================
Instead of the above logic which you provided and it works as expected, wouldn't be easier to just:
Try to update the 'visitors' record, for that 'page' and that 'host'
if update fails(doesnt return any data), then we insert a new record entry.
We dont have to check 'SELECT * FROM visitors WHERE counterID = %s and host = %s' first, this one less cursor.execute.
[toc] | [prev] | [next] | [standalone]
| From | Ferrous Cranus <nikos.gr33k@gmail.com> |
|---|---|
| Date | 2013-01-26 02:35 -0800 |
| Message-ID | <mailman.1071.1359199323.2939.python-list@python.org> |
| In reply to | #37695 |
Τη Παρασκευή, 25 Ιανουαρίου 2013 11:56:44 μ.μ. UTC+2, ο χρήστης Dennis Lee Bieber έγραψε:
=====================
#find the visitor record for the (saved) cID and current host
cur.execute('''SELECT * 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) )
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, hits = hits + 1, lastvisit = %s
WHERE counterID = %s and host = %s''', (useros, browser, date, vID, host) )
=======================================
Instead of the above logic which you provided and it works as expected, wouldn't be easier to just:
Try to update the 'visitors' record, for that 'page' and that 'host'
if update fails(doesnt return any data), then we insert a new record entry.
We dont have to check 'SELECT * FROM visitors WHERE counterID = %s and host = %s' first, this one less cursor.execute.
[toc] | [prev] | [next] | [standalone]
| From | Ferrous Cranus <nikos.gr33k@gmail.com> |
|---|---|
| Date | 2013-01-25 07:43 -0800 |
| Message-ID | <mailman.1043.1359128642.2939.python-list@python.org> |
| In reply to | #37629 |
Τη Πέμπτη, 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?
[toc] | [prev] | [standalone]
Page 2 of 2 — ← Prev page 1 [2]
Back to top | Article view | comp.lang.python
csiph-web