SQL for Data Science

SQLITE Setup

There is nothing to download to setup SQLITE This SQLite Viewer VS Code extension will be helpful to explore the database

Downloads

Download this sqlite db file Save it in the same place as the .py or .qmd file created in the next step

Test Your Setup

Copy the code below and test it in a .py file. If everything works you are all set

import pandas as pd 
import numpy as np
import sqlite3

# %%
# careful to list your path to the file or save it in the same place as your .qmd or .py file
sqlite_file = 'lahmansbaseballdb.sqlite'
con = sqlite3.connect(sqlite_file)

q = 'SELECT * FROM allstarfull LIMIT 5'
results = pd.read_sql_query(q,con)

results

You can see the list of tables available in the database

q = '''
    SELECT * 
    FROM sqlite_master 
    WHERE type='table'
    '''
table = pd.read_sql_query(q,con)
table.filter(['name'])