TABLE OF CONTENTS (HIDE)

Python

Database and Web Applications

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

(Ubuntu) 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 3)(Ubuntu) Installing Python-MySQL Driver mysqlclient

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

We shall use pip (Python Package Manager) to install Python packages (instead of apt-get) to get the latest releases. See "Python IDEs and Tools" on how to use pip.

# Install mysqlclient via pip, system-wide with sudo
$ sudo pip3 install mysqlclient

# Verify the installation
$ pip3 show --files mysqlclient
Name: mysqlclient
Version: 1.3.13
Summary: Python interface to MySQL
Location: /usr/local/lib/python3.6/dist-packages
......
(Python 2)(Ubuntu) Installing Python-MySQL Driver MySQLdb
# Install MySQLdb via pip, system-wide with sudo
$ sudo pip2 install MySQL-python

# Verify the installation
$ pip2 show --files MySQL-python
Name: MySQL-python
Version: 1.2.5
Summary: Python interface to MySQL
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.

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

testdbconn.py
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
 
#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""
testdbconn: Test MySQL Database Connection
"""
import sys
import MySQLdb

print(sys.version_info)   # Print Python version for debugging
print('--------------')
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: {}'.format(version))  # one-item tuple

except MySQLdb.Error as e:
    print('error {}: {}'.format(e.args[0], e.args[1]))  # Error code number, description
    sys.exit(1)  # Raise a SystemExit exception for cleanup, but honor finally-block

finally:
    print('finally...')
    if conn:
        # Always close the connection
        conn.close()
        print('Closed...')
Output (Python 3)
sys.version_info(major=3, minor=6, micro=6, releaselevel='final', serial=0)
--------------
Connected...
Database version: ('5.7.24-0ubuntu0.18.04.1',)
finally...
Closed...
Output (Python 2)
sys.version_info(major=2, minor=7, micro=15, releaselevel='candidate', serial=1)
--------------
Connected...
Database version : 5.7.24-0ubuntu0.18.04.1 
finally...
Closed...
How It Works
  1. This script shoud run on both Python 3 and Python 2. To run on Python 2, change Line 1 to #!/usr/bin/env python2. Although you need to install different MySQL driver packages for Python 3 (mysqlclient) and Python 2 (MySQLdb), both drivers use module MySQLdb.
  2. print(sys.version_info) (Line 9): Print the Python version for debugging. Check the major=3|2.
  3. I use the new style formatting str.format(), which is supported by both Python 3 and Pythobn 2.
  4. Uncomment Line 24 to trigger exception handling: 
    Connected...
error 1054: Unknown column 'xxx' in 'field list'
Closed...
    Observe that except clause was run, followed by finally, before sys.exit()
  5. [TODO]

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

testsqlstmt.py
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
 
#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""
testsqlstmt: Testing MySQL statements: CREATE TABLE, INSERT and SELECT
"""
import sys
import MySQLdb

print(sys.version_info)   # Print Python version for debugging
print('--------------')
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}'.format(desc[1][0], desc[2][0], desc[3][0]))
    print('{:10s}-{:20s}-{:6s}'.format('-'*10, '-'*20, '-'*6))  # Print divider

    for i in range(cursor.rowcount):
        row = cursor.fetchone()
        print('{:<10s} {:<20s} {:6.2f}'.format(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)
Output
sys.version_info(major=3, minor=6, micro=6, releaselevel='final', serial=0)
--------------
(1, 'coffee', 'Espresso', Decimal('3.19'))
(2, 'coffee', 'Cappuccino', Decimal('3.29'))
(3, 'coffee', 'Caffe Latte', Decimal('3.39'))
(4, 'tea', 'Green Tea', Decimal('2.99'))
(5, 'tea', 'Wulong Tea', Decimal('2.89'))
category   name                 price 
--------------------------------------
coffee     Espresso               3.19
coffee     Cappuccino             3.29
coffee     Caffe Latte            3.39
tea        Green Tea              2.99
tea        Wulong Tea             2.89
(1, 'coffee', 'Espresso', Decimal('3.19'))
(2, 'coffee', 'Cappuccino', Decimal('3.29'))
(3, 'coffee', 'Caffe Latte', Decimal('3.39'))
(4, 'tea', 'Green Tea', Decimal('2.99'))
(5, 'tea', 'Wulong Tea', Decimal('2.89'))
How It Works
  1. The with conn: statement always closes the conn, and provide error handling.
  2. [TODO]

MySQL EG 3: Using Dictionary Cursor

testdictcursor.py
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
 
#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""
testdictcursor: Using Dictionary Cursor
"""
import sys
import MySQLdb

print(sys.version_info)   # Print Python version for debugging
print('--------------')
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('{}: {}'.format(row['category'], row['name']))  # via dictionary keys
Output
sys.version_info(major=3, minor=6, micro=6, releaselevel='final', serial=0)
--------------
coffee: Espresso
coffee: Cappuccino
coffee: Caffe Latte
tea: Green Tea
tea: Wulong Tea
How It Works
  1. [TODO]

MySQL EG 4: Using Prepared-Statements

testpreparedstmt.py
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
 
#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""
testpreparedstmt: Using SQL Prepared-Statement
"""
import sys
import MySQLdb

print(sys.version_info)   # Print Python version for debugging
print('--------------')
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)
Output
sys.version_info(major=3, minor=6, micro=6, releaselevel='final', serial=0)
--------------
(1, 'coffee', 'Espresso', Decimal('3.19'))
(2, 'coffee', 'Cappuccino', Decimal('3.29'))
(3, 'coffee', 'Caffe Latte', Decimal('3.39'))
(4, 'tea', 'Green Tea', Decimal('2.99'))
(5, 'tea', 'Wulong Tea', Decimal('2.89'))
./testpreparedstmt_p3.py:46: Warning: (1265, "Data truncated for column 'price' at row 2")
  cursor.execute('update cafe set price = price * 1.1 where name = %s', (item,))  # or one-element list: [item]
(2, 'coffee', 'Cappuccino', Decimal('3.62'))
How It Works
  1. Python MySQLdb supports prepared-statements via printf formatting specifiers.
  2. [TODO]

MySQL EG 5: Managing Transactions

testtran.py
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
 
#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""
testtran: Testing SQL Transaction of commit and rollback
"""
import sys
import MySQLdb

print(sys.version_info)   # Print Python version for debugging
print('--------------')
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:
    print('error {}: {}'.format(e.args[0], e.args[1]))
    if conn:
        conn.rollback()
        print('rolled back...')
    sys.exit(1)  # Raise a SystemExit exception for cleanup, but honor finally-block

finally:
    print('finally...')
    if conn:
        cursor.execute('select * from cafe')
        rows = cursor.fetchall()
        for row in rows:
            print(row)
        conn.close()
Output
sys.version_info(major=3, minor=6, micro=6, releaselevel='final', serial=0)
--------------
finally...
(1, 'coffee', 'Espresso', Decimal('3.19'))
(2, 'coffee', 'Cappuccino', Decimal('3.29'))
(3, 'tea', 'Green Tea', Decimal('2.99'))
(4, 'tea', 'Wulong Tea', Decimal('2.89'))
How It Works
  1. Uncomment Line 34 to trigger exception handling and rollback. 
    error 1054: Unknown column 'xxx' in 'field list'
rolled back...
finally...
(1, 'coffee', 'Espresso', Decimal('3.19'))
(2, 'coffee', 'Cappuccino', Decimal('3.29'))
    Observe that changes after the last commit (Line 33 insert with id=3) were discarded.
  2. [TODO]
Using with statement

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
 
#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""
testtranwith: Testing Transaction (commit/rollback) under with-statement
"""
import sys
import MySQLdb

print(sys.version_info)   # Print Python version for debugging
print('--------------')
conn = MySQLdb.connect('localhost', 'testuser', 'xxxx', 'testdb')

try:
    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)

except MySQLdb.Error as e:
    print('error {}: {}'.format(e.args[0], e.args[1]))
    # auto rollback
    #if conn:
    #    conn.rollback()
    sys.exit(1)  # Raise a SystemExit exception for cleanup, but honor finally-block

finally:
    print('finally...')
    if conn:
        cursor.execute('select * from cafe')
        rows = cursor.fetchall()
        print(rows)   # for debugging
        for row in rows: print(row)
        conn.close()
Output
sys.version_info(major=3, minor=6, micro=6, releaselevel='final', serial=0)
--------------
(1, 'tea', 'Green Tea', Decimal('2.99'))
(2, 'tea', 'Wulong Tea', Decimal('2.89'))
finally...
......
How It Works
  1. Uncomment Line 27 to trigger exception handling and rollback: 
    error 1054: Unknown column 'xxx' in 'field list'
finally...
()
    Observe that the table is created (auto-commit), but all insertions were rolled back resulted in an empty table.
  2. [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-10 libpq-dev python-dev

# 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.7.6.1
Location: /usr/local/lib/python3.6/dist-packages
......

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

Notes: You may also use "apt-get install python-psycopg2" to install psycopg2. However, you might not get the latest version.

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: xxxx

# 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

PG EG 1: Connecting to MySQL Database Server

testdbconn.py
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
 
#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""
testdbconn: Testing PostgreSQL database connection
"""
import sys
import psycopg2

print(sys.version_info)   # Print Python version for debugging
print('--------------')
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: {}'.format(version))  # one-item tuple

except psycopg2.DatabaseError as e:
    print('Error code {}: {}'.format(e.pgcode, e))
    sys.exit(1)  # Raise a SystemExit exception for cleanup, but honor finally-block

finally:
    print('finally...')
    if conn:
        # Always close the connection
        conn.close()
        print('Closed...')
Output (Python 3)
sys.version_info(major=3, minor=6, micro=7, releaselevel='final', serial=0)
--------------
Connected...
Database version: ('PostgreSQL 10.6 (Ubuntu 10.6-0ubuntu0.18.04.1) ......)
finally...
Closed...
Output (Python 2)
sys.version_info(major=2, minor=7, micro=15, releaselevel='candidate', serial=1)
--------------
Connected...
Database version: ('PostgreSQL 10.6 (Ubuntu 10.6-0ubuntu0.18.04.1) ......)
finally...
Closed...
How It Works
  1. The above code runs under Python 3 as well as Python 2. Modify the first line to choose the Python interpreter if running standalone.
  2. Try un-comment Line 23 to trigger exception handling: 
    sys.version_info(major=3, minor=6, micro=7, releaselevel='final', serial=0)
--------------
Connected...
Error code 42703: column "xxx" does not exist
......
finally...
Closed...
  3. [TODO] more

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

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.

testsqlstmts.py
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
 
#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""
testsqlstmts: Testing SQL statements: CREATE TABLE, INSERT, SELECT
"""
import psycopg2

import sys
print(sys.version_info)   # Print Python version for debugging
print('--------------')

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)

        # 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}'.format(desc[1][0], desc[2][0], desc[3][0]))
        print('{:10s}-{:20s}-{:6s}'.format('-'*10, '-'*20, '-'*6))  # Print divider

        for i in range(cursor.rowcount):
            row = cursor.fetchone()
            print('{:<10s} {:<20s} {:6.2f}'.format(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)
Output
sys.version_info(major=2, minor=7, micro=15, releaselevel='candidate', serial=1)
--------------
(1, 'coffee', 'Espresso', Decimal('3.19'))
(2, 'coffee', 'Cappuccino', Decimal('3.29'))
(3, 'coffee', 'Caffe Latte', Decimal('3.39'))
(4, 'tea', 'Green Tea', Decimal('2.99'))
(5, 'tea', 'Wulong Tea', Decimal('2.89'))
category   name                 price 
--------------------------------------
coffee     Espresso               3.19
coffee     Cappuccino             3.29
coffee     Caffe Latte            3.39
tea        Green Tea              2.99
tea        Wulong Tea             2.89
(1, 'coffee', 'Espresso', Decimal('3.19'))
(2, 'coffee', 'Cappuccino', Decimal('3.29'))
(3, 'coffee', 'Caffe Latte', Decimal('3.39'))
(4, 'tea', 'Green Tea', Decimal('2.99'))
(5, 'tea', 'Wulong Tea', Decimal('2.89'))
How It Works
  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,

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
 
#!/usr/bin/env python3
# -*- 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
Output
(1, 'coffee', 'Espresso', Decimal('3.19'))
(2, 'coffee', 'Cappuccino', Decimal('3.29'))
(3, 'coffee', 'Caffe Latte', Decimal('3.39'))
(4, 'tea', 'Green Tea', Decimal('2.99'))
(5, 'tea', 'Wulong Tea', Decimal('2.89'))
How It Works
  1. [TODO]

PG EG 3: Dictionary Cursor

testdictcursor.py
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
 
#!/usr/bin/env python3
# -*- 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']))
Output
  coffee             Espresso   3.19
  coffee           Cappuccino   3.29
  coffee          Caffe Latte   3.39
     tea            Green Tea   2.99
     tea           Wulong Tea   2.89
How It Works
  1. [TODO]

PG EG 4: Using Prepared-Statements

testprepared.py
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
 
#!/usr/bin/env python3
# -*- 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)
Output
(1, 'coffee', 'Espresso', Decimal('3.19'))
(2, 'coffee', 'Cappuccino', Decimal('3.29'))
(3, 'coffee', 'Caffe Latte', Decimal('3.39'))
(4, 'tea', 'Green Tea', Decimal('2.99'))
(5, 'tea', 'Wulong Tea', Decimal('2.89'))
(2, 'coffee', 'Cappuccino', Decimal('3.62'))
How It Works
  1. [TODO]

PG EG 5: Transaction Management

testtran.py
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
 
#!/usr/bin/env python3
# -*- 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 {}: {}'.format(e.pgcode, e))
    if conn:
        conn.rollback()
        print('rolled back...')
    sys.exit(1)  # Raise a SystemExit exception for cleanup, but honor finally-block

finally:
    print('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...')
Output
finally...
(1, 'coffee', 'Espresso', Decimal('3.19'))
(2, 'coffee', 'Cappuccino', Decimal('3.29'))
(3, 'tea', 'Green Tea', Decimal('2.99'))
(4, 'tea', 'Wulong Tea', Decimal('2.89'))
Closed...
How It Works
  1. Uncomment Line 32 to trigger exception handling: 
    error code 42703: column "xxx" of relation "cafe" does not exist
LINE 1: insert into cafe (xxx) values ('tea')
rolled back...
finally...
(1, 'coffee', 'Espresso', Decimal('3.19'))
(2, 'coffee', 'Cappuccino', Decimal('3.29'))
Closed...
  2. [TODO]

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 (or PostgreSQL), 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 (otherwise, check out the Apache section).

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 apache's 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/PostgreSQL

Configure MySQL/PostgreSQL, by creating a user (called testuser) and a database (called testdb). See previous sections.

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 python3
# -*- 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")

# For MySQL
import MySQLdb
conn = MySQLdb.connect('localhost', 'testuser', 'xxxx', 'testdb')
# For PostgreSQL
#import psycopg2
#conn = psycopg2.connect(database='testdb', user='testuser', password='xxxx', host='localhost', port='5432')

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>')   # Print HTML paragraphs

Note: For Python 2, change the first line to python2.

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. If error 50x occurs, check the apache log @ /var/log/apache2/error.log.

Debugging Python Webapps

Under Eclipse-PyDev

[TODO]

Under PyCharm

[TODO]

REFERENCES & RESOURCES

  1. The Python's mother site @ www.python.org.

Latest version tested: Python 2.7.12 and 3.6 under Ubuntu 18.04LTS
Last modified: November, 2018