subject:"mysql solution"

Re: mysql solution

2013-01-26 Thread Ferrous Cranus

Τη Παρασκευή, 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()#cIDhost 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.
-- 
http://mail.python.org/mailman/listinfo/python-list

Re: mysql solution

2013-01-25 Thread Ferrous Cranus

Τη Πέμπτη, 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()   #cIDhost 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) )

mysql solution

2013-01-24 Thread Ferrous Cranus

# 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) )

==

I;am now convinced the hash solution isn't reversible and also isn't unique.
I'am trying the database oriented solution.

pin column = 5-digit integer Primary Key.

When i'am inserting a new record to table counters, a sequenced number is 
crated as pin. Thats works ok.


But when i try to Update or Insert into the visitors table the 'pin' comunn is 
needed to to identify the rrecord for which iam trying to update like here:


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))


how is the mysql statement is going to find the 'pin' to update the specific 
record.
And also in here:


if cursor.rowcount == 0:
cursor.execute( '''INSERT INTO visitors(pin, hits, host, 
useros, browser, date) VALUES(%s, %s, %s, %s, %s, %s)''', (pin, 1, host, 
useros, browser, date) )


'pin' column's value is also need to make insert


-- 
http://mail.python.org/mailman/listinfo/python-list

Re: mysql solution

2013-01-24 Thread Chris Angelico

On Thu, Jan 24, 2013 at 10:04 PM, Ferrous Cranus nikos.gr...@gmail.com wrote:
 I;am now convinced the hash solution isn't reversible and also isn't unique.
 I'am trying the database oriented solution.

Glad you've listened to at least some of what you've been told. But if
you want to be taken seriously on this list, I recommend going back to
your previous name of Νικόλαος Κούρας (which Google Translate tells me
transliterates as Nicholas Kouri), apologizing for trolling, and being
VERY careful to be respectful. I suspect a number of the list's best
posters have already killfiled you.

ChrisA
-- 
http://mail.python.org/mailman/listinfo/python-list

Re: mysql solution

2013-01-24 Thread Lele Gaifax

Ferrous Cranus nikos.gr...@gmail.com writes:

 I;am now convinced the hash solution isn't reversible and also isn't
 unique.

Great!

 how is the mysql statement is going to find the 'pin' to update the
 specific record.

The simplest way is to execute a SELECT just after the insertion, doing
a

  SELECT pin FROM counters WHERE page = %s

I don't use MySQL, so I can't say if it supports INSERT ... RETURNING ...
SQL syntax: should it, then you could insert the data and fetch
the pin in one shot, with something like

  INSERT INTO counters (page, hits) VALUES (%s, %s) RETURNING (pin)

ciao, lele.
--
nickname: Lele Gaifax | Quando vivrò di quello che ho pensato ieri
real: Emanuele Gaifas | comincerò ad aver paura di chi mi copia.
l...@metapensiero.it  | -- Fortunato Depero, 1929.

-- 
http://mail.python.org/mailman/listinfo/python-list

Re: mysql solution

2013-01-24 Thread Chris Angelico

On Thu, Jan 24, 2013 at 10:25 PM, Lele Gaifax l...@metapensiero.it wrote:
 The simplest way is to execute a SELECT just after the insertion, doing
 a

   SELECT pin FROM counters WHERE page = %s

 I don't use MySQL, so I can't say if it supports INSERT ... RETURNING ...
 SQL syntax: should it, then you could insert the data and fetch
 the pin in one shot, with something like

   INSERT INTO counters (page, hits) VALUES (%s, %s) RETURNING (pin)

AFAIK it doesn't, but if pin is an AUTO_INCREMENT primary key, you can
retrieve the ID of the newly inserted record. It's not nearly as
flexible as INSERT... RETURNING, but it covers the most common use
case.

ChrisA
-- 
http://mail.python.org/mailman/listinfo/python-list

Re: mysql solution

2013-01-24 Thread Ferrous Cranus

Τη Πέμπτη, 24 Ιανουαρίου 2013 1:25:20 μ.μ. UTC+2, ο χρήστης Lele Gaifax έγραψε:
 Ferrous Cranus nikos.gr...@gmail.com writes:
 
 
 
  I;am now convinced the hash solution isn't reversible and also isn't
 
  unique.
 
 
 
 Great!
 
 
 
  how is the mysql statement is going to find the 'pin' to update the
 
  specific record.
 
 
 
 The simplest way is to execute a SELECT just after the insertion, doing
 
 a
 
 
 
   SELECT pin FROM counters WHERE page = %s
 
 
 
 I don't use MySQL, so I can't say if it supports INSERT ... RETURNING ...
 
 SQL syntax: should it, then you could insert the data and fetch
 
 the pin in one shot, with something like
 
 
 
   INSERT INTO counters (page, hits) VALUES (%s, %s) RETURNING (pin)
 
 
 
 ciao, lele.
 
 --
 
 nickname: Lele Gaifax | Quando vivrò di quello che ho pensato ieri
 
 real: Emanuele Gaifas | comincerò ad aver paura di chi mi copia.
 
 l...@metapensiero.it  | -- Fortunato Depero, 1929.

I just tried this statement:

==
cursor.execute( '''INSERT INTO counters(page, hits) VALUES(%s, %s) RETURNING 
(pin) 
ON DUPLICATE 
KEY UPDATE hits = hits + 1''', (htmlpage, 1) )
except MySQLdb.Error, e:
print ( Query Error: , sys.exc_info()[1].excepinfo()[2] )
==

and the error python tells me is:

type 'exceptions.AttributeError': 'ProgrammingError' object has no attribute 
'excepinfo' 
  args = ('ProgrammingError' object has no attribute 'excepinfo',) 
  message = 'ProgrammingError' object has no attribute 'excepinfo'
-- 
http://mail.python.org/mailman/listinfo/python-list

Re: mysql solution

2013-01-24 Thread Lele Gaifax

Ferrous Cranus nikos.gr...@gmail.com writes:

 Τη Πέμπτη, 24 Ιανουαρίου 2013 1:25:20 μ.μ. UTC+2, ο χρήστης Lele Gaifax 
 έγραψε:

Please, trim your response messages, cutting away useless details.


 I just tried this statement:

 ==
 cursor.execute( '''INSERT INTO counters(page, hits) VALUES(%s, %s) RETURNING 
 (pin) 
   ON DUPLICATE 
 KEY UPDATE hits = hits + 1''', (htmlpage, 1) )
   except MySQLdb.Error, e:
   print ( Query Error: , sys.exc_info()[1].excepinfo()[2] )
 ==

 and the error python tells me is:

 type 'exceptions.AttributeError': 'ProgrammingError' object has no 
 attribute 'excepinfo' 
   args = ('ProgrammingError' object has no attribute 'excepinfo',) 
   message = 'ProgrammingError' object has no attribute 'excepinfo'

As the error message says, you have a typo in your exception handler.

I suggest using the logging[1] module to print out such information, as it
expose a bunch of handy methods that make it easier to print the
exception, for example:

  ...
  except MySQLdb.Error:
logging.error('Query Error!', exc_info=True)

or even

  ...
  except MySQLdb.Error:
logging.exception('Query Error!')

ciao, lele.

[1] http://docs.python.org/2.7/library/logging.html#module-logging
-- 
nickname: Lele Gaifax | Quando vivrò di quello che ho pensato ieri
real: Emanuele Gaifas | comincerò ad aver paura di chi mi copia.
l...@metapensiero.it  | -- Fortunato Depero, 1929.

-- 
http://mail.python.org/mailman/listinfo/python-list

Re: mysql solution

2013-01-24 Thread Ferrous Cranus

column 'pin' is an 5-digit integer auto_increment primary key.

What i want is to insert a new record or update the existing one, if 'pin' 
column's value exist. The following statement fails.

[code]
cursor.execute( '''INSERT INTO counters(page, hits) VALUES(%s, %s) RETURNING 
(pin) 
ON DUPLICATE KEY UPDATE hits = hits + 1''', (htmlpage, 
1) )
[/code] 

Also except from the inserting/updating job, i also need 'pin' colum's value to 
be extracted from the above statement so to be used to subsequent statement 
like the following. This is not happening, hence the following statement have 
no way to find 'pin' column's value which is to be used as a parameter to it.

[code]
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)) 
[/code]

Can someone correct this please and explain?
-- 
http://mail.python.org/mailman/listinfo/python-list

Re: mysql solution

2013-01-24 Thread Ferrous Cranus

Τη Πέμπτη, 24 Ιανουαρίου 2013 1:16:51 μ.μ. UTC+2, ο χρήστης Chris Angelico 
έγραψε:
 On Thu, Jan 24, 2013 at 10:04 PM, Ferrous Cranus nikos.gr...@gmail.com 
 wrote:
 
  I;am now convinced the hash solution isn't reversible and also isn't unique.
 
  I'am trying the database oriented solution.
 
 
 
 Glad you've listened to at least some of what you've been told. But if
 
 you want to be taken seriously on this list, I recommend going back to
 
 your previous name of Νικόλαος Κούρας (which Google Translate tells me
 
 transliterates as Nicholas Kouri), apologizing for trolling, and being
 
 VERY careful to be respectful. I suspect a number of the list's best
 
 posters have already killfiled you.

First of all i'am not trolling, it looks like i'am trolling because i persist 
on my way of handling a problem.

Secondly, why go back to that handle?
-- 
http://mail.python.org/mailman/listinfo/python-list

Re: mysql solution

2013-01-24 Thread Lele Gaifax

Ferrous Cranus nikos.gr...@gmail.com writes:

 The following statement fails.

 [code]
 cursor.execute( '''INSERT INTO counters(page, hits) VALUES(%s, %s) RETURNING 
 (pin) 
 ON DUPLICATE KEY UPDATE hits = hits + 1''', 
 (htmlpage, 1) )
 [/code] 

How? What's the error message/traceback?

If, as Chris said, MySQL does not support the “RETURNING” syntax, you
cannot use that. I gave two different solutions in my previous message,
did you try the “simplest” one?

ciao, lele.
-- 
nickname: Lele Gaifax | Quando vivrò di quello che ho pensato ieri
real: Emanuele Gaifas | comincerò ad aver paura di chi mi copia.
l...@metapensiero.it  | -- Fortunato Depero, 1929.

-- 
http://mail.python.org/mailman/listinfo/python-list

Re: mysql solution

2013-01-24 Thread Ferrous Cranus

Τη Πέμπτη, 24 Ιανουαρίου 2013 3:37:24 μ.μ. UTC+2, ο χρήστης Lele Gaifax έγραψε:

 How? What's the error message/traceback?

REURNING is not a correct mysql syntax thats why it produces errors.

 If, as Chris said, MySQL does not support the “RETURNING” syntax, you
 
 cannot use that. I gave two different solutions in my previous message,
 
 did you try the “simplest” one?

SELECT pin FROM counters WHERE page = %s 

I can do that but then i have to use that pin column's value in my next 
statement.

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)) 
-- 
http://mail.python.org/mailman/listinfo/python-list

On Fri, Jan 25, 2013 at 12:31 AM, Ferrous Cranus nikos.gr...@gmail.com wrote:
Τη Πέμπτη, 24 Ιανουαρίου 2013 1:16:51 μ.μ. UTC+2, ο χρήστης Chris Angelico
έγραψε:
Glad you've listened to at least some of what you've been told. But if
you want to be taken seriously on this list, I recommend going back to
your previous name of Νικόλαος Κούρας (which Google Translate tells me
transliterates as Nicholas Kouri), apologizing for trolling, and being
VERY careful to be respectful. I suspect a number of the list's best
posters have already killfiled you.

First of all i'am not trolling, it looks like i'am trolling because i persist
on my way of handling a problem.

Then why do you use, as your name, something which everyone who tries
a quick web search will see is the name of a category of troll? And,
what's more, a category which clearly includes you?

Python believes in duck typing. Instead of writing a function that
expects a File object, Python tends toward writing functions that
expect an object that can be given data to write() out. Or, instead of
looking for an integer, Python code will look for something that can
be added to 5. In the same way, we on this list do not ask are you a
troll. We ask does your behaviour match that of a troll. You are
treated as a troll because you act like one.

Secondly, why go back to that handle?

That said, though, carrying the name of a troll doesn't help. Using
your real name is the best way to get started. If you want to win
respect, you want to win it for yourself, not for some strange title.
(There are exceptions to that principle. Some people on the list don't
use their names, and I'm on another list where one of the regular
posters freely admits that the name John Spartan isn't actually his.
But in general, you should use your real name.)

Among geeks (and this list/newsgroup is full of them), respect is
everything. You earn it, you give it. The best ways to earn respect
are to give respect and to contribute to the community. Contributing
is fairly obvious; answering questions, helping out, submitting
patches, triaging bugs, reviewing and confirming bug reports. Stuff
that takes time and benefits other people. The top dogs in a geeky
community are usually the ones who give the most time. I've no idea
how many hours Guido puts into Python, but it'll be rather a lot.

Giving respect is a little harder to define, but just as important.
The main thing to remember is that we, here, helping you, are
volunteers. Nobody is paying us to solve your problems, especially not
you yourself. Demanding that we solve your problems is NOT respectful.
Offering us interesting problems (which we enjoy), following up
courteously, helping to maintain the community's standards (even in
supposedly-trivial matters like bottom-posting), and doing your own
work before asking for help, ARE. I recently dropped someone a private
note thanking him for the way he phrased his question, because it made
for a very interesting little puzzle, and he'd clearly put work into
it. It was a pleasure to help him, cliche though that sound. He was
respectful of the time people would put in, and afterward of the time
they had put in, and thus he won respect.

Ferrous/Nicholas, you are currently looking like that very worst thing
on a mailing list: an open-ended time sink. You are looking like
you'll cost huge numbers of smart-person-hours (that's like man-hours
but not gender specific, or like programmer-hours but less mythical)
with little or no benefit to the community. Continue down this path
and you will find yourself with nobody to talk to, as everyone will
decide that the best use of time is to delete your posts unread.

ChrisA
--
http://mail.python.org/mailman/listinfo/python-list

Re: mysql solution

2013-01-24 Thread Duncan Booth

Ferrous Cranus nikos.gr...@gmail.com wrote:

 I can do that but then i have to use that pin column's value in my
 next statement. 
 
 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)) 

I'm not MySQL expert, but something like this might work:

cursor.execute('''UPDATE visitors,counter 
SET visitors.hits=visitors.hits+1, visitors.useros=%s,
visitors.browser =%s, visitors.date=%s
WHERE visitors.pin=counter.pin AND counter.page = %s 
AND visitors.host=%s''',
   (useros, browser, date, page, host))

-- 
Duncan Booth http://kupuguy.blogspot.com
-- 
http://mail.python.org/mailman/listinfo/python-list

Re: mysql solution

2013-01-24 Thread Chris Angelico

On Fri, Jan 25, 2013 at 2:19 AM, Duncan Booth
duncan.booth@invalid.invalid wrote:
 Ferrous Cranus nikos.gr...@gmail.com wrote:

 I can do that but then i have to use that pin column's value in my
 next statement.

 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))

 I'm not MySQL expert, but something like this might work:

 cursor.execute('''UPDATE visitors,counter
 SET visitors.hits=visitors.hits+1, visitors.useros=%s,
 visitors.browser =%s, visitors.date=%s
 WHERE visitors.pin=counter.pin AND counter.page = %s
 AND visitors.host=%s''',
(useros, browser, date, page, host))

Not sure that that works. This should, though:

UPDATE visitors SET hits=hits+1,blah,blah WHERE visitors.pin=(SELECT
pin FROM counter WHERE page=%s)

I prefer not to mention a table for updating if it's not actually being updated.

ChrisA
-- 
http://mail.python.org/mailman/listinfo/python-list

Re: mysql solution

2013-01-24 Thread Lele Gaifax

Duncan Booth duncan.booth@invalid.invalid writes:

 I'm not MySQL expert, but something like this might work:

 cursor.execute('''UPDATE visitors,counter 
 SET visitors.hits=visitors.hits+1, visitors.useros=%s,
 visitors.browser =%s, visitors.date=%s
 WHERE visitors.pin=counter.pin AND counter.page = %s 
 AND visitors.host=%s''',
(useros, browser, date, page, host))

I stopped surprising at MySQL syntax eons ago, so if that works... great!

Otherwise I would write the equivalent statement with a more standard
syntax (for whatever meaning of standard in the SQL world :-) as:

UPDATE visitors
   SET visitors.hits=visitors.hits+1, 
   visitors.useros=%s,
   visitors.browser=%s, 
   visitors.date=%s
WHERE visitors.pin=(SELECT counters.pin
FROM counters
WHERE counters.page=%s)
  AND visitors.host=%s

But I wonder about the logic here: why are you storing the useros,
browser and date in a table where the primary key seems to be
(pin, host)? I mean, what happens if a user visits the same page
twice, first with Firefox and then with Chrome?

hope this helps,
ciao, lele.
-- 
nickname: Lele Gaifax | Quando vivrò di quello che ho pensato ieri
real: Emanuele Gaifas | comincerò ad aver paura di chi mi copia.
l...@metapensiero.it  | -- Fortunato Depero, 1929.

-- 
http://mail.python.org/mailman/listinfo/python-list

Re: mysql solution

2013-01-24 Thread Ferrous Cranus

Τη Πέμπτη, 24 Ιανουαρίου 2013 5:39:54 μ.μ. UTC+2, ο χρήστης Lele Gaifax έγραψε:

 UPDATE visitors
 
SET visitors.hits=visitors.hits+1, 
 
visitors.useros=%s,
 
visitors.browser=%s, 
 
visitors.date=%s
 
 WHERE visitors.pin=(SELECT counters.pin
 
 FROM counters
 
 WHERE counters.page=%s)
 
   AND visitors.host=%s
 
 
 
 But I wonder about the logic here: why are you storing the useros,
 
 browser and date in a table where the primary key seems to be
 
 (pin, host)? I mean, what happens if a user visits the same page
 
 twice, first with Firefox and then with Chrome?


it doesn't work, it creates new entries on every webpage visit instead of 
updating.

this is what i have up until now:

[code]
# 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 ( Error %d: %s % (e.args[0], e.args[1]) )

# 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 id=(SELECT id FROM 
counters WHERE page=%s) AND host=%s''',

(useros, browser, date, htmlpage, 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) )
[/code]

Something is definately wrong here, its logic is not correct.
-- 
http://mail.python.org/mailman/listinfo/python-list

Re: mysql solution

Re: mysql solution

mysql solution

Re: mysql solution

Re: mysql solution

Re: mysql solution

Re: mysql solution

Re: mysql solution

Re: mysql solution

Re: mysql solution

Re: mysql solution

Re: mysql solution

Re: mysql solution

Re: mysql solution

Re: mysql solution

Re: mysql solution

Re: mysql solution

17 matches

Site Navigation

Mail list logo

Footer information