Python MySQL Integration: Scripting for Data Management with MySQL

This assignment provides a comprehensive overview of integrating MySQL databases with Python scripts. It begins by establishing a connection to a MySQL database using the `mysql.connector` library, demonstrating how to authenticate and select a database. The script includes functions to execute SQL queries, such as selecting all records from a payments table filtered by month, and updating records to deduct a percentage from payment amounts. The assignment also covers how to format and display query results in a readable manner using Python's string formatting capabilities. Furthermore, it explains how to implement table joins in Python using SQL queries, providing an example of joining the products and productlines tables to fetch related data. The assignment concludes by highlighting MySQL's data replication and security features, as well as Python's versatility in data processing, with references to relevant online resources.

Running head: Python MYSQL integration 1
Assignment
Student
Course
Instructor
Date

Python MYSQL integration 2
Integrating MySQL Table Data with a Python Script
Introduction
A database is a systematic collection of data which supports storage and manipulation of data
and it is also an organized collection which is stored electronically in a computer system. A
database is usually controlled by a database management System. Relational database systems
stores this data in the form of rows and columns using a number of tables. This data can be very
easily accessed, managed, modified, updated and controlled using these database systems.
MYSQL is an open source database which is used by a number of web applications. It basically
uses a client-server model and it provides a lot of security features which helps in keeping the
data secure. MYSQL can be used on a number of platforms and a number of languages can be
used to integrate a web application with MYSQL. (Oracle, 2019)

Python MYSQL integration 3
Steps needed to integrate Python and MySQL
Program Description:
import mysql.connector
mydb = mysql.connector.connect(
host="localhost",
user="root",
passwd="Pugi@123",
database="classicmodels"
)
def printResult(result):
print('{:<20s}{:<15s}{:<20s}{:<10s}'.format('Customer Number', 'Check Number',
'Payment Date','Amount'))
for x in result:
print('{:<20d}{:<15s}{:<20s}{:<10.2f}'.format(x[0], x[1], str(x[2]),x[3]))
Here, the import mysql.connector will interact with the database. It will make a connection to the
database using this python library and the user will pass their credentials and the name of the
database. A connection will be made and the reference will be stored in the mydb variable.This

Python MYSQL integration 4
utility method printResult will print the output rows in a formatted way on the console. A header
row will be printed at the top followed by the final result received after query execution. The
format command of python is used for formatting the output and printing it in a more readable
way.
crsr = mydb.cursor()
crsr.execute("SELECT * FROM payments where MONTH(paymentDate)=12")
result = crsr.fetchall()
printResult(result)
Here, a cursor crsr is created to the database connection and it will execute the query to fetch all
the records from the payments table with the month as December. The result fetched from the
database is stored in the result variable and then the printResult method is used to print the result
on the console. This printResult method is being used to display the results in a formatted
manner. (W3Schools, 2019)
updateStatement="UPDATE payments SET amount = amount-(amount*0.01) where
MONTH(paymentDate)=12"
crsr = mydb.cursor()
crsr.execute(updateStatement)
mydb.commit()

Python MYSQL integration 5
print(crsr.rowcount, "\nRecords updated")
Here, the update statement is being used to deduct one per cent from the amounts paid for
purchases made in December. After committing the transaction, the number of rows updated on
the execution of this query is printed on the console as the result and the commit method will
commit the changes executed in the previous line. (PyNative, 2019)
crsr.execute("SELECT * FROM payments where MONTH(paymentDate)=12")
result = crsr.fetchall()
print("---------------------------")
print("| Payments after Rebate: |")
print("---------------------------")
printResult(result)
Here, the query to fetch all the records from the payments table with month as december is
executed and the the updated rows are printed on the console using the printResult utility
method. The crsr.execute and crsr.fetchall methods are again used for executing the query and
fetching the results.
Question. provide details on how you implement a table join in a Python script

Python MYSQL integration 6
Ans. A table join is a simple SQL query which is used to fetch the data which is stored in a
number of tables. A table join is implemented using a MySQL query in python. The crsr.execute
statement is used to execute it. This query will return all the results that are fetched from the
database after executing the query with the table joins. Typically, those tables are joined which
have foreign key relationships among them.
Eg. This query will fetch the productCode, productName and textDescription from the
description. They have productCode and productDescription in the products table and the
textDescription of the productLines in the productlines table. (Javatpoint, 2019)
Python Script:
import mysql.connector
mydb = mysql.connector.connect(
host="localhost",
user="root",
passwd="Pugi@123",
database="classicmodels"
)

Python MYSQL integration 7
query= 'SELECT productCode, productName, textDescription FROM products t1 INNER JOIN
productlines t2 ON t1.productline = t2.productline'
crsr = mydb.cursor()
crsr.execute(query)
result = crsr.fetchall()
for x in result:
print(x)
Test Screenshot:
Output Screenshot:

Python MYSQL integration 8
Conclusion
It can be concluded that MYSQL has the capability to replicate data partitioning and provide
better durability and performance. Also, it is observed that the SQL queries instantiate the
database using the provided SQL dump file and encrypted password system and access
privileges are being used by this dump file for the sake of security. Furthermore, python has the
capability to connect to MYSQL and execute various Data definition and data manipulation
queries. Lastly, Python provides various other capabilities along with MYSQL query execution
and it can be further utilized to process the data fetched from MYSQL.
References

Python MYSQL integration 9
MySQL | The Most Popular Open-Source Database | Oracle. (2019). Retrieved from
https://www.oracle.com/mysql/
Python MySQL. (2019). Retrieved from https://www.w3schools.com/python/python_mysql_getstarted.asp
Python MySQL Join Operation - Javatpoint. (2019). Retrieved from https://www.javatpoint.com/python-
mysql-join-operation
Python MySQL Select From. (2019). Retrieved from
https://www.w3schools.com/python/python_mysql_select.asp
Python MySQL Update Database Table [Guide]. (2019). Retrieved from https://pynative.com/python-
mysql-update-data/