Go4Expert

Go4Expert (http://www.go4expert.com/)
-   Python (http://www.go4expert.com/articles/python-tutorials/)
-   -   oursql: The Best Python Module for MySQL Access (http://www.go4expert.com/articles/oursql-python-module-mysql-access-t28749/)

pradeep 18Jul2012 18:53

oursql: The Best Python Module for MySQL Access
 
In a previous article Access MySQL Databases in Python 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: Python

## 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: Python

## 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: Python

## 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/


All times are GMT +5.5. The time now is 14:10.