<jupyter_start><jupyter_text>Working with Databases Takeaways for Today1. Reading large data sets into memory can be surprisingly slow, and in some cases completely impractical. 2. Databases offer a convenient way to perform selective *queries* on our data, allowing us to retrieve only the data that we want at any given time. 3. The `sqlite3` package enables us to work with databases using Python commands, and the `pandas` package makes it easy to read the results of database queries as data frames.  Intro So far, we've worked exclusively with data that could conveniently fit in memory. Generally speaking, data sets of under 20 megabytes or so can be loaded easily on most modern laptops, although subsequent analysis activities may be slow. Larger data sets can be noticeably slow to even load into memory. For example, you may  have noticed that so far, I've been using a small subset of the NOAA-GHCN data corresponding to the decade 2011-2020. There's much  more data, and I have the complete data set in a file (also uploaded on CCLE) in the same directory as these lecture notes on my personal machine. The complete set of data is not too big to fit in memory, but it's large enough that it's noticeably slow to even load into pandas. Let's take a look.  One second just to load the data set! Note: I'm not even *down*loading the data set -- this is just how long it takes to move the data from my computer's storage into RAM. We can get a pretty good estimate of how much RAM is required to store the data using a handy method of Pandas data frames:  The data contains 1.35M rows and 14 columns. Altogether, these consume roughly 150 MB of RAM. This is an appreciable fraction of the RAM on most laptops. In some cases, we might need to work with even larger data sets that don't fit in RAM at all.   Introducing DatabasesWhen dealing with large data sets like these, it's relatively rare that we absolutely *have* to operate on the entire data set. In most cases, we can work with parts of the data at a time. In the context of the temperature data, 1. We might want temperature measurements only between years 1990-2020. 2. We might want temperature measurements only for a certain set of countries - maybe in Asia, say. 3. We might only want temperature measurements only in the month of March. 4. We might want a random 1% of all the data. **Databases** provide us with a structured way to move subsets of data from storage into memory. Python has a module called `sqlite3` (already installed) which we can use to create, manipulate, and query databases. There's also a very handy `pandas` interface, enabling us to efficiently create `pandas` data frames containing exactly the data that we want. Let's get started.  Creating and Populating DatabasesAfter importing the `sqlite3` module, the first thing we should do is connect to a database. In case the specified database does not exist, instantiating the connection will also create an empty database with the specified name.   A quick check in our file directory reveals that we now have a file called `temps.db`. Great!  There are many ways to add data to a database. Since we are already familiar with Pandas data frames, we'll make use of some extremely convenient functionality which allows us to directly write data frames to a database. But wait! Wasn't the whole point of this to avoid reading in an entire data frame? Indeed! Pandas supplies a nice approach to this using the familiar `pd.read_csv()` function. Supplying a value of `chunksize` will cause `read_csv()` to return not a data frame but an *iterator*, each of whose elements is a piece of the data with number of rows equal to `chunksize`. The data is read "on the fly" -- i.e. it's not actually read until we start querying the iterator. Here's an example:  Great! Actually, there are a few cleaning steps that we'll make before incorporating this data into our database. If you're not sure what's going on here, you may want to check the recent lecture in which we cover `stack`.<jupyter_code>def prepare_df(df):
    df = df.set_index(keys=["ID", "Year"])
    df = df.stack()
    df = df.reset_index()
    df = df.rename(columns = {"level_2"  : "Month" , 0 : "Temp"})
    df["Month"] = df["Month"].str[5:].astype(int)
    df["Temp"]  = df["Temp"] / 100
    return(df)<jupyter_output><empty_output><jupyter_text>Ok! We are finally ready to populate a *table* in our database. You can think of a table as a data frame-like object, represented in SQLite rather than in Python. The `df.to_sql()` method writes to a specified table in the database (the `conn` object from earlier). We need to specify `if_exists` to ensure that we add each piece to the table, rather than overwriting them each time.  Let's similarly add a table for the metadata in our database. This is a pretty small data set so we don't need to worry about reading it in by chunks.<jupyter_code>url = "https://raw.githubusercontent.com/PhilChodrow/PIC16B/master/datasets/noaa-ghcn/station-metadata.csv"<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.05591909577632362, 'fraction_numerical': 0.00892325996430696, 'mean_word_length': 4.315068493150685, 'pattern_counts': {'":': 0, '<': 9, '<?xml version=': 0, '>': 9, 'https://': 1, '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': '2', 'dir': 'jupyter-structured-clean-dedup', 'id': '37219', 'license': 'MIT', 'n_tokens_mistral': 1326, 'n_tokens_neox': 1245, 'n_words': 808, 'path': 'lectures/sql/sql-1-live.ipynb', 'repo_name': 'malvin0704/PIC16B', 'size': '5043'}