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').

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

Booboo · Accepted Answer · 2019-04-24 17:55:02Z

3

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.

answered Apr 24, 2019 at 17:55

Booboo

45.6k4 gold badges46 silver badges74 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Tanner Clark Over a year ago

Thank you! I was concerned about the load of connections.

Booboo Over a year ago

An alternate approach is to install a pymysql connection pool package from the Python Package Index (PyPI), such as pymysql-pooling. This would be a good approach if you were doing, for instance, multithreading and you did need multiple, simultaneous connections.

Collectives™ on Stack Overflow

Database Connection Function for Python

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related