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

Groups > comp.lang.python > #56373 > unrolled thread

inserting or updating appropriately

Back to article view | Back to comp.lang.python

Contents

  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

#56373 — inserting or updating appropriately

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.

[toc] | [next] | [standalone]

#56375

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

[toc] | [prev] | [next] | [standalone]

#56377

Νίκος Αλεξόπουλος <nikos.gr33k@gmail.com> writes:

> 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"),)

This is an error from the database server. It has nothing to do with
Python, as you already know from previous discussions here.

Please do not ask questions about usage of the database server here any
more.

-- 
 \                            “Holy knit one purl two, Batman!” —Robin |
  `\                                                                   |
_o__)                                                                  |
Ben Finney

[toc] | [prev] | [standalone]

Back to top | Article view | comp.lang.python

csiph-web