hey there,
i have a huge app that connects to MySQL. There are three threads that
are continually connecting and disconnecting to the db. The problem is,
if there is an error, it faults out sometimes without closing the
connection. i connect like this.
db = MySQLdb.connect(host="localhost", user="MyUser",
passwd="MyPassword", db="Stuff")
cursor=db.cursor()
then i use the cursor.execute("SELECT yadda yadda
my question is, is there a way i can set up a global connection so that
when the program loads, it connects once, then stays connected ? maybe
i could assign instances of the cursor ?
please someone let me know if you have any good ideas
sk 10 12353
that's definitely the way to go ..
-create a database_object
-initialise at start up
-then pass the database object to other classes as needed ...
If you want to get really fancy have a look at some ORM's ... I think
there is a Python one called SQLObject? ne*****@xit.net wrote: hey there, i have a huge app that connects to MySQL. There are three threads that are continually connecting and disconnecting to the db. The problem is, if there is an error, it faults out sometimes without closing the connection. i connect like this. db = MySQLdb.connect(host="localhost", user="MyUser", passwd="MyPassword", db="Stuff") cursor=db.cursor()
then i use the cursor.execute("SELECT yadda yadda
my question is, is there a way i can set up a global connection so that when the program loads, it connects once, then stays connected ? maybe i could assign instances of the cursor ?
please someone let me know if you have any good ideas
sk
This is great !
ok, i dont really have a lot of time to get into the ORMS (before your
post, this is the first i have heard of it) and my stuff is due on
Monday. he he.
but, if i am able to make a global db connection, and multiple cursors
pointing to the same connection object, how do i pull that off without
making new db connections?
something like
class db(self):
def __init__(self):
db = MySQLdb.connect(host="localhost", user="MyUser",
passwd="MyPassword",
db="Stuff")
def cursor(self):
cursor = db.cursor()
return cursor
then have in my threads that need to connect
cursor = db.cursor()
cursor2 = db.cursor()
and so on ? i may be way outta whack here. i am still new at classes,
methods, and modules.
i do have Essential Reference on the way from Amazon though ! :)
thanks again
your on the right track ... create something like this ( hope the formatting doesn't go to hay wire )
class DB_Connector(object):
""" Humble Database Connection Class """
def __init__(self, host="localhost", user="MyUser",passwd="MyPassword", **other_db_arguments):
self.host = host
self.user = user
self.passwd = passwd
# Unpack Other Database Arguments Here
self.CreateConnection()
def CreateConnection( self ):
self.cursor = MySQLdb.connect(self.host, self.user, self.passwd)
def DestroyConnection( self ):
self.cursor.close()
def Execute( self, sql_statement ):
self.cursor.Execute( sql_statement )
return self.cursor.FetchAll()
Then when you run your program create an instance of the object
db_connection = DB_Connector( 'localhost', 'administrator', 'betelgeuse99', auto_commit=1, other_keyword_arg="yes" )
now when you pass the db_connection instance to other classes, a copy will be made automagically
thread_1_instance = ThreadingClass( db_connection )
thread_2_instance = ThreadingClass( db_connection )
thread_3_instance = ThreadingClass( db_connection )
should work ..
I hope this is useful ne*****@xit.net wrote: This is great !
ok, i dont really have a lot of time to get into the ORMS (before your post, this is the first i have heard of it) and my stuff is due on Monday. he he.
but, if i am able to make a global db connection, and multiple cursors pointing to the same connection object, how do i pull that off without making new db connections?
something like class db(self): def __init__(self): db = MySQLdb.connect(host="localhost", user="MyUser", passwd="MyPassword", db="Stuff") def cursor(self): cursor = db.cursor() return cursor
then have in my threads that need to connect
cursor = db.cursor() cursor2 = db.cursor()
and so on ? i may be way outta whack here. i am still new at classes, methods, and modules. i do have Essential Reference on the way from Amazon though ! :)
thanks again
Oops .. slight edit
now when you pass the db_connection instance to other classes, a reference will be passed automagically
On 04/28/2006 07:54 AM, *binarystar* wrote:
Just wondering: is there any risk of two threads accessing the Execute
function at the same time and getting something like this on the same
cursor object:
thread_1: self.cursor.Execute( sql_statement )
thread_2: self.cursor.Execute( sql_statement )
thread_1: return self.cursor.FetchAll()
thread_2: return self.cursor.FetchAll()
In that case the queries would seriously be messed up. My intuition says
this would need some locking or a 'cursor-pool'.
best wishes,
Winfried your on the right track ... create something like this ( hope the formatting doesn't go to hay wire )
class DB_Connector(object):
""" Humble Database Connection Class """ def __init__(self, host="localhost", user="MyUser",passwd="MyPassword", **other_db_arguments): self.host = host self.user = user self.passwd = passwd # Unpack Other Database Arguments Here self.CreateConnection() def CreateConnection( self ): self.cursor = MySQLdb.connect(self.host, self.user, self.passwd) def DestroyConnection( self ): self.cursor.close() def Execute( self, sql_statement ): self.cursor.Execute( sql_statement ) return self.cursor.FetchAll() Then when you run your program create an instance of the object
db_connection = DB_Connector( 'localhost', 'administrator', 'betelgeuse99', auto_commit=1, other_keyword_arg="yes" )
now when you pass the db_connection instance to other classes, a copy will be made automagically
thread_1_instance = ThreadingClass( db_connection ) thread_2_instance = ThreadingClass( db_connection ) thread_3_instance = ThreadingClass( db_connection )
should work .. I hope this is useful
I suppose that is possible because you are calling the one instance of a cursor object ... maybe you have to create a copy of the cursor object, rather than passing a reference to the one object? or set up the db_connection objects inside each of the threads? ..
Winfried Tilanus wrote: On 04/28/2006 07:54 AM, *binarystar* wrote:
Just wondering: is there any risk of two threads accessing the Execute function at the same time and getting something like this on the same cursor object:
thread_1: self.cursor.Execute( sql_statement ) thread_2: self.cursor.Execute( sql_statement ) thread_1: return self.cursor.FetchAll() thread_2: return self.cursor.FetchAll()
In that case the queries would seriously be messed up. My intuition says this would need some locking or a 'cursor-pool'.
best wishes,
Winfried
your on the right track ... create something like this ( hope the formatting doesn't go to hay wire )
class DB_Connector(object):
""" Humble Database Connection Class """ def __init__(self, host="localhost", user="MyUser",passwd="MyPassword", **other_db_arguments): self.host = host self.user = user self.passwd = passwd # Unpack Other Database Arguments Here self.CreateConnection() def CreateConnection( self ): self.cursor = MySQLdb.connect(self.host, self.user, self.passwd) def DestroyConnection( self ): self.cursor.close() def Execute( self, sql_statement ): self.cursor.Execute( sql_statement ) return self.cursor.FetchAll() Then when you run your program create an instance of the object
db_connection = DB_Connector( 'localhost', 'administrator', 'betelgeuse99', auto_commit=1, other_keyword_arg="yes" )
now when you pass the db_connection instance to other classes, a copy will be made automagically
thread_1_instance = ThreadingClass( db_connection ) thread_2_instance = ThreadingClass( db_connection ) thread_3_instance = ThreadingClass( db_connection )
should work .. I hope this is useful
On 04/28/2006 08:35 AM, *binarystar* wrote:
Looking better at the """ Humble Database Connection Class """: if I am
not mistaken, it seems to mix up connections and cursors.
MySQLdb has a thread safety level of '1', meaning: "Threads may share
the module, but not connections". So you have to give each thread an own
connection. Beside of that I would prefer not to share the cursor
object, although it should not really matter.
The following should work and make the cursors private to the Execute
function (untested):
class DB_Connector(object):
""" Humble Database Connection Class """
def __init__(self, host="localhost",
user="MyUser",
passwd="MyPassword",
**other_db_arguments):
self.host = host
self.user = user
self.passwd = passwd
# Unpack Other Database Arguments Here
self.CreateConnection()
def CreateConnection(self):
self.connection = MySQLdb.connect(self.host,
self.user,
self.passwd)
def DestroyConnection(self):
self.connection.close()
def Execute(self, sql_statement):
cursor = self.connection.cursor()
cursor.execute(sql_statement)
result = cursor.fetchall()
cursor.close()
return result I suppose that is possible because you are calling the one instance of a cursor object ... maybe you have to create a copy of the cursor object, rather than passing a reference to the one object? or set up the db_connection objects inside each of the threads? ..
Winfried Tilanus wrote: On 04/28/2006 07:54 AM, *binarystar* wrote:
Just wondering: is there any risk of two threads accessing the Execute function at the same time and getting something like this on the same cursor object:
thread_1: self.cursor.Execute( sql_statement ) thread_2: self.cursor.Execute( sql_statement ) thread_1: return self.cursor.FetchAll() thread_2: return self.cursor.FetchAll()
In that case the queries would seriously be messed up. My intuition says this would need some locking or a 'cursor-pool'.
best wishes,
Winfried
your on the right track ... create something like this ( hope the formatting doesn't go to hay wire )
class DB_Connector(object):
""" Humble Database Connection Class """ def __init__(self, host="localhost", user="MyUser",passwd="MyPassword", **other_db_arguments): self.host = host self.user = user self.passwd = passwd # Unpack Other Database Arguments Here self.CreateConnection() def CreateConnection( self ): self.cursor = MySQLdb.connect(self.host, self.user, self.passwd) def DestroyConnection( self ): self.cursor.close() def Execute( self, sql_statement ): self.cursor.Execute( sql_statement ) return self.cursor.FetchAll() Then when you run your program create an instance of the object
db_connection = DB_Connector( 'localhost', 'administrator', 'betelgeuse99', auto_commit=1, other_keyword_arg="yes" )
now when you pass the db_connection instance to other classes, a copy will be made automagically
thread_1_instance = ThreadingClass( db_connection ) thread_2_instance = ThreadingClass( db_connection ) thread_3_instance = ThreadingClass( db_connection )
should work .. I hope this is useful
So this opens and closes the connection every time i run the query?
thats cool. i think that would fit in well. so when i need to run the
query, i pass something like
query = "SELECT * FROM `Table` WHERE `foo` = 'bar'"
result = DB_Connector.Execute(query)
and the result would be the same as if i ran the query right there in
the thread?
thanks for all your help gents, this is helping me a lot.
way cool, i think that this will work.
thanks very much
-sk
On Apr 27, 2006, at 8:15 PM, ne*****@xit.net wrote: my question is, is there a way i can set up a global connection so that when the program loads, it connects once, then stays connected ? maybe i could assign instances of the cursor ?
We do something like this in Dabo. We define connections, giving
each an identifying name, and the application object loads the
definitions at startup (without actually connecting). When a business
object needs a connection, they call
self.Application.getConnectionByName(connName). If that connection
hasn't been made, the app connects and returns a reference to the
connection. It also stores the reference, so that the next time a
business object requests that connection, it simply returns the
existing reference.
<shameless plug>
If you're developing database applications, especially if they
involve a GUI, you really should take a look at Dabo. It's an app
framework written by database developers for database developers.
</shameless plug>
-- Ed Leafe
-- http://leafe.com
-- http://dabodev.com This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: Tom Martin |
last post by:
I'm a Java Web developer NEWBIE that has inherited a website that
fails every 2 hours due to poor connection pooling between Tomcat
4.0.6 and mySQL. In efforts to resolve this problem, I've...
|
by: sridhar nagabhurshana |
last post by:
hello
I am trying to connect to mysql database using connector/J jdbc driver
this is my code
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
|
by: Robert Mazur |
last post by:
MySQL 5.0 alpha (binary install) on
Solaris 9 -or- RedHat 8.0
mysql-connector-java-3.0.8-stable
-----------------------
Is there something different going on with JDBC and the alpha version...
|
by: Mike Chirico |
last post by:
Interesting Things to Know about MySQL
Mike Chirico (mchirico@users.sourceforge.net)
Copyright (GPU Free Documentation License) 2004
Last Updated: Mon Jun 7 10:37:28 EDT 2004
The latest...
|
by: Peter Höltschi |
last post by:
I try to install MySQL Connection/J on a Windows XP box. MySQL on
localhost works fine. But I cannot load the MySQL Connection/J Driver.
The CLASSPATH variable is set like this:...
| |
by: Ike |
last post by:
Let's say I have a MySQL 4.12 database, opened to the internet on
111.111.111.111 allowing all incoming and outgoing ports. I have a username
and password setup, which CAN connect to this database,...
|
by: menmysql |
last post by:
i am not bale to solve this problem since two weeks
i am trying to access records from mysql database using jsp. inside this jsp program i wrote all my JDBC code. it is working very nicely and...
|
by: oll3i |
last post by:
my librarybean
package library.ejb;
import java.sql.*;
import javax.ejb.*;
import library.common.*;
@Stateless @Remote
|
by: paulq182 |
last post by:
PLEASE HELP ME WITH MY CODE??
import java.sql.*;
import java.io.*;
class min_filmdb_rel_mysql {
public static void main (String args )
throws SQLException, IOException {
|
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,...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
| |
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
|
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...
|
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...
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence...
| |