<jupyter_start><jupyter_text>SQLAlchemy, Sqlite, and Dates  Setup<jupyter_code># Python SQL toolkit and Object Relational Mapper
import sqlalchemy
from sqlalchemy.ext.automap import automap_base
from sqlalchemy.orm import Session
from sqlalchemy import create_engine, inspect, func
engine = create_engine("sqlite:///../Resources/dow.sqlite", echo=False)<jupyter_output><empty_output><jupyter_text>Explore Database<jupyter_code>inspector = inspect(engine)
inspector.get_table_names()
# Get a list of column names and types
columns = inspector.get_columns('dow')
for c in columns:
    print(c['name'], c["type"])
# columns
engine.execute('SELECT * FROM dow LIMIT 5').fetchall()<jupyter_output><empty_output><jupyter_text>Reflect and query dates<jupyter_code># Reflect Database into ORM class
Base = automap_base()
Base.prepare(engine, reflect=True)
Dow = Base.classes.dow
session = Session(engine)<jupyter_output><empty_output><jupyter_text>How many dates do we have?<jupyter_code># Total dates
session.query(func.count(Dow.date)).all()<jupyter_output><empty_output><jupyter_text>What is the earliest date?<jupyter_code># Earliest Date
session.query(Dow.date).order_by(Dow.date).first()<jupyter_output><empty_output><jupyter_text>What is the latest date?<jupyter_code># Latest Date
session.query(Dow.date).order_by(Dow.date.desc()).first()<jupyter_output><empty_output><jupyter_text>Find all of the dates great than `2011-03-01`<jupyter_code>session.query(Dow.date).\
    filter(Dow.date > '2011-03-01').\
    order_by(Dow.date).all()<jupyter_output><empty_output><jupyter_text>Important Note! Sqlite does not support a date column type, but SQLAlchemy will allow you to work with dates in the iso format. [sqlite dates](http://docs.sqlalchemy.org/en/latest/dialects/sqlite.html)  Quick Review of DateTime<jupyter_code>import datetime as dt
# Print a date object and a datetime object 
print(dt.date.today())
print(dt.date(2017, 1 ,31))
print(dt.datetime.utcnow())
print(dt.datetime(2017, 1, 31))<jupyter_output>2018-09-27 22:27:10.421112
2017-01-31 00:00:00<jupyter_text>Calculate a time delta<jupyter_code># date 1 week ago from today
week_ago = dt.date.today() - dt.timedelta(days=7)<jupyter_output><empty_output><jupyter_text>Query for the Dow closing price 1 week before `2011-04-08` using the datetime library<jupyter_code># Query for the Dow closing price for `CSCO` 1 week before `2011-04-08` using the datetime library
query_date = dt.date(2011, 4, 8) - dt.timedelta(days=7)
print("Query Date: ", query_date)
session.query(Dow.date, Dow.close_price).\
    filter(Dow.stock == 'CSCO').\
    filter(Dow.date == query_date).all()
# Parse out just the day from the datetime object
dt.date.today().strftime("%d")<jupyter_output><empty_output><jupyter_text>Putting it all together<jupyter_code># Query for all dates matching the 
# following date string in the format `%d`
date_str = "14"
session.query(Dow.date).\
    filter(func.strftime("%d", Dow.date) == date_str).all()<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.12845257903494176, 'fraction_numerical': 0.031281198003327786, 'mean_word_length': 6.727506426735219, 'pattern_counts': {'":': 0, '<': 44, '<?xml version=': 0, '>': 45, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, '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': '11', 'dir': 'jupyter-structured-clean-dedup', 'id': '309932', 'license': 'MIT', 'n_tokens_mistral': 1145, 'n_tokens_neox': 1059, 'n_words': 241, 'path': 'Dates.ipynb', 'repo_name': 'christineton/HTML_CSS_JavaScript', 'size': '3005'}