Python Programming by Example (2015)
14. Database Programming
This chapter explains how to build database application using Python.
14.1 Database for Python
Python can communicate with database server through database driver. We can use MySQL driver fro Python. In this chapter, I only focus on MySQL scenario.
14.2 MySQL Driver for Python
We use MySQL driver for Python. Further information about this driver, please visit on https://dev.mysql.com/downloads/connector/python/2.1.html to download and install.
You also can install MySQL driver for Python via pip3 (Python 3.x).
$ sudo pip3 install --allow-external mysql-connector-python mysql-connector-python
14.3 Testing Connection
In this section, we try to connect MySQL database. We can use connect() from mysql.connector object.
import mysql.connector
print('connecting to mysql server...')
cnx = mysql.connector.connect(user='pyuser',
password='password123',
host='127.0.0.1',
database='pydb')
print('connected')
You should pass username, password, database server and database name.
14.4 CRUD (Create, Read, Update and Delete) Operations
In this section, we try to create, read, update and delete data on MySQL. Firstly, we create database and its table.
The following is our table scheme on MySQL.
CREATE DATABASE `pydb`;
CREATE TABLE `pydb`.`product` (
`idproduct` INT NOT NULL AUTO_INCREMENT,
`name` VARCHAR(30) NOT NULL,
`code` VARCHAR(10) NOT NULL,
`price` DECIMAL NOT NULL,
`quantity` INT NULL,
`created` DATETIME NULL,
PRIMARY KEY (`idproduct`));
File: pydb.sql
Run these SQL scripts into your MySQL.
14.4.1 Create Data
To create data, we use SQL statement, INSERT INTO, which pass to execute() function.
def create_data(conn):
cursor = conn.cursor()
print('inserting data...')
for i in range(1,5):
insert_product = ("INSERT INTO product "
"(name, code, price, quantity, created) "
"VALUES (%s, %s, %s, %s, %s)")
data_product = ("product " + str(i), "F029" + str(i), i*0.21, i, datetime.now())
cursor.execute(insert_product, data_product)
product_id = cursor.lastrowid
print('inserted with id=',product_id)
conn.commit()
cursor.close()
print('done')
To obtain the last inserted id, we can use lastrowid from cursor object.
14.4.2 Read Data
To read data, you can use SELECT...FROM query on your Python scripts.
def read_data(conn):
print('reading data....')
selected_id = 0
cursor = conn.cursor()
query = "SELECT idproduct, name, code, price, quantity, created FROM product"
cursor.execute(query)
for (id, name, code, price, quantity, created) in cursor:
print("{}, {}, {}, {}, {}, {:%d %b %Y %H:%M:%S}".format(
id, name, code, price, quantity, created))
if selected_id <= 0:
selected_id = id
cursor.close()
print('done')
return selected_id
14.4.3 Update Data
To update data, you can use UPDATE....SET query on your Python scripts.
def update_data(conn, id):
print('updating data with idproduct=', id, '...')
cursor = conn.cursor()
query = "UPDATE product SET name=%s, code=%s, price=%s, quantity=%s, created=%s where idproduct=%s"
name = 'updated-name'
code = 'F9999'
price = 0.99
quantity = 10
created = datetime.now()
cursor.execute(query, (name, code, price, quantity, created, id))
conn.commit()
cursor.close()
print('done')
Don't forget to call commit() after changed the data.
14.4.4 Delete Data
To update data, you can use DELETE FROM query on your Python scripts.
def delete_data(conn, id):
print('deleting data on idproduct=', id, '...')
cursor = conn.cursor()
query = "DELETE FROM product where idproduct = %s "
cursor.execute(query, (id,))
conn.commit()
cursor.close()
print('done')
def delete_all(conn):
print('deleting all data....')
cursor = conn.cursor()
query = "DELETE FROM product"
cursor.execute(query)
conn.commit()
cursor.close()
print('done')
Don't forget to call commit() after changed the data.
14.4.5 Write them All
Now we can write our scripts about CRUD.
Write these scripts.
import mysql.connector
from datetime import datetime
def create_data(conn):
cursor = conn.cursor()
print('inserting data...')
for i in range(1,5):
insert_product = ("INSERT INTO product "
"(name, code, price, quantity, created) "
"VALUES (%s, %s, %s, %s, %s)")
data_product = ("product " + str(i), "F029" + str(i), i*0.21, i, datetime.now())
cursor.execute(insert_product, data_product)
product_id = cursor.lastrowid
print('inserted with id=',product_id)
conn.commit()
cursor.close()
print('done')
def read_data(conn):
print('reading data....')
selected_id = 0
cursor = conn.cursor()
query = "SELECT idproduct, name, code, price, quantity, created FROM product"
cursor.execute(query)
for (id, name, code, price, quantity, created) in cursor:
print("{}, {}, {}, {}, {}, {:%d %b %Y %H:%M:%S}".format(
id, name, code, price, quantity, created))
if selected_id <= 0:
selected_id = id
cursor.close()
print('done')
return selected_id
def update_data(conn, id):
print('updating data with idproduct=', id, '...')
cursor = conn.cursor()
query = "UPDATE product SET name=%s, code=%s, price=%s, quantity=%s, created=%s where idproduct=%s"
name = 'updated-name'
code = 'F9999'
price = 0.99
quantity = 10
created = datetime.now()
cursor.execute(query, (name, code, price, quantity, created, id))
conn.commit()
cursor.close()
print('done')
def delete_data(conn, id):
print('deleting data on idproduct=', id, '...')
cursor = conn.cursor()
query = "DELETE FROM product where idproduct = %s "
cursor.execute(query, (id,))
conn.commit()
cursor.close()
print('done')
def delete_all(conn):
print('deleting all data....')
cursor = conn.cursor()
query = "DELETE FROM product"
cursor.execute(query)
conn.commit()
cursor.close()
print('done')
print('connecting to mysql server...')
cnx = mysql.connector.connect(user='pyuser',
password='password123',
host='127.0.0.1',
database='pydb')
print('connected')
create_data(cnx)
selected_id = read_data(cnx)
update_data(cnx, selected_id)
read_data(cnx)
delete_data(cnx, selected_id)
read_data(cnx)
delete_all(cnx)
cnx.close()
print('closed connection')
Save this program. Then, you can run it.
$ python3 ch14_01.py
Program output:
