<jupyter_start><jupyter_text>Built-in read/write functions<jupyter_code># create our text file to read
with open('textfile.txt', mode='w') as f:
    f.writelines(['This is a text file.\n', 'Now you can read it!'])
# open a file and read the contents
file = open(file='textfile.txt', mode='r')
text = file.readlines()
text  # remember if our last line is a variable, jupyter notebook will print out that variable
# this is the file object
file
# the file is 'open' until we close it.
file.close()
# the 'with' context will automatically close the file once we are out of the 'with' section
with open(file='textfile.txt', mode='r') as f:
    text = f.readlines()

text
# the read function reads the entire file at once
with open(file='textfile.txt', mode='r') as f:
    text = f.read()

print(text)
# remember we can take a subset of strings like this
text[:10]
# writing to a file
# open the file in a text editor to see the results, or read it with Python
with open(file='writetest.txt', mode='w') as f:
    f.write('testing writing out')
# writing a list of text to a file
# open the file in a text editor or read it through Python to see the results
text_lines = ['This is text for testing writing.', 'Now you can write to a file!']
with open(file='writetest2.txt', mode='w') as f:
    f.writelines(text_lines)<jupyter_output><empty_output><jupyter_text>JSON<jupyter_code>import json
data_dictionary = {'books': 12, 'articles': 100, 'subjects': ['math', 'programming', 'data science']}
json_string = json.dumps(data_dictionary)
json_string
data_dict = json.loads(json_string)
data_dict
with open('reading.json', 'w') as f:
    json.dump(data_dictionary, f)
with open('reading.json') as f:
    loaded_data = json.load(f)
loaded_data<jupyter_output><empty_output><jupyter_text>Credentials in a .py file<jupyter_code>import credentials as creds
print(f'username: {creds.username}\npassword: {creds.password}')<jupyter_output>username: datasci
password: <PASSWORD><jupyter_text>The pickle librarypickle can be used for saving and loading raw Python objects.<jupyter_code>import pickle as pk

data_dictionary = {'books': 12, 'articles': 100, 'subjects': ['math', 'programming', 'data science']}

with open('readings.pk', 'wb') as f:
    pk.dump(data_dictionary, f)
with open('readings.pk', 'rb') as f:
    data = pk.load(f)

print(data)<jupyter_output>{'books': 12, 'articles': 100, 'subjects': ['math', 'programming', 'data science']}<jupyter_text>The joblib libraryWe can also save and load data with joblib. First be sure to install it with `conda install -c conda-forge joblib -y` if you don't already have it installed. Joblib has extra features beyond pickle, such as compression, automatic opening and closing of files, and features to make saving/loading specific data (numpy arrays) faster. Pickle is often faster than joblib, except in special situations (which is why we didn't cover it in the book). This extra section is for your extra knowledge.<jupyter_code>import joblib

joblib.dump(value=data_dictionary, filename='readings.job', compress=True)
data = joblib.load(filename='readings.job')
data<jupyter_output><empty_output><jupyter_text>We can time how long something takes with the magic command `%%timeit` in Jupyter Notebooks. Note that pickle is faster for saving and reading this dictionary.<jupyter_code>%%timeit
joblib.dump(value=data_dictionary, filename='readings.job', compress=True)
%%timeit
with open('readings.pk', 'wb') as f:
    pk.dump(data_dictionary, f)
%%timeit
data = joblib.load(filename='readings.job')
%%timeit
with open('readings.pk', 'rb') as f:
    data = pk.load(f)<jupyter_output>65.4 µs ± 3.36 µs per loop (mean ± std. dev. of 7 runs, 10000 loops each)<jupyter_text>We can see that for normal dictionaries, pickle is much faster for saving and loading than joblib.  sqlite3 When we install Python, we also install SQLite3. We can use it from within Python, but also from the command line. If we open a terminal and type `sqlite3`, it takes us to the SQLite shell. From the shell, we can run any SQLite command. For example, if we are in the directory with the chinook.db file from this GitHub repo (within the same folder that contains this notebook), we can type `.open chinook.db` to load the database. Then we can see the tables within the database with `.tables`. We can also connect to the database through Python and run commands as show below.<jupyter_code>import sqlite3
connection = sqlite3.connect('chinook.db')
cursor = connection.cursor()
# list out tables -- .table does not work from Python sqlite3
cursor.execute("SELECT name FROM sqlite_master WHERE type='table';")
cursor.fetchall()
# get table information (column names, types, settings)
cursor.execute('PRAGMA table_info(artists);')
cursor.fetchall()
# SELECT the first 5 rows of artists
cursor.execute('SELECT * FROM artists LIMIT 5;')
cursor.fetchall()
# especially for longer queries, it helps to format them like this, with each SQL command on a separate line
query = """
SELECT *
FROM artists
LIMIT 5;
"""
cursor.execute(query)
cursor.fetchall()
# get table information (column names, types, settings)
cursor.execute('PRAGMA table_info(invoices);')
cursor.fetchall()
# save table column names in a list
cursor.execute('PRAGMA table_info(invoices);')
results = cursor.fetchall()
column_names = [r[1] for r in results]
column_names
cursor.execute('SELECT * FROM invoices LIMIT 5;')
cursor.fetchall()
# ORDER BY
cursor.execute('SELECT Total, InvoiceDate from invoices ORDER BY Total DESC LIMIT 5;')
cursor.fetchall()
# WHERE statement
cursor.execute('SELECT Total, BillingCountry from invoices WHERE BillingCountry == "Canada" LIMIT 5;')
cursor.fetchall()
# WHERE using an inserted argument
cursor.execute('SELECT Total, BillingCountry from invoices WHERE BillingCountry == ? LIMIT 5;', ('Canada',))
cursor.fetchall()
# LIKE command
cursor.execute('SELECT Total, BillingCountry from invoices WHERE BillingCountry LIKE "%can%" LIMIT 5;')
cursor.fetchall()
# GROUP BY statement
cursor.execute('SELECT SUM(Total), BillingCountry from invoices GROUP BY BillingCountry ORDER BY SUM(Total) DESC LIMIT 5;')
cursor.fetchall()
# examine column names for invoice_items table
cursor.execute('PRAGMA table_info(invoice_items);')
cursor.fetchall()
# examine a sample of the data
cursor.execute('SELECT * FROM invoice_items LIMIT 5;')
cursor.fetchall()
# aliases can be used to rename columns and tables
# according to some SQL style guides, it's not best practice to alias a table
cursor.execute('SELECT i.TrackID as tid, i.UnitPrice as up FROM invoice_items as i LIMIT 5;')
cursor.fetchall()
# DISTINCT
cursor.execute('SELECT DISTINCT UnitPrice FROM invoice_items;')
cursor.fetchall()
# JOIN
# get tracks that were purchased and combine with the country
query = """
SELECT invoices.BillingCountry, invoice_items.TrackId
FROM invoices
JOIN invoice_items
ON invoices.InvoiceId = invoice_items.InvoiceId
LIMIT 5;
"""
cursor.execute(query)
cursor.fetchall()
# get number of purchased tracks for each track by country, sorted by the top-most purchased
query = """
SELECT invoice_items.TrackId, COUNT(invoice_items.TrackId), invoices.BillingCountry
FROM invoices
JOIN invoice_items
ON invoices.InvoiceId = invoice_items.InvoiceId
GROUP BY invoices.BillingCountry
ORDER BY COUNT(invoice_items.TrackId) DESC
LIMIT 5;
"""
cursor.execute(query)
cursor.fetchall()
# multiple JOINs
query = """
SELECT tracks.Name, COUNT(invoice_items.TrackId), invoices.BillingCountry
FROM invoices
JOIN invoice_items
ON invoices.InvoiceId = invoice_items.InvoiceId
JOIN tracks
ON tracks.TrackId = invoice_items.TrackId
GROUP BY invoices.BillingCountry
ORDER BY COUNT(invoice_items.TrackId) DESC
LIMIT 5;
"""
cursor.execute(query)
cursor.fetchall()
# this same command as above can also be done with a subquery like this, but is easier with multiple joins
query = """
SELECT tracks.Name, invoice_merged.track_count, invoice_merged.BillingCountry
FROM
    (SELECT ii.TrackId, COUNT(ii.TrackId) as track_count, i.BillingCountry
    FROM invoices as i
    JOIN invoice_items as ii
    ON i.InvoiceId = ii.InvoiceId
    GROUP BY BillingCountry) as invoice_merged
JOIN tracks
ON tracks.TrackId = invoice_merged.TrackId
ORDER BY track_count DESC
LIMIT 5;
"""
cursor.execute(query)
cursor.fetchall()
# be sure to close the connection when done
connection.close()<jupyter_output><empty_output><jupyter_text>Storing data in a sqlite3 database<jupyter_code># hypothetical book sales data
book_data = [('12-1-2020', 'Practical Data Science With Python', 19.99, 1),
       ('12-15-2020', 'Python Machine Learning', 27.99, 1),
       ('12-17-2020', 'Machine Learning For Algorithmic Trading', 34.99, 1)]
# CREATE and INSERT
connection = sqlite3.connect('book_sales.db')
cursor = connection.cursor()
# Create table
cursor.execute('''CREATE TABLE IF NOT EXISTS book_sales
             (date text, book_title text, price real, quantity real)''')
# the table is now there
cursor.execute("SELECT name FROM sqlite_master WHERE type='table';")
cursor.fetchall()
# Insert a row of data
cursor.execute("INSERT INTO book_sales VALUES (?, ?, ?, ?)", book_data[0])
cursor.execute('SELECT * FROM book_sales ;')
cursor.fetchall()
# Save the changes with .commit()
# Without this line, the inserted data will not be saved in the database after we close the connection
connection.commit()
# insert several records at a time
cursor.executemany('INSERT INTO book_sales VALUES (?, ?, ?, ?)', book_data[1:])
# don't forget to save the changes
connection.commit()
cursor.execute('SELECT * FROM book_sales;')
cursor.fetchall()
connection.close()<jupyter_output><empty_output><jupyter_text>SQLAlchemy<jupyter_code>from sqlalchemy import create_engine
engine = create_engine('sqlite:///book_sales.db')
connection = engine.connect()
result = connection.execute("select * from book_sales")
result
list(result)
for row in result:
    print(row['date'])
result = connection.execute("select * from book_sales")
for row in result:
    print(row['date'])
# be sure to close the connection when finished
connection.close()
# we can also use the with clause to automatically close the connection
with engine.connect() as connection:
    result = connection.execute("select * from book_sales")
    for row in result:
        print(row)
connection.closed
# the connection is closed from the 'with' statement, so we can't use it
# notice in the middle and at the bottom of the error, it says 'This Connection is closed'
result = connection.execute("select * from book_sales")
from sqlalchemy import MetaData, Table

metadata = MetaData(engine)
book_sales = Table('book_sales', metadata, autoload=True)
conn = engine.connect()
res = conn.execute(book_sales.select())
for r in res:
    print(r)
ins = book_sales.insert().values(book_title='machine learining', price='10.99')
conn.execute(ins)
res = conn.execute(book_sales.select())
for r in res:
    print(r)<jupyter_output>('12-1-2020', 'Practical Data Science With Python', 19.99, 1.0)
('12-15-2020', 'Python Machine Learning', 27.99, 1.0)
('12-17-2020', 'Machine Learning For Algorithmic Trading', 34.99, 1.0)
(None, 'machine learining', 10.99, None)
{'fraction_non_alphanumeric': 0.10712680866361103, 'fraction_numerical': 0.012851622180282197, 'mean_word_length': 5.48862973760933, 'pattern_counts': {'":': 0, '<': 34, '<?xml version=': 0, '>': 34, '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': '9', 'dir': 'jupyter-structured-clean-dedup', 'id': '491383', 'license': 'MIT', 'n_tokens_mistral': 3552, 'n_tokens_neox': 3325, 'n_words': 1249, 'path': '3-Chapter-3/Chapter_3.ipynb', 'repo_name': 'DiegoMerino28/Practical-Data-Science-with-Python', 'size': '11123'}