classicmodels - database

1. Retailer of scale models of classic cars database
2. Contains following business data
   1. Customers
   2. Products
   3. Sales Orders
   4. Sales Order Line Items

MySQL Sample Database

how to load sample database into MySQL database server tutorial

1. Test

    USE classicmodels;
    SELECT * FROM customers;
   

MySQL Sample Database Schema

1. Customers: stores customer's data
2. Products: stores a list of scale model cars
3. ProductLines: stores a list of product line categories
4. Orders: stores sales orders placed by customers
5. OrderDetails: stores sales order line items for each sales order
6. Payments: stores payments made to customers based on their accounts
7. Employees: stores all employee information as well as the organization structure such as who reports to whom
8. Offices: stores sales office data

1. SELECT
2. INSERT
3. UPDATE
4. DELETE

1. MySQL connector/Python - standard database driver
   1. Supports almost all features of v 5.7
   2. Allows conversion of parameter values between Python and MySQL datatypes
      1. Python datetime and MySQL DATETIME
   3. Supports MySQL extensions to standard SQL (LIMIT clause)
   4. Allows compression of data stream between Python and MySQL database server using protocol compression.
      1. Supports TCP/IP socket and secure TCP/IP connection using SSL
   5. It is API implemented using pure Python
      1. No need for client library or module but only standard library
2. Supported versions of Python 2.6, 2.7, and 3.3

1. Install

Open Python3 command line

Run the following:

 import mysql.connector

 mysql.connector.connect(host='localhost', database='mysql', user='root', password='')

1. Output: <mysql.connector.connection.MySQLConnection object at xxxxx>

1. In workbench

2. MySQL client

    CREATE DATABASE python_mysql;
   

python_mysql_connect1.py

 import mysql.connector
 from mysql.connector import Error

 def connect():
 	""" Connect to MySQL database """
 	try:
 		conn = mysql.connector.connect(host='localhost', database='python_mysql', user='root', password='secret')
 		if conn.is_connected():
 			print('Connected to MySQL database')
 	except Error as e:
 		print(e)
 	finally:
 		conn.close()

 if __name__ == '__main__':
 	connect()

1. mysql.connector and Error objects are imported from MySQL Connector/Python package
2. connect() accepts parameters host, database, user, password.
   1. Establishes connection to python_mysql database and returns MySQLConnection object
3. is_connected() is used to check connection to MySQL database
4. Error exception is raised if eg:
   1. database server is not available
   2. database does not exist
   3. invalid user name or password
5. close() method of MySQLConnection closes database connection

Testing:

 python python_mysql_connect1.py

A database configuration file config.ini

 [mysql]
 host = localhost
 database = python_mysql
 user = root
 password = 

New module python_mysql_dbconfig.py reads database configuration from config.ini and returns dictionary

 from ConfigParser import ConfigParser

 def read_db_config(filename='config.ini', section='mysql'):
 	""" Read database configuration file and return a dictionary object
 	:param filename: name of the configuration file
 	:param section: section of database configuration
 	:return: a dictionary of database parameters
 	"""
 	# creation of parser and read ini configuration file
 	parser = ConfigParser()
 	parser.read(filename)
 	
 	# get section, default to mysql
 	db = {}
 	if parser.has_section(section):
 		items = parser.items(section)
 		for item in items:
 			db[item[0]] = item[1]
 	else:
 		raise Exception('{0} not found in the {1} file'.format(section, filename))
 	return db

1. ConfigureParser - Used to read configuration file

2. Testing:

    from python_mysql_dbconfig import read_db_config
    read_db_config()
   

New module python_mysql_connect2.py which uses MySQLConnection object to connect to python_mysql

 from mysql.connector import MySQLConnection, Error
 from python_mydql_dbconfig import read_db_config

 def connect():
 	""" Connect to MySQL database """

 	db_config = read_db_config()

 	try:
 		print('Connecting to MySQL database...')
 		conn = MySQLConnection(**db_config)

 		if conn.is_connected():
 			print('connection established.')
 		else:
 			print('connection failed.')

 	except Error as error:
 		print(error)

 	finally:
 		conn.close()
 		print('Connection closed.')

 if __name__ == '__main__':
 	connect()

1. MySQLConnection, Error are imported from MySQL Connector/Python package
2. read_db_config is imported from python_mysql_dbconfig module
3. connect() is used to read database configuration

Testing:

 python python_mysql_connect2.py

1. fetchone()
2. fetchmany()
3. fetchall()

1. Connect to MySQL database and you get MySQLConnection object
2. Instantiate MySQLCursor object from MySQLConnection object
3. Use cursor to execute a query by calling its execute() method
4. Use fetchone(), fetchmany() or fetchall() method to fetch data from result set
5. Close the cursor as well as database connection by calling close() method of corresponding object

Example:

 from mysql.connector import MySQLConnection, Error
 from python_mysql_dbconfig import read_db_config

 def query_with_fetchone():
 	try:
 		dbconfig = read_db_config()
 		conn = MySQLConnection(**dbconfig)
 		cursor = conn.cursor()
 		cursor.execute('SELECT * FROM books')

 		row = cursor.fetchone()

 		while row is not None:
 			print(row)
 			row = cursor.fetchone()

 	except Error as e:
 		print(e)

 	finally:
 		cursor.close()
 		conn.close()

 if __name__ == '__main__':
 	query_with_getchone()

1. Connected to db using MySQLConnection object
2. Instantiated new MySQLCursor object from MySQLConnection object
3. Executed query that selects all rows from books table
4. Called fetchone() to fetch next row in result set
5. Closed connection object by invoking close() method of corresponding object

1. Fetches all rows from database

    from mysql.connector import MySQLConnection
    from python_mysql_dbconfig import read_db_config
   
    def query_with_fetchall():
    	try:
    		dbconfig = read_db_config()
    		conn = MySQLConnection(**dbconfig)
    		cursor = conn.cursor()
    		cursor.execute('SELECT * FROM books')
    		rows = cursor.fetchAll()
   
    		print('Total rows(s):', cursor.rowcount)
    		for row in rows:
    			print(row)
   
    	except Error as e:
    		print(e)
   
    	finally:
    		cursor.close()
    		conn.close()
   
    if __name__ == '__main__':
    	query_with_fetchall()
   

   1. All rows are fetched into memory

fetchmany() returns next number of rows (n) of result set - allows balance between time and space

 def iter_row(cursor, size=10):
 	while True:
 		rows = cursor.fetchmany(size)
 		if not rows:
 			break
 		for row in rows:
 			yield row

 def query_with_fetchmany():
 	try:
 		dbconfig = read_db_config()
 		conn = MySQLConnection(**dbconfig)
 		cursor = conn.cursor()

 		cursor.execute('SELECT * FROM books')

 		for row in iter_row(cursor, 10):
 			print(row)

 	except Error as e:
 		print(e)

 	finally:
 		cursor.close()
 		conn.close()

1. Connect to MySQL database server by instantiating MySQLConnection object
2. Initiate MySQLCursor object from MySQLConnection object
3. Execute INSERT statement to insert data into intended table
4. Close database connection

Example: New book into books table

 from mysql.connector import MySQLConnection, Error
 from python_mysql_dbconfig import read_db_config

 def insert_book(title, isbn):
 	query = 'INSERT INTO books (title, isbn) ' \
 			'VALUES (%s, %s)'
 	args = (title, isbn)

 	try:
 		db_config = read_db_config()
 		conn = MySQLConnection(**db_config)

 		cursor = conn.cursor()
 		cursor.execute(query, args)

 		if cursor.lastrowid:
 			print('last insert id', cursor.lastrowid)
 		else:
 			print('last insert id not found')

 		conn.commit()
 	except Error as error:
 		print(error)

 	finally:
 		cursor.close()
 		conn.close()

 def main():
 	insert_book('A Sudden Light', '9781439187036')

 if __name__ == '__main__':
 	main()

1. insert_book() accepts title and isbn
   1. We prepare INSERT statement (query) and data (args)
      1. Data is passed as a tuple
2. commit() - called explicitly to make changes to database
3. lastrowid - gets the last insert id of AUTO_INCREMENT column
4. Call insert_book() and pass title and isbn