Chapter 24. Network, web, and database programming – The Quick Python Book

Chapter 24. Network, web, and database programming

This chapter covers

  • Accessing databases in Python
  • Network programming in Python
  • Creating Python web applications
  • Writing a sample project: creating a message wall

By this point, we’ve surveyed a lot of what Python can do. The final area to examine is one of the most important: using Python to build web applications that serve dynamic content. Many libraries and frameworks for creating web applications are available, in many languages—Java, PHP, Perl, and Ruby to name a few. As web applications continue to evolve and increase in importance, Python’s role in this space continues to grow.

Dynamic web applications typically store their content in databases and use the results of queries on those databases to generate page content dynamically. Various templating libraries and application frameworks are commonly used to handle URLs and format content. In this chapter, we’ll look at the pieces of this process using simple examples. When you see how the pieces fit together in Python, using almost any combination of database and application framework is possible.

24.1. Accessing databases in Python

Database access is a large part of many applications, including dynamic web applications. By using external modules, Python can access most popular databases, and in general the interface for each follows the DB-API 2.0 standard database specification detailed in PEP (Python Enhancement Proposal) 249. The specification calls for the use of a connection object to manage the connection to the database and for the use of cursor objects to manage the interaction with the database, for fetching data from the database and updating its contents.

The fact that Python database libraries conform to the DB-API 2.0 spec has a couple of obvious advantages. For one thing, writing code for different databases is easier, because the general rules are the same. The other advantage is that it’s fairly easy to prototype an application using a lightweight database and then switch the application over to a production database after the basic design of the application has been finalized.

24.1.1. Using the sqlite3 database

Although there are Python modules for many databases, for the following examples we’ll look at the one that comes included with Python: sqlite3. Although not suited for large, high-traffic applications, sqlite3 has two advantages: first, because it’s part of the standard library it can be used anywhere you need a database, without worrying about adding dependencies; second, sqlite3 stores all of its records in a local file, so it doesn’t need both a client and server, like MySQL or other common databases. These features make sqlite3 a handy option for both smaller applications and quick prototypes.

To use a sqlite3 database, the first thing you need is a connection object. Getting a connection object requires only calling the connect function with the name of file that will be used to store the data:

>>> import sqlite3
>>> conn = sqlite3.connect("datafile")

It’s also possible to hold the data in memory by using ":memory:" as the filename. For storing Python integers, strings, and floats, nothing more is needed. If you want sqlite3 to automatically convert query results for some columns into other types, it’s useful to include the detect_types parameter set to sqlite3.PARSE_DECLTYPES| sqlite3.PARSE_COLNAMES, which will direct the connection object to parse the name and types of columns in queries and attempt to match them with converters you’ve already defined.

The second step is to create a cursor object from the connection:

>>> cursor = conn.cursor()
>>> cursor
<sqlite3.Cursor object at 0xb7a12980>

At this point, you’re able to make queries against the database. In our current situation, because there are no tables or records in the database yet, we need to create one and insert a couple of records:

>>> cursor.execute("create table test (name text, count integer)")
>>> cursor.execute("insert into test (name, count) values ('Bob', 1)")
>>> cursor.execute("insert into test (name, count) values (?, ?)",
... ("Jill", 15))

The last insert query illustrates the preferred way to make a query with variables; rather than constructing the query string, it’s more secure to use a ? for each variable and then pass the variables as a tuple parameter to the execute method. The advantage is that you don’t need to worry about incorrectly escaping a value; sqlite3 takes care of it for you.

You can also use variable names prefixed with : in the query and pass in a corresponding dictionary with the values to be inserted:

>>> cursor.execute("insert into test (name, count) values (:username, \
:usercount)", {"username": "Joe", "usercount": 10})

After a table is populated, you can query the data using SQL commands, again using either ? for variable binding or names and dictionaries:

>>> result = cursor.execute("select * from test")
>>> print(result.fetchall())
[('Bob', 1), ('Jill', 15), ('Joe', 10)]
>>> result = cursor.execute("select * from test where name like :name",
... {"name": "bob"})
>>> print(result.fetchall())
[('Bob', 1)]
>>> cursor.execute("update test set count=? where name=?", (20, "Jill"))
>>> result = cursor.execute("select * from test")
>>> print(result.fetchall())
[('Bob', 1), ('Jill', 20), ('Joe', 10)]

In addition to the fetchall method, the fetchone method gets one row of the result and fetchmany returns an arbitrary number of rows. For convenience, it’s also possible to iterate over a cursor object’s rows similar to iterating over a file:

>>> result = cursor.execute("select * from test")
>>> for row in result:
... print(row)
('Bob', 1)
('Jill', 20)
('Joe', 10)

Finally, by default, sqlite3 doesn’t immediately commit transactions. That means you have the option of rolling back a transaction if it fails, but it also means you need to use the connection object’s commit method to ensure that any changes made have been saved. This is a particularly good idea before you close a connection to a database, because the close method doesn’t automatically commit any active transactions:

>>> cursor.execute("update test set count=? where name=?", (20, "Jill"))
>>> conn.commit()
>>> conn.close()

Table 24.1 gives an overview of the most common operations on a sqlite3 database.

Table 24.1. Common database operations


Sqlite3 command

Create a connection to a database conn = sqlite3.connect(filename)
Create a cursor for a connection Cursor = conn.cursor()
Execute a query with the cursor cursor.execute(query)
Return the results of a query cursor.fetchall(), cursor.fetchmany(num_row
s), cursor.fetchone()
for row in cursor:
Commit a transaction to a database conn.commit()
Close a connection conn.close()

These operations are usually all you need to manipulate a sqlite3 database. Of course, several options let you control their precise behavior; see the Python documentation for more information.

24.2. Network programming in Python

The Python standard library has everything you need to handle the standard internet protocols and to create both clients and servers. The following examples use HTTP, but similar patterns are used in most protocols.

24.2.1. Creating an instant HTTP server

The standard library has a number of modules for writing servers for various network protocols. In many cases, you can create a server in only a few lines of code. Suppose we want to make a particular folder’s files freely accessible via HTTP, perhaps to share a few files with coworkers without the hassle of setting up a formal repository or file share. With Python, we don’t need to install and configure a server. With a few lines of code, we can create a temporary server on the fly:

>>> from http.server import HTTPServer, SimpleHTTPRequestHandler
>>> server = HTTPServer(("", 8000), SimpleHTTPRequestHandler)
>>> server.serve_forever()

This server will serve the contents of the folder it’s run in on port 8000 for all active network interfaces. The tuple ("", 8000) sets the address and port for the server. Leaving the address an empty string allows it to use all of the machine’s network addresses, and the 8000 specifies the port. The second parameter to HTTPServer is the request handler; and SimpleHTTPRequestHandler is a subclass of BaseHTTPRequest-Handler, which is written to serve files from the current directory. In particular, SimpleHTTPRequestHandler defines do_GET and do_HEAD methods, which map the requests to the contents of the current folder, returning either directory listings or the contents of a file, as needed.

Another predefined request-handler class, CGIHTTPRequestHandler, serves files or the output of CGI scripts in the current folder. CGIHTTPRequestHandler defines an additional method, do_POST, which responds to POST requests on CGI scripts. Any of the request-handler classes can be subclassed to create the particular behavior you desire.

24.2.2. Writing an HTTP client

Writing Python code to interact with an HTTP server is also quite easy. The urllib.request module is designed to enable interaction with URLs in all their real-world complexity, including authentication, cookies, redirections, and the like.

But for all of its power and options, basic interactions with the urllib.request module are simple. The URL is opened and returns a file-like object, which you can read and treat like any other file. The object returned also has two additional methods: geturl, which returns the URL retrieved, so you can tell if the original request was redirected; and info, which returns the page’s headers. For example, if we were to leave our simple HTTP server running and open another Python interactive shell, we could access the server in this way:

>>> from urllib.request import urlopen
>>> url_file = urlopen("http://localhost:8000")
>>> print(url_file.geturl())
>>> print(
Server: SimpleHTTP/0.6 Python/3.1.1
Date: Sat, 06 Jun 2009 20:28:13 GMT
Content-type: text/html; charset=utf-8
Content-Length: 15395

>>> for line in url_file.readlines():
... print(line)
b'<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 3.2 Final//EN"><html>\n'
b'<title>Directory listing for /</title>\n'
b'<h2>Directory listing for /</h2>\n'
# file names would be in HTML links in list items here...

24.3. Creating a Python web application

Although the http.server module has the basics of a web server, you need more than the basics to write a full-featured web application. You need to manage users, authentication, and sessions; you need a way to generate HTML pages. The solution to this is to use a web framework, and over the years many frameworks have been created in Python, leading to the present generation, which includes Zope and Plone, Django, TurboGears, web2py, and many more.

One thing to keep in mind in considering web applications is that the web server functionality—the part that processes HTTP requests and returns responses—isn’t necessarily (or even usually) closely tied to the web application itself. Most web frameworks today can either run their own HTTP servers or rely on an external server, depending on the situation. For development, it’s often preferred to use the application’s internal server, because there’s less to install and set up. For heavy traffic in production, often an external server handles the load better and can be tweaked as needed.

24.3.1. Using the web server gateway interface

In the early days of Python web frameworks, there was little standardization of how web applications interacted with web servers. Consequently, the choice of a web framework often limited the possible web servers you could use and made migration from one to the other difficult. The WSGI (“whiz-ghee”), or web server gateway interface, specification was created to provide a standard for the interaction of web server and applications, so that it would be easier to use a web application and framework on different web servers. Figure 24.1 is a simple schematic of how a WSGI application combines with a server.

Figure 24.1. How a WSGI application works with a server

You need to know the details of WSGI design only if you’re writing web servers or web application frameworks, but it’s useful to understand the basic idea of how a WSGI application is set up.

24.3.2. Using the wsgi library to create a basic web app

Although WSGI is technically more of a specification than a library, the standard library does include a reference implementation of WSGI server and utilities in the wsgiref module. It includes a simple server implementation as well as various utilities. Creating a basic WSGI application using wsgiref.simple_server is almost as easy as our bare-bones HTTP server earlier. The one thing you need to pass to the server when it’s created is a callable application object that receives two parameters from the server: a dictionary of environment variables and a callable object to receive HTTP status and headers for the response. Here’s a basic “Hello World” WSGI application, based on the one in the standard library documentation:

If you run this script and open a browser to, you should be greeted by “Hello World.”

The two main parts of this are hello_world_app and the creation of the server itself. The creation of the server is easy: a call to make_server:make_server, passing in the application object, and the address and port for the server. In this example the address is blank, so the server will listen on all active interfaces. After the server has been created, you start it by calling its serve_forever:serve_forever method; or you can make it serve only one request and quit by using the handle_request:handle_request method.

Creating the application object, hello_world_app, is more involved but still fairly simple, because it’s a function with the two required parameters, environ and start_response. The former is a CGI-style dictionary of variables from the server, which in this example is ignored. The second parameter, start_response, is a callable object that the application uses to send the HTTP status and headers of its response. In this case, as usual, we send a status of '200 OK' and the headers indicating that this response will be plain text, encoded as UTF-8. We’ll be using this basic implementation as the starting point to create a simple “message wall” application at the end of this chapter.

24.3.3. Using frameworks to create advanced web apps

The WSGI specification goes a long way toward standardizing the way that Python web applications can interact with web servers and handle HTTP requests and responses. It doesn’t specify how applications behave internally, however, nor does it mandate what the application does with the requests it receives or how it generates its responses. This means that with a bare WSGI application a lot must be implemented. And a lot of that is repetitive: handling URLs, extracting the information from web forms, creating HTML pages, inserting dynamic content into those pages, and the like. Although it’s possible to code everything by hand or to assemble and/or write a custom collection of modules to handle these chores, for most projects it’s preferable to use an existing web framework. The most popular frameworks include a server (good enough for at least development and testing) and the machinery for creating and customizing web applications.

At the time of this writing, none of the major frameworks have been ported to Python 3, but the work is in progress; Python 3 versions of all the major web app frameworks should be ready by the time Python 3.2 is released in the summer of 2010.

24.4. Sample project—creating a message wall

To see what you can do with a WSGI application, let’s create a simple example: a message wall where the messages are stored in a sqlite3 database and the URL is used to indicate the user and tags being searched for. Note that this is a simple example, and we won’t be implementing any kind of security, session management, and so on, so don’t use this in production. This application is intended to show you the possibilities for writing web applications in Python.

As you’ve seen, we need to create an application object to pass to the server when we create it. This application needs to be able to do the following:

  • Retrieve messages written by a user, and display them along with their ages: for example, “5 minutes ago,” “2 days ago,” and so on
  • Retrieve messages addressed to a user (messages beginning with @user), and display them and their ages
  • Display messages in chronological order from newest to oldest
  • Allow users to enter messages and store them in the database

24.4.1. Creating the database

The first thing we need to do is to create the sqlite3 database for the app. We’ll need three fields in the messages table: the user, the message itself, and its timestamp. Both the user and the message are naturally text fields in sqlite3 and will map automatically to strings in Python. For the timestamp, it would be convenient if we could read the values as Python datetime values. Fortunately, Python’s sqlite3 module includes converters for date and datetime types; we’ll need to activate type detection on the connection object in order to have a timestamp database type available. To set up the database, we need to enter the following from a Python shell in the directory where we want to run our database:

>>> import sqlite3
>>> conn = sqlite3.connect("messagefile", \
... detect_types=sqlite3.PARSE_DECLTYPES | sqlite3.PARSE_COLNAMES)
>>> cursor = conn.cursor()
>>> cursor.execute("create table messages(user text, message text, "
... "ts timestamp)")
>>> conn.commit()
>>> conn.close()

This creates a database in the file messagefile and creates a table called messages with text fields for the user and the message and a timestamp field called ts.

24.4.2. Creating an application object

The next step is to create the application. First, let’s write the application to display the title Message Wall, as shown in listing 24.1.

Listing 24.1. File

Running this program gets a similar result to our early simple example. When you visit with a web browser, you see only “Message Wall.” Note, however, that we did change the type of the response in our header from text/plain to text/html.

24.4.3. Adding a form and retrieving its contents

With the basic application working, it’s time to add its functionality. The first thing we can do is add a form for message submissions. This will require two things: sending the HTML code to make the form and then retrieving its values when the user submits the form. Sending the HTML code means that we’ll be creating longer and longer strings to return from our application object; rather than using string concatenation, we can use a StringIO object from the io library to let us print our output as if we were writing it to a file and then return it as one string.

Retrieving the values of the form can happen only when values are submitted, which makes the request’s REQUEST_METHOD be POST instead of the normal GET. When that happens, we can get the values from the form by using the CONTENT_LENGTH item from environ to find out how long the form string is and then reading that amount from environ’s wsgi.input. At this point, we aren’t saving the form data, but we’ll print it to the browser so that we can see what’s going on (see listing 24.2).

Listing 24.2. File

We use the print function’s file parameter to print to StringIO object . We then return the entire contents of output by calling its getvalue method .

Figure 24.2 is a screen shot of the browser window, with the server running in a command window behind it.

Figure 24.2. The browser and server after a form POST

Note that the value of the form fields is returned as a single bytes object.

24.4.4. Saving the form’s contents

When we have a message, we need to save the message to the database. To do this, we’ll need to parse the value we read from the form and then use a sqlite3 query to store it in the database. To make this easier, we’ve added a helper function to parse the form string into a dictionary, and we’ve added the current timestamp to that dictionary. Then, the dictionary is used as the parameter for a SQL query to save the message to the database (see listing 24.3).

Listing 24.3. File

We use a dictionary comprehension to transform post_str into a dictionary . Then, we add a timestamp to the form_vals dictionary . Sqlite3 connection and cursor objects are created only once for an application .

24.4.5. Parsing the URL and retrieving messages

What remains is to retrieve messages from or addressed to a particular user. As described in our requirements, a message addressed to a user will begin with @ and that user’s username. Also shown in the specs, the desired way of indicating the user is to append the username to the base URL. To get all messages to user vceder, for example, that username would be added to the URL, which in the case of our localhost server would be http://localhost:8000/vceder.

That means we need to get the PATH_INFO:PATH_INFO from the request, which is anything beyond the base URL, and split it on/into fields. We can use Python’s flexible tuple unpacking to put the first field into a username variable and anything beyond the first field into a tags list, so that we can easily add tagging later.

In this case, it’s simpler to create a query using ? to mark the variables. We can create a tuple of (user, "@"+user+"%") to hold the values, with the first part of the query looking for an exact match on the username and the second part being a match with any message that starts with an @ and the username.

We’ve also taken care of a couple of other issues. First, a helper function message_table puts any matching records from the query into an HTML table. Second, the post_str variable is now decoded from a bytes object to a string, and it also has any quoting removed with the unquote_plus function from the urllib.parse library (see listing 24.4).

Listing 24.4. File

Here we decode from bytes to string and unquote . We read and parse PATH_INFO for username . Then, we query the database for matching messages .

Figure 24.3 is a screenshot of the server and browser after a message has been posted.

Figure 24.3. Browser and server after a message POST

If you compare the timestamp of the message in the browser and the time of the POST statement in the server’s command window, you can see that the last message has just been posted.

24.4.6. Adding an HTML wrapper

If we were to use our browser’s View Source feature to look at the HTML this application is generating, we’d see that we’re not returning complete pages. Instead, we’re returning only the HTML code for the contents of the page, with no beginning or end. The last step is to wrap our output in HTML headers and footers to make it a more legal HTML page. To do that, we can add some variables to hold header and footer strings and a little function to put everything together (listing 24.5).

Listing 24.5. File
from wsgiref.simple_server import make_server
from io import StringIO
from urllib.parse import unquote_plus
import sqlite3
import datetime

header = "<html><header> <title>Message Wall</title></header><body>"
footer = "</body></html>"

def html_page(content):
page = "%s\n%s\n%s" % (header, content, footer)
return page

def get_form_vals(post_str):
form_vals = {item.split("=")[0]: item.split("=")[1] for item \
in post_str.split("&")}
return form_vals

def message_table(messages):
table = "<table>\n"
for message in messages:
row_str = "<tr><td>{0}</td><td>{1}</td><td>{2}</td></tr>\n"
table += row_str.format(message[2], message[0], message[1])
table += "</table>"
return table

def message_wall_app(environ, start_response):
from io import StringIO
output = StringIO()
status = b'200 OK' # HTTP Status
headers = [(b'Content-type', b'text/html; charset=utf-8')]
start_response(status, headers)
print("<h1>Message Wall</h1>", file=output)
if environ['REQUEST_METHOD'] == 'POST':
size = int(environ['CONTENT_LENGTH'])
post_str = unquote_plus(environ['wsgi.input'].read(size).decode())
form_vals = get_form_vals(post_str)
form_vals['timestamp'] =
cursor.execute("""insert into messages (user, message, ts) values
(:user, :message, :timestamp)""", form_vals)
path_vals = environ['PATH_INFO'][1:].split("/")
user, *tag = path_vals
cursor.execute("""select * from messages where user like ? or message
like ? order by ts""", (user, "@" + user + "%"))
print(message_table(cursor.fetchall()), "<p>", file=output)

print('<form method="POST">User: <input type="text" '
'name="user">Message: <input type="text" '
'name="message"><input type="submit" value="Send"></form>',
return [html_page(output.getvalue())]

httpd = make_server('', 8000, message_wall_app)
print("Serving on port 8000...")

conn = sqlite3.connect("messaging")
cursor = conn.cursor()

Although it works fine for a tiny demo application, this method of applying HTML formatting can become tedious as pages and their contents get more complex. In addition, hand-coding the generation of HTML can make designing the appearance of the page doubly burdensome, because whoever is doing it must be both a programmer and a designer. Obviously, this example suffers from a plain look that could be dressed up with advanced HTML code. Web application frameworks use some sort of templating system to handle formatting so that you don’t need to worry about it.

24.5. Summary

The previous example illustrates the basics of how Python combines databases and the WSGI standard to create dynamic web applications. The choices of web application frameworks in Python are extensive, but almost all of them work in the same way and handle the same basic problems: handling requests, parsing URLs and mapping them to parts of the web application, handling user data, creating dynamic pages based on database queries, and producing HTML.