How to Integrate MySQL Table Data with a Python Script

This week's assignment involved writing Python scripts to integrate MySQL with Python. The task was a bit challenging, but through research and a bit of practice, I was able to grasp the new concepts I learnt for this week. I was able to understand how Python integrates with MySQL to create tables and manage records that can be retrieved and modified using Python scripts. I followed the step by step procedure required to integrate MySQL with Python, which initially starts with connecting to the MySQL database. I was interested in the whole process of setting up MySQL and the installation process, which allowed me to learn more about my computer, including new terminal commands in the command line. 

The first step in the integration of MySQL involved creating my python scripts for the database connector where first I had to import the connector using the import mysql.connector command. The second step that followed involved writing the scripts for connecting to the database, then creating the cursor object. The next process involved creating the database called PythonDatabase in MySQL that i would use for data manipulation. To confirm that my database was successfully created, I wrote another script to get the list of all available databases. The scripts for connecting to MySQL and creating the database are as shown below. 

import mysql.connector 

mydb = mysql.connector.connect( 

host="localhost", 

user="root", 

passwd="admin" 

) 

mycursor = mydb.cursor() 

mycursor.execute("CREATE DATABASE PythonDatabase") 

mycursor.execute("SHOW DATABASES") 

for x in mycursor: 

print(x) 

After creating my Python database, I created further scripts to create two tables called table1 and table2. The script for creating the new tables is as follows 

mycursor.execute("CREATE TABLE client (id int(20) not null primary keyaddress, name VARCHAR(255), address VARCHAR(255), productid int(20) not null)") 

mycursor.execute("CREATE TABLE product (id int(20) not null primary keyaddress, name VARCHAR(255))") 

After creating my tables, I populated them with a bit of data and included a script to show that the data was inserted successfully using the following scripts. 

sql = "INSERT INTO client (name, address, productid) VALUES (%s, %s)" 

val = ("Paul", "CH", 1) 

mycursor.execute(sql, val) 

mydb.commit() 

print(mycursor.rowcount, "record inserted.") 

I did the same for the next table as shown below. 

sql = "INSERT INTO products (name, id) VALUES (%s, %s)" 

val = ("Milk", 1) 

mycursor.execute(sql, val) 

mydb.commit() 

print(mycursor.rowcount, "record inserted.") 

After creating the scripts for creating the database, tables and populating the data in the tables, I was able to learn how Python scripts are powerful and effective in integrating with the MySQL database. I learnt how to write SQL scripts and combine them with Python scripts to achieve the database manipulation. Python provides a powerful library to work with MySQL and execute queries to retrieve and modify data in databases. 

Implement a Table Join 

Once my database and tables were created, the next step involved writing Python scripts that would implement a table join between the two tables I had created. This seemed like a challenging task because I was implementing a new concept. I was able to learn that a table join involved combining the rows from two or more tables in a database based on a related column. The related filed used to join the rows between two tables is usually the unique identifier field which is also usually the primary key field. In my case, the two columns that I used were the id in the client and products tables because they were the unique fields. The Python script to join the two tables is as shown below. 

import mysql.connector 

mydb = mysql.connector.connect( 

host="localhost", 

user="root", 

passwd="admin" 

) 

mycursor = mydb.cursor() 

sql = "SELECT \ 

clients.name AS client, \ 

products.name AS order \ 

FROM clients \ 

INNER JOIN products ON clients.productid = products.id" 

mycursor.execute(sql) 

myresult = mycursor.fetchall() 

After implementing the join scripts in Python, I realized that the join statement was used to reveal fields related to the two tables which can be used for comparison. For example, in my tables, I implemented the join scripts to identify specific products for each client. My experience interacting with Python and integrating with MySQL was exciting, and I learnt quite a lot of new concept related to databases and Python.