Home | | Internet & World Wide Web HOW TO PROGRAM | | Internet Programming | | Web Programming | Database Application Programming Interface (DB-API) - Python

Chapter: Internet & World Wide Web HOW TO PROGRAM - Python

| Study Material, Lecturing Notes, Assignment, Reference, Wiki description explanation, brief detail |

Database Application Programming Interface (DB-API) - Python

Python’s open-source nature encourages independent developers to contribute additions to the language.

Database Application Programming Interface (DB-API)

 

Python’s open-source nature encourages independent developers to contribute additions to the language. In earlier versions of Python, many developers contributed modules that pro-vided interfaces to several databases. Unfortunately, these interfaces rarely resembled one another; if an application developer wanted to change the application’s database, the whole program had to be rewritten.

 

The Python Database Special Interest Group (SIG) was formed to develop a specifi-cation for Python database application programming interface (DB-API). The specifica-tion is now in version 2.0, and modules that conform to this specification exist for many databases. In this section we illustrate the Python interface to MySQL (module MySQLdb).

 

1. Setup

 

The next programming example assumes the user has installed MySQL and the MySQLdb module. The MySQLdb module must be downloaded and installed. [Note: The resources for this book posted at our Web site, www.deitel.com, include step-by-step instructions for installing MySQLdb.]

 

2. Simple DB-API Program

 

The example in this section lets the user choose an author from an XHTML drop-down list. The user then clicks a button to query the database. The database query returns a list of all books by that author.

Figure 28.22 is a CGI script that creates the XHTML author selection list by querying the database. Line 4 imports the MySQLdb module. This provides access to a MySQL data-base using the Python DB-API.

 

Lines 6–13 print the HTTP header and XHTML DOCTYPE string to the browser. The remainder of the program is contained in a try/except/else block. In the try block (lines 15–16), we attempt to connect to the MySQL database called books. Line 16 con-nects to the database. The call to connect in module MySQLdb returns an instance of a Connection object. In the call to connect, we pass the value "books" to the keyword argument db. A keyword argument is a named argument defined by a function. To pass a value to a named argument, we assign a value to the name inside the function call’s paren-theses, as in line 16. The Connection object returned by the call is stored in local vari-able connection.

 

    #!c:\Python\python.exe

    # Fig. 28.22: fig28_22.py

    # A program to illustrate Python's database connectivity.

    import MySQLdb

    print "Content-type: text/html"

    print

print """

 

    <html xmlns = "http://www.w3.org/1999/xhtml" xml:lang="en"

      lang="en">

      <head><title>Select Author</title></head>

      <body style =

      font-family: Arial, sans-serif; font-size: 11pt">"""

      try:

      connection = MySQLdb.connect( db = "books" )

      except OperationalError:

      print "Unable to connect to database: %s" % message

      else:

      cursor = connection.cursor()

      cursor.execute( "SELECT * FROM Authors" )

authorList = cursor.fetchall()

          cursor.close()     # close cursor

          connection.close()         # close connection

      print """

      <form method = "post" action = "/cgi-bin/fig28_23.py">

      <select name = "authorID">"""

      for author in authorList:

      print """<option value = %d>%s, %s</option>"""  \

      % ( author[ 0 ], author[ 2 ], author[ 1 ] )

                    print """

                    </select>

      <input type = "submit" value = "Execute Query" />

      </ form>"""

 

      print """</body></html>"""


Fig. 28.22  CGI script to create list of authors

 

If the call to MySQLdb.connect succeeds, we have connected to the database. If the call does not succeed, the program receives an OperationalError exception. We catch this exception in lines 17–18, where we print an appropriate error message.

 

If the program does not encounter an OperationalError exception, we execute the code in the else block (lines 19–38). Line 20 calls the cursor method of object

connection. This method returns a Cursor object that allows us to execute queries against the database. We store this object in local variable cursor.

 

Line 21 calls method execute to execute an SQL query against the database. The execute method takes as an argument a valid SQL string and runs that string against the database. The results of the query are stored in object cursor. We retrieve the results by calling method fetchall (line 22). This method returns a list of all the records that matched the query string we passed to the execute call. In our example, method fetchall returns a list of all the records from the Authors table in the books database. We store this list in local variable authorList. In lines 24–25, we close the cursor and the connection by calling their respective close methods.

 

The remainder of the else block (lines 29–38) writes the XHTML form that lets the user choose an author and query the database. The form is posted to fig28_23.py, which queries the database for the user-selected author. Lines 27–29 create the XHTML select item from which the user will choose an author, named authorID. Lines 31–33 contain a for loop that creates an option for each author in the database. Each record in authorList is a tuple with the following format

 

(authorID, firstName, lastName, birthYear)

We construct each option by assigning a value that corresponds to the ID (au-thor[0]) and displaying the last name followed by the first name (author[2] and au-thor[1], respectively). Lines 35–37 complete the select item and add a button to the form, so the user can execute the query.

 

Figure 28.23 is the CGI script that executes a query against the database based on the author chosen from the form in fig28_22.html. Line 10 retrieves the form using the FieldStorage class from module cgi. Lines 21–30 contain a try/except block that attempts to retrieve the authorID selected by the user. If the form contains a value for authorID, we store that value in local variable authorID; otherwise, we print an error message to the browser (lines 24–29). Line 30 calls function sys.exit, causing the pro-gram to terminate.

 

    #!c:\Python\python.exe

    # Fig. 28.23: fig28_23.py

    # A program to illustrate Python's database connectivity.

 

    import cgi

    import MySQLdb

    import sys

    # get results from form

      form = cgi.FieldStorage()

 

      print "Content-type: text/html"

 

      print

      print """

      <html xmlns = "http://www.w3.org/1999/xhtml" xml:lang="en"

      lang="en">

      <head><title>Query results</title></head>

      <body style =

      font-family: Arial, sans-serif; font-size: 11pt">"""

      try:

      authorID = form[ "authorID" ].value

      except KeyError:

      print """<span style = "color: red size = 15pt">

      FORM ERROR</span><br />

      You did not select an author.<br />

      <span style = "color: blue"> Click the Back button,

      fill out the form and resubmit.<br /><br />

      Thank You.</span></body></html>"""

      sys.exit()

      # connect to database and get cursor

      try:

      connection = MySQLdb.connect( db = 'books' )

      except OperationalError:

      print """<span style = "color: red size = 15pt">

      DATABASE ERROR</span><br /> Unable to connect to database.

      </body></html>"""

      sys.exit()

          queryString = """select Titles.* from Titles, AuthorISBN

          where AuthorISBN.AuthorID=%s and          

          Titles.ISBN=AuthorISBN.ISBN""" %            authored

          cursor = connection.cursor()

          cursor.execute( queryString )

         

          results = cursor.fetchall()         

           

          cursor.close()     # close cursor

          connection.close()         # close connection

           

          # display results

          print """<table style = "border: groove 2 pt;

          border-colapse: separate">

          <tr>

          <th>ISBN</th>

          <th>Title</th>

          <th>Edition</th>

          <th>Year</th>

          <th>Description</th>

          <th>Publisher ID</th>

          </tr>"""

      for row in results:

      print "<tr>"

      for entry in row:

      print '<td style = "border: solid 2pt">%s</td>' % entry

 

          print "</tr>"

 

        print """</table></body></html>"""

 

 

Fig. 28.23  CGI script to create table of titles, given an author

 

 

We attempt to connect to the MySQL database called books in lines 33–39. If we are unable to obtain a connection, we print an error message and call sys.exit to exit the program (lines 36–39).

 

Lines 41–43 construct a query string to execute against the database. This query selects all columns from table Title where the ISBN matches all ISBNs from table AuthorISBN that correspond to the authorID specified in the form. Lines 45–46 create a cursor for the database and execute the query string against the database. We retrieve the results of the query using method fetchall and store the records in local variable results (line 48). We then close the cursor and the connection (lines 50–51).

 

The remainder of the program (lines 54–73) displays the results of the query. We create a table and label the headers with the column names from the database (lines 54–63). Line 65 begins a for loop that iterates over each record in local variable results. For each record, we create a row in the table (lines 66–71). Each column value has a corresponding entry in the row (lines 68–69). After we have printed all the records, we print a closing table tag (line 73).

 

In this section we have illustrated Python’s DB-API through a specific implementation of the DBI, module MySQLdb. Because MySQLdb conforms to the DB-API, the code in our examples would not require many changes to work with another module that conforms to the DB-API. In fact, we could use many other databases, such as Microsoft Access or Informix, because their respective modules (odbc and informixdb) conform to the DB-API.


Study Material, Lecturing Notes, Assignment, Reference, Wiki description explanation, brief detail


Copyright © 2018-2020 BrainKart.com; All Rights Reserved. Developed by Therithal info, Chennai.