mysql solution

Discussion in 'Python' started by Ferrous Cranus, Jan 24, 2013.

  1. # 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
     
    Ferrous Cranus, Jan 24, 2013
    #1
    1. Advertising

  2. On Thu, Jan 24, 2013 at 10:04 PM, Ferrous Cranus <> 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
     
    Chris Angelico, Jan 24, 2013
    #2
    1. Advertising

  3. Ferrous Cranus

    Lele Gaifax Guest

    Ferrous Cranus <> 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.
    | -- Fortunato Depero, 1929.
     
    Lele Gaifax, Jan 24, 2013
    #3
  4. On Thu, Jan 24, 2013 at 10:25 PM, Lele Gaifax <> 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
     
    Chris Angelico, Jan 24, 2013
    #4
  5. Τη Πέμπτη, 24 ΙανουαÏίου 2013 1:25:20 μ.μ. UTC+2, ο χÏήστης Lele Gaifax έγÏαψε:
    > Ferrous Cranus <> 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.
    >
    > | -- 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, Jan 24, 2013
    #5
  6. Τη Πέμπτη, 24 ΙανουαÏίου 2013 1:25:20 μ.μ. UTC+2, ο χÏήστης Lele Gaifax έγÏαψε:
    > Ferrous Cranus <> 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.
    >
    > | -- 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, Jan 24, 2013
    #6
  7. Ferrous Cranus

    Lele Gaifax Guest

    Ferrous Cranus <> 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.
    | -- Fortunato Depero, 1929.
     
    Lele Gaifax, Jan 24, 2013
    #7
  8. 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) )
    
    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)) 
    
    Can someone correct this please and explain?
     
    Ferrous Cranus, Jan 24, 2013
    #8
  9. 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) )
    
    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)) 
    
    Can someone correct this please and explain?
     
    Ferrous Cranus, Jan 24, 2013
    #9
  10. Τη Πέμπτη, 24 ΙανουαÏίου 2013 1:16:51 μ.μ. UTC+2, ο χÏήστης Chris Angelico έγÏαψε:
    > On Thu, Jan 24, 2013 at 10:04 PM, Ferrous Cranus <> 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?
     
    Ferrous Cranus, Jan 24, 2013
    #10
  11. Τη Πέμπτη, 24 ΙανουαÏίου 2013 1:16:51 μ.μ. UTC+2, ο χÏήστης Chris Angelico έγÏαψε:
    > On Thu, Jan 24, 2013 at 10:04 PM, Ferrous Cranus <> 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?
     
    Ferrous Cranus, Jan 24, 2013
    #11
  12. Ferrous Cranus

    Lele Gaifax Guest

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


    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.
    | -- Fortunato Depero, 1929.
     
    Lele Gaifax, Jan 24, 2013
    #12
  13. Τη Πέμπτη, 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, Jan 24, 2013
    #13
  14. Τη Πέμπτη, 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, Jan 24, 2013
    #14
  15. On Fri, Jan 25, 2013 at 12:31 AM, Ferrous Cranus <> 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 Íéêüëáïò Êïýñáò (whichGoogle 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
     
    Chris Angelico, Jan 24, 2013
    #15
  16. On Fri, Jan 25, 2013 at 2:19 AM, Duncan Booth
    <> wrote:
    > Ferrous Cranus <> 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
     
    Chris Angelico, Jan 24, 2013
    #16
  17. Ferrous Cranus

    Lele Gaifax Guest

    Duncan Booth <> 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.
    | -- Fortunato Depero, 1929.
     
    Lele Gaifax, Jan 24, 2013
    #17
  18. Τη Πέμπτη, 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) )
    
    Something is definately wrong here, its logic is not correct.....
     
    Ferrous Cranus, Jan 24, 2013
    #18
  19. Τη Πέμπτη, 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) )
    
    Something is definately wrong here, its logic is not correct.....
     
    Ferrous Cranus, Jan 24, 2013
    #19
  20. On Thu, 24 Jan 2013 03:04:46 -0800 (PST), Ferrous Cranus
    <> 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
    HTTP://wlfraed.home.netcom.com/
     
    Dennis Lee Bieber, Jan 24, 2013
    #20
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Andrew Francis
    Replies:
    0
    Views:
    426
    Andrew Francis
    Jun 28, 2006
  2. =?Utf-8?B?Y2FzaGRlc2ttYWM=?=

    Solution file not in the solution folder

    =?Utf-8?B?Y2FzaGRlc2ttYWM=?=, Sep 12, 2006, in forum: ASP .Net
    Replies:
    2
    Views:
    1,115
    Laurent Bugnion
    Sep 12, 2006
  3. , India
    Replies:
    17
    Views:
    1,068
    James Kanze
    Oct 1, 2007
  4. Replies:
    8
    Views:
    516
  5. email55555 email55555

    [SOLUTION] Ruby Quiz #14 LCD Numbers ( solution #2 )

    email55555 email55555, Jan 9, 2005, in forum: Ruby
    Replies:
    16
    Views:
    285
    David Tran
    Jan 10, 2005
Loading...

Share This Page