Re: inserting or updating appropriately

Path	csiph.com!v102.xanadu-bbs.net!xanadu-bbs.net!feeder.erje.net!eu.feeder.erje.net!eternal-september.org!feeder.eternal-september.org!news.eternal-september.org!.POSTED!not-for-mail
From	Νίκος Αλεξόπουλος <nikos.gr33k@gmail.com>
Newsgroups	comp.lang.python
Subject	Re: inserting or updating appropriately
Date	Tue, 08 Oct 2013 12:20:33 +0300
Organization	A noiseless patient Spider
Lines	57
Message-ID	<l30iov$1os$1@dont-email.me> (permalink)
References	<l30ifv$dd$1@dont-email.me>
Mime-Version	1.0
Content-Type	text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding	8bit
Injection-Date	Tue, 8 Oct 2013 09:20:31 +0000 (UTC)
Injection-Info	mx05.eternal-september.org; posting-host="8cd1be0de122bb1e01122753902ed596"; logging-data="1820"; mail-complaints-to="abuse@eternal-september.org"; posting-account="U2FsdGVkX18F2DloC+lPwBUgUxGHY7lo"
User-Agent	Mozilla/5.0 (Windows NT 6.2; WOW64; rv:24.0) Gecko/20100101 Thunderbird/24.0
In-Reply-To	<l30ifv$dd$1@dont-email.me>
Cancel-Lock	sha1:Q/B8yF9rQwwYl1AHobIAfprUN60=
Xref	csiph.com comp.lang.python:56375

Show key headers only | View raw

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

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