oursql: The Best Python Module for MySQL Access

Discussion in 'Python' started by pradeep, Jul 18, 2012.

  1. pradeep

    pradeep Team Leader

    Joined:
    Apr 4, 2005
    Messages:
    1,645
    Likes Received:
    87
    Trophy Points:
    0
    Occupation:
    Programmer
    Location:
    Kolkata, India
    Home Page:
    http://blog.pradeep.net.in
    In a previous article [THREAD=28490]Access MySQL Databases in Python[/THREAD] few months back, in that I had discussed using MySQLdb python module to access MySQL in Python scripts, though that being easy & good lacked many important features like placeholders/parameterization, lazy execute & fetch, buffering of large fields like BLOB fields in queries. So, here we'd be looking into Python module oursql which covers these missing features.

    Installation



    Installation is pretty simple and straightforward, you can install using pip or you can compile from source, here are they two ways to do it.

    To install using pip:

    Code:
    $ pip install oursql
    To install from source, get the source from https://launchpad.net/oursql

    Unpack and get in source directory, then issue the following commands:

    Code:
    $ python setup.py build_ext
    $ python setup.py install
    Make sure you have the MySQL C Client libraries available on your system.

    Basics



    In the following sample, we'll look at basic operations like connecting, querying and fetching the resultset.

    Code:
    ## import the module
    import oursql
    
    ## connect
    conn = oursql.connect(host='localhost', user='root', passwd='SXSXSX', db='test')
    
    ## get the cursor object, by the specifying the cursor class to be used, 
    ## if left blank defaults will be used
    curs = conn.cursor(oursql.DictCursor)
    
    ## execute a query
    curs.execute("SELECT * FROM Employee")
    
    ## loop, an fetch the resultset
    while (1):
         row = curs.fetchone()
         ## row empty marks end of resultset
         if row == None:
           break
         ## do something with the rows
         print "%s" % (row['name'])
    

    Advanced Usage



    One of the most important features needed by any database library is placeholders, and the most popular placeholder style is the qmark style, the places where the values need to be filled in are replaced with ?, this leaves the headache of quoting & escaping values to the database library. Follow the example below:

    Code:
    ## execute a query with placeholders
    curs.execute("SELECT * FROM Employee WHERE salary >= ? AND city = ?",(50000,'Kolkata'))
    
    ## loop, an fetch the resultset
    while (1):
         row = curs.fetchone()
         ## row empty marks end of resultset
         if row == None:
           break
         ## do something with the rows
         print "%s" % (row['name'])
    
    Some uses require us to store a binary file into the database, traditionally we would slurp the file into a variable and then pass it to the query, but it taxed the memory of the system depending on the size of the file, with oursql we can do this much more efficiently, here's how:

    Code:
    ## get a binary file wrapper object
    bfw_image = oursql.BinaryFileWrapper(open('profile_image.jpg', 'rb'))
    
    ## pass the wrapper object instead of the value
    curs.execute('INSERT INTO blob_data VALUES (?, ?)',(profile_id, bfw_image))
    

    References



    http://packages.python.org/oursql/
     

Share This Page

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice