How to simplify MySQLdb connect from being called many times

Question

I have the following MySQL connection:

import MySQLdb as mdb
rbpdb = mdb.connect(host='db01.myhost.co.nl,
                        user='pdbois',
                        passwd='triplex',
                        db='myxxx')

In the course of coding, I will re-use this connection many times in many function. Both for reading, creating and updating the DB.

What's the best way to implement that without having to call the above snippets many times? Can we put it in separate file as class or functions? If so how can we call it?

possible duplicate of [What is the best solution for database connection pooling in python?](http://stackoverflow.com/questions/98687/what-is-the-best-solution-for-database-connection-pooling-in-python) — CDahn, Jun 18 '14 at 01:10
Yeah you can put it in a different class and then include it in your other files. — Shahar, Jun 18 '14 at 01:11

score 1 · Answer 1 · answered Jul 30 '14 at 22:25

The thing that works for me is just a module in a separate .py file and then importing it once want to use MySQL connections in any script.

In this file you have a definition of a connection- eg. MySQLdb or/and any other MySQL connector. I use also mysql.connector.

def connection(host, db, user, pass):
    try:
        import mysql.connector
        connection_db = mysql.connector.connect(
            user=user, 
            passwd=pass, 
            db=db,  
            host=host
        )
        return connection_db
    except:
        return None # or define here any other connection eg MySQLdb

And then you could define a function for each DML operation, e.g.

def insert(host, db, user, pass, sql):
    connection_db = connection(host, db, user, pass)
    cursor = connection_db.cursor()
    cursor.execute(sql)
    connection_db.commit()
    cursor.close()
    connection_db.close()

Finally at the end it is enough in any your script just to add:

import xxxxx.py
sql = """ INSERT INTO tbl (col1, col2) VALUES ('m','n'); """
var = xxxxx.insert(host, db, user, pass, sql)

How to simplify MySQLdb connect from being called many times

1 Answers1