TABLE OF CONTENTS (HIDE)

Python

Database and Web Applications

Python Tools

python-dev (Python development tools)

Many of the Python packages require python-dev (Python development tools) to install and build.

To install python-dev:

# Python 2
$ sudo apt-get install python-dev
......
The following NEW packages will be installed:
  libexpat1-dev libpython-dev libpython2.7-dev python-dev python2.7-dev
......

$ dpkg --status python-dev
Version: 2.7.11-1
......

# Python 3
$ sudo apt-get install python3-dev
......
The following NEW packages will be installed:
  libpython3-dev libpython3.5-dev python3-dev python3.5-dev
......

$ dpkg --status python3-dev
Version: 3.5.1-3
......

pip - Python Package Manager

References
  1. pip @ https://pypi.python.org/pypi/pip.
  2. pip documentation @ https://pip.readthedocs.org/en/stable.

pip (Python Package Manager) is used for downloading, installing/un-installing, and managing Python packages. It is a replacement for an earlier tool called easy_install.

Installing pip

There are two versions of pip: pip2 for Python 2 and pip3 for Python 3. The pip executable could be symlinked to either pip2 or pip3.

# (For Python 2) Install 'pip2'
$ sudo apt-get install python-pip
......
$ which pip
/usr/bin/pip
$ ll /usr/bin/pip*
-rwxr-xr-x 1 root root 281 Jun 17 05:52 /usr/bin/pip*
-rwxr-xr-x 1 root root 283 Jun 17 05:52 /usr/bin/pip2*
$ pip2 --version
pip 8.1.1 from /usr/lib/python2.7/dist-packages (python 2.7)
$ pip --version
pip 8.1.1 from /usr/lib/python2.7/dist-packages (python 2.7)

# (For Python 3) Install 'pip3'
$ sudo apt-get install python3-pip
......
$ which pip3
/usr/bin/pip3
$ ll /usr/bin/pip*
-rwxr-xr-x 1 root root 281 Jun 17 05:52 /usr/bin/pip*
-rwxr-xr-x 1 root root 283 Jun 17 05:52 /usr/bin/pip2*
-rwxr-xr-x 1 root root 284 Jun 17 05:52 /usr/bin/pip3*
$ pip3 --version
pip 8.1.1 from /usr/lib/python3/dist-packages (python 3.5)
$ pip --version
pip 8.1.1 from /usr/lib/python2.7/dist-packages (python 2.7)

Take note that pip2 uses Python 2; while pip3 uses Python 3.

Upgrading pip

The apt-get install may not install the latest pip version. You could upgrade pip using pip itself, as follows:

# Python 3
$ pip3 install --upgrade pip
$ pip3 --version
pip 9.0.1 from /home/username/.local/lib/python3.5/site-packages (python 3.5)

# Python 2
$ pip install --upgrade pip
$ pip --version
pip 9.0.1 from /home/username/.local/lib/python2.7/site-packages (python 2.7)

Take note that we did not use 'sudo' in the above commands. As the result, the upgrades were installed in /home/username/.local/lib, which is meant for the current user. On the other hand, if you use 'sudo' in the above commands, the upgrades will be installed in /usr/lib, which will be available system-wide and for all users. There is no need to use 'sudo', i.e., pip can be used without the administrative right.

Using pip

Installing a package:

# Syntax: pip install [package-name]
$ pip install virtualenv
Downloading/unpacking virtualenv
  Downloading virtualenv-13.1.2-py2.py3-none-any.whl (1.7MB): 1.7MB downloaded
Installing collected packages: virtualenv
Successfully installed virtualenv
Cleaning up...

Showing what files were installed and where:

# Syntax: pip show --files [package-name]
$ pip show --files virtualenv
Name: virtualenv
Version: 15.1.0
Location: /usr/local/lib/python2.7/dist-packages
......

Installing a package of a specific or minimum version:

# Syntax for installing specific version: pip install [package-name]==[version]
# Syntax for installing minimum version: pip install [package-name]>=[version]
$ pip install virtualenv==13.1.1  # specific version
$ pip install virtualenv>=13.1.1  # minimum version

Installing a package from a URL:

Syntax: pip install [url]
$ pip install https://github.com/pypa/virtualenv/archive/develop.tar.gz

Listing all/out-dated packages:

# List all installed packages
$ pip list

# List out-dated packages
$ pip list --outdated
requests (Current: 2.2.1 Latest: 2.8.1)
......

Upgrading a package:

# Syntax: pip install --upgrade [package-name]
$ pip install --upgrade requests

Un-Installing a package:

# Syntax: pip uninstall [package-name]
$ pip uninstall requests

Search for a package:

# Syntax: pip search [package-name]
$ pip search requests

Installing package for a user:

# Syntax: pip install --user [package-name]
$ pip install --user requests
   # The package will be installed in $HOME/.local/lib/python2.7/site-packages
Managing a list of packages at specific version numbers

The most important feature of pip is to manage a full list of packages at specific version numbers, so as to synchronize two environments (such as the development and production environments). Furthermore, via the so-called virtual environment (to be described next), each Python application can maintain its own list (and versions) of packages.

To create a list of pip-installed packages in a requirement file:

# Syntax: pip freeze > [list-name.txt]
$ pip freeze > requirements.txt

The requirement file contains a list of packages and their version numbers, e.g.,

apturl==0.5.2
beautifulsoup4==4.4.1
blinker==1.3
......

You can then duplicate this list of packages in another machine:

# Syntax: pip install -r [list-name.txt]
$ pip install -r requirements.txt

virtualenv - Virtual Environment for Python Application Development

References
  1. virtualenv @ https://pypi.python.org/pypi/virtualenv.
  2. virtualenv documentation @ https://virtualenv.readthedocs.org/en/latest.

A Python "Virtual Environment" is a dedicated directory which contains everything that a Python application needed to run the application in an isolated environment. It includes its own Python interpreter, its own pip and all the pip-installed packages. Via virtual environments, you can test and run different Python applications with different Python versions and packages. You can also easily synchronize the development Python environment and the production Python environment.

Installing virtualenv

To install virtualenv via pip:

$ sudo pip install virtualenv

# Verifying the installation
$ pip show --files virtualenv
Name: virtualenv
Version: 15.1.0
Location: /usr/local/lib/python2.7/dist-packages
......
$ which virtualenv
/usr/local/bin/virtualenv
$ virtualenv --version
15.1.0
Using virtualenv

Suppose that we start a new Python project in the development environment, and would like the project to run under specific versions of Python Interpreter and packages, which is isolated from the system environment and can be duplicated to the production environment. We can create a virtual environment for this project, which is basically a directory keeping its own Python Interpreter and packages isolated from the system packages and can be duplicated in another environment.

For example,

# Goto the project directory
$ cd /path/to/project-directory
# Create a virtual environment called 'myvenv' for this project, 
# which is a directory containing its Python interpreter and packages.
# Syntax: virtualenv [env-folder-name]
$ virtualenv myvenv
New python executable in myvenv/bin/python
Installing setuptools, pip, wheel...done.
$ ll myvenv
drwxrwxr-x 2 chua chua 4096 Nov 16 11:54 bin/
drwxrwxr-x 2 chua chua 4096 Nov 16 11:54 include/
drwxrwxr-x 3 chua chua 4096 Nov 16 11:54 lib/
drwxrwxr-x 2 chua chua 4096 Nov 16 11:54 local/
-rw-rw-r-- 1 chua chua   60 Feb  7 23:50 pip-selfcheck.json
$ ll myvenv/bin
-rw-rw-r-- 1 chua chua    2253 Nov 16 11:54 activate
-rwxrwxr-x 1 chua chua     242 Nov 16 11:54 pip*
-rwxrwxr-x 1 chua chua     242 Nov 16 11:54 pip2*
-rwxrwxr-x 1 chua chua     242 Nov 16 11:54 pip2.7*
-rwxrwxr-x 1 chua chua 3345416 Nov 16 11:54 python*
lrwxrwxrwx 1 chua chua       6 Nov 16 11:54 python2 -> python*
lrwxrwxrwx 1 chua chua       6 Nov 16 11:54 python2.7 -> python*
-rwxrwxr-x 1 chua chua     249 Nov 16 11:54 wheel*

To create a virtual environment for Python 3, use the -p python3 (or --python=python3) flag, as follows:

$ virtualenv -p python3 myvenv3
New python executable in .../myvenv3/bin/python3
Also creating executable in .../myvenv3/bin/python
Installing setuptools, pip, wheel...done.
$ ll myvenv3/bin
total 4408
-rw-rw-r-- 1 chua chua    2098 Feb  6 15:09 activate
-rwxrwxr-x 1 chua chua     241 Feb  6 15:09 pip*
-rwxrwxr-x 1 chua chua     241 Feb  6 15:09 pip3*
-rwxrwxr-x 1 chua chua     241 Feb  6 15:09 pip3.5*
lrwxrwxrwx 1 chua chua       7 Feb  6 15:09 python -> python3*
-rwxrwxr-x 1 chua chua 4460336 Feb  6 15:09 python3*
lrwxrwxrwx 1 chua chua       7 Feb  6 15:09 python3.5 -> python3*
-rwxrwxr-x 1 chua chua    2357 Feb  6 15:09 python-config*
-rwxrwxr-x 1 chua chua     248 Feb  6 15:09 wheel*

To activate/deactivate a virtual environment:

# Syntax: source [env-name]/bin/activate
$ cd /path/to/project-directory
$ source myvenv/bin/activate
(myvenv)....$
   # The command-prompt changes to "(venv)...$"
   # All Python tasks are carried out inside the virtual environment
   # with the specific version of Python interpreter and packages

# To deactivate virtual environment
(myvenv)....$ deactivate
$
   # The command-prompt returns to "$"

Take note that all commands issued under a virtual environment run under the Python of the virtual environment. Similarly, pip issued under a virtual environment installs the package inside the virtual environment.

virtualenvwrapper

Reference: virtualenvwrapper documentation @ https://virtualenvwrapper.readthedocs.org/en/latest/.

"virtualenvwrapper is a set of extensions to Ian Bicking’s virtualenv tool. The extensions include wrappers for creating and deleting virtual environments and otherwise managing your development workflow, making it easier to work on more than one project at a time without introducing conflicts in their dependencies."

[TODO] more

Python-MySQL Database Programming

References:
  1. MySQL for Python (MySQLdb) @ http://sourceforge.net/projects/mysql-python/.
  2. MySQLdb User's Guide @ http://mysql-python.sourceforge.net/MySQLdb.html.

I assume that you are familiar with MySQL. Otherwise, read the "MySQL" section.

Setting Up

Preparation
# These Python packages and libraries might be needed to build the driver
$ sudo apt-get update
$ sudo apt-get install python-dev libmysqlclient-dev
(Python 2) Installing Python-MySQL Driver: MySQLdb
# Install MySQLdb via pip (System-wide)
$ sudo pip install MySQL-python

# Verify the installation
$ pip show --files MySQL-python
Name: MySQL-python
Version: 1.2.5
Location: /usr/local/lib/python2.7/dist-packages
......

Notes: You could also use "apt-get install python-mysqldb". But that might not install the latest version.

(Python 3) Installing Python-MySQL Driver: mysqlclient

The MySQLdb (in the above section) does not support Python 3. We could use mysqlclient (which is a fork of MySQLdb) or PyMySQL for Python 3.

$ sudo pip3 install mysqlclient

$ sudo pip3 show --files mysqlclient
Name: mysqlclient
Version: 1.3.9
Summary: Python interface to MySQL
Location: /usr/local/lib/python3.5/dist-packages
......
Setting up MySQL

Login to MySQL. Create a test user (called testuser) and a test database (called testdb) as follows:

$ mysql -u root -p
......

mysql> create user 'testuser'@'localhost' identified by 'xxxx';
Query OK, 0 rows affected (0.00 sec)

mysql> create database if not exists testdb;
Query OK, 1 row affected (0.00 sec)

mysql> grant all on testdb.* to 'testuser'@'localhost';
Query OK, 0 rows affected (0.00 sec)

mysql> quit

MySQL EG 1: Connecting to MySQL Database Server

#!/usr/bin/env python2
# -*- coding: utf-8 -*-
"""
testdbconn: Test MySQL Database Connection
"""
import sys
import MySQLdb

conn = None  # Database connection

try:
    # Open database connection.
    # Parameters are: (server_hostname, username, password, database_name, server_port=3306)
    conn = MySQLdb.connect('localhost', 'testuser', 'xxxx', 'testdb')
    print('Connected...')
    # Get a cursor from the connection, for traversing the records in result-set
    cursor = conn.cursor()
    # Execute a MySQL query via execute()
    cursor.execute('SELECT VERSION()')
    #cursor.execute('SELECT xxx')   # uncomment to trigger an exception
    # Fetch one (current) row into a tuple
    version = cursor.fetchone()
    print('Database version : %s ' % version)  # one-item tuple
    
except MySQLdb.Error as e:
    print('Error %d: %s' % (e.args[0], e.args[1]))  # Error code number, description 
    sys.exit(1)  # Raise a SystemExit exception for cleanup, but honor finally-block
    
finally:    
    if conn:
        # Always close the connection
        conn.close()
        print('Closed...')
Dissecting the program
  1. [TODO]
Python 3

The above program works in Python 3, provided you installed the mysqlclient package, which is a fork of MySQLdb that adds support to Python 3. Modify the first line for 'python3' if running standalone.

MySQL EG 2: SQL CREATE/DROP TABLE, INSERT and SELECT

#!/usr/bin/env python2
# -*- coding: utf-8 -*-
"""
testsqlstmt: Testing MySQL statements: CREATE TABLE, INSERT and SELECT
"""
import MySQLdb

conn = MySQLdb.connect('localhost', 'testuser', 'xxxx', 'testdb')

with conn:   # Automatically close with error handling
    cursor = conn.cursor()
    # Create a new table
    cursor.execute('drop table if exists cafe')
    cursor.execute('''create table if not exists cafe (
                        id int unsigned not null auto_increment,
                        category enum('tea', 'coffee') not null,
                        name varchar(50) not null,
                        price decimal(5,2) not null,
                        primary key (id)
                      )''')
    # Insert one record
    cursor.execute('''insert into cafe (category, name, price) values
                        ('coffee', 'Espresso', 3.19)''')
    # Insert multiple records
    cursor.execute('''insert into cafe (category, name, price) values
                        ('coffee', 'Cappuccino', 3.29),
                        ('coffee', 'Caffe Latte', 3.39),
                        ('tea', 'Green Tea', 2.99),
                        ('tea', 'Wulong Tea', 2.89)''')
    # Commit the insert
    conn.commit()
    # Query all records
    cursor.execute('select * from cafe')
    # Fetch all rows from result-set into 'a tuple of tuples'
    rows = cursor.fetchall()
    #print(rows)  # For debugging
    # Process each row (tuple)
    for row in rows:
        print(row)
        
    # Instead of fetching all rows (which may not be feasible),
    # we can fetch row by row.
    # We also fetch the column names.
    cursor.execute('select * from cafe')
    # Fetch the column descriptions in 'a tuple of tuples'
    # Each inner tuple describes a column
    desc = cursor.description  
    #print(desc)  # For debugging
    # Print header of column names (first item of inner tuple)
    print('%-10s %-20s %-6s' % (desc[1][0], desc[2][0], desc[3][0]))
    print('%10s-%20s-%6s' % ('-'*10, '-'*20, '-'*6))  # Print divider
    
    for i in range(cursor.rowcount):
        row = cursor.fetchone()
        print('%-10s %-20s %6.2f' % (row[1], row[2], row[3]))  # Using tuple indexes
        
    # Another way to fetch row-by-row
    cursor.execute('select * from cafe')
    while True:
        row = cursor.fetchone()
        if row == None:
            break
        print(row)
Dissecting the program
  1. The with conn: statement always closes the conn, and provide error handling.
  2. [TODO]

MySQL EG 3: Using Dictionary Cursor

#!/usr/bin/env python2
# -*- coding: utf-8 -*-
"""
testdictcursor: Using Dictionary Cursor
"""
import MySQLdb

conn = MySQLdb.connect('localhost', 'testuser', 'xxxx', 'testdb')

with conn:
    # Using a dictionary cursor.
    # Each row of the result-set is a dictionary of column names and values
    cursor = conn.cursor(MySQLdb.cursors.DictCursor)
    cursor.execute('drop table if exists cafe')
    cursor.execute('''create table if not exists cafe (
                     id int unsigned not null auto_increment,
                     category enum('tea', 'coffee') not null,
                     name varchar(50) not null,
                     price decimal(5,2) not null,
                     primary key (id)
                   )''')
    cursor.execute('''insert into cafe (category, name, price) values
                     ('coffee', 'Espresso', 3.19),
                     ('coffee', 'Cappuccino', 3.29),
                     ('coffee', 'Caffe Latte', 3.39),
                     ('tea', 'Green Tea', 2.99),
                     ('tea', 'Wulong Tea', 2.89)''')
    conn.commit()  # Commit the insert

    # Query all records
    cursor.execute('select * from cafe')
    # Fetch all rows from result-set into 'a tuple of dictionary'
    rows = cursor.fetchall()
    #print(rows)  # For debugging
    # Process each row (dictionary)
    for row in rows:
        #print(row)  # For debugging
        print(row['category'] + ': ' + row['name'])  # via dictionary keys
Dissecting the program
  1. [TODO]

MySQL EG 4: Using Prepared-Statements

#!/usr/bin/env python2
# -*- coding: utf-8 -*-
"""
testpreparedstmt: Using SQL Prepared-Statement
"""
import MySQLdb

conn = MySQLdb.connect('localhost', 'testuser', 'xxxx', 'testdb')

with conn:
    cursor = conn.cursor()
    cursor.execute('drop table if exists cafe')
    cursor.execute('''create table if not exists cafe (
                        id int unsigned not null auto_increment,
                        category enum('tea', 'coffee') not null,
                        name varchar(50) not null,
                        price decimal(5,2) not null,
                        primary key (id)
                      )''')

    # Using prepared-statement via printf-like formatting specifiers
    # Use %s for all fields?!
    sql = 'insert into cafe (category, name, price) values (%s, %s, %s)'
    
    # Execute for one set of data
    cursor.execute(sql, ('coffee', 'Espresso', 3.19))
                   
    # Execute for more than one set of data
    data = [('coffee', 'Cappuccino', 3.29),
            ('coffee', 'Caffe Latte', 3.39),
            ('tea', 'Green Tea', 2.99),
            ('tea', 'Wulong Tea', 2.89)]
    cursor.executemany(sql, data)
    conn.commit()  # Commit the insert
    
    cursor.execute('select * from cafe')
    rows = cursor.fetchall()
    for row in rows:
        print(row)

    # Another example
    item = 'Cappuccino'
    cursor.execute('update cafe set price = price * 1.1 where name = %s', (item,))  # or one-element list: [item]
    cursor.execute('select * from cafe where name = %s', [item])
    row = cursor.fetchone()
    print(row)
Dissecting the program
  1. Python MySQLdb supports prepared-statements via printf formatting specifiers.
  2. [TODO]

MySQL EG 5: Managing Transactions

#!/usr/bin/env python2
# -*- coding: utf-8 -*-
"""
testtran: Testing SQL Transaction of commit and rollback
"""
import sys
import MySQLdb

conn = None

try:
    conn = MySQLdb.connect('localhost', 'testuser', 'xxxx', 'testdb')

    cursor = conn.cursor()
    # A transaction is started silently when the cursor is created.
    # No BEGIN statement is needed. 
    cursor.execute('drop table if exists cafe')
    cursor.execute('''create table if not exists cafe (
                     id int unsigned not null auto_increment,
                     category enum('tea', 'coffee') not null,
                     name varchar(50) not null,
                     price decimal(5,2) not null,
                     primary key (id)
                   )''')
    conn.commit()
    
    cursor.execute("insert into cafe values (NULL, 'coffee', 'Espresso', 3.19)")
    cursor.execute("insert into cafe values (NULL, 'coffee', 'Cappuccino', 3.29)")
    conn.commit()
    
    cursor.execute("insert into cafe values (NULL, 'tea', 'Green Tea', 2.99)")
    #cursor.execute("insert into cafe (xxx) values ('tea')")  # uncomment to trigger an error
    cursor.execute("insert into cafe values (NULL, 'tea', 'Wulong Tea', 2.89)")
    conn.commit()

except MySQLdb.Error as e:
    if conn:
        conn.rollback()
        print('rolled back...')
    print('Error %d: %s' % (e.args[0], e.args[1]))
    sys.exit(1)  # Raise a SystemExit exception for cleanup, but honor finally-block
    
finally:
    if conn:
        cursor.execute('select * from cafe')
        rows = cursor.fetchall()
        for row in rows:
            print(row)
        conn.close()
Dissecting the program
  1. [TODO]

We can also use with statement, which provide automatic commit and rollback.

#!/usr/bin/env python2
# -*- coding: utf-8 -*-
"""
testtranwith: Testing Transaction (commit/rollback) under with-statement
"""
import MySQLdb

conn = MySQLdb.connect('localhost', 'testuser', 'xxxx', 'testdb')

with conn:   # Provide automatic commit and rollback
    cursor = conn.cursor()
    cursor.execute('drop table if exists cafe')
    cursor.execute('''create table if not exists cafe (
                     id int unsigned not null auto_increment,
                     category enum('tea', 'coffee') not null,
                     name varchar(50) not null,
                     price decimal(5,2) not null,
                     primary key (id)
                   )''')
    #conn.commit()   # auto commit for create table
    
    cursor.execute("insert into cafe values (NULL, 'tea', 'Green Tea', 2.99)")
    #cursor.execute("insert into cafe (xxx) values ('tea')")  # uncomment to trigger an error
    cursor.execute("insert into cafe values (NULL, 'tea', 'Wulong Tea', 2.89)")

    cursor.execute('select * from cafe')
    rows = cursor.fetchall()
    for row in rows:
        print(row)
Dissecting the program
  1. [TODO]

Python-PostgreSQL Database Programming

References:
  1. Psycopg2 @ http://initd.org/psycopg/.
  2. Psycopg2 Documentation & API @ http://initd.org/psycopg/docs/.
  3. [TODO]

I assume that you are familiar with PostgreSQL. Otherwise, read the "PostgreSQL" section.

Setting Up

Installing Python-PostgreSQL Driver: psycopg2
# These packages might be needed to build psycopg2
$ sudo apt-get update
   # Update package list
$ sudo apt-get install postgresql-server-dev-9.5 libpq-dev python-dev

# Install psycopg2 via pip (for Python 2)
$ sudo pip install psycopg2
Successfully installed psycopg2
# Verify the installation
$ pip show --files psycopg2
Name: psycopg2
Version: 2.6.2
Location: /usr/local/lib/python2.7/dist-packages
......

# Install psycopg2 via pip3 (for Python 3)
$ sudo pip3 install psycopg2
Successfully installed psycopg2
# Verify the installation
$ pip3 show --files psycopg2
Name: psycopg2
Version: 2.6.2
Location: /usr/local/lib/python3.5/dist-packages
......

Notes: You may also use "apt-get install python-psycopg2" to install psycopg2. However, that might install an outdated version (2.4.5).

Setting Up PostgreSQL

Create a test user (called testuser) and a test database (called testdb owned by testuser) as follows:

# Create a new PostgreSQL user called testuser, allow user to login, but NOT creating databases
$ sudo -u postgres createuser --login --pwprompt testuser
Enter password for new role: ......

# Create a new database called testdb, owned by testuser.
$ sudo -u postgres createdb --owner=testuser testdb

Tailor the PostgreSQL configuration file /etc/postgresql/9.5/main/pg_hba.conf to allow user testuser to login to PostgreSQL server, by adding the following entry:

# TYPE  DATABASE    USER        ADDRESS          METHOD
local   testdb      testuser                     md5

Restart PostgreSQL server:

$ sudo service postgresql restart

PostgreSQL EG 1: Connecting to MySQL Database Server

#!/usr/bin/env python2
# -*- coding: utf-8 -*-
"""
testdnconn: Testing PostgreSQL database connection
"""
import sys
import psycopg2

conn = None

try:
    # Open database connection.
    conn = psycopg2.connect(database='testdb', user='testuser', password='xxxx', host='localhost', port='5432')
    print('Connected...')
    # Get a cursor from the connection, for traversing the records in result-set
    cursor = conn.cursor()
    # Execute a MySQL query via execute()
    cursor.execute('SELECT VERSION()')
    #cursor.execute('SELECT xxx')   # uncomment to trigger an exception
    # Fetch one (current) row into a tuple
    version = cursor.fetchone()
    print('Database version : %s ' % version)  # one-item tuple
    
except psycopg2.DatabaseError as e:
    print('Error code %s: %s' % (e.pgcode, e))
    sys.exit(1)  # Raise a SystemExit exception for cleanup, but honor finally-block
    
finally:
    if conn:
        # Always close the connection
        conn.close()
        print('Closed...')

The above code runs under Python 2 as well as Python 3. (Modify the first line to choose the Python interpreter if running standalone.)

PostgreSQL EG 2: SQL CREATE/DROP TABLE, INSERT and SELECT

Note: This example uses Python's with statement to automatic commit and close resources. This requires psycopg 2.5 or above. If your psycopg2 is lower than 2.5, use try-except-finally statement, as in Example 1.

#!/usr/bin/env python2
# -*- coding: utf-8 -*-
"""
testsqlstmts: Testing SQL statements: CREATE TABLE, INSERT, SELECT
"""
import psycopg2

with psycopg2.connect(database='testdb', 
                      user='testuser', 
                      password='xxxx', 
                      host='localhost', 
                      port='5432') as conn:
    with conn.cursor() as cursor:
        # Create a new table
        cursor.execute('drop table if exists cafe')
        cursor.execute('''create table if not exists cafe (
                            id serial,
                            category varchar(10) not null,
                            name varchar(50) not null,
                            price decimal(5,2) not null,
                            primary key (id)
                          )''')
        # Insert records
        cursor.execute('''insert into cafe (category, name, price) values
                            ('coffee', 'Espresso', 3.19),
                            ('coffee', 'Cappuccino', 3.29),
                            ('coffee', 'Caffe Latte', 3.39),
                            ('tea', 'Green Tea', 2.99),
                            ('tea', 'Wulong Tea', 2.89)''')
        # Commit the insert
        conn.commit()
        # Query all records
        cursor.execute('select * from cafe')
        # Fetch all rows from result-set into 'a tuple of tuples'
        rows = cursor.fetchall()
        #print(rows)  For debugging
        # Process each row (tuple)
        for row in rows:
            print(row)
Dissecting the Program
  1. As explained in http://initd.org/psycopg/docs/usage.html, starting from psycopg 2.5, the connection and cursor are context manager and can be used with Python's with-statement.
    • When a connection exits the with block, the transaction is committed if no exception has been raised; otherwise, it is rolled back.
    • When a cursor exits the with block it is closed, releasing any resource eventually associated with it. The state of the transaction is not affected.

Alternatively,

#!/usr/bin/env python2
# -*- coding: utf-8 -*-
"""
testwith: Testing with-statement
"""
import psycopg2

conn = psycopg2.connect(database='testdb', 
                      user='testuser', 
                      password='xxxx', 
                      host='localhost', 
                      port='5432') 

with conn:
    with conn.cursor() as cursor:
        # Create a new table
        cursor.execute('drop table if exists cafe')
        cursor.execute('''create table if not exists cafe (
                            id serial,
                            category varchar(10) not null,
                            name varchar(50) not null,
                            price decimal(5,2) not null,
                            primary key (id)
                          )''')
        # Insert records
        cursor.execute('''insert into cafe (category, name, price) values
                            ('coffee', 'Espresso', 3.19),
                            ('coffee', 'Cappuccino', 3.29),
                            ('coffee', 'Caffe Latte', 3.39),
                            ('tea', 'Green Tea', 2.99),
                            ('tea', 'Wulong Tea', 2.89)''')
# automatic commit when conn exits 
        
with conn:
    with conn.cursor() as cursor:
        # Query all records
        cursor.execute('select * from cafe')
        # Fetch all rows from result-set into 'a tuple of tuples'
        rows = cursor.fetchall()
        #print(rows)  For debugging
        # Process each row (tuple)
        for row in rows:
            print(row)

conn.close()  # Need to close connection manually

PostgreSQL EG 3: Dictionary Cursor

#!/usr/bin/env python2
# -*- coding: utf-8 -*-
"""
testdictcursor: Using PostgreSQL Dictionary Cursor
"""
import psycopg2
import psycopg2.extras   # Needed for dictionary cursor

with psycopg2.connect(database='testdb',
                      user='testuser', 
                      password='xxxx', 
                      host='localhost', 
                      port='5432') as conn:
    
    # Create a dictionary cursor
    with conn.cursor(cursor_factory=psycopg2.extras.DictCursor) as cursor:
        cursor.execute('drop table if exists cafe')
        cursor.execute('''create table if not exists cafe (
                            id serial,
                            category varchar(10) not null,
                            name varchar(50) not null,
                            price decimal(5,2) not null,
                            primary key (id)
                          )''')
        cursor.execute('''insert into cafe (category, name, price) values
                            ('coffee', 'Espresso', 3.19),
                            ('coffee', 'Cappuccino', 3.29),
                            ('coffee', 'Caffe Latte', 3.39),
                            ('tea', 'Green Tea', 2.99),
                            ('tea', 'Wulong Tea', 2.89)''')
        conn.commit()
        cursor.execute('select * from cafe')
        rows = cursor.fetchall()
        for row in rows:
            #print(row)  For debugging
            # Using column names
            print("%8s %20s %6.2f" % (row['category'], row['name'], row['price']))

PostgreSQL EG 4: Using Prepared-Statements

#!/usr/bin/env python2
# -*- coding: utf-8 -*-
"""
testprepared: Testing SQL prepared statements: CREATE TABLE, INSERT, SELECT
"""
import psycopg2

with psycopg2.connect(database='testdb', 
                      user='testuser', 
                      password='xxxx', 
                      host='localhost', 
                      port='5432') as conn:
    with conn.cursor() as cursor:
        cursor.execute('drop table if exists cafe')
        cursor.execute('''create table if not exists cafe (
                            id serial,
                            category varchar(10) not null,
                            name varchar(50) not null,
                            price decimal(5,2) not null,
                            primary key (id)
                          )''')

        # Using prepared-statement via printf formatting specifiers
        # Use %s for all fields?!
        sql = 'insert into cafe (category, name, price) values (%s, %s, %s)'
    
        # Execute for one set of data
        cursor.execute(sql, ('coffee', 'Espresso', 3.19))
                   
        # Execute for more than one set of data
        data = [('coffee', 'Cappuccino', 3.29),
                ('coffee', 'Caffe Latte', 3.39),
                ('tea', 'Green Tea', 2.99),
                ('tea', 'Wulong Tea', 2.89)]
        cursor.executemany(sql, data)
        conn.commit()  # Commit the insert
    
        cursor.execute('select * from cafe')
        rows = cursor.fetchall()
        for row in rows:
            print(row)

        # Another example
        item = 'Cappuccino'
        cursor.execute('update cafe set price = price * 1.1 where name = %s', (item,))
            # the second argument must be either a tuple or a list
        item_tuple = (item,)
        cursor.execute('select * from cafe where name = %s', item_tuple)
        row = cursor.fetchone()
        print(row)

PostgreSQL EG 5: Transaction Management

#!/usr/bin/env python2
# -*- coding: utf-8 -*-
"""
testtran: Test transaction commit and rollback
"""
import sys
import psycopg2

conn = None

try:
    conn = psycopg2.connect(database='testdb', user='testuser', password='xxxx', host='localhost', port='5432')
    cursor = conn.cursor()
    # A transaction is started silently when the cursor is created.
    # No BEGIN statement is needed. 
    cursor.execute('drop table if exists cafe')
    cursor.execute('''create table if not exists cafe (
                        id serial,
                        category varchar(10) not null,
                        name varchar(50) not null,
                        price decimal(5,2) not null,
                        primary key (id)
                      )''')
    conn.commit()
    
    cursor.execute("insert into cafe values (DEFAULT, 'coffee', 'Espresso', 3.19)")
       # PostgreSQL uses DEFAULT (instead of NULL in MySQL)
    cursor.execute("insert into cafe values (DEFAULT, 'coffee', 'Cappuccino', 3.29)")
    conn.commit()
    
    cursor.execute("insert into cafe values (DEFAULT, 'tea', 'Green Tea', 2.99)")
    #cursor.execute("insert into cafe (xxx) values ('tea')")  # uncomment to trigger an error
    cursor.execute("insert into cafe values (DEFAULT, 'tea', 'Wulong Tea', 2.89)")
    conn.commit()
    
except psycopg2.DatabaseError as e:
    print('Error code %s: %s' % (e.pgcode, e))
    if conn:
        conn.rollback()
        print('rolled back...')
    sys.exit(1)  # Raise a SystemExit exception for cleanup, but honor finally-block
    
finally:
    if conn:
        cursor.execute('select * from cafe')
        rows = cursor.fetchall()
        for row in rows:
            print(row)
        # Always close the connection
        conn.close()
        print('Closed...')

Python Web Applications without Framework

In this section, I shall present an example of Python webapp, running under Apache 2.4 and MySQL 5.6, without using a Python webapp framework (such as Django or Flask).

Configuring Apache

I assume that you have installed Apache 2.4 and are familiar with Apache. To check your apache version:

$ apachectl -version
Server version: Apache/2.4.18 (Ubuntu)
Server built:   2016-07-14T12:32:26

Python scripts are run as cgi script under Apache. Goto /etc/apache2/mods-enabled/, check if modules cgi and mpm_prefork are enabled. If not,

$ sudo a2enmod mpm_prefork cgi
   # You might need to disable module mpm_event

Create the home directory for our webapp, say /var/www/mypython-test:

$ sudo mkdir /var/www/mypython-test

Create a port-based virtual host for our webapp on port 8100, by creating a new configuration file mypython-test.conf in /etc/apache2/sites-available.

$ cd /etc/apache2/sites-available
$ gksu gedit mypython-test.conf
<VirtualHost *:8100>
    ServerAdmin webmaster@localhost
    DocumentRoot /var/www/mypython-test

    ErrorLog ${APACHE_LOG_DIR}/error.log
    CustomLog ${APACHE_LOG_DIR}/access.log combined

    <Directory /var/www/mypython-test>
        Options Indexes FollowSymLinks ExecCGI
        DirectoryIndex index.py
        # apache 2.4
        Require all granted
    </Directory>
    AddHandler cgi-script .py
</VirtualHost>

Edit /etc/apache2/ports.conf to add "Listen 8100".

Make the new configuration available:

$ sudo a2ensite mypython-test
$ sudo service apache2 reload

You can now access the webapp via http://localhost:8100/, which shall show an empty directory at this moment.

Setup MySQL

Configure MySQL, by creating a user (called testuser) and a database (called testdb). See above "Python-MySQL Database Programming".

Write the Web Page in Python Script

Create a new Python script called index.py under /var/www/mypython-test, as follows:

#!/usr/bin/env python2
# -*- coding: utf-8 -*-
"""
index: Test home page
"""

# Turn on debug mode to show the error message. To disable for production
import cgitb
cgitb.enable()

# Print HTML response header, followed by a new line
print("Content-Type: text/html\n")

import MySQLdb

conn = MySQLdb.connect('localhost', 'testuser', 'xxxx', 'testdb')

with conn:
    cursor = conn.cursor()
    # Create table
    cursor.execute('drop table if exists cafe')
    cursor.execute('''create table if not exists cafe (
                        id int unsigned not null auto_increment,
                        category enum('tea', 'coffee') not null,
                        name varchar(50) not null,
                        price decimal(5,2) not null,
                        primary key (id)
                      )''')
    # Insert rows
    cursor.execute('''insert into cafe (category, name, price) values
                        ('coffee', 'Espresso', 3.19),
                        ('coffee', 'Cappuccino', 3.29),
                        ('coffee', 'Caffe Latte', 3.39),
                        ('tea', 'Green Tea', 2.99),
                        ('tea', 'Wulong Tea', 2.89)''')
    # Commit the insert
    conn.commit()

    # Query all records
    cursor.execute('select * from cafe')
    rows = cursor.fetchall()
    for row in rows:
        print('<p>' + str(row) + '</p>')

Note: For Python 3, change the first line to python3.

Set permissions: read and execute for Apache's user www-data.

$ cd /var/www/mypython-test
$ sudo chmod 755 index.py

Try running the Python script, by itself:

$ /var/www/mypython-test/index.py
Run the Webapp

Now, access the page via http://localhost:8100 again. If error 50x occurs, check the apache log @ /var/log/apache2/error.log.

Debugging Python Webapps

[TODO]

REFERENCES & RESOURCES

  1. The Python's mother site @ www.python.org.
  2. Vernon L. Ceder, "The Quick Python Book", 2nd ed, 2010, Manning (Good starting guide for experience programmers who wish to learning Python).
  3. Mark Lutz, "Learning Python", 4th ed, 2009; "Programming Python", 4th ed, 2011; "Python Pocket Reference", 4th ed, 2010, O'reilly.