Python-MySQL Database Programming
References:
- MySQL for Python (MySQLdb) @ http://sourceforge.net/projects/mysql-python/.
- 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
- 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 moduleMySQLdb
. - print(sys.version_info) (Line 9): Print the Python version for debugging. Check the
major=3|2
. - I use the new style formatting
str.format()
, which is supported by both Python 3 and Pythobn 2. - Uncomment Line 24 to trigger exception handling:
Connected... error 1054: Unknown column 'xxx' in 'field list' Closed...
Observe thatexcept
clause was run, followed byfinally
, beforesys.exit()
- [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
- The
with conn:
statement always closes theconn
, and provide error handling. - [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
- [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
- Python MySQLdb supports prepared-statements via printf formatting specifiers.
- [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
- 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 33insert
withid=3
) were discarded. - [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
- 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. - [TODO]
Python-PostgreSQL Database Programming
References:
Psycopg2
@ http://initd.org/psycopg/.Psycopg2
Documentation & API @ http://initd.org/psycopg/docs/.- [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
- The above code runs under Python 3 as well as Python 2. Modify the first line to choose the Python interpreter if running standalone.
- 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...
- [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
- 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
- [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
- [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
- [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
- 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...
- [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
- The Python's mother site @ www.python.org.