Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.lang.python > #37561 > unrolled thread

mysql solution

Back to article view | Back to comp.lang.python

Contents

  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 1 of 2  [1] 2  Next page →

#37561 — mysql solution

# 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

[toc] | [next] | [standalone]

#37564

On Thu, Jan 24, 2013 at 10:04 PM, Ferrous Cranus <nikos.gr33k@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

[toc] | [prev] | [next] | [standalone]

#37587

Τη Πέμπτη, 24 Ιανουαρίου 2013 1:16:51 μ.μ. UTC+2, ο χρήστης Chris Angelico έγραψε:
> On Thu, Jan 24, 2013 at 10:04 PM, Ferrous Cranus <nikos.gr33k@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?

[toc] | [prev] | [next] | [standalone]

#37594

On Fri, Jan 25, 2013 at 12:31 AM, Ferrous Cranus <nikos.gr33k@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

[toc] | [prev] | [next] | [standalone]

#37588

Τη Πέμπτη, 24 Ιανουαρίου 2013 1:16:51 μ.μ. UTC+2, ο χρήστης Chris Angelico έγραψε:
> On Thu, Jan 24, 2013 at 10:04 PM, Ferrous Cranus <nikos.gr33k@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?

[toc] | [prev] | [next] | [standalone]

#37566

Ferrous Cranus <nikos.gr33k@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.
lele@metapensiero.it  |                 -- Fortunato Depero, 1929.

[toc] | [prev] | [next] | [standalone]

#37576

Τη Πέμπτη, 24 Ιανουαρίου 2013 1:25:20 μ.μ. UTC+2, ο χρήστης Lele Gaifax έγραψε:
> Ferrous Cranus <nikos.gr33k@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.
> 
> lele@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'"

[toc] | [prev] | [next] | [standalone]

#37580

Ferrous Cranus <nikos.gr33k@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.
lele@metapensiero.it  |                 -- Fortunato Depero, 1929.

[toc] | [prev] | [next] | [standalone]

#37585

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?

[toc] | [prev] | [next] | [standalone]

#37589

Ferrous Cranus <nikos.gr33k@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.
lele@metapensiero.it  |                 -- Fortunato Depero, 1929.

[toc] | [prev] | [next] | [standalone]

#37592

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

[toc] | [prev] | [next] | [standalone]

#37593

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

[toc] | [prev] | [next] | [standalone]

#37597

Ferrous Cranus <nikos.gr33k@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

[toc] | [prev] | [next] | [standalone]

#37598

On Fri, Jan 25, 2013 at 2:19 AM, Duncan Booth
<duncan.booth@invalid.invalid> wrote:
> Ferrous Cranus <nikos.gr33k@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

[toc] | [prev] | [next] | [standalone]

#37601

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.
lele@metapensiero.it  |                 -- Fortunato Depero, 1929.

[toc] | [prev] | [next] | [standalone]

#37616

Τη Πέμπτη, 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.....

[toc] | [prev] | [next] | [standalone]

#37617

Τη Πέμπτη, 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.....

[toc] | [prev] | [next] | [standalone]

#37586

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?

[toc] | [prev] | [next] | [standalone]

#37578

Τη Πέμπτη, 24 Ιανουαρίου 2013 1:25:20 μ.μ. UTC+2, ο χρήστης Lele Gaifax έγραψε:
> Ferrous Cranus <nikos.gr33k@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.
> 
> lele@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'"

[toc] | [prev] | [next] | [standalone]

#37567

On Thu, Jan 24, 2013 at 10:25 PM, Lele Gaifax <lele@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

[toc] | [prev] | [next] | [standalone]

Page 1 of 2  [1] 2  Next page →

Back to top | Article view | comp.lang.python

csiph-web