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

    Join Date
    Mar 2003
    Posts
    325
    Rep Power
    12

    OO for db connection


    I have a problem with the db class.

    Do I have to close the connection for every query?

    It would be prefereable if I don't have to manually close it in the interface .. the class should take care of that itself.


    For example, I use the db class from another class
    So in the method I would need to open a connection, execute the query and close it again. And do this for every query.

    Is there a better way around this problem ?

    Cheers,
    Eli
  2. #2
  3. Hello World :)
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Mar 2003
    Location
    Hull, UK
    Posts
    2,537
    Rep Power
    69
    Hey ele, nope, you can do multiple queries from each connection! So in theory closing your cursor followed by the conection when your done should work fine!

    Mmmm, if this isnt sensative data just attach it so we can see what your doing, or even email it to me..

    are we talking about a brand new interface i.e. from scratch or just a wrapper for an existing module like MySQLdb

    Summary: There has definatly got to a better way to do it than this! opening and closing a new connection each time would really naf preformace! I think it just depends on how your planning to implement your class...

    Mark.
    programming language development: www.netytan.com Hula

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

    Join Date
    Mar 2003
    Posts
    325
    Rep Power
    12
    Nah ... I'm just trying to go full OO so am just redesigning some of the libraries.

    For examle, my user class would be somethign like

    Code:
    class User:
        def __init__(self,username,password):
            self.username = username
            self.password = password
    
        def addUser(self,username,password):
            mysql = MySQL() # I can put the db connection in the constructor
            mysql.executeQuery("Insert ....... ")
            mysql.close()
    
        def removeUser(self,id)
            mysql = MySQL()
            mysql.executeQuery("Delete ....... ")
            mysql.close()
    Whereas I would rather connect to mysql in the constructor of the user class and have the mysql as an attribute of hte class. Such as:

    Code:
    class User:
        def __init__(self,username,password):
            self.username = username
            self.password = password
            self.mysql = MySQL() # db connection in the constructor
    
        def addUser(self,username,password):
            self.mysql.executeQuery("Insert ....... ")
    
        def removeUser(self,id)
            self.mysql.executeQuery("Delete ....... ")
    You can see how much cleaner this class looks now I've moved the mysql connetion implementation into the costructor so the mysql objects connection and setup is hidden from the user class. So much more modular too. And it all uses the same connection.

    In fact, if I make it a static variable (I think its called static - where there is only a single shared instance among all uesr objects created) this would almost do the trick, or atleast make it some degree simpler as it could make all user objects use the one connection, so the amx number of connections in the system is the number of classes you have, as opposed to the number of class instances!. I've seen a static variable done once but never did it myself. Acutally !! This might work!

    But there needs to be a way to disconnect from mysql when the user object is deleted. This is the problem in a nutshell!

    Eli
  6. #4
  7. Hello World :)
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Mar 2003
    Location
    Hull, UK
    Posts
    2,537
    Rep Power
    69
    Yeah i get what your trying to do bro, and i totaly agree, def allot cleaner, but wheres MySQL() from? I don't see it in MySQLdb, your rewriting the entire wrapper???

    Very interested, if you attach the full code so i can see whats going on, done right this would make a nice project, especially since Pythons DBI's aint really too my taste!

    Mark.
    programming language development: www.netytan.com Hula

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

    Join Date
    Mar 2003
    Posts
    325
    Rep Power
    12
    yea .. was going to write a wrapper class for it. Havent done it yet tho. Hopefully writing a simple mysqldb wrapper class shouldn't be a whole project ...

    For clarity when I say mysql class, I mean the wrapper I class I'm writing.

    I was going to make it similar to a php one I was writing .. but came across the same problem. It'd be something similar to this http://cgi.cse.unsw.edu.au/~elip/class.mysql
    But trying to fix that also.

    The main differences I'm looking for is

    1. One mysql instance as an attribute for each class instance that is created (in the example above) and somehow auto close the myscql connection in the mysql class when the mysql class object is deleted which is what would happen when hte class using mysql is delted.
    Eg.

    controller program using the user class object
    Code:
    class controller:
        def __init__(...):
            ....
        def addUser(...):
    
            user = User() # connection to mysql initiated in __init__ method of user class
            user.addUser(name='blah')
            user.deleteUser(id=7)
            # end of method, so user object deleted, and so the mysql wrapper 
            # class instance which was a class variable of user is detled, so 
            # would like to auto disconnect when mysql class instance is deleted
    
        def someothermethod():
    and possibly this also:
    2. static mysql class object as I described above .. so only one instance of it per class (as oppose to per class instance) .. but stil need a way to close the connection in the myxql wrapprer class when the last class that uses it is deleted; But if the first thing works, there's prob no need for this now I think of it/



    Eli
  10. #6
  11. Hello World :)
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Mar 2003
    Location
    Hull, UK
    Posts
    2,537
    Rep Power
    69
    Ah ok, so you havn't written MySQL() yet then... this alone wouldnt be a whole project, i was talking about writing from scratch

    Well' id say you should start by writing MySQL(). Then you can worry about hiw User is gonna work! One thing though, your MySQL() class will need to take several options so it knows how to connnect or your programs gonna barf bigtime...

    Which i know you already know, anyway with your permission i'll try and rewrite your PHP class in Python (only so it works ), just as an example.

    Mark.
    programming language development: www.netytan.com Hula

  12. #7
  13. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2003
    Posts
    325
    Rep Power
    12
    Originally posted by netytan
    Ah ok, so you havn't written MySQL() yet then... this alone wouldnt be a whole project, i was talking about writing from scratch

    Well' id say you should start by writing MySQL(). Then you can worry about hiw User is gonna work! One thing though, your MySQL() class will need to take several options so it knows how to connnect or your programs gonna barf bigtime...

    Which i know you already know, anyway with your permission i'll try and rewrite your PHP class in Python (only so it works ), just as an example.

    Mark.
    Yea I'm building reusable libraries that I can use in all other applications, so just want to get this module perfect. You need the lower layers correct cuz you have to re write whole systems if u modify lower layers later.

    I have to worry about the MySQL() class now rather than later since I want to use it to build upon.

    Yea sure .. go ahead, but the problem still remains .. I need to catch the MySQL() object instance just before it's deleted (ie garbage collected) so i can disconnect it from the database.

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

    Join Date
    Mar 2003
    Posts
    325
    Rep Power
    12
    FOUND IT !! =))))


    In python ... the destrcutor is just an inherited method in the class that is called __del__(self)

    This is an example of the destructor being used. It is called automatically when an object is destroyed.

    Code:
    #!/usr/bin/python
    
    class MyTest:
    
      def __init__(self):
        print "object created"
    
      def __del__(self):
        print "object deleted"
    
    
    
    def fn():
    
      t = MyTest()
      raw_input('type something and then test will delete')
    
    
    if __name__=="__main__":
      raw_input('press enter to enter function')
      fn()
      raw_input('fn finished, press enter to end prog')
    So I can just disconnect from the db in the destructor.

    And for anyones information, this is the situation in php:
    In php5, classes will have destructors, but till that comes out you can just register a method using 'register_shutdown_function()', which registers a function for execution on shutdown (ie when the script ends)

    so to make it disconnect I will just do :

    Code:
    class DB {
       function DB() {
           $this->host = "mysql.host.com";
           $this->db = "myDatabase";
           $this->user = "root";
           $this->pass = "mysql";
           $this->link = mysql_connect($this->host, $this->user, $this->pass);
           mysql_select_db($this->db);
           register_shutdown_function($this->close);
       }
       function close() {
           mysql_close($this->link);
       }
    }
    It's a hack, but it's a very very simple modification when php5 comes out soon .. just remove this :
    Code:
    'register_shutdown_function($this->close);'
    and put in this:
    Code:
     function __destructor() {
        $this->close
     }
    Eli
    Last edited by lazy_yogi; December 13th, 2003 at 08:03 AM.
  16. #9
  17. Hello World :)
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Mar 2003
    Location
    Hull, UK
    Posts
    2,537
    Rep Power
    69
    Surprisingly not far off what i had

    Code:
    #!/usr/bin/env python
    
    import MySQLdb
    
    class mySQL:
    	
    	def __init__(self, host, username, password, database):
    		self.connection = MySQLdb.connect(host, username, password, database)
    		self.cursor = self.connection.cursor()
    	
    	def query(self, SQL):
    		self.cursor.execute(SQL)
    		result = self.cursor.fetchall()
    		return result
    		
    	def __del__(self):
    		self.cursor.close()
    		self.connection.close()
    Mark.
    programming language development: www.netytan.com Hula

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

    Join Date
    Mar 2003
    Posts
    325
    Rep Power
    12
    Ok ... this is what I have.
    It iterates internally, it's cursor gets by field name instead of field number, and I think the interface is nice to use

    Any comments or improvements that anyone can see ?

    Cheers,
    Eli

    Oh and on a side note, some of the method comments may be redundant .. do you think it's good practice to have the line of comment in anyway?

    Code:
    #!/usr/bin/python
    
    import MySQLdb
    import MySQLdb.cursors
    
    
    # EXAMPLE USAGE
    # -------------
    # 
    # SELECT
    # ------
    # import MySql
    # mysql = MySql()
    # mysql.query("SELECT field FROM table")
    # if mysql.numrows() > 0:
    #     while mysql.movenext():
    #         print mysql.getfield("field")
    #
    # INSERT
    # ------
    # import MySql
    # mysql = MySql()
    # mysql.query("INSERT INTO table (field) values ('value')")
    #
    # UPDATE
    # ------
    # import MySql
    # mysql = MySql()
    # mysql.query("UPDATE table SET field='newvalue' WHERE fieldID=1")
    # 
    # DELETE
    # ------
    # import MySql
    # mysql = MySql()
    # mysql.query("DELETE FROM table WHERE fieldID=1")
    
    
    class MySql:
    
        def __init__(self, host='localhost', username='', password='', database='test'):
            """constructor: open the connection and a get a handle"""
    
            print "constructing...",
            self.connection = MySQLdb.connect(host, username, password, database)
            self.cursor = self.connection.cursor(cursorclass=MySQLdb.cursors.DictCursor)
            print "constructed"
    
    
        def query(self, SQL):
            """ execute the query"""
    
            self.cursor.execute(SQL)
            self.currentRecord = None
    
    
        def numrows(self):
            """return the total number of rows in the resultset"""
    
            return self.cursor.rowcount
    
    
        def movenext(self):
            """move foreward a row and return whether we reached end of result set"""
    
            self.currentRecord = self.cursor.fetchone()
            return self.currentRecord is not None
    
    
        def getfield(self,field):
            """return the field value from the current row"""
    
            return self.currentRecord[field]
    
    
        def __del__(self):
            """destructor: close the handle and connection before destruction"""
    
            print "destructing...",
            self.cursor.close()
            self.connection.close()
            print "destructed"
  20. #11
  21. Hello World :)
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Mar 2003
    Location
    Hull, UK
    Posts
    2,537
    Rep Power
    69
    Impressive, definalty a joy to use! MUCH cleaner than using MySQLdbs usual DBI. good work Ele

    I'm thinking maybe you should make movenext() (or another the whole class) an itorator. This would make it faster, not to mention easier to use with for loops

    I'm not sure how fetchall() and fetchone() compare, but i'm a fetchall() guy myself, this way you can store results in a list for manipulation or just for latter. you could do this with your code, but you'd have to iterate over and append to a new list

    I'v playing with there base dll (_mysql.pyd) and it is seriously wierd, i can get it to connect but i dont know where to start with retrieving results. Ah i'll work it out...maybe! I'll post the code if i ever get it done!

    You done with thisof you plan on taking it further?

    Mark.
    programming language development: www.netytan.com Hula

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

    Join Date
    Mar 2003
    Posts
    325
    Rep Power
    12
    I'm not sure what you mean exactly by changing it to an iterator, could you show me with an example what you mean ?
    From what I understand, mine is very much the same as an iterator - going by what this looks like: http://www.boost.org/libs/python/doc...terators.html. But if u don't mind could you gimme an example to clarify. The searched sites from google discuss iterators with generators which I never really understood either =/


    Yea, I don't see a need to do anything except move one item at a time .. otherwise I would switch back to fetchall.

    Umm .. just moving to pure OO. I love the concept of working hard to make something work perfectly and testing it thoroughly and not looking at it again .. and then just using a nice simple interface of it (such as in OO style). So just building libraries at the moment. This particular problem was with my previous project, and so am glad I worked out an ideal solution. I'm working full time now .. and wanna redo a website I webmastered last year (in php) so will be working on that in my spare time. But wanted to get proper class libraries for db access, sessions, and user obects. With this idea of having a mysql object as an attribute, it makes the whole process so much simpler and more idealistic. Not to mention re-usable in probably every other app that I write.

    Dun forget to get back to me on the iterator issue if u have the time =)

    Cheers,
    Eli
  24. #13
  25. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2003
    Posts
    325
    Rep Power
    12
    Ok .. figured out generators and iterators ... here's examples to show. I'd probably say generator1 is the way to go .. but since the result of a query is not the same as the mysql object(ie they are 2 different objects), I'm thinking I should make another object nested in the mysql class (like T_Iter) but use generators such as in generator1 within the nested object

    Any comments/ideas ?

    Cheers,
    Eli


    Code:
    #!/usr/bin/python
    from __future__ import generators
    
    class T:
    
            class T_Iter:
                    def __init__(self,obj):
                            self.obj = obj
                            self.pos = -1
                    def movenext(self):
                            self.pos += 1
                            return self.pos < len(self.obj)
                    def val(self):
                            return self.obj[self.pos]
    
            def __init__(self):
                    self.arr = [1,2,3,4]
    
            def __iter__(self):
                    return self.T_Iter(self.arr)
    
            def generator1(self):
                    ## can make the result from db local here
                    ## by doing result = self.executeQuery(sql)
                    result = self.arr
                    for row in result:
                            yield row
    
            def generator2(self):
                    pos = 0
                    while pos < len(self.arr):
                            yield self.arr[pos]
                            pos += 1
    
    
    def fn1():
            t = T()
            iter = t.__iter__()
            while iter.movenext():
                    print iter.val()
    
    def fn2():
            t = T()
            for elem in t.generator1():
                    print elem
    
    def fn3():
            t = T()
            for elem in t.generator2():
                    print elem
    
    fn1()
    fn2()
    fn3()
  26. #14
  27. Hello World :)
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Mar 2003
    Location
    Hull, UK
    Posts
    2,537
    Rep Power
    69
    Good man , iterators and generators are pretty new and i havnt actually used them more than once...

    anyway i've created a prototype multi DBI class named pySQL, i've mananged to test this with MySQL and SQLite (and in theory should work with PostgreSQL too) with the same results so i'm pretty impressed

    Code:
    docstrings have been removed to save space
    
    class pySQL:
    
    	def __init__(self, dbi, *options):	
    		self.connection = dbi.connect(*options)
    		self.cursor = self.connection.cursor()
    
    	def __del__(self):
    		self.cursor.close()
    		self.connection.close()
    		
    	def __iter__(self):	
    		return self
    		
    	def next(self):
    		self.result = self.cursor.fetchone()
    		if self.result is None:
    			raise StopIteration
    		return self.result
    		
    	def query(self, SQL):
    		self.cursor.execute(SQL)
    		
    	def count(self):
    		return int(self.cursor.rowcount)
    Advantages:
    easily change between DBIs
    same simple wrapper for muliple DBIs
    allows iteration over results which makes accessing them simple and elegant

    Disadvantages:
    since dbi.cursors.DictCursor isnt available in SQLite (and maybe others) results can only be referanced by index which is a shame

    Mark.
    Last edited by netytan; December 14th, 2003 at 08:50 PM.
    programming language development: www.netytan.com Hula

  28. #15
  29. Hello World :)
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Mar 2003
    Location
    Hull, UK
    Posts
    2,537
    Rep Power
    69
    incase your a little comfused some lil examples, the main improvments can be seen while selecting data since you can simply itertate over the pySQL istance

    Code:
    Retrieving Results
    
    import MySQLdb, pysql
    
    mysql = pysql.pySQL(MySQLdb, 'localhost', 'user', 'pass', 'db')
    mysql.query('SELECT * FROM sampledb')
    for result in mysql:
        print result[0], result[1]
    
    -----
    
    import sqlite, pysql
    
    lite = pysql.pySQL(sqlite, 'sample.db')
    lite.query('SELECT * FROM sampledb')
    for result in mysql:
        print result[0], result[1]
    Code:
    General Query
    
    import MySQLdb, pysql
    
    mysql = pysql.pySQL(MySQLdb, 'localhost', 'user', 'pass', 'db')
    mysql.query("INSERT INTO table (field) values ('value')")
    
    -----
    
    import sqlite, pysql
    
    lite = pysql.pySQL(sqlite, 'sample.db')
    lite.query("INSERT INTO table (field) values ('value')")
    Note: i just typed these in here so they are very much untested!

    Mark.
    programming language development: www.netytan.com Hula

Page 1 of 2 12 Last
  • Jump to page:

IMN logo majestic logo threadwatch logo seochat tools logo