Python Quick Notes :: Part - 4

Hands-on With Python - IV

To access relational databases in Python, one must use the standard DB-API interfaces. For MySQL, download the MySQL Connector for Python from the following website:

MySQL Connector/Python

Follow the setup procedure oulined in the following website:

MySQL Connector/Python Installation

To work with MySQL, one must import the module mysql.connector. The following are some of the frequently used functions from the Python DB-API:

• con = mysql.connector.connect(host=H, port=P, user=U, password=S, database=D) - open a database connection with the specified database D running on the specified host H and port P using the specified credentials for the user U and password S. Returns a connection object con on success

• con.close() - close the connection to the database using the connection object con

• con.commit() - issue a database commit using the connection object con

• cur = con.cursor() - return a new cursor object cur using the connection object con. One must have a cursor object to perform any database operation

• con.rollback() - issue a database rollback using the connection object con

• cur.close() - close the database cusror using the cursor object cur

• cur.execute(sql) - execute the specified sql statement using the cursor object cur

• cur.execute(sql, {args}) - execute the specified prepared sql statement with the specified dictionary of arguments args using the cursor object cur

• cur.fetchone() - fetch the next row from the result set of the SQL query using the cursor object cur

• cur.fetchall() - fetch all the rows from the result set of the SQL query using the cursor object cur

The following is the python program named sample-20.py:

#
# Name: sample-20.py
#

# ----- Python DB-API using MySQL Connector/Python -----

import mysql.connector

# Get a connection object

print("Ready to connect to mytestdb .....")

try:
    con = mysql.connector.connect(host='localhost',
                                  port=3306,
                                  user='python',
                                  password='python123',
                                  database='mytestdb')
except Exception as e:
    print(e)
    exit(1)

print("Ready to create a cursor .....")

# Get the cursor object

try:
    cur = con.cursor()
except Exception as e:
    print(e)
    con.close()
    exit(1)

# Create the table product_tbl

sql01 = """CREATE TABLE product_tbl(
              item_no VARCHAR(10) NOT NULL PRIMARY KEY,
              item_name VARCHAR(50) NOT NULL,
              quantity INT,
              price FLOAT(5,2)
            )"""

print("Ready to create table product_tbl .....")

try:
    cur.execute(sql01)
except Exception as e:
    con.close()
    print(e)
    exit(1)

# Insert into table product_tbl

sql02 = """INSERT INTO product_tbl
              (item_no, item_name, quantity, price)
              VALUES ('1000', 'Python Quick Start Part 1', 10, 2.99)"""

sql03 = """INSERT INTO product_tbl
              (item_no, item_name, quantity, price)
              VALUES ('1001', 'Python Quick Start Part 2', 10, 2.99)"""

sql04 = """INSERT INTO product_tbl
              (item_no, item_name, quantity, price)
              VALUES ('1002', 'Python Quick Start Part 3', 10, 2.99)"""

print("Ready to insert into table product_tbl .....")

try:
    cur.execute(sql02)
    cur.execute(sql03)
    cur.execute(sql04)
    con.commit()
except Exception as e:
    con.rollback()
    cur.close()
    con.close()
    print(e)
    exit(1)

# Select no of records in table product_tbl

sql05 = """SELECT COUNT(*) FROM product_tbl"""

print("Ready to select no of records in table product_tbl .....")

try:
    cur.execute(sql05)
    print("Record count in table product_tbl", cur.fetchone())
except Exception as e:
    cur.close()
    con.close()
    print(e)
    exit(1)

# Select all records from table product_tbl

sql06 = """SELECT * FROM product_tbl"""

print("Ready to select all records from table product_tbl .....")

try:
    cur.execute(sql06)
    res = cur.fetchall()
    for r in res:
        print("%s, %s, %d, %.2f" % (r[0], r[1], r[2], r[3]))
except Exception as e:
    cur.close()
    con.close()
    print(e)
    exit(1)

# Update item 1001 in table product_tbl

sql07 = """UPDATE product_tbl SET quantity = 5 WHERE item_no = '1001'"""

print("Ready to update item 1001 in table product_tbl .....")

try:
    cur.execute(sql07)
    con.commit()
except Exception as e:
    con.rollback()
    cur.close()
    con.close()
    print(e)
    exit(1)

# Select item 1001 in table product_tbl

sql08 = """SELECT * FROM product_tbl WHERE item_no = '1001'"""

print("Ready to select item 1001 from table product_tbl .....")

try:
    cur.execute(sql08)
    res = cur.fetchall()
    for r in res:
        print("%s, %s, %d, %.2f" % (r[0], r[1], r[2], r[3]))
except Exception as e:
    cur.close()
    con.close()
    print(e)
    exit(1)

# Delete item 1000 in table product_tbl

sql09 = """DELETE FROM product_tbl WHERE item_no = '1002'"""

print("Ready to delete item 1002 from table product_tbl .....")

try:
    cur.execute(sql09)
    con.commit()
except Exception as e:
    con.rollback()
    cur.close()
    con.close()
    print(e)
    exit(1)

# Select all records from table product_tbl

sql10 = """SELECT * FROM product_tbl"""

print("Ready to select all records from table product_tbl .....")

try:
    cur.execute(sql10)
    res = cur.fetchall()
    for r in res:
        print("%s, %s, %d, %.2f" % (r[0], r[1], r[2], r[3]))
except Exception as e:
    cur.close()
    con.close()
    print(e)
    exit(1)

# Insert into table product_tbl using prepared statement

sql11 = """INSERT INTO product_tbl
              (item_no, item_name, quantity, price)
              VALUES (%(no)s, %(name)s, %(qty)s, %(price)s)"""
args1 = {'no': '1003', 'name': 'C Manual Vol 1', 'qty': '3', 'price': '1.99'}
args2 = {'no': '1004', 'name': 'C Manual Vol 2', 'qty': '6', 'price': '1.99'}

print("Ready to insert into table product_tbl (prepared) .....")

try:
    cur.execute(sql11, args1)
    cur.execute(sql11, args2)
    con.commit()
except Exception as e:
    con.rollback()
    cur.close()
    con.close()
    print(e)
    exit(1)

# Select all records from table product_tbl

print("Ready to select all records from table product_tbl .....")

try:
    cur.execute(sql10)
    res = cur.fetchall()
    for r in res:
        print("%s, %s, %d, %.2f" % (r[0], r[1], r[2], r[3]))
except Exception as e:
    cur.close()
    con.close()
    print(e)
    exit(1)

# Update item 1004 in table product_tbl using prepared statement

sql12 = """UPDATE product_tbl SET quantity = %(qty)s WHERE item_no = %(no)s"""

print("Ready to update item in table product_tbl (prepared) .....")

try:
    cur.execute(sql12, {'no': '1004', 'qty': '1'})
    con.commit()
except Exception as e:
    con.rollback()
    cur.close()
    con.close()
    print(e)
    exit(1)

# Select item from table product_tbl using prepared statement

sql13 = """SELECT * FROM product_tbl WHERE item_no = %(no)s"""

print("Ready to select item from table product_tbl (prepared) .....")

try:
    cur.execute(sql13, {'no': '1004'})
    res = cur.fetchall()
    for r in res:
        print("%s, %s, %d, %.2f" % (r[0], r[1], r[2], r[3]))
except Exception as e:
    cur.close()
    con.close()
    print(e)
    exit(1)

# Drop the table product_tbl

sql14 = """DROP TABLE product_tbl"""

print("Ready to drop the table product_tbl .....")

try:
    cur.execute(sql14)
except Exception as e:
    cur.close()
    con.close()
    print(e)
    exit(1)

print("Done !!!")

Execute the following command:

python sample-20.py

The following is the output:

Output (sample-20.py)

Ready to connect to mytestdb .....
Ready to create a cursor .....
Ready to create table product_tbl .....
Ready to insert into table product_tbl .....
Ready to select no of records in table product_tbl .....
Record count in table product_tbl (3,)
Ready to select all records from table product_tbl .....
1000, Python Quick Start Part 1, 10, 2.99
1001, Python Quick Start Part 2, 10, 2.99
1002, Python Quick Start Part 3, 10, 2.99
Ready to update item 1001 in table product_tbl .....
Ready to select item 1001 from table product_tbl .....
1001, Python Quick Start Part 2, 5, 2.99
Ready to delete item 1002 from table product_tbl .....
Ready to select all records from table product_tbl .....
1000, Python Quick Start Part 1, 10, 2.99
1001, Python Quick Start Part 2, 5, 2.99
Ready to insert into table product_tbl (prepared) .....
Ready to select all records from table product_tbl .....
1000, Python Quick Start Part 1, 10, 2.99
1001, Python Quick Start Part 2, 5, 2.99
1003, C Manual Vol 1, 3, 1.99
1004, C Manual Vol 2, 6, 1.99
Ready to update item in table product_tbl (prepared) .....
Ready to select item from table product_tbl (prepared) .....
1004, C Manual Vol 2, 1, 1.99
Ready to drop the table product_tbl .....
Done !!!

NOTE :: The parameter(s) in a prepared statement must be specified as %(name)s and the value corresponding for name must be provided in a dictionary as {'name': 'value'}

Python's standard library modules xml.sax and xml.dom provide the necessary interfaces to work with XML. The module xml.sax provides the Simple API for XML (SAX) parser interface while the module xml.dom provides the Document Object Model (DOM) parser interface.

The Simple API for XML (SAX) parser is an event based interface for parsing XML in which one must register a custom content handler that defines callback functions for the desired events. To register a custom content handler, one must subclass the class xml.sax.ContentHandler and override the desired callback methods. The following are the commonly used callback methods from the class xml.sax.ContentHandler:

• startDocument() - is called at the start of the XML document

• endDocument() - is called at the end of the XML document

• startElement(name, attrs) - is called at the start of the XML element name. attrs is the list of attribute name-value pairs. To extract the value of an attribute with name price one has to invoke the method attrs.getValue('price')

• startElementNS(name, qname, attrs) - is called when the parser is namespace aware and at the start of the XML element name in the namespace qname

• endElement(name) - is called at the end of the XML element name

• endElementNS(name, qname) - is called when the parser is namespace aware and at the end of the XML element name in the namespace qname

• characters(data) - is called when a chunk of data is identified by the parser. The parser may provide data in chunks

For our test, we will use the following XML named Product.xml:

<Products>
    <Item no='1000'>
        <name>Python Quick Start - 1</name>
        <quantity>5</quantity>
        <price>0.99</price>
    </Item>
    <Item no='1001'>
        <name>Python Quick Start - 2</name>
        <quantity>10</quantity>
        <price>1.99</price>
    </Item>
    <Item no='1002'>
        <name>Python Quick Start - 3</name>
        <quantity>15</quantity>
        <price>2.99</price>
    </Item>
</Products>

The following is the python program named sample-21.py:

#
# Name: sample-21.py
#

# ----- Python XML parsing using SAX parser -----

import xml.sax

# Define the custom Product content handler

class ProductHandler(xml.sax.ContentHandler):

    def startDocument(self):
        print("---> Start of the Product XML")
        return

    def endDocument(self):
        print("---> End of the Product XML")
        return

    def startElement(self, name, attrs):
        print("---> Start of the XML element " + name)
        print("---> Attributes:")
        for an in attrs.getNames():
            print("Name: " + an + ", Value: " + attrs.getValue(an))
        return

    def endElement(self, name):
        print("---> End of the XML element " + name)
        return

    def characters(self, data):
        if len(data.strip()) > 0:
            print("---> Data = " + data)
        return

# Main

# Create the SAX parser

parser = xml.sax.make_parser()

# Not namespace aware

parser.setFeature(xml.sax.handler.feature_namespaces, 0)

# Set the custom content handler

parser.setContentHandler(ProductHandler())

# Parse the Product.xml document

parser.parse("Product.xml")

Execute the following command:

python sample-21.py

The following is the output:

Output (sample-21.py)

---> Start of the Product XML
---> Start of the XML element Products
---> Attributes:
---> Start of the XML element Item
---> Attributes:
Name: no, Value: 1000
---> Start of the XML element name
---> Attributes:
---> Data = Python Quick Start - 1
---> End of the XML element name
---> Start of the XML element quantity
---> Attributes:
---> Data = 5
---> End of the XML element quantity
---> Start of the XML element price
---> Attributes:
---> Data = 0.99
---> End of the XML element price
---> End of the XML element Item
---> Start of the XML element Item
---> Attributes:
Name: no, Value: 1001
---> Start of the XML element name
---> Attributes:
---> Data = Python Quick Start - 2
---> End of the XML element name
---> Start of the XML element quantity
---> Attributes:
---> Data = 10
---> End of the XML element quantity
---> Start of the XML element price
---> Attributes:
---> Data = 1.99
---> End of the XML element price
---> End of the XML element Item
---> Start of the XML element Item
---> Attributes:
Name: no, Value: 1002
---> Start of the XML element name
---> Attributes:
---> Data = Python Quick Start - 3
---> End of the XML element name
---> Start of the XML element quantity
---> Attributes:
---> Data = 15
---> End of the XML element quantity
---> Start of the XML element price
---> Attributes:
---> Data = 2.99
---> End of the XML element price
---> End of the XML element Item
---> End of the XML element Products
---> End of the Product XML

The Document Object Model (DOM) parser represents the XML document as a tree structure in memory and allows for easy traversal of nodes of the tree. To use the DOM parser, one needs to use the Python module xml.dom.minidom. The following are the commonly used data members and methods from the interface xml.dom.minidom:

• xml.dom.minidom.DOM - represents an in-memory tree structure of the XML document

• xml.dom.minidom.parse(xml) - returns a xml.dom.minidom.DOM object after parsing the specified xml file

• xml.dom.minidom.DOM.documentElement - represents the root node of the xml.dom.minidom.DOM object

• xml.dom.minidom.Node - represents a node in the XML document

• xml.dom.minidom.Node.nodeType - data member that indicates the type of node in the XML document. An XML element is represented by the type xml.dom.minidom.Node.ELEMENT_NODE. The characters between an XML element is represented by the type xml.dom.minidom.Node.TEXT_NODE

• node.hasAttributes() - returns True if the node has attributes

• node.hasChildNodes() - returns True if the node has child nodes

• node.attributes.items() - returns a list of attributes of the node. Each item in the list is a tuple where the first member is the name and the second is the value

• node.getElementsByTagName(element) - search the descendants of the node for the element

The following is the python program named sample-22.py:

#
# Name: sample-22.py
#

# ----- Python XML parsing using DOM parser -----

import xml.dom.minidom

def traverseChild(node):
    for n in node.childNodes:
        printNode(n)
    return

def printAttributes(node):
    for attr in list(node.attributes.items()):
        print("---> Attribute Name: " + attr[0] + ", Value: " + attr[1])
    return

def printNode(node):
    if node.nodeType == xml.dom.minidom.Node.ELEMENT_NODE:
        print("---> Element Name: " + node.tagName)
        if node.hasAttributes():
            printAttributes(node)
        if node.hasChildNodes():
            traverseChild(node)
    elif node.nodeType == xml.dom.minidom.Node.TEXT_NODE:
        if len(node.data.strip()) > 0:
            print("---> Text: " + node.data)
    return

dom = xml.dom.minidom.parse("Product.xml")

root = dom.documentElement

traverseChild(root)

print("====================================================")

# Find the Item element in the tree

items = root.getElementsByTagName('Item')

# There are 3 Item elements in our example

for item in items:
    if item.hasAttribute('no'):
        print("---> Item No: " + item.getAttribute('no'))
        name = item.getElementsByTagName('name')[0]
        print("       name: %s" % name.childNodes[0].data)

Execute the following command:

python sample-22.py

The following is the output:

Output (sample-22.py)

---> Element Name: Item
---> Attribute Name: no, Value: 1000
---> Element Name: name
---> Text: Python Quick Start - 1
---> Element Name: quantity
---> Text: 5
---> Element Name: price
---> Text: 0.99
---> Element Name: Item
---> Attribute Name: no, Value: 1001
---> Element Name: name
---> Text: Python Quick Start - 2
---> Element Name: quantity
---> Text: 10
---> Element Name: price
---> Text: 1.99
---> Element Name: Item
---> Attribute Name: no, Value: 1002
---> Element Name: name
---> Text: Python Quick Start - 3
---> Element Name: quantity
---> Text: 15
---> Element Name: price
---> Text: 2.99
====================================================
---> Item No: 1000
       name: Python Quick Start - 1
---> Item No: 1001
       name: Python Quick Start - 2
---> Item No: 1002
       name: Python Quick Start - 3

References

Python Quick Notes :: Part - 1

Python Quick Notes :: Part - 2

Python Quick Notes :: Part - 3