Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.lang.python > #56373

inserting or updating appropriately

Show all headers | View raw

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.

Back to comp.lang.python | Previous | Next — Next in thread | Find similar | Unroll thread

Thread

inserting or updating appropriately Νίκος Αλεξόπουλος <nikos.gr33k@gmail.com> - 2013-10-08 12:15 +0300
  Re: inserting or updating appropriately Νίκος Αλεξόπουλος <nikos.gr33k@gmail.com> - 2013-10-08 12:20 +0300
  Re: inserting or updating appropriately Ben Finney <ben+python@benfinney.id.au> - 2013-10-08 20:29 +1100

csiph-web