inserting or updating appropriately
Νίκος Αλεξόπουλος
nikos.gr33k at gmail.com
Tue Oct 8 05:20:33 EDT 2013
Στις 8/10/2013 12:15 μμ, ο/η Νίκος Αλεξόπουλος έγραψε:
> Hello, i'am trying to insert a new record or update an existing one in
> case counterID(stands for the page's URL) and cookieID(random number) is
> the same:
>
> try:
> # if first time for webpage; create new record( primary key is
> automatic, hit is defaulted ), if page exists then update record
> cur.execute('''INSERT INTO counters (url) VALUES (%s) ON
> DUPLICATE KEY UPDATE hits = hits + 1''', page )
> # get the primary key value of the new added record
> cID = cur.lastrowid
>
> # if first time visitor on this page, create new record, if
> visitor exists then update record
> cur.execute('''INSERT INTO visitors (counterID, cookieID, host,
> city, useros, browser, ref, lastvisit) VALUES (%s, %s, %s, %s, %s, %s,
> %s, %s)
> ON DUPLICATE KEY UPDATE cookieID = %s, host =
> %s, city = %s, useros = %s, browser = %s, ref = %s, hits = hits + 1,
> lastvisit = %s
> WHERE counterID = %s and cookieID = %s''',
> (cID, cookieID, host, city, useros, browser,
> ref, lastvisit, cookieID, host, city, useros, browser, ref, lastvisit,
> cID, cookieID) )
> =============
>
> Error is: ProgrammingError(ProgrammingError(1064, "You have an error in
> your SQL syntax; check the manual that corresponds to your MySQL server
> version for the right syntax to use near 'WHERE counterID = 1 and
> cookieID = '3815'' at line 3"),)
>
> i notticed that if i remove the WHERE clause in the last execute it
> works but then its not updating properly.
>
> Can this happen in 1-statemnt with the ON DUPLICATE KEY INVOLVED WITHOUT
> BREAKING IT IN IN 2-STATEMNTS?
>
> THANKS.
Actually what i want is this effect in cur.execute statement:
# if first time visitor on this page, create new record, if visitor
exists then update record
cur.execute('''INSERT INTO visitors (counterID, cookieID, host, city,
useros, browser, ref, lastvisit) VALUES (%s, %s, %s, %s, %s, %s, %s, %s)''',
(cID, cookieID, host, city, useros, browser, ref, lastvisit)
cur.execute('''UPDATE visitors SET cookieID = %s, host = %s, city =
%s, useros = %s, browser = %s, ref = %s, hits = hits + 1, lastvisit = %s
WHERE counterID = %s and cookieID = %s''',
(cookieID, host, city, useros, browser, ref, lastvisit, cID,
cookieID) )
--
What is now proved was at first only imagined! & WebHost
<http://superhost.gr>
More information about the Python-list
mailing list