Στις 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>
--
https://mail.python.org/mailman/listinfo/python-list
