<jupyter_start><jupyter_text>Databases ![Data Science Workflow](img/ds-workflow.png)  Acquire Data Common Data Sources- The Internet - Web Scraping- **Databases**- CSV- Excel- Parquet  Database- **Relational databases** models data in rows and columns in a series of tables.- Like a collection of **DataFrames/Excel sheets**.- **SQL** (often pronounced *sequel*): Structured Query Language- Non-relational databases [NoSQL](https://en.wikipedia.org/wiki/NoSQL)  SQLite database- [SQLite database](https://www.sqlite.org/index.html) software library that provides a relational database management system- Lightweight to setup, administrate, and requires low resources.  Dataset (in a SQLite database)- Dallas Police Officer-Involved Shootings- Has three tables: **incidents, officers, subjects** Other SQLite datasets- [SQLite Data Starter Packs](http://2016.padjo.org/tutorials/sqlite-data-starterpacks/)- [Iris Species (Kaggle)](https://www.kaggle.com/uciml/iris)- [European Soccer Database (Kaggle)](https://www.kaggle.com/hugomathien/soccer)  Database connector- The [sqlite3](https://docs.python.org/3/library/sqlite3.html) is an interface for SQLite databases.- No installation needed. Other database connectors for Python* [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 SQL To import and connect to a SQLite database.```Pythonimport sqlite3conn = sqlite3.connect('files/dallas-ois.sqlite')```<jupyter_code>import sqlite3
conn = sqlite3.connect('files/dallas-ois.sqlite')<jupyter_output><empty_output><jupyter_text>The interface to the datases is through the `cursor`.```Pythoncursor = conn.cursor()```The `execute` method allows to run `SQL` queries on our database.To get a list of all the tables the following query can be applied.```SQLSELECT name FROM sqlite_master WHERE type='table' ORDER BY name```The result is fetched with the `fetchall()` method.<jupyter_code>cursor = conn.cursor()
cursor.execute("SELECT name FROM sqlite_master WHERE type='table' ORDER BY name")
cursor.fetchall()<jupyter_output><empty_output><jupyter_text>To get a description of a table (SQLite specific)```SQLPRAGMA table_info(officers)```<jupyter_code>cursor.execute("PRAGMA table_info(officers)").fetchall()<jupyter_output><empty_output><jupyter_text>SQL syntaxGet all data from table```SQLSELECT * FROM table_name```Limit the response```SQLSELECT * FROM table_name LIMIT 100```Filter data```SQLSELECT * FROM table_name WHERE column_name > 1```  Import data into DataFrameExample of how to read data into a DataFrame from a SQL statement```Pythonimport pandas as pdofficers = pd.read_sql('SELECT * FROM officers', conn)```<jupyter_code>import pandas as pd
officers = pd.read_sql('SELECT * FROM officers', conn)
officers.head()<jupyter_output><empty_output><jupyter_text>SQL join syntax`(INNER) JOIN`: returns records that have matching values in both tables```SQLSELECT * FROM table_1 JOIN table_2 ON table_1.column_name_1=table_2.column_name_2````LEFT JOIN`: returns all records from the left table, and the matched records from the right table```SQLSELECT * FROM table_1 LEFT JOIN table_2 ON table_1.column_name_1=table_2.column_name_2```<jupyter_code>officers = pd.read_sql('SELECT * FROM officers JOIN incidents ON officers.case_number=incidents.case_number', conn)
officers.head()
incidents = pd.read_sql('SELECT * FROM incidents', conn)
incidents.shape
subjects = pd.read_sql('SELECT * FROM subjects', conn)
subjects.shape<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.11785912368275098, 'fraction_numerical': 0.0074875207986688855, 'mean_word_length': 6.593684210526316, 'pattern_counts': {'":': 0, '<': 21, '<?xml version=': 0, '>': 22, 'https://': 6, 'lorem ipsum': 0, 'www.': 3, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '5', 'dir': 'jupyter-structured-clean-dedup', 'id': '403603', 'license': 'MIT', 'n_tokens_mistral': 1140, 'n_tokens_neox': 1063, 'n_words': 321, 'path': 'Data Science With Python/04 - Lesson - Databases.ipynb', 'repo_name': 'shreejitverma/Data-Scientist', 'size': '3606'}