Access MySQL Databases in Python

Discussion in 'Python' started by pradeep, May 31, 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
    Python being one of the most popular languages, it's being used increasingly used for creating dynamic websites or for specific parts of websites, like Google's support fully runs on Python scripts. But, for this type of usage the ability to access RDBMS is indispensable, although unlike PHP, Python does not provide any in-built functions to access the popular relational database system used on the web, it has plenty of stable & feature-rich third party modules which can help accomplish the requirement.

    In this article we'll look into the module MySQLdb which allows Python scripts to connect & MySQL databases.

    Installing MySQLdb



    MySQLdb requires Python 2.3.4 or higher to work, also it'll work only with MySQL 3.23.32 or higher. Download latest stable release of MySQLdb from http://sourceforge.net/projects/mysql-python/, extract and follow the instructions in the README file, the installation is pretty simple if you have the dependencies in place. If you have to running Debian or Ubuntu, all you need to do is to run the following command as root:

    Code:
    apt-get install python-mysqldb

    Basic Usage



    In basic usage we'll see how to connect to a MySQL database server, execute a SQL & fecthing a record, see the following sample, I've added comments to help understand the important steps.

    Code:
    import MySQLdb
    
    ## connect to database with the required credentials & details like, host, user, password etc.
    db = MySQLdb.connect(
        host = "localhost",
        user = "pradeep",
        passwd = 'mypass',
        db = "g4e")
    
    ## get a cursor
    my_cursor = db.cursor()
    
    user_name = 'pradeep'
    
    ## execute the query
    my_cursor.execute("""SELECT * FROM posts WHERE user = %s""", (user_name,))
    
    ## fetch a single row
    row = my_cursor.fetchone()
    
    ## mostly we'll be needing to fetch multiple rows, let's see how to go about it
    
    my_cursor.execute("""SELECT * FROM posts WHERE user = %s""", (user_name,))
    
    while (1):
         row = my_cursor.fetchone()
         ## row empty marks end of resultset
         if row == None:
           break
         ## do something with the rows
       
    ## getting no of rows returned
    print "rows returned: %d" % my_cursor.rowcount
    

    Advanced Usage



    In the following code example I would demonstrate advanced usage like error handling, fetching the whole resultset at once, closing statements handles & connection handles. Follow the sample code below:

    Code:
    import MySQLdb
    import sys
    
    ## connect to database with the required credentials & details like, host, user, password etc.
    ## we'll try to catch any errors, report error no. & message and exit gracefully
    try:
        db = MySQLdb.connect(
            host = "localhost",
            user = "pradeep",
            passwd = 'mypass',
            db = "g4e")
    except MySQLdb.Error, e:
        print "Errorno %d: %s" % (e.args[0], e.args[1])
        sys.exit(1)
    
    ## get a cursor
    my_cursor = db.cursor()
    
    user_name = 'pradeep'
    
    try:
        ## execute the query
        my_cursor.execute("""SELECT * FROM posts WHERE user = %s""", (user_name,))
    
        ## fetch alls rows in the resultset
        all_rows = my_cursor.fetchone()
    
        for row in all_rows:
            print "%s => %s" % (row["post_id"], row["post_url"])
    
        print "Total posts for user %s: %d" % (user_name, cursor.rowcount)
    
        ## now that we are done working with the cursor, let's close it
        my_cursor.close()
    except MySQLdb.Error, e:
        print "Errorno %d: %s" % (e.args[0], e.args[1])
        sys.exit(1)
    
    
    ## lastly close the connection to database
    db.close()
    

    References



    http://mysql-python.sourceforge.net/MySQLdb.html
     

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