1

If you have a lot of functions connecting to the database, how do you do it so you don’t 1) pass a lot of arguments to each function 2) don’t establish (and close) a new connection each time. In pythonic pseudocode

main():
    db = …
    host = …
    username = …
    password = …
    some_function(db, host, username, password)

some_function(db, host, username, password):
   try:
   con = psycopg2.connect("dbname='{}' user='{}' password='{}' host='{}'".format(db, username, password, host))
   cur = con.cursor()
   cur.execute("SELECT * FROM {}.{};".format(schema, ‘myTable’))
   function2(cur.fetchall(),db, host, username, password)
…
    except:
        …

Function2(db, host, username, password):
   try:
     con = psycopg2.connect("dbname='{}' user='{}' password='{}' host='{}'".format(db, username, password, host))
     cur = con.cursor()
     cur.execute("SELECT * FROM {}.{};".format(schema, ‘otherTable’))
     function3(cur.fetchall(),db, host, username, password)
     …
    except:
        …

It’s unfortunate that some_function() and function2() are almost exactly the same (though the … inside would be different). I’m wondering how can I extract this code to reduce redundancy? At the very least how do I reduce the function signatures with db, host, username, password?

I thought of putting these as global variables so at least I wouldn't have to pass the variables to each function that needs the database.

Celeritas
  • 14,489
  • 36
  • 113
  • 194

2 Answers2

0

Try creating a singleton db class that takes the arguments once, and then you only ever call getInstance() to get the object and open() to get the connection.

Derek Snider
  • 76
  • 1
  • 2
  • It's not that straightforward in `Python`: http://stackoverflow.com/questions/6760685/creating-a-singleton-in-python – DeepSpace Jul 29 '15 at 21:13
0

Here's what I do

class Database():
    def __init__(self):
        db = ....
        host = …
        username = …
        password = …
        self.con = psycopg2.connect("dbname='{}' user='{}' password='{}' host='{}'".format(db, username, password, host))
        self.cur = con.cursor()

    def function1(self):
        try:
            self.cur.execute("SELECT * FROM {}.{};".format(schema, ‘myTable’))
            msg = self.cursor.fetchall()
        except:
            ...

With this you only have to connect once and you can access cursor and database using self.cur and self.con respectively from within your class.
You can make any number of functions now, similar to function1 through which you can access the database .

Digvijayad
  • 534
  • 6
  • 12