Python and MySQL
Python is a powerful machine learning and data science programming language. Using SQL database software becomes a necessity to realizing Python's full potential and luckily for us, the syntax is very easy to learn and use. In this article, I will cover the basic functions and commands used to create and update table entries. To start we will install the necessary modules using pip and import them at the top of the document. Next, we create the connection to our new database passing in properties for our host location, user name, password, and database name. Using these settings we can create a database cursor that will allow us to give the database commands.
The execute method will be our primary method for interfacing with the database and we begin by creating our customers table with the proper key/value types. After this command, we will execute a command to alter the table and add a balance column to our previously created customers table. Now we are ready to insert values into the newly created table. We start by declaring a string variable to represent the SQL query to be used in creating these entries followed by a list of tuples containing the values to be entered. notice the formatting syntax of “%s” which corresponds to the values located within the tuples we are passing into the query. Lastly, we call the execute command with two arguments, the first being the SQL query and the second being the values to be added followed by the commit method to finalize our entries into the table.
In the next section of the code, we will select the values we just entered into the database and print each row. To start we will create the SQL query to select all of the columns in the customers table we added in the previous section. to retrieve these results we will call the fetchall method on our cursor. We will assign this value to a variable of results followed by a for-in loop to print each of the entires. We will follow this with the insertion of a single row into our table by passing in a single tuple, as opposed to a list of tuples as we did before. We finalize this again with the commit method.
In the next section, I will demonstrate how to select a single entry from the table we have created as well as create a new table of services. First, we write the select query where are specifying we only want customers whose address is “French quarter”. After executing this query on our cursor we follow it with a fetchone method and assign its return value to a variable to be printed in the console. After this, we will create a new services table which we will relationally add to our existing table. Similar to the method used to create the customers table we will pass in a list of tuples containing the values we wish to add to the table along with a string query. To finalize this we pass these variables into the execute function to insert the new values into the newly created services table.
In the last section, we will create the necessary foreign key columns on our customers table and make the necessary relational connections between the two tables. To start this process we will alter our customers table to add the column of service_id to our customers table. This column will reference the id column of the services we created in the last section. Next, we will create the update query to set the service_id of the customer row with the appropriate id passed in using a tuple containing the id and customer name.
Now for the most complex part of our exercise. We will construct the query to pull the relational data of our customers and their services. The query starts with specifying that we would like to select the customer name and the service name. We are then specifying that from the customers table we want to join the information from our services table based on the related service_id stored on the customer row. After executing this query we will assign the return value of the fetchall method of a variable. This variable will finally be used to print our results to the console.