[DB-SIG] new to list - subclass of 'MySQLdb'??

[Chris Cogdon]

On Nov 19, 2003, at 07:17, Hardy Merrill wrote:

> I'm new to this list, and just started experimenting with
> database access with Python.  I come from the Perl world
> where Perl DBI is the "standard" for database access in
> Perl.
>
> I've chosen MySQL to start with, so I downloaded
> MySQLdb and installed it(from source).  So, noticing that
> I have to 'import MySQLdb' and then refer to MySQLdb-specific
> functions, like
>
>    MySQLdb.connect(blah blah)
>
> I thought I'd try to find or write a database independent
> layer so that I could do something like
>
>    MyDB.py
>    -------
>    #!/usr/bin/python
>
>    import MySQLdb
>
>    class MyDB(MySQLdb):
>        def __init__(self):
>            MySQLdb.__init__(self)
>
>    test.py
>    -------
>    #!/usr/bin/python
>    import MyDB
>    mydb = MyDB()
>    conn = MyDB.connect(db="mydatabase", user="myuser", passwd="mypass")
>
> to make the code more generic and more portable if I were
> to change the database in the future.
>
> This doesn't work - when I run test.py I get this error:
>
>    [hmerrill at merrill mysqldb]$ ./test.py
>    Traceback (most recent call last):
>      File "./test.py", line 3, in ?
>        mydb = MyDB()
>    TypeError: 'module' object is not callable
>
> Am I doing this wrong?  How *should* I be doing this?  Anyone
> have some similar code they can show me to start me off in
> the right direction for subclassing MySQLdb to create a db
> independent interface to the MySQLdb module?

When you subclass, you need to subclass a CLASS, and not a MODULE.

The 'connect' function is just that, a function that will return an 
instance of a Connection to the database. To subclass this, you, 
unfortunately, need to know a little more about the internal structure 
of the MySQLdb package, and this means looking at the source.

Looking at MySQLdb/__init__.py you'll see that 'connect' is aliased to 
a Connect function. This in turn will import Connection from the 
MySQLdb/connections.py module, so we need to look at that:

In there, we see that Connection is indeed a standard class, so if we 
wanted, we could subclass that:

import MySQLdb.connections

class MyDB ( MySQLdb.connections.Connection ):

	def __init__ ( self, *args, **kwargs ):
		MySQLdb.connections.Connection ( self, *args, **kwargs )

Note how I'm doing the arguments, too.

Now, this can get really messy, and you'll need to 'track' any changes 
made to the MySQLdb module with your own code. Ick.

A BETTER way to handle this is to write a 'wrapper' rather than 
'subclass'. Like this:


class MyDB:

	def __init__ ( self, *args, **kwargs ):
		self.db = MySQLdb.connect ( *args, **kwargs )


And then you can expose whatever functions of the underlying DB through 
use of stub methods. Eg:

	def cursor ( self, *args, **kwargs ):
		return self.db.cursor ( *args, **kwargs )


However, what using this method allows you to do is to hide the SQL 
implementation completely from the users of your MyDB. Ie, anything 
that creates an instance of MyDB can be shielded from the SQL 
implementation completely. So... ignore the 'cursor' method above, and 
instead code things like this:

	def add_user ( self, user ):
		try:
			cur = self.db.cursor ()
			cur.execute ( "insert into users (id,name,shoe_size) values ( 
%(id)s, %(name)s, %(shoe_size)s )", user.__dict__ )
			self.db.commit ()
		except:
			self.db.rollback ()
			raise

If you end up with a LOT of methods, you can use the 'mix-in' to split 
your functions over a number of modules:

db_users.py:

class MyDBUsers:

	# All your methods relating to users

db_cars.py:

class MyDBCars:

	# All your methods relating to cars

my_db.py

import db_users, db_cars:

class MyDB ( db_users.MyDBUsers, db_cars.MyDBCars ):

	# All your 'generic methods'


And, when you create a MyDB, you get ALL the methods just as if they 
were defined in the same module.


-- 
    ("`-/")_.-'"``-._        Chris Cogdon <chris at cogdon.org>
     . . `; -._    )-;-,_`)
    (v_,)'  _  )`-.\  ``-'
   _.- _..-_/ / ((.'
((,.-'   ((,/   fL