CODEWITHSUNDEEP
pythonoop
oz123
oz123

Reputation: 28838

Design a connection to multiple databases in Python

I have a Python application which uses both SQLite and Postgresql. It has a connector class for each database:

 class PostgresqlDatabase(Database):
     ...


 class SQLite(Database):
     ...

Both class share the same methods and logic, and the only thing that differentiate them the SQL is the parametrization of SQL queries. Most of the SQL queries are even identical, e.g. both have a method called _get_tag:

 # postgresql method with %s
 def _get_tag(self, tagcipher):
    sql_search = "SELECT ID FROM TAG WHERE DATA = %s"
    self._cur.execute(sql_search, ([tagcipher]))
    rv = self._cur.fetchone()
    return rv

# sqlite method with ?
def _get_tag(self, tagcipher):
    sql_search = "SELECT ID FROM TAG WHERE DATA = ?"
    self._cur.execute(sql_search, ([tagcipher]))
    rv = self._cur.fetchone()
    return rv

To really make it clear, the classes have exact identical method names. The SQL queries differ in each method. So what is my problem?

I find maintaining both classes annoying, and I feel a common class would benefit the code in the long run. However, creating a common class, would create a complex code. The __init__ would probably have to initialize the correct underlying cursor. This would create a small starting overhead, and small performance penalty if for example I would lookup the correct string every time, e.g. 

 @property: 
 def sql_search(self):
     return "SELECT ID FROM TAG WHERE DATA = {}".format(
          '?' if self.db == 'SQLite' else '%s')


 def _get_tag(self, tagcipher):

    self._cur.execute(self.sql_search, ([tagcipher]))
    rv = self._cur.fetchone()
    return rv

I am also afraid this approach would be also harder to understand when first looking at it.

Leaving my personal example, I would like to know what is the most acceptable way here. Should I keep maintaining both classes or write one more complicated class that does it all?

Is there a general rule of thumb?

Upvotes: 3

Views: 8144

Answers (1)

tenwest
tenwest

Reputation: 2317

It seems that inheritance is what you're looking for. It is a key feature of [OOP][1] (Another one in Java, Yes Java, but I like their docs).

As thefourtheye said in the comments, I believe you should move the identical methods into one class (in other words, delete one set of the identical methods).

Here is a very quick example:

class Connector(Database):
    """This is a super class, common elements go here"""
    def __init__(self):
        self.sql_search = "SELECT ID FROM TAG WHERE DATA = %s"
        self.common_varialbe = None #placeholder
        Database.__init__(self) #add necessary arguments

    def _get_tag(self, tagcipher, wildcard):
        #replace the sql search string with the wildcard.
        self._cur.execute(self.sql_search % (wildcard) , ([tagcipher]))
        rv = self._cur.fetchone()
        return rv
   def some_common_method(self, uncommon_value):
       self.common_variable = uncommon_value

class Postgresql(Connector):
    """postgresql subclass using %s. 
    unique postgresql elements go here"""
    def __init__(self):
        #initialise the superclass
        Connector.__init__(self)

        self.wildcard = '%s'
        self.uncommon_value = 'py hole'
        #other unique values go here

class Sqlite(Connector):
    """etc"""
    def __init__(self):
        #initialise the superclass
        Connector.__init__(self)

        self.wildcard = '?'
        #other unique values go here

    #other methods

Even from this example you can see some redundancy, but was included to show how things could be split up if necessary. With this class, i can:

>>>import connector
>>>sqlite = connector.Sqlite()
>>>sqlite.wilcard
`?`
>>>sqlite.sql_search
`SELECT ID FROM TAG WHERE DATA = %s`
>>>sqlite.sql_search % sqlite.wildcard
`SELECT ID FROM TAG WHERE DATA = ?`

If they truly differ only by strings, only one subclass is needed. you can use dict()s to store the unique bits:

class Connector(Database):
    def __init__(self,type):
        #describe all types in this dict
        types = {"sqlite":"?",
                 "postgre":"%s"}

        #Database.__init__(self) as necessary
        self.sql_search = "SELECT ID FROM TAG WHERE DATA = %s" % types[type]

    def _get_tag(self, tagcipher):
        #replace the sql search string with the wildcard.
        self._cur.execute(self.sql_search, ([tagcipher]))
        rv = self._cur.fetchone()
        return rv

So with this class:

>>>c = connector.Connector('sqlite')
>>>c.sql_search
`SELECT ID FROM TAG WHERE  DATA = ?`

As long as they are properly inheriting from the Database superclass, subclasses will share its cursor when Database.__init__(*args) is called

Upvotes: 2

Related Questions