CRUD operations are the bread and butter of all databases, including MySQL. Learn about the different types of operations—create, read, update, and delete. Also learn about the limitations of performance data in MySQL.
- CRUD Operations are the bread and butter of interacting with any database. And MySQL is no exception. In the challenges so far, I've done a lot of reading of the database and some creating and initially loading data. However, in this challenge we'll have to do all four CRUD operations using the text of A Midsummer Nights Dream, along with a list of its characters. Although these operations are simple to perform in MySQL, gathering the execution time is not. You've probably seen the query times in the output window during the previous challenges. And that information is useful for fine tuning single queries.
The Workbench also has a performance display that shows the number of CRUD operations being performed per second. However, neither of these options stores performance data in an accessible way. And in any case, the individual queries will be so fast their time will be rounded off to zero in the output window. For that reason, I've decided to create the necessary database and table structures in the MySQL Workbench. And then perform the queries using the Pi MySQL module in Python. I can then use the time module in Python to capture the time in detail and insert it back into the database. Using external applications is often important for MySQL.
Let's take a look at our data. I've already downloaded the data set for this challenge to my desktop. And you can see that we have the text of the play, A Midsummer Nights Dream, and a list of characters. I open the plain text in Notepad++, you can see that it immediately starts with the dialogue. Part of this challenge is numbering each line in the play, but we need to avoid numbering line that indicate which character is speaking. You can see an example down here on line 47. It's initially not clear who's speaking, but starting in line 48, we know that it's Theseus. We're going to be handling this by creating a line number column in our table.
And it's doing the auto increment flag on that column. That means that every time a row is inserted in the database it will auto-fill that column with the next available number. It's often useful to have a column that's nothing but a unique numeric value. It can be used as a kind of ID column and a core table. And then rows in other tables can be linked back to it. Kind of like we saw in challenge two with the accident index column. Now, let's get started.
- Strengths and weaknesses of MySQL
- Creating a database
- Joining data sets
- Integrating Python with MySQL
- Searching a database
- CRUD operations
- Performing calculations