Live Code, Structured Query Language Outrank your Competitors with This Comprehensive Guide on Using the IrisDatabase A well-known database called the Iris database has information on 50 different species of iris blossoms. This database is frequently used by developers as a go-to resource for testingand training because despite its tiny size, it nevertheless offers enough complexity for themost fundamental machine learning applications. This tutorial will go over using the KaggleIris database in SQLite format, which includes downloading the dataset, importing it intoPython, and performing fundamental SQL operations. Downloading the Dataset First things first, we need to download the Iris dataset in order to follow along with this lesson. The "Working with Databases" notebook can be found in your Week 8 folder, or youcan get it by going to the kaggle.com/uciml/iris/ link. There, you will discover a file with thename database.sqlite. Save this file in your Week 8 data/iris folder after downloading it. Importing the Iris Database We can now import the dataset into Python once it has been downloaded and saved in the appropriate folder. Sqlite3 Python package will be used to communicate with our database.This module makes it simple to carry out SQL operations in Python programming. Creating aconnection to the database.sqlite file in the data/iris folder will be the first step. The first thing we need to do is import the sqlite3 module in Python using the import statement. This is done with the following code: import sqlite3 After we have imported the module, we can create a connection object using the connect method. We will pass the file path to our database.sqlite file as a parameter to this method,as shown below: connection = sqlite3.connect('data/iris/database.sqlite') We now have a connection object that we will use to interact with our database. Executing SQL Operations on the Iris Database To execute SQL operations on the Iris database, we need to create a cursor object using the cursor method of our connection object. This cursor object is our interface to thedatabase, and it allows us to execute SQL queries and retrieve data from the database.
cursor = connection.cursor() Let's now do some SQL commands on the Iris database. By reading the field name from the SQLite master metadata table, we may obtain a list of all the tables recorded in thedatabase. The following code is used to accomplish this: cursor.execute("SELECT name FROM sqlite_master WHERE type='table';") The output of this execution will be turned into an iterator, which we can use to loop through and print each row: for row in cursor: print(row) In the case of the Iris database, we only have one table called "Iris." We can also retrieve data from a particular table using a SELECT query. In this case, we will use the Iris table to retrieve all rows using the following code: cursor.execute("SELECT * FROM Iris;") To fetch all the rows from the result set, we can use the fetchall method, which returns a list of tuples: data = cursor.fetchall() The data variable will now contain a list of tuples, where each tuple represents a row in the Iris table. Creating a New Table in the Iris Database Let's now use the CREATE TABLE statement to add a new table to the Iris database. The "id" and "name" columns of a new table with the name "new table" will be created in thisexample. cursor.execute("CREATE TABLE new_table (id INT, name TEXT);") We can verify that our new table has been created by executing the following SQL statement: cursor.execute("SELECT name FROM sqlite_master WHERE