<jupyter_start><jupyter_text>Access a Database with Python - Iris DatasetThe Iris dataset is a popular dataset especially in the Machine Learning community, it is a set of features of 50  Iris flowers and their classification into 3 species.It is often used to introduce classification Machine Learning algorithms.First let's download the dataset in `SQLite` format from Kaggle:Download `database.sqlite` and save it in the `data/iris` folder.  From Wikimedia, by Diliff - Own work, CC BY-SA 3.0, Link First let's check that the sqlite database is available and display an error message if the file is not available (`assert` checks if the expression is `True`, otherwise throws `AssertionError` with the error message string provided):<jupyter_code>import os
data_iris_folder_content = os.listdir("data/iris")
error_message = "Error: sqlite file not available, check instructions above to download it"
assert "database.sqlite" in data_iris_folder_content, error_message<jupyter_output><empty_output><jupyter_text>Access the Database with the sqlite3 Package We can use the `sqlite3` package from the Python standard library to connect to the `sqlite` database:<jupyter_code>import sqlite3
conn = sqlite3.connect('data/iris/database.sqlite')
cursor = conn.cursor()
type(cursor)<jupyter_output><empty_output><jupyter_text>A `sqlite3.Cursor` object is our interface to the database, mostly throught the `execute` method that allows to run any `SQL` query on our database.First of all we can get a list of all the tables saved into the database, this is done by reading the column `name` from the `sqlite_master` metadata table with:    SELECT name FROM sqlite_master    The output of the `execute` method is an iterator that can be used in a `for` loop to print the value of each row.<jupyter_code>for row in cursor.execute("SELECT name FROM sqlite_master"):
    print(row)<jupyter_output><empty_output><jupyter_text>a shortcut to directly execute the query and gather the results is the `fetchall` method:<jupyter_code>cursor.execute("SELECT name FROM sqlite_master").fetchall()<jupyter_output><empty_output><jupyter_text>**Notice**: this way of finding the available tables in a database is specific to `sqlite`, other databases like `MySQL` or `PostgreSQL` have different syntax. Then we can execute standard `SQL` query on the database, `SQL` is a language designed to interact with data stored in a relational database. It has a standard specification, therefore the commands below work on any database.If you need to connect to another database, you would use another package instead of `sqlite3`, for example:* [MySQL Connector](https://dev.mysql.com/doc/connector-python/en/) for MySQL* [Psycopg](http://initd.org/psycopg/docs/install.html) for PostgreSQL* [pymssql](http://pymssql.org/en/stable/) for Microsoft MS SQLthen you would connect to the database using specific host, port and authentication credentials but then you could execute the same exact `SQL` statements.Let's take a look for example at the first 3 rows in the Iris table:<jupyter_code>sample_data = cursor.execute("SELECT * FROM Iris LIMIT 20").fetchall()
print(type(sample_data))
sample_data
[row[0] for row in cursor.description]<jupyter_output><empty_output><jupyter_text>It is evident that the interface provided by `sqlite3` is low-level, for data exploration purposes we would like to directly import data into a more user friendly library like `pandas`.  Import data from a database to `pandas`<jupyter_code>import pandas as pd
iris_data = pd.read_sql_query("SELECT * FROM Iris", conn)
iris_data.head()
iris_data.dtypes<jupyter_output><empty_output><jupyter_text>`pandas.read_sql_query` takes a `SQL` query and a connection object and imports the data into a `DataFrame`, also keeping the same data types of the database columns. `pandas` provides a lot of the same functionality of `SQL` with a more user-friendly interface.However, `sqlite3` is extremely useful for downselecting data **before** importing them in `pandas`.For example you might have 1 TB of data in a table stored in a database on a server machine. You are interested in working on a subset of the data based on some criterion, unfortunately it would be impossible to first load data into `pandas` and then filter them, therefore we should tell the database to perform the filtering and just load into `pandas` the downsized dataset.<jupyter_code>iris_setosa_data = pd.read_sql_query("SELECT * FROM Iris WHERE Species == 'Iris-setosa'", conn)
iris_setosa_data
print(iris_setosa_data.shape)
print(iris_data.shape)<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.07161515019590771, 'fraction_numerical': 0.00391815411406182, 'mean_word_length': 5.889055472263868, 'pattern_counts': {'":': 0, '<': 29, '<?xml version=': 0, '>': 29, 'https://': 1, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 3, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '7', 'dir': 'jupyter-structured-clean-dedup', 'id': '29726', 'license': 'MIT', 'n_tokens_mistral': 1289, 'n_tokens_neox': 1252, 'n_words': 573, 'path': 'projects/NLP/.ipynb_checkpoints/Working with Databases-checkpoint.ipynb', 'repo_name': 'arman-zhumakhan/portfolio.github.io', 'size': '4594'}