Page 2 of 2 First 12
  • Jump to page:
    #16
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2003
    Posts
    325
    Rep Power
    12
    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
  2. #17
  3. Hello World :)
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Mar 2003
    Location
    Hull, UK
    Posts
    2,537
    Rep Power
    69
    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.
    Last edited by netytan; December 15th, 2003 at 10:52 AM.
    programming language development: www.netytan.com Hula

  4. #18
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2003
    Posts
    325
    Rep Power
    12
    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
  6. #19
  7. Hello World :)
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Mar 2003
    Location
    Hull, UK
    Posts
    2,537
    Rep Power
    69
    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
    programming language development: www.netytan.com Hula

  8. #20
  9. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2003
    Posts
    325
    Rep Power
    12
    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
  10. #21
  11. Hello World :)
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Mar 2003
    Location
    Hull, UK
    Posts
    2,537
    Rep Power
    69
    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.
    programming language development: www.netytan.com Hula

  12. #22
  13. Hello World :)
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Mar 2003
    Location
    Hull, UK
    Posts
    2,537
    Rep Power
    69
    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.
    programming language development: www.netytan.com Hula

  14. #23
  15. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2003
    Posts
    325
    Rep Power
    12
    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
Page 2 of 2 First 12
  • Jump to page:

IMN logo majestic logo threadwatch logo seochat tools logo