Page 2 - OO for db connection

Dev Shed Forums Sponsor:

#16

December 15th, 2003, 07:56 AM

lazy_yogi

Contributing User

Join Date: Mar 2003

Posts: 325

Time spent in forums: 7 h 58 m 36 sec
Reputation Power: 6

unfortunantly this piece of code has problems with queries before the current query had finished iterating:

Code:

    mysql = MySql()
    mysql.query("select * from user")
    for row in mysql:
        print row["id"]

        mysql.query("select * from user")
        for row in mysql:
            print '-'+str(row["id"])

So I've got two options that work. Each uses fetchall. The first one is with an iterator class inside the main class - this is often how you implement iterators in c++. The other is a generator. The code is much shorter and simpler, but you don't get to see how it all works.

Code:

#!/usr/bin/python

import MySQLdb

class MySql:

    def __init__(self, host='localhost', username='', password='', database='test'):
        self.connection = MySQLdb.connect(host, username, password, database)
        self.cursor = self.connection.cursor(cursorclass=MySQLdb.cursors.DictCursor)

    def __del__(self):
        self.cursor.close()
        self.connection.close()

    def query(self, SQL):
        self.cursor.execute(SQL)

    def iterator(self):
        return self.MySql_Iter(self.cursor.fetchall())

    class MySql_Iter:
        def __init__(self,obj):
            self.obj = obj
            self.pos = -1

        def __iter__(self):	
            return self

        def next(self):
            self.pos += 1
            if self.pos >= len(self.obj):
                raise StopIteration
            return self.obj[self.pos]

mysql = MySql()
mysql.query("select * from user")
for row in mysql.iterator():
    print row["id"]

    mysql.query("select * from user")
    for row in mysql.iterator():
        print '-'+str(row["id"])

Code:

#!/usr/bin/python

from __future__ import generators
import MySQLdb


class MySql:

    def __init__(self, host='localhost', username='', password='', database='test'):
        self.connection = MySQLdb.connect(host, username, password, database)
        self.cursor = self.connection.cursor(cursorclass=MySQLdb.cursors.DictCursor)

    def __del__(self):
        self.cursor.close()
        self.connection.close()

    def query(self, SQL):
        self.cursor.execute(SQL)

    def generator(self):
        for row in self.cursor.fetchall(): yield row


mysql = MySql()
mysql.query("select * from user")
for row in mysql.generator():
    print row["id"]

    mysql.query("select * from user")
    for row2 in mysql.generator():
        print '-'+str(row2["id"])

Cheers,
Eli

#17

December 15th, 2003, 09:50 AM

netytan

Hello World :)

Join Date: Mar 2003

Location: Hull, UK

Posts: 2,536

Time spent in forums: 1 Week 2 Days 18 h 3 m 4 sec
Reputation Power: 63

yeah, thats the disadvantage of not closing the cursor each time... the cursor can only hold the results for one query at a time so the next query overwrites any results not retrieved!

I have NO idea why anyone would want to call a query within a for loop but i imagin this could be overly slow! If you do have to do this then it would make no differance if you create another DBI instance

Your classes are pretty nice... although they still lack the elegance of "true" iteration since you have to call a method i.e.

Code:

for each in mysql.interator():
    print each

#instead of

for each in mysql:
    print each

If this is what you wanted to do you could simply have a method return fetchall() and let for iterate over the results itself which would be allot simpler.

See where i'm coming from here Ele?

Anyway you could write the example above semthing like with pysql...

Code:

import pysql, sqlite

main = pysql.pySQL('sample.db')
main.query('SELECT * FROM user')
subs = pysql.pySQL('sample.db')
subs.query('SELECT * FROM user')

for each in main:
    print each[0]
    for each in subs:
        print each[0]

too answer your previous question (in the reply you deleted), SQLite is very sweet! Definatly a massive improvment over other flatfile DBIs, the speed is excelent and its so easy to embed in apps! Gotta love it.. although as a replacment for MySQL i dont know, depends on the app... and the programmer i guess

Mark.

__________________
programming language development: www.netytan.com – Hula

Last edited by netytan : December 15th, 2003 at 09:52 AM.

#18

December 15th, 2003, 04:31 PM

lazy_yogi

Contributing User

Join Date: Mar 2003

Posts: 325

Time spent in forums: 7 h 58 m 36 sec
Reputation Power: 6

Hi Mark,

Yea, it's an unlikely situation, however you're not seeing the big picture. I created this class so that other classes can have this as a *single* object attribute that is auto created in its constructor and auto removed in its destructor such as in my user class example:

Code:

class User:
    def __init__(self):
        self.mysql = MySql()

    def other_methods(self):
        self.mysql.query(sql)
        use it

When this user object is is created in the main code such as this, I don't have to do anything with the db .. its all done automaticalty in the the db access is created and deleted in the method as you see here.

Code:

class mainController():

    def do_something_using_user():
        user = User()
        user.addNewUser()

The point is that I don't want to have to instantiate another mysql() object at any time other than the one in the constructor of the user class or any other class that needs db access. So only want to use one instance of MySql. Which is why I'm doing it my way.

The second point is that this doesn't seem right.

Code:

for each in mysql:
   print each

I mean, you're not iterating over the db accessing object, you're iterating over the resultset, which is why i prefer this interface :

Code:

for row in mysql.getResultset():
    print row["id"]

or this interface

Code:

for row in mysql.getResultset:
    print row["id"]

And the third piont is robustness in such that you can have nested queryes withoout breaking the code. This also has to do with the first point where I only want one instance of the db access object in each other object.

concerning sqlite:
Ahh ok .. but as a user of db's I don't see a piont of concerning myself with whether its useing flat file or not. The important points are speed, and robustness, and full-featuredness of the db. So I don't see that as an issue. It's either better or worse in these regards that would sway me to change or not.

Thanx for the info and discussion Mark

Cheers,
Eli

#19

December 15th, 2003, 04:58 PM

netytan

Hello World :)

Join Date: Mar 2003

Location: Hull, UK

Posts: 2,536

Time spent in forums: 1 Week 2 Days 18 h 3 m 4 sec
Reputation Power: 63

I see your point Ele

and if i ever get to the point where i need to do nested queries i may adapt the interface however at least for now i'm going for DBI compatability and most importantly ease of use...

I guess if i needed to do nested queries id set up the code so each query has a seperate cursor and so seperate result pool

that seems like the best way to do this.

With what you have no i really dont think id bother with an iterator either:

a) it really isnt needed and could be done so much easier without it.
b) any speed increase you have is likly being lost by calling your subclass.
c) simplicity is being decreased (and i like the fact that u can move over a result set so damn easily

)

Anyway just my oppion... i just upgraded my PySQLite installation so i have made a few changes to my class. The class itself couldnt be simpler and if other features are required you could just inherit from pySQL and build on that

Feature wise SQLite is different to MySQL because the subset of SQL that SQLite uses is quite different to SQLite, plus SQLite is typeless

. Give it ago, sure you'll get into it really quickly!!!

Mark.

Attached Files

pysql.py (1.1 KB, 204 views)

#20

December 15th, 2003, 05:43 PM

lazy_yogi

Contributing User

Join Date: Mar 2003

Posts: 325

Time spent in forums: 7 h 58 m 36 sec
Reputation Power: 6

yea, each query having a seperate cursor seems like a good idea, but how would I close them all automatically. I think it has the same problem as making multiple instances of the mysql object that I mentioned before. I want the connections (and cursors) to be handled automatically in the mysql constructor and destructor (ie encapsulation/data hiding)

It's not so much for nested queries, as being able to do queries without killing the previous query result. All while having only the one instances of mysql for the reasons I mentioned before(ie bing an attribute of another object)

I think we're looking for different qualities in our sql wrappers

... which is fair enough. I'm looking for it to work seamlessly within my whole system, whereas you're looking for simplicity of the class itself.

sqlite is typeless ??? hmmm ... I'll take a look when I have some time. Sounds like it'd fit in well with php then too.

Cheers,
Eli

#21

December 15th, 2003, 06:06 PM

netytan

Hello World :)

Join Date: Mar 2003

Location: Hull, UK

Posts: 2,536

Time spent in forums: 1 Week 2 Days 18 h 3 m 4 sec
Reputation Power: 63

Yeah

, Mmm, well it is possable! It just depends on how you work it i guess, will think about it and let ya know

.. if not, nowhere in the docs does it say cursors must be closed

but i'd rather them be.

as for that, i could always add a fetchall method to the class so data from the db can be stored in a variable untill needed

we'll see how it goes!

New: when printed a pySQL instance now outputs the number of results. I'm kinda just building it up slowly!

Def, i want it to be easy to use and learn and work with mulitple DBIs (thats the big one). I also want it as a baseclass for more specalised/extended wrappers but it does everythin i want so

Yeah, types dont mean anything in SQLite; all SQLite really understands is strings and numbers to a degree...

Mark.

#22

December 20th, 2003, 07:45 PM

netytan

Hello World :)

Join Date: Mar 2003

Location: Hull, UK

Posts: 2,536

Time spent in forums: 1 Week 2 Days 18 h 3 m 4 sec
Reputation Power: 63

Sat down this evening and gave this another think. I've come up with what seems like a nice solusion

or at least to me so i thought i'd show!

Code:

#!/usr/bin/env python

from MySQLdb import *

class myCursor:
	
	def __init__(self, connection, query):
		self.cursor = connection.cursor(cursors.DictCursor)
		self.cursor.execute(query)
		
	def __iter__(self):
		return self
		
	def __del__(self):
		self.cursor.close()
		
	def next(self):
		result = self.cursor.fetchone()
		if result is None:
			raise StopIteration
		return result

class mySQL:
	
	def __init__(self, *arg, **kwd):
		self.connection = connect(*arg, **kwd)
		
	def __del__(self):
		self.connection.close()
		
	def query(self, SQL):
		return myCursor(self.connection, SQL)

This new method returns a myCursor instance for each query, this cursor supports iteration but is till under deleopment, i plan to add at least a few more features to it before i'm done.

Mark.

#23

December 20th, 2003, 09:38 PM

lazy_yogi

Contributing User

Join Date: Mar 2003

Posts: 325

Time spent in forums: 7 h 58 m 36 sec
Reputation Power: 6

Nice code Mark

Yea, I wonder if its better to have a nested class for the cursor.

But this way seems to make more sense because the result set is a completely different object to the mysql accesor class

Eli

Thread Tools	Search this Thread
Email this Page	Search this Thread: Advanced Search
Display Modes	Rate This Thread
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode	Rate This Thread: