Creating Pokemon Database with MySQL & Python
Relational databases are used to store structured data in an organized manner. In this case study, we could perform the 4 most fundamental operations in a relational database: Create, Read, Update and Delete (CRUD). Then, we will use Python to communicate with the MySQL database management system.
Data types could affect how users can interact with the data. In this case, we would be using INT, VARCHAR(size), and FLOAT to create a simple database for the top 5 dragon-type pokemon in Pokemon GO (as shown below). In addition, you may notice that we have the ‘ID’ column as the primary key to uniquely identify each row of data and ensure that there are no duplicate records in a table.
First, enter the following commands in the query tab to create a database called “pokemon”, connect to the newly created database, and create a new table in that database. Click the lightning icon in the SQL Query toolbar to execute it. Please note that all SQL commands end with a semicolon. 🎶
After clicking on the refresh icon on the top right-hand side of the “SCHEMAS” panel, you should be able to see the “dragon” table under the “pokemon” database.
Now, let’s insert the data into the empty table! 😃
We have decided to keep the top 3 dragon pokemon. Oops… We entered the wrong name for the first pokemon.😥Besides, the wrong spelling was found in the “Fast move” column. It should be “Dragon Breathe” instead of “Dragon Breath”. Let’s make some updates. 🫧
These are examples of how we perform CRUD operations directly in the MYSQL workbench. 🌱
Alternatively, we can use Python to insert these 3 pokemon in the database. First, we need to open a new file in IDE where we can write code. Next, we will need to import the necessary module, create a series of functions, and create a menu function so that the users can use the program to interact with the database.
Please note that you need to replace the “ngpooiling” in the code above with the password that you entered for MySQL during setup. We are still using the “pokemon” database that we created earlier. However, we are creating a new table called “dragon_pokemon” so that we can differentiate it from the “dragon” table that we created earlier. After you successfully ran the code above, you should see the following menu in your IDE.
Type “1” to insert the information about the top 3 dragon pokemon. Once done, enter “2” into your program to view the pokemon.🐉
To verify whether the top 3 pokemon have been entered correctly in the “dragon_pokemon” table in the “pokemon” database, we can enter the following highlighted command in the query tab of MySQL Workbench. You should be able to see the top 3 pokemon in the result grid as shown below.
These are the steps we can take to perform CRUD operations on a MySQL Table using Python.
I hope you found this article useful. The complete code could be found in my GitHub repository. Happy learning! 😎
