473,897 Members | 2,557 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

MySQLdb and Cursor

Hi all,

I'm writing a program that will read an ASCII file periodically and update
several tables in a MySQL database.
My question is "Can I use the same cursor for several SQL requests (SELECT
and INSERT) or do I have to close the cursor between 2 requests ?".

Regards
Michel Combe
Jul 18 '05 #1
3 8646
Michel Combe wrote:
Hi all,

I'm writing a program that will read an ASCII file periodically and update
several tables in a MySQL database.
My question is "Can I use the same cursor for several SQL requests (SELECT
and INSERT) or do I have to close the cursor between 2 requests ?".

Regards
Michel Combe


best way is to create a connection ar the start of one session work. use
this connection to do an entire transaction. At the end of work you must
commit or rollback. But this don't mean that you can misc select and
insert on tha same cursor.

con = MySql.connectio n....
cur1 = con.cursor()
cur1.execute("s elect.....
for rec in cur1:
do something
cur2 = con.cursor()
cur2.execute("i nsert ....

conn.commit or rollback
conn.close()

In this stupid example you CANNOT use only cur1 !!
Is not necessary to close cursors , last line do this for you
Bye
Glauco
Jul 18 '05 #2
Michel Combe wrote:
Hi all,

I'm writing a program that will read an ASCII file periodically and update
several tables in a MySQL database.
My question is "Can I use the same cursor for several SQL requests (SELECT
and INSERT) or do I have to close the cursor between 2 requests ?".


You can use the same cursor object.

-- Gerhard

Jul 18 '05 #3
Glauco wrote:
Michel Combe wrote:
Hi all,

I'm writing a program that will read an ASCII file periodically and
update
several tables in a MySQL database.
My question is "Can I use the same cursor for several SQL requests
(SELECT
and INSERT) or do I have to close the cursor between 2 requests ?".

Regards
Michel Combe


best way is to create a connection ar the start of one session work. use
this connection to do an entire transaction. At the end of work you must
commit or rollback. But this don't mean that you can misc select and
insert on tha same cursor.

con = MySql.connectio n....
cur1 = con.cursor()
cur1.execute("s elect.....
for rec in cur1:
do something
cur2 = con.cursor()
cur2.execute("i nsert ....

conn.commit or rollback
conn.close()

In this stupid example you CANNOT use only cur1 !!
Is not necessary to close cursors , last line do this for you
Bye
Glauco


You don't need to close the connection. By issuing a commit or a
rollback you are finishing the transaction (if you have transaction
aware tables, e.g. InnoDB). The point that Glauco is making is that you
can't use one cursor for two operations simultaneously.

Following his example, if you try and do;
cur1.execute("S ELECT a, b, c FROM table1")
for row in cur1.fetchone() :
# do something
cur1.execute("I NSERT INTO table2 VALUES (?, ?, ?)" % (row.a, row.b, row.c))

Then you will get an exception because the second execute will
obliterate the result set of the first - which you are trying to loop
through. Its perfectly possibly though, to do;
cur1.execute("S ELECT a, b, c FROM table1")
results = cur1.fetchall()
for row in results:
# do something
cur1.execute("I NSERT INTO table2 VALUES (?, ?, ?)" % (row.a,

row.b, row.c))

Which is fine as long as the first select doesn't return too many rows ;-)

As a general rule of thumb establish a connection when your program
starts and close it when you finish. Oh, and don't create a new cursor
within a loop, that will not be very efficient.

Regards,
Andy
--
--------------------------------------------------------------------------------
From the desk of Andrew J Todd esq - http://www.halfcooked.com/

Jul 18 '05 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

5
5093
by: Chris Stromberger | last post by:
When issuing updates in mysql (in the console window), mysql will tell you if any rows matched and how many rows were updated (see below). I know how to get number of rows udpated using MySQLdb, but is there any way to get the number of rows matched? I want to find out, when rows updated = 0, if there were no updates because the row wasn't found (rows matched will = 0) or because the update would not have changed any data (rows matched =...
7
10580
by: Brian Kelley | last post by:
I am trying to use threads and mysqldb to retrieve data from multiple asynchronous queries. My basic strategy is as follows, create two cursors, attach them to the appropriate databases and then spawn worker functions to execute sql queries and process the results. This works occasionally, but fails a lot taking python down with it. Sometimes it also loses connection to the database. Sometimes I get an error, "Commands out of sync; ...
2
5069
by: Tim Williams | last post by:
I'm trying to write a simple python program to access a MySQL database. I'm having a problem with using MySQLdb to get the results of a SQL command in a cursor. Sometimes the cursor.execute works, sometimes not. From mysql: mysql> show databases; +-----------+ | Database |
21
5270
by: John Fabiani | last post by:
Hi, I'm a newbie and I'm attempting to learn howto create a select statement. When I use >>> string1='18 Tadlock Place' >>> cursor.execute("SELECT * FROM mytest where address = %s",string1) All works as expected. But >>> numb=10 >>> cursor.execute("SELECT * FROM mytest where clientID = %d",numb) Traceback (innermost last): File "<stdin>", line 1, in ?
0
1480
by: Wesley Kincaid | last post by:
I'm attempting to run a simple query through MySQLdb's cursor.execute(). However, when the request includes a timestamp field, I'm getting "ValueError: invalid literal for int(): 9-." Could someone please explain what I'm doing wrong? The table is served off of MySQL 4.0.20 and contains the following fields:
1
3342
by: Hector Villafuerte | last post by:
Hi, I have been using Python 2.3.4 with MySQL 4.0.16-max-nt (through MySQLdb) for months. Today this messages showed up: Exception exceptions.AttributeError: "'NoneType' object has no attribute 'close'" in <bound method Cursor.__del__ of <MySQLdb.cursors.Cursor instance at 0x0099C148>> ignored Exception exceptions.AttributeError: "'NoneType' object has no attribute 'close'" in <bound method Cursor.__del__ of <MySQLdb.cursors.Cursor...
2
2203
by: ws Wang | last post by:
MySQLdb is working fine at command line, however when I tried to use it with mod_python, it give me a "server not initialized" error. This is working fine: ----------------------- testmy.py ------------------------------- #!/usr/bin/python import MySQLdb db = MySQLdb.connect(host="localhost", user="root", passwd="mypass", db="my_db") cursor = db.cursor()
11
25644
by: Fred | last post by:
I hope someone can help me with the below problem... Thanks, Fred My enviroment: -------------------------- Slackware Linux 10.2 Python 2.4.2 MySql version 4.1.14
1
7537
by: shearichard | last post by:
Hi - I have written some python to insert a row into a table using MySQLDB. I have never before written SQL/Python using embedded parameters in the SQL and I'm having some difficulties. Could someone point me in the right direction please ? The python looks like this : import MySQLdb import MySQLdb.cursors conn = MySQLdb.Connect(host='localhost', user='abc,passwd='def',
2
4093
rhitam30111985
by: rhitam30111985 | last post by:
hi all .. consider the following code: i basically need to build a table in the mysql database with two fields , country and office list... import MySQLdb import sys import os os.system('clear') try: conn = MySQLdb.connect (host = "localhost",
0
9990
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
11250
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
10850
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
8032
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
7185
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5873
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
6074
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4698
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
4293
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.