Database statements via python but database left intact

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

Στις 6/10/2013 6:52 μμ, ο/η Adam Tauno Williams έγραψε:
>>>> Are you sure that you're committing your changes (either by having
>>>> autocommit set or using an explicit con.commit() call)?
>>>> http://geert.vanderkelen.org/dont-forget-the-commit-in-mysql/
>>> I dont think that is the issue, because up until now i never used
>> commit and
>>> all transaction were successfully were happening.
>
> Depending on autocommit is a bug [when does commit happen then?  consistency is a real problem].  Code should always explicitly ate least COMMIT or ROLLBACK if not explicitly BEGIN.  Not to mention how much easier it makes it to read the code and understand the units of work.
>
>> Well, have you changed anything in your database configuration?
>
> a big downside of autocommit - backend changes can break you app
>
>
>
	try:
		# locate the ID of the page's URL
		cur.execute('''SELECT ID FROM counters WHERE url = %s''', page )
		data = cur.fetchone()		#URL is unique, so should only be one
		
		if not data:
			#first time for page; primary key is automatic, hit is defaulted
			cur.execute('''INSERT INTO counters (url) VALUES (%s)''', page )
			cID = cur.lastrowid		#get the primary key value of the new added record
		else:
			#found the page, save primary key and use it to issue hit UPDATE
			cID = data[0]
			cur.execute('''UPDATE counters SET hits = hits + 1 WHERE ID = %s''', 
cID )


		# find the visitor record for the (saved) cID and Cookie
		cur.execute('''SELECT * FROM visitors WHERE counterID = %s and 
cookieID = %s''', (cID, cookieID) )
		data = cur.fetchone()		#cookieID is unique
			
		if not data:
			# first time visitor on this page, create new 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) )
		else:
			# found the page, save its primary key for later use
			vID = data[0]
			# UPDATE record using retrieved vID
			cur.execute('''UPDATE visitors SET host = %s, city = %s, useros = %s, 
browser = %s, ref= %s, hits = hits + 1, lastvisit = %s
						   WHERE counterID = %s and cookieID = %s''', (host, city, useros, 
browser, ref, lastvisit, vID, cookieID) )
		
		con.commit()
	except pymysql.ProgrammingError as e:
		con.rollback()
		print( repr(e) )
		sys.exit(0)

Before is qw your post i have chnaged it to this.
rollback() is correct where i placed it, i hope con.commit() is also 
correct too.

-- 
What is now proved was at first only imagined! & WebHost
<http://superhost.gr>