inserting or updating appropriately

[Νίκος Αλεξόπουλος]

Στις 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>