mysql solution

[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

 

[Chris Angelico]

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

 

[Lele Gaifax]

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.

 

[Chris Angelico]

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

 

[Ferrous Cranus]

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

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.

(e-mail address removed) | -- 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'"

 

[Ferrous Cranus]

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

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.

(e-mail address removed) | -- 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'"

 

[Lele Gaifax]

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

 

[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.

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

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.

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

Can someone correct this please and explain?

 

[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.

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

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.

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

Can someone correct this please and explain?

 

[Ferrous Cranus]

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

Secondly, why go back to that handle?

 

[Ferrous Cranus]

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

Secondly, why go back to that handle?

 

[Lele Gaifax]

The following statement fails.

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

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.

 

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

 

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

 

[Chris Angelico]

Ôç ÐÝìðôç, 24 Éáíïõáñßïõ 2013 1:16:51ì.ì. UTC+2, ï ÷ñÞóôçò Chris Angelico Ýãñáøå:

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

 

[Chris Angelico]

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

 

[Lele Gaifax]

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.

 

[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:

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

Something is definately wrong here, its logic is not correct.....

 

[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:

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

Something is definately wrong here, its logic is not correct.....

 

[Dennis Lee Bieber]

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