[lnkForumImage]
TotalShareware - Download Free Software
Confronta i prezzi di migliaia di prodotti.
Asp Forum
 Home | Login | Register | Search 


 

Forums >

comp.lang.python

MySQLdb: commit before cursor close, or after?

John Nagle

2/4/2008 6:14:00 PM

I'm getting some wierd commit-related behavior from MySQLdb. I'm
using InnoDB, so transactions really matter.

I'm currently doing

cursor = db.cursor()
cursor.execute(...)
cursor.close()
db.commit()

Is that the correct order, or should I call "db.commit()" before
"cursor.close()"? Does anyone know for sure? The MySQLdb documentation
("http://mysql-python.sourceforge.net/MySQLdb....) doesn't
say. There are some discussions of this in blogs, but nobody
really seems to know.

John Nagle
19 Answers

Frank Aune

2/4/2008 6:54:00 PM

0

On Monday 04 February 2008 19:14:13 John Nagle wrote:
> I'm getting some wierd commit-related behavior from MySQLdb. I'm
> using InnoDB, so transactions really matter.
>
> I'm currently doing
>
> cursor = db.cursor()
> cursor.execute(...)
> cursor.close()
> db.commit()
>
> Is that the correct order, or should I call "db.commit()" before
> "cursor.close()"? Does anyone know for sure? The MySQLdb documentation
> ("http://mysql-python.sourceforge.net/MySQLdb....) doesn't
> say. There are some discussions of this in blogs, but nobody
> really seems to know.
>
> John Nagle


No, you obviously need to commit your changes before closing the cursor. I'm
surprised if your code above even works if adding content to the db.

Regards,
Frank

Carsten Haese

2/4/2008 8:02:00 PM

0

On Mon, 2008-02-04 at 19:53 +0100, Frank Aune wrote:
> No, you obviously need to commit your changes before closing the cursor. I'm
> surprised if your code above even works if adding content to the db.

Why is that obvious? Is this some MySQL-specific oddity? In other
databases, it's the cursor's execute() method that adds the content to
the db (pending a commit of the transaction), and closing the cursor
simply means that you are explicitly releasing the resources that the
cursor used. Whether the cursor is closed before or after the
transaction is committed, or even whether the cursor is explicitly
closed at all or not, should make no difference whatsoever.

--
Carsten Haese
http://informixdb.sourc...

Steve Holden

2/4/2008 8:45:00 PM

0

Carsten Haese wrote:
> On Mon, 2008-02-04 at 19:53 +0100, Frank Aune wrote:
>> No, you obviously need to commit your changes before closing the cursor. I'm
>> surprised if your code above even works if adding content to the db.
>
> Why is that obvious? Is this some MySQL-specific oddity? In other
> databases, it's the cursor's execute() method that adds the content to
> the db (pending a commit of the transaction), and closing the cursor
> simply means that you are explicitly releasing the resources that the
> cursor used. Whether the cursor is closed before or after the
> transaction is committed, or even whether the cursor is explicitly
> closed at all or not, should make no difference whatsoever.
>
Certainly isn't "obvious" to me. The whole point of the way
connection/cursor relationship is structured is to allow the possibility
of several cursors on the same connection. So cursors can be created,
used and closed at will without any effect on the underlying connection.

regards
Steve
--
Steve Holden +1 571 484 6266 +1 800 494 3119
Holden Web LLC http://www.hold...

John Nagle

2/4/2008 9:17:00 PM

0

Steve Holden wrote:
> Carsten Haese wrote:
>> On Mon, 2008-02-04 at 19:53 +0100, Frank Aune wrote:
>>> No, you obviously need to commit your changes before closing the
>>> cursor. I'm surprised if your code above even works if adding content
>>> to the db.
>>
>> Why is that obvious? Is this some MySQL-specific oddity? In other
>> databases, it's the cursor's execute() method that adds the content to
>> the db (pending a commit of the transaction), and closing the cursor
>> simply means that you are explicitly releasing the resources that the
>> cursor used. Whether the cursor is closed before or after the
>> transaction is committed, or even whether the cursor is explicitly
>> closed at all or not, should make no difference whatsoever.
>>
> Certainly isn't "obvious" to me. The whole point of the way
> connection/cursor relationship is structured is to allow the possibility
> of several cursors on the same connection. So cursors can be created,
> used and closed at will without any effect on the underlying connection.

It really isn't clear. MySQL has no concept of "cursors". See
the C api: "http://dev.mysql.com/doc/refman/5.0/en/c-api-function-overview....
Cursors are an illusion created by the MySQLdb library. What MySQL does
have is a "current result set". After doing something that generates a
result set, the C API call mysql_result_set() is called, then mysql_fetch_row()
is called for each row, followed by mysql_free_result().

You can't really have multiple cursors on the same connection at the same
time. MySQL doesn't allow issuing new queries until mysql_result_set() is done.
See "http://dev.mysql.com/doc/refman/5.0/en/mysql-use-result..... If
you try, a "commands out of sync" error should be generated. The
MySQL C interface only allows one thing at a time per connection, and
MySQLdb is written on top of that.

Given that, it makes sense to close the cursor before committing. That
frees up the connection for the next command.
But I'm not sure, and I have a bug that seems to be related to
a transaction not committing properly.

John Nagle

Free Lunch

1/18/2013 10:58:00 PM

0

On Fri, 18 Jan 2013 13:25:51 -0800, Jason@nospam.com (Jason) wrote in
alt.atheism:

>In article <XnsA14C49191247Anomailverizonnet@216.196.121.131>, Mitchell
>Holman <nomailverizon.net> wrote:
>
>> Jason@nospam.com (Jason) wrote in
>> news:Jason-1701132223070001@66-53-211-80.lsan.mdsg-pacwest.com:
>>
>> > In article <hlwdjsd2-DCE327.21572117012013@news.giganews.com>, Jeanne
>> > Douglas <hlwdjsd2@NOSPAMgmail.com> wrote:
>> >
>> >> In article
>> >> <Jason-1701132158200001@66-53-211-80.lsan.mdsg-pacwest.com>,
>> >> Jason@nospam.com (Jason) wrote:
>> >>
>> >> > In article <hlwdjsd2-9365A0.21162617012013@news.giganews.com>,
>> >> > Jeanne Douglas <hlwdjsd2@NOSPAMgmail.com> wrote:
>> >> >
>> >> > > > PS: Do you know what the term "going postal" means?
>> >> > > >
>> >> > > > Yes, almost every person has the potential of going postal.
>> >> > > > That is the problem. We don't know whom may go postal.
>> >> > >
>> >> > > That is not even close to a definition of "going postal".
>> >> >
>> >> > Re-read what he wrote. He asked me a question and my answer was
>> >> > yes.
>> >>
>> >> Any normal person would have recognized that as a request to define
>> >> the expression.
>> >
>> > OK--going postal is someone losing it and shooting people. In the
>> > beginning, it only applied to postal workers but now applies to anyone
>> > that does is.
>>
>>
>> So what is your solution to law abiding gun
>> owners who snap and go on shooting rampages?
>>
>
>I don't have a solution. It's my guess that it happens in other countries
>that prohibit gun ownership but the people going postal use knives or
>swords.
>
There have not been any comparable mass murders with knives or swords.
Stop making excuses for the evil that a cult of guns brings.
>
>If so, what is your solution in those situations? Would you ban the use of
>knives and swords?
>

Jason

1/18/2013 11:51:00 PM

0

In article <mqkjf812llhvdgh2v6rlngarfjbuog2g6e@4ax.com>, Free Lunch
<lunch@nofreelunch.us> wrote:

> On Fri, 18 Jan 2013 13:25:51 -0800, Jason@nospam.com (Jason) wrote in
> alt.atheism:
>
> >In article <XnsA14C49191247Anomailverizonnet@216.196.121.131>, Mitchell
> >Holman <nomailverizon.net> wrote:
> >
> >> Jason@nospam.com (Jason) wrote in
> >> news:Jason-1701132223070001@66-53-211-80.lsan.mdsg-pacwest.com:
> >>
> >> > In article <hlwdjsd2-DCE327.21572117012013@news.giganews.com>, Jeanne
> >> > Douglas <hlwdjsd2@NOSPAMgmail.com> wrote:
> >> >
> >> >> In article
> >> >> <Jason-1701132158200001@66-53-211-80.lsan.mdsg-pacwest.com>,
> >> >> Jason@nospam.com (Jason) wrote:
> >> >>
> >> >> > In article <hlwdjsd2-9365A0.21162617012013@news.giganews.com>,
> >> >> > Jeanne Douglas <hlwdjsd2@NOSPAMgmail.com> wrote:
> >> >> >
> >> >> > > > PS: Do you know what the term "going postal" means?
> >> >> > > >
> >> >> > > > Yes, almost every person has the potential of going postal.
> >> >> > > > That is the problem. We don't know whom may go postal.
> >> >> > >
> >> >> > > That is not even close to a definition of "going postal".
> >> >> >
> >> >> > Re-read what he wrote. He asked me a question and my answer was
> >> >> > yes.
> >> >>
> >> >> Any normal person would have recognized that as a request to define
> >> >> the expression.
> >> >
> >> > OK--going postal is someone losing it and shooting people. In the
> >> > beginning, it only applied to postal workers but now applies to anyone
> >> > that does is.
> >>
> >>
> >> So what is your solution to law abiding gun
> >> owners who snap and go on shooting rampages?
> >>
> >
> >I don't have a solution. It's my guess that it happens in other countries
> >that prohibit gun ownership but the people going postal use knives or
> >swords.
> >
> There have not been any comparable mass murders with knives or swords.
> Stop making excuses for the evil that a cult of guns brings.
> >
> >If so, what is your solution in those situations? Would you ban the use of
> >knives and swords?
> >

You failed to answer the above simple question.

Jason

1/18/2013 11:53:00 PM

0

In article <XnsA14C9D7949695nomailverizonnet@216.196.121.131>, Mitchell
Holman <nomailverizon.net> wrote:

> Jason@nospam.com (Jason) wrote in
> news:Jason-1801131325510001@66-53-208-190.lsan.mdsg-pacwest.com:
>
> > In article <XnsA14C49191247Anomailverizonnet@216.196.121.131>,
> > Mitchell Holman <nomailverizon.net> wrote:
> >
> >> Jason@nospam.com (Jason) wrote in
> >> news:Jason-1701132223070001@66-53-211-80.lsan.mdsg-pacwest.com:
> >>
> >> > In article <hlwdjsd2-DCE327.21572117012013@news.giganews.com>,
> >> > Jeanne Douglas <hlwdjsd2@NOSPAMgmail.com> wrote:
> >> >
> >> >> In article
> >> >> <Jason-1701132158200001@66-53-211-80.lsan.mdsg-pacwest.com>,
> >> >> Jason@nospam.com (Jason) wrote:
> >> >>
> >> >> > In article <hlwdjsd2-9365A0.21162617012013@news.giganews.com>,
> >> >> > Jeanne Douglas <hlwdjsd2@NOSPAMgmail.com> wrote:
> >> >> >
> >> >> > > > PS: Do you know what the term "going postal" means?
> >> >> > > >
> >> >> > > > Yes, almost every person has the potential of going postal.
> >> >> > > > That is the problem. We don't know whom may go postal.
> >> >> > >
> >> >> > > That is not even close to a definition of "going postal".
> >> >> >
> >> >> > Re-read what he wrote. He asked me a question and my answer was
> >> >> > yes.
> >> >>
> >> >> Any normal person would have recognized that as a request to
> >> >> define the expression.
> >> >
> >> > OK--going postal is someone losing it and shooting people. In the
> >> > beginning, it only applied to postal workers but now applies to
> >> > anyone that does is.
> >>
> >>
> >> So what is your solution to law abiding gun
> >> owners who snap and go on shooting rampages?
> >>
> >
> > I don't have a solution. It's my guess that it happens in other
> > countries that prohibit gun ownership but the people going postal use
> > knives or swords.
> >
>
> Really?
>
> Name for us a school massacre committed
> with knives or swords.
>
> >
> > If so, what is your solution in those situations? Would you ban the
> > use of knives and swords?
>
>
> Your hypothetical is just silly.

And it is just as silly to ask me how many children that I am willing to
sacrifice.

Jason

1/18/2013 11:54:00 PM

0

In article
<1a4d5c60-ef9d-44b0-86f1-c98bf982e16f@v7g2000yqv.googlegroups.com>, Robert
H <crazyco@comcast.net> wrote:

> On Jan 18, 4:33=A0pm, Mitchell Holman <nomailverizon.net> wrote:
> > Ja...@nospam.com (Jason) wrote innews:Jason-1801131325510001@66-53-208-19=
> 0.lsan.mdsg-pacwest.com:
> >
> >
> >
> >
> >
> >
> >
> >
> >
> > > In article <XnsA14C49191247Anomailverizon...@216.196.121.131>,
> > > Mitchell Holman <nomailverizon.net> wrote:
> >
> > >> Ja...@nospam.com (Jason) wrote in
> > >>news:Jason-1701132223070001@66-53-211-80.lsan.mdsg-pacwest.com:
> >
> > >> > In article <hlwdjsd2-DCE327.21572117012...@news.giganews.com>,
> > >> > Jeanne Douglas <hlwdj...@NOSPAMgmail.com> wrote:
> >
> > >> >> In article
> > >> >> <Jason-1701132158200...@66-53-211-80.lsan.mdsg-pacwest.com>,
> > >> >> =A0Ja...@nospam.com (Jason) wrote:
> >
> > >> >> > In article <hlwdjsd2-9365A0.21162617012...@news.giganews.com>,
> > >> >> > Jeanne Douglas <hlwdj...@NOSPAMgmail.com> wrote:
> >
> > >> >> > =A0> > =A0 =A0 PS: Do you know what the term "going postal" means=
> ?
> >
> > >> >> > > > Yes, almost every person has the potential of going postal.
> > >> >> > > > That is the problem. We don't know whom may go postal.
> >
> > >> >> > > That is not even close to a definition of "going postal".
> >
> > >> >> > Re-read what he wrote. He asked me a question and my answer was
> > >> >> > yes.
> >
> > >> >> Any normal person would have recognized that as a request to
> > >> >> define the expression.
> >
> > >> > OK--going postal is someone losing it and shooting people. In the
> > >> > beginning, it only applied to postal workers but now applies to
> > >> > anyone that does is.
> >
> > >> =A0 =A0 So what is your solution to law abiding gun
> > >> owners who snap and go on shooting rampages?
> >
> > > I don't have a solution. It's my guess that it happens in other
> > > countries that prohibit gun ownership but the people going postal use
> > > knives or swords.
> >
> > =A0 =A0 =A0Really?
> >
> > =A0 =A0 =A0Name for us a school massacre committed
> > with knives or swords.
>
> So your concern is the "massacre" part, the numbers?
>
> >
> >
> >
> > > If so, what is your solution in those situations? Would you ban the
> > > use of knives and swords?
> >
> > =A0 =A0 =A0Your hypothetical is just silly.
>
> Well? Would you? Do you want to ban all knives and swords?
>
> Do you want to ban all handguns from the law abiding citizens too?

I don't want guns or knives to be banned.

Mitchell Holman

1/19/2013 2:49:00 AM

0

Robert H <crazyco@comcast.net> wrote in
news:1a4d5c60-ef9d-44b0-86f1-c98bf982e16f@v7g2000yqv.googlegroups.com:

> On Jan 18, 4:33?pm, Mitchell Holman <nomailverizon.net> wrote:
>> Ja...@nospam.com (Jason) wrote
>> innews:Jason-1801131325510001@66-53-208-19
> 0.lsan.mdsg-pacwest.com:
>>
>>
>>
>>
>>
>>
>>
>>
>>
>> > In article <XnsA14C49191247Anomailverizon...@216.196.121.131>,
>> > Mitchell Holman <nomailverizon.net> wrote:
>>
>> >> Ja...@nospam.com (Jason) wrote in
>> >>news:Jason-1701132223070001@66-53-211-80.lsan.mdsg-pacwest.com:
>>
>> >> > In article <hlwdjsd2-DCE327.21572117012...@news.giganews.com>,
>> >> > Jeanne Douglas <hlwdj...@NOSPAMgmail.com> wrote:
>>
>> >> >> In article
>> >> >> <Jason-1701132158200...@66-53-211-80.lsan.mdsg-pacwest.com>,
>> >> >> ?Ja...@nospam.com (Jason) wrote:
>>
>> >> >> > In article
>> >> >> > <hlwdjsd2-9365A0.21162617012...@news.giganews.com>,
>> >> >> > Jeanne Douglas <hlwdj...@NOSPAMgmail.com> wrote:
>>
>> >> >> > ?> > ? ? PS: Do you know what the term "going postal" means
> ?
>>
>> >> >> > > > Yes, almost every person has the potential of going
>> >> >> > > > postal. That is the problem. We don't know whom may go
>> >> >> > > > postal.
>>
>> >> >> > > That is not even close to a definition of "going postal".
>>
>> >> >> > Re-read what he wrote. He asked me a question and my answer
>> >> >> > was yes.
>>
>> >> >> Any normal person would have recognized that as a request to
>> >> >> define the expression.
>>
>> >> > OK--going postal is someone losing it and shooting people. In
>> >> > the beginning, it only applied to postal workers but now applies
>> >> > to anyone that does is.
>>
>> >> ? ? So what is your solution to law abiding gun
>> >> owners who snap and go on shooting rampages?
>>
>> > I don't have a solution. It's my guess that it happens in other
>> > countries that prohibit gun ownership but the people going postal
>> > use knives or swords.
>>
>> ? ? ?Really?
>>
>> ? ? ?Name for us a school massacre committed
>> with knives or swords.
>
> So your concern is the "massacre" part, the numbers?


Isn't it a concern to you - or are school
massacres just something we are supposed to live
with?



>>
>>
>> > If so, what is your solution in those situations? Would you ban the
>> > use of knives and swords?
>>
>> ? ? ?Your hypothetical is just silly.
>
> Well? Would you? Do you want to ban all knives and swords?
>
> Do you want to ban all handguns from the law abiding citizens too?


What is your solution to "law abiding citizens"
who go postal and shoot up schools and theaters and
malls?

Mitchell Holman

1/19/2013 2:50:00 AM

0

Jason@nospam.com (Jason) wrote in news:Jason-1801131553040001@67-150-121-
102.lsan.mdsg-pacwest.com:

> In article <XnsA14C9D7949695nomailverizonnet@216.196.121.131>, Mitchell
> Holman <nomailverizon.net> wrote:
>
>> Jason@nospam.com (Jason) wrote in
>> news:Jason-1801131325510001@66-53-208-190.lsan.mdsg-pacwest.com:
>>
>> > In article <XnsA14C49191247Anomailverizonnet@216.196.121.131>,
>> > Mitchell Holman <nomailverizon.net> wrote:
>> >
>> >> Jason@nospam.com (Jason) wrote in
>> >> news:Jason-1701132223070001@66-53-211-80.lsan.mdsg-pacwest.com:
>> >>
>> >> > In article <hlwdjsd2-DCE327.21572117012013@news.giganews.com>,
>> >> > Jeanne Douglas <hlwdjsd2@NOSPAMgmail.com> wrote:
>> >> >
>> >> >> In article
>> >> >> <Jason-1701132158200001@66-53-211-80.lsan.mdsg-pacwest.com>,
>> >> >> Jason@nospam.com (Jason) wrote:
>> >> >>
>> >> >> > In article <hlwdjsd2-9365A0.21162617012013@news.giganews.com>,
>> >> >> > Jeanne Douglas <hlwdjsd2@NOSPAMgmail.com> wrote:
>> >> >> >
>> >> >> > > > PS: Do you know what the term "going postal" means?
>> >> >> > > >
>> >> >> > > > Yes, almost every person has the potential of going
postal.
>> >> >> > > > That is the problem. We don't know whom may go postal.
>> >> >> > >
>> >> >> > > That is not even close to a definition of "going postal".
>> >> >> >
>> >> >> > Re-read what he wrote. He asked me a question and my answer
was
>> >> >> > yes.
>> >> >>
>> >> >> Any normal person would have recognized that as a request to
>> >> >> define the expression.
>> >> >
>> >> > OK--going postal is someone losing it and shooting people. In the
>> >> > beginning, it only applied to postal workers but now applies to
>> >> > anyone that does is.
>> >>
>> >>
>> >> So what is your solution to law abiding gun
>> >> owners who snap and go on shooting rampages?
>> >>
>> >
>> > I don't have a solution. It's my guess that it happens in other
>> > countries that prohibit gun ownership but the people going postal
use
>> > knives or swords.
>> >
>>
>> Really?
>>
>> Name for us a school massacre committed
>> with knives or swords.
>>


Well?


>> >
>> > If so, what is your solution in those situations? Would you ban the
>> > use of knives and swords?
>>
>>
>> Your hypothetical is just silly.
>
> And it is just as silly to ask me how many children that I am willing
to
> sacrifice.


There is no sword violence, there IS gun violence.

See the difference?