Database Connection Function for Python

Question

I have written a function for connecting to a database using pymysql. Here is my code:

def SQLreadrep(sql):
    connection=pymysql.connect(host=############,
                               user=#######,
                               password=########,
                               db=#########)
    with connection.cursor() as cursor:
            cursor.execute(sql)
            rows=cursor.fetchall()
    connection.commit()
    connection.close()
    return rows

I pass the SQL into this function and return the rows. However, I am doing quick queries to the database. (Something like "SELECT sku WHERE object='2J4423K').

1. What is a way to avoid so many connections?
2. Should I be avoiding this many connections to begin with?
3. Could I crash a server using this many connections and queries?

Answer 1

Let me answer your last question first. Your function is acquiring a connection but it is closing it prior to returning. So, I see no reason why unless your were multithreading or multiprocessing you would ever be using more than one connection at a time and you should not be crashing the server.

The way to avoid the overhead of creating and closing so many connections would be to "cache" the connection. One way to do that would be to replace your function by a class:

import pymysql

class DB(object):
    def __init__(self, datasource, db_user, db_password):
        self.conn = pymysql.connect(db=datasource, user=db_user, password=db_password)

    def __del__(self):
        self.conn.close()

    def query(self, sql):
        with self.conn.cursor() as cursor:
            cursor.execute(sql)
            self.conn.commit()
            return cursor.fetchall()

Then you instantiate an instance of the DB class and invoke its query method. When the DB instance is grabage collected, the connection will be automatically closed.