<span class="float-right"><a class="btn btn-outline-primary btn-sm" href="../09/">Next&nbsp;<i class="fa fa-forward fa-fw" aria-hidden="true"></i></a></span>

<span class="float-right"><a class="btn btn-outline-primary btn-sm" href="../09/">Next&nbsp;<i class="fa fa-forward fa-fw" aria-hidden="true"></i></a></span>

<button class="navbar-toggler" type="button" data-toggle="collapse" data-target="#navbarContent" aria-controls="navbarContent" aria-expanded="false" aria-label="Toggle navigation">

<span class="navbar-toggler-icon"></span>

</button>

<div class="collapse navbar-collapse" id="navbarContent">

<div class="navbar-nav ml-auto">

<a class="btn btn-link btn-lg" href="https://twitter.com/opcampbell" title="twitter"><i class="fa fa-twitter" aria-hidden="true"></i></a>

<a class="btn btn-link btn-lg" href="https://www.linkedin.com/in/owencampbell" title="linkedin"><i class="fa fa-linkedin" aria-hidden="true"></i></a>

<a class="btn btn-link btn-lg" href="http://github.com/meatballs" title="github"><i class="fa fa-github" aria-hidden="true"></i></a>

<div class="row mt-3 mb-3">

<div class="col-sm">

<a class="btn btn-outline-primary btn-sm" href="../08/"><i class="fa fa-backward fa-fw" aria-hidden="true"></i>&nbsp;Previous</a>

<span class="float-right"><a class="btn btn-outline-primary btn-sm" href="../01/">Next&nbsp;<i class="fa fa-forward fa-fw" aria-hidden="true"></i></a></span>

</div>

<h1 id="Extract-The-Data">Extract The Data<a class="anchor-link" href="#Extract-The-Data">&#182;</a></h1><p>We've learned how to create a database, insert data and then update or delete it.</p>

<p>We've also seen how to view the content of a table using pandas.</p>

<p>But we've only seen how to display the entire contents of a single table. What if we only want a subset of the records? What if we want to extract a set based on records in more than one table?</p>

<p>It's time to learn about the SELECT statement.</p>

<p>First, let's connect to our database, empty both tables and create records for three flights.</p>

<p>We'll need to enable foreign keys again as this setting applies to the connection:</p>

<div class="cell border-box-sizing code_cell rendered">

<div class="input">

<div class="prompt input_prompt">In&nbsp;[29]:</div>

<div class="inner_cell">

<div class="input_area">

<div class=" highlight hl-ipython3"><pre><span></span><span class="kn">import</span> <span class="nn">sqlalchemy</span> <span class="k">as</span> <span class="nn">sa</span>

<span class="kn">import</span> <span class="nn">pandas</span> <span class="k">as</span> <span class="nn">pd</span>

<span class="kn">from</span> <span class="nn">collections</span> <span class="k">import</span> <span class="n">namedtuple</span>

<span class="n">engine</span> <span class="o">=</span> <span class="n">sa</span><span class="o">.</span><span class="n">create_engine</span><span class="p">(</span><span class="s1">&#39;sqlite:///flight.db&#39;</span><span class="p">)</span>

<span class="n">connection</span> <span class="o">=</span> <span class="n">engine</span><span class="o">.</span><span class="n">connect</span><span class="p">()</span>

<span class="n">connection</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="s2">&quot;PRAGMA foreign_keys=on&quot;</span><span class="p">)</span>

<span class="n">connection</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="s2">&quot;DELETE FROM flights&quot;</span><span class="p">)</span>

<span class="n">Flight</span> <span class="o">=</span> <span class="n">namedtuple</span><span class="p">(</span><span class="s1">&#39;Flight&#39;</span><span class="p">,</span> <span class="s1">&#39;name country_code&#39;</span><span class="p">)</span>

<span class="n">flights</span> <span class="o">=</span> <span class="p">[</span>

<span class="n">Flight</span><span class="p">(</span><span class="n">name</span><span class="o">=</span><span class="s1">&#39;hab1&#39;</span><span class="p">,</span> <span class="n">country_code</span><span class="o">=</span><span class="s1">&#39;GB&#39;</span><span class="p">),</span>

<span class="n">Flight</span><span class="p">(</span><span class="n">name</span><span class="o">=</span><span class="s1">&#39;hab2&#39;</span><span class="p">,</span> <span class="n">country_code</span><span class="o">=</span><span class="s1">&#39;GB&#39;</span><span class="p">),</span>

<span class="n">Flight</span><span class="p">(</span><span class="n">name</span><span class="o">=</span><span class="s1">&#39;hab3&#39;</span><span class="p">,</span> <span class="n">country_code</span><span class="o">=</span><span class="s1">&#39;US&#39;</span><span class="p">),</span>

<span class="p">]</span>

<span class="n">sql</span> <span class="o">=</span> <span class="s2">&quot;&quot;&quot;</span>

<span class="s2"> INSERT INTO flights(name, country_code)</span>

<span class="s2"> VALUES(?, ?)</span>

<span class="s2">&quot;&quot;&quot;</span>

<span class="k">for</span> <span class="n">flight</span> <span class="ow">in</span> <span class="n">flights</span><span class="p">:</span>

<span class="n">connection</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="n">sql</span><span class="p">,</span> <span class="p">(</span><span class="n">flight</span><span class="o">.</span><span class="n">name</span><span class="p">,</span> <span class="n">flight</span><span class="o">.</span><span class="n">country_code</span><span class="p">))</span>

<span class="n">pd</span><span class="o">.</span><span class="n">read_sql</span><span class="p">(</span><span class="s1">&#39;flights&#39;</span><span class="p">,</span> <span class="n">connection</span><span class="p">)</span>

<div class="output_wrapper">

<div class="output">

<div class="output_area">

<div class="prompt output_prompt">Out[29]:</div>

<div class="output_html rendered_html output_subarea output_execute_result">

<style scoped>

.dataframe tbody tr th:only-of-type {

vertical-align: middle;

}

.dataframe tbody tr th {

vertical-align: top;

}

.dataframe thead th {

text-align: right;

}

<table border="1" class="dataframe">

<thead>

<tr style="text-align: right;">

<th></th>

<th>name</th>

<th>country_code</th>

<th>latitude</th>

<th>longitude</th>

</tr>

</thead>

<tbody>

<tr>

<th>0</th>

<td>hab1</td>

<td>GB</td>

<td>0.0</td>

<td>0.0</td>

</tr>

<tr>

<th>1</th>

<td>hab2</td>

<td>GB</td>

<td>0.0</td>

<td>0.0</td>

</tr>

<tr>

<th>2</th>

<td>hab3</td>

<td>US</td>

<td>0.0</td>

<td>0.0</td>

</tr>

</tbody>

<div class="cell border-box-sizing text_cell rendered"><div class="prompt input_prompt">

<div class="inner_cell">

<div class="text_cell_render border-box-sizing rendered_html">

<p>And then generate 20 randomised readings for each flight:</p>

<div class="cell border-box-sizing code_cell rendered">

<div class="input">

<div class="prompt input_prompt">In&nbsp;[30]:</div>

<div class="inner_cell">

<div class="input_area">

<div class=" highlight hl-ipython3"><pre><span></span><span class="kn">from</span> <span class="nn">random</span> <span class="k">import</span> <span class="n">uniform</span><span class="p">,</span> <span class="n">randint</span>

<span class="n">Reading</span> <span class="o">=</span> <span class="n">namedtuple</span><span class="p">(</span><span class="s1">&#39;Reading&#39;</span><span class="p">,</span> <span class="s1">&#39;flight, ts, temp, pressure, humidity&#39;</span><span class="p">)</span>

<span class="n">readings</span> <span class="o">=</span> <span class="p">[</span>

<span class="n">Reading</span><span class="p">(</span>

<span class="n">flight</span><span class="o">=</span><span class="n">flight</span><span class="o">.</span><span class="n">name</span><span class="p">,</span>

<span class="n">ts</span><span class="o">=</span><span class="n">f</span><span class="s1">&#39;2015-01-01 09:{str(i+1).zfill(2)}:00&#39;</span><span class="p">,</span>

<span class="n">temp</span><span class="o">=</span><span class="nb">round</span><span class="p">(</span><span class="n">uniform</span><span class="p">(</span><span class="mi">23</span><span class="p">,</span> <span class="mi">27</span><span class="p">),</span> <span class="mi">1</span><span class="p">),</span>

<span class="n">pressure</span><span class="o">=</span><span class="n">randint</span><span class="p">(</span><span class="mi">1020</span><span class="p">,</span> <span class="mi">1025</span><span class="p">),</span>

<span class="n">humidity</span><span class="o">=</span><span class="n">randint</span><span class="p">(</span><span class="mi">30</span><span class="p">,</span> <span class="mi">50</span><span class="p">))</span>

<span class="k">for</span> <span class="n">flight</span> <span class="ow">in</span> <span class="n">flights</span>

<span class="k">for</span> <span class="n">i</span> <span class="ow">in</span> <span class="nb">range</span><span class="p">(</span><span class="mi">20</span><span class="p">)</span>

<span class="p">]</span>

<span class="n">sql</span> <span class="o">=</span> <span class="s2">&quot;&quot;&quot;</span>

<span class="s2">INSERT INTO readings</span>

<span class="s2"> (flight, ts, temp, pressure, humidity)</span>

<span class="s2">VALUES</span>

<span class="s2"> (?, ?, ?, ?, ?)</span>

<span class="s2">&quot;&quot;&quot;</span>

<span class="k">for</span> <span class="n">reading</span> <span class="ow">in</span> <span class="n">readings</span><span class="p">:</span>

<span class="n">values</span> <span class="o">=</span> <span class="p">(</span><span class="n">reading</span><span class="o">.</span><span class="n">flight</span><span class="p">,</span> <span class="n">reading</span><span class="o">.</span><span class="n">ts</span><span class="p">,</span> <span class="n">reading</span><span class="o">.</span><span class="n">temp</span><span class="p">,</span> <span class="n">reading</span><span class="o">.</span><span class="n">pressure</span><span class="p">,</span> <span class="n">reading</span><span class="o">.</span><span class="n">humidity</span><span class="p">)</span>

<span class="n">connection</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="n">sql</span><span class="p">,</span> <span class="n">values</span><span class="p">)</span>

<div class="row mt-3 mb-3">

<div class="col-sm">

<a class="btn btn-outline-primary btn-sm" href="../08/"><i class="fa fa-backward fa-fw" aria-hidden="true"></i>&nbsp;Previous</a>

<span class="float-right"><a class="btn btn-outline-primary btn-sm" href="../01/">Next&nbsp;<i class="fa fa-forward fa-fw" aria-hidden="true"></i></a></span>

</div>

<script src="https://code.jquery.com/jquery-3.2.1.slim.min.js" integrity="sha384-KJ3o2DKtIkvYIK3UENzmM7KCkRr/rE9/Qpg6aAZGJwFDMVNA/GpGFF93hXpG5KkN" crossorigin="anonymous"></script>

<script src="https://cdnjs.cloudflare.com/ajax/libs/popper.js/1.12.9/umd/popper.min.js" integrity="sha384-ApNbgh9B+Y1QKtv3Rn7W3mgPxhU9K/ScQsAP7hUibX39j7fakFPskvXusvfa0b4Q" crossorigin="anonymous"></script>

<script src="https://maxcdn.bootstrapcdn.com/bootstrap/4.0.0/js/bootstrap.min.js" integrity="sha384-JZR6Spejh4U02d8jOt6vLEHfe/JQGiRRSQQxSfFWpi1MquVdAyjUar5+76PVCmYl" crossorigin="anonymous"></script>

<li class="list-group-item borderless clearfix">

<div class="row">

<div class="col-sm">

<a href=/sql_python_tutorial/chapters/09 class="btn btn-link" role="button"/> Extract The Data</i></a>

</div>

<div class="col-sm">

<a href=notebooks/09-Extract-The-Data.ipynb class="btn btn-outline-light text-dark" role="button"><i class="fa fa-download fa-fw" aria-hidden="true"></i>&nbsp;Download</a>

</div>

</div>

</li>