<jupyter_start><jupyter_text>This workbook contains notes for connecting directly to a local SQLite DatabaseFor this section, you'll need to download portal_mammals.sqlite from https://figshare.com/articles/Portal_Project_Teaching_Database/1314459<jupyter_code>import sqlite3
conn = sqlite3.connect("data/portal_mammals.sqlite")
c = conn.cursor()
### reading into rows
for row in c.execute('SELECT * FROM species LIMIT 5'):
    print(row, ':', row[0])
# working with pandas dataframes
import pandas as pd
df_species = pd.read_sql_query("SELECT species.taxa, surveys.hindfoot_length \
          FROM species \
          JOIN surveys \
          ON species.species_id = surveys.species_id", conn)
df_species
df_species.set_index('species_id',inplace=True)
#list(df_species.index)
df_species.loc['AB']
c.execute("CREATE VIEW taxa_hindfoot_view AS \
          SELECT species.taxa, surveys.hindfoot_length \
          FROM species \
          JOIN surveys \
          ON species.species_id = surveys.species_id")
c.execute("CREATE TABLE taxa_hindfoot AS \
    SELECT species.taxa, surveys.hindfoot_length \
    FROM species \
    JOIN surveys \
    ON species.species_id = surveys.species_id")<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.09359605911330049, 'fraction_numerical': 0.0090311986863711, 'mean_word_length': 4.016460905349795, 'pattern_counts': {'":': 0, '<': 5, '<?xml version=': 0, '>': 5, 'https://': 1, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 1, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '1', 'dir': 'jupyter-structured-clean-dedup', 'id': '396089', 'license': 'MIT', 'n_tokens_mistral': 398, 'n_tokens_neox': 370, 'n_words': 91, 'path': 'Python-SQLite.ipynb', 'repo_name': 'geoffswc/Python-SQL-Workshop', 'size': '1218'}