5

I'm currently developing a Python script that does a few things with some data pulled from a MySQL database. To access this data, I'm using the module MySQLdb.

This module follows the guidelines laid out in PEP 249, the Python DB API, and involves creating a connection object, and a subsequent cursor object, which is used to iterate through the information.

Currently, In my project, I create a connection object any time I need to do a block of MySQL reading/writing, then close it when I'm done. However, I could easily pass the connection object around in order to avoid these repeated open/closes.

My question is: Taking into account security, resource management, etc., is the open; read/write; close; repeat for the next read/write; methodology better than the open; read/write; pass connection on for the next read/write; approach?

EDIT: Some more context. This specific Python script is heavily multithreaded. Does a complex process/thread environment affect which method is more apropos?

Patrick Perini
  • 22,555
  • 12
  • 59
  • 88

3 Answers3

1

the way I think about it, it's best to only keep a connection object open while you need it. It can be cumbersome at times to keep instantiating new connections, but generally speaking that is the better practice.

Jon Martin
  • 3,252
  • 5
  • 29
  • 45
  • 1
    In order to keep this from being a simple "What's your opinion?" question, do you have stats/facts/experiences to back that up? Thanks! – Patrick Perini Jul 22 '11 at 14:40
  • to be honest, it's not really a "facts" question aside from the fact that your current method of closing connections after finishing is slightly better resource management. having a connection open all the time means that the resource is being used even when it's not needed. it really won't make much of a difference though, this is more of a good practice question. – Jon Martin Jul 22 '11 at 14:43
1

a multithreaded application that uses database connections should probably use some sort of connection pool. in this scheme, you have a predifined number of connections, all managed in some sort of queue. When a thread needs to run a transaction, it gets a connection from the pool. If the pool is currently idle, then a new connection will be created for the thread; If it's very busy, it will make the thread wait until another thread frees a connection. when the thread is done with a connection, it returns the connection to the pool, which hands it off to another thread, or if there are no threads waiting for a connection, it closes it.

There are probably dozens of implementations of this logic, but I can highly recommend SQLAlchemy, and for more than just it's connection management (although you don't have to use it for much more than that)

SingleNegationElimination
  • 151,563
  • 33
  • 264
  • 304
0

As one of the best practices I would recommend using OurSQL over MySQLdb.

If I were you I'd keep a connection open at all times during the execution of your program only closing it when the program ends. The bad part about it is that your connection pool has one less connection in it, the good part is it saves you a lot of boilerplate.

supakeen
  • 2,876
  • 19
  • 19
  • That's pretty much what it boils down to. More boilerplate, or unnecessary resource usage. – Jon Martin Jul 22 '11 at 14:57
  • Also, making new connection is always slower than querying inside existing connection, so if your app make frequent quieries, constant reconnecting may slow it down significantly. Also, keep eye on max_connection limit of the server. – spacediver Jul 22 '11 at 15:38