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

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

Database statements via python but database left intact

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

Contents

  Database statements via python but database left intact Νίκος Αλεξόπουλος <nikos.gr33k@gmail.com> - 2013-10-05 22:16 +0300
    Re: Database statements via python but database left intact Νίκος Αλεξόπουλος <nikos.gr33k@gmail.com> - 2013-10-05 22:27 +0300
    Re: Database statements via python but database left intact Zero Piraeus <z@etiol.net> - 2013-10-05 16:29 -0300
      Re: Database statements via python but database left intact Νίκος Αλεξόπουλος <nikos.gr33k@gmail.com> - 2013-10-05 22:36 +0300
        Re: Database statements via python but database left intact Ian Kelly <ian.g.kelly@gmail.com> - 2013-10-05 14:31 -0600
          Re: Database statements via python but database left intact Νίκος Αλεξόπουλος <nikos.gr33k@gmail.com> - 2013-10-06 00:02 +0300
            Re: Database statements via python but database left intact Ned Batchelder <ned@nedbatchelder.com> - 2013-10-05 17:39 -0400
            Re: Database statements via python but database left intact Chris Angelico <rosuav@gmail.com> - 2013-10-06 09:51 +1100
            Database engine bindings for Python (was: Database statements via python but database left intact) Ben Finney <ben+python@benfinney.id.au> - 2013-10-06 12:05 +1100
            Re: Database engine bindings for Python (was: Database statements via python but database left intact) Chris Angelico <rosuav@gmail.com> - 2013-10-06 12:19 +1100
            Re: Database engine bindings for Python (was: Database statements via python but database left intact) Dan Sommers <dan@tombstonezero.net> - 2013-10-06 06:22 +0000
              Re: Database engine bindings for Python (was: Database statements via python but database left intact) Chris “Kwpolska” Warrick <kwpolska@gmail.com> - 2013-10-06 11:10 +0200
              Re: Database engine bindings for Python (was: Database statements via python but database left intact) Chris Angelico <rosuav@gmail.com> - 2013-10-07 00:37 +1100
                Re: Database engine bindings for Python (was: Database statements via python but database left intact) Roy Smith <roy@panix.com> - 2013-10-06 10:05 -0400
            Re: Database statements via python but database left intact Chris “Kwpolska” Warrick <kwpolska@gmail.com> - 2013-10-06 11:05 +0200
              Re: Database statements via python but database left intact rusi <rustompmody@gmail.com> - 2013-10-06 04:36 -0700
                Re: Database statements via python but database left intact Chris “Kwpolska” Warrick <kwpolska@gmail.com> - 2013-10-06 14:01 +0200
            Re: Database statements via python but database left intact Zero Piraeus <z@etiol.net> - 2013-10-05 18:45 -0300
              Re: Database statements via python but database left intact Νίκος Αλεξόπουλος <nikos.gr33k@gmail.com> - 2013-10-06 18:57 +0300
                Re: Database statements via python but database left intact Piet van Oostrum <piet@vanoostrum.org> - 2013-10-06 17:40 -0400
            Re: Database statements via python but database left intact Adam Tauno Williams <awilliam@whitemice.org> - 2013-10-06 11:55 -0400
        Re: Database statements via python but database left intact Adam Tauno Williams <awilliam@whitemice.org> - 2013-10-06 11:52 -0400
          Re: Database statements via python but database left intact Νίκος Αλεξόπουλος <nikos.gr33k@gmail.com> - 2013-10-06 19:49 +0300

Page 2 of 2 — ← Prev page 1 [2]

#56265

>I neved had though of than an engine type could make so much mess.

Because your app and how it is written is broken.

>MyISAM is the way to go then for my web development?
>Why InnoDB failed to execute the queries?

No, nothing failed.  Your app is broken.  You are depending on auto commit - and that is a back end implementation detail.  DO NOT USE AUTOCOMMIT.

The newer engine is expecting you to do things the right way.  The old engine was sloppy and does serialization wrong - the reason there is a new engine.


-- 
Adam Tauno Williams

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

#56267

>>> 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



-- 
Adam Tauno Williams

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

#56270

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

[toc] | [prev] | [standalone]

Page 2 of 2 — ← Prev page 1 [2]

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

csiph-web