{"cells":[{"cell_type":"markdown","id":"96549bb7-9943-4c04-97ab-bdee8d493bd6","metadata":{"id":"96549bb7-9943-4c04-97ab-bdee8d493bd6"},"source":["<a href=\"https://colab.research.google.com/github/smart-stats/ds4bio_book/blob/main/book/pysqlite.ipynb\" target=\"_parent\"><img src=\"https://colab.research.google.com/assets/colab-badge.svg\" alt=\"Open In Colab\"/></a>\n","\n","# sqlite in python\n","\n","An sqlite3 library ships with python. In this tutorial, we'll discuss how to utilize this library and read sqlite tables into pandas. With this, you can generalize to other python APIs to other databases. First, let's continue on with our work from the previous notebook. A nice little tutorial can be found [here](https://datacarpentry.org/python-ecology-lesson/09-working-with-sql/index.html)."]},{"cell_type":"code","execution_count":null,"id":"6dc9eb71-e4eb-4a7e-8c78-11d8bc266353","metadata":{"colab":{"base_uri":"https://localhost:8080/"},"id":"6dc9eb71-e4eb-4a7e-8c78-11d8bc266353","executionInfo":{"status":"ok","timestamp":1713207215487,"user_tz":240,"elapsed":4142,"user":{"displayName":"Brian Caffo","userId":"07979705296072332292"}},"outputId":"b0c05179-1048-415d-af0d-d24e373e8554"},"outputs":[{"output_type":"stream","name":"stdout","text":["--2024-04-15 18:53:36--  https://raw.githubusercontent.com/smart-stats/ds4bio_book/main/book/sql/opioid.db\n","Resolving raw.githubusercontent.com (raw.githubusercontent.com)... 185.199.108.133, 185.199.110.133, 185.199.111.133, ...\n","Connecting to raw.githubusercontent.com (raw.githubusercontent.com)|185.199.108.133|:443... connected.\n","HTTP request sent, awaiting response... 200 OK\n","Length: 7774208 (7.4M) [application/octet-stream]\n","Saving to: ‘opioid.db’\n","\n","opioid.db           100%[===================>]   7.41M  21.5MB/s    in 0.3s    \n","\n","2024-04-15 18:53:36 (21.5 MB/s) - ‘opioid.db’ saved [7774208/7774208]\n","\n","('1', 'AUTAUGA', 'AL', '01001', '1', '1', 'Autauga', 'Autauga County, Alabama', 'B01003_001', '2006', '51328', 'Autauga, AL', '594.44')\n","('2', 'BALDWIN', 'AL', '01003', '1', '3', 'Baldwin', 'Baldwin County, Alabama', 'B01003_001', '2006', '168121', 'Baldwin, AL', '1589.78')\n","('3', 'BARBOUR', 'AL', '01005', '1', '5', 'Barbour', 'Barbour County, Alabama', 'B01003_001', '2006', '27861', 'Barbour, AL', '884.88')\n","('4', 'BIBB', 'AL', '01007', '1', '7', 'Bibb', 'Bibb County, Alabama', 'B01003_001', '2006', '22099', 'Bibb, AL', '622.58')\n","('5', 'BLOUNT', 'AL', '01009', '1', '9', 'Blount', 'Blount County, Alabama', 'B01003_001', '2006', '55485', 'Blount, AL', '644.78')\n"]},{"output_type":"execute_result","data":{"text/plain":["<function Connection.close()>"]},"metadata":{},"execution_count":1}],"source":["import sqlite3 as sq3\n","import pandas as pd\n","\n","##Download the database using the unix command wget\n","!wget https://raw.githubusercontent.com/smart-stats/ds4bio_book/main/book/sql/opioid.db\n","\n","## Create the connection\n","con = sq3.connect(\"opioid.db\")\n","\n","## cursor() creates an object that can execute functions in the sqlite cursor\n","sql = con.cursor()\n","\n","## execute a command, loop through the output and print out\n","for row in sql.execute(\"select * from county_info limit 5;\"):\n","    print(row)\n","\n","\n","# you have to close the connection\n","con.close"]},{"cell_type":"markdown","id":"ae5fc83a-3440-42f4-a938-ecff8f43988e","metadata":{"id":"ae5fc83a-3440-42f4-a938-ecff8f43988e"},"source":["Let's read this dataset into pandas."]},{"cell_type":"code","execution_count":null,"id":"b54a4a17-8dc6-421e-80e7-08f39893e28a","metadata":{"colab":{"base_uri":"https://localhost:8080/","height":206},"id":"b54a4a17-8dc6-421e-80e7-08f39893e28a","executionInfo":{"status":"ok","timestamp":1713207217688,"user_tz":240,"elapsed":2208,"user":{"displayName":"Brian Caffo","userId":"07979705296072332292"}},"outputId":"29347935-8fb6-4c99-8338-af36b5f021ef"},"outputs":[{"output_type":"execute_result","data":{"text/plain":["     BUYER_COUNTY BUYER_STATE countyfips STATE COUNTY county_name  \\\n","0  1      AUTAUGA          AL      01001     1      1     Autauga   \n","1  2      BALDWIN          AL      01003     1      3     Baldwin   \n","2  3      BARBOUR          AL      01005     1      5     Barbour   \n","3  4         BIBB          AL      01007     1      7        Bibb   \n","4  5       BLOUNT          AL      01009     1      9      Blount   \n","\n","                      NAME    variable  year population     Areaname  \\\n","0  Autauga County, Alabama  B01003_001  2006      51328  Autauga, AL   \n","1  Baldwin County, Alabama  B01003_001  2006     168121  Baldwin, AL   \n","2  Barbour County, Alabama  B01003_001  2006      27861  Barbour, AL   \n","3     Bibb County, Alabama  B01003_001  2006      22099     Bibb, AL   \n","4   Blount County, Alabama  B01003_001  2006      55485   Blount, AL   \n","\n","  LND110210D  \n","0     594.44  \n","1    1589.78  \n","2     884.88  \n","3     622.58  \n","4     644.78  "],"text/html":["\n","  <div id=\"df-c947a664-b141-463c-9e05-febf9839f065\" class=\"colab-df-container\">\n","    <div>\n","<style scoped>\n","    .dataframe tbody tr th:only-of-type {\n","        vertical-align: middle;\n","    }\n","\n","    .dataframe tbody tr th {\n","        vertical-align: top;\n","    }\n","\n","    .dataframe thead th {\n","        text-align: right;\n","    }\n","</style>\n","<table border=\"1\" class=\"dataframe\">\n","  <thead>\n","    <tr style=\"text-align: right;\">\n","      <th></th>\n","      <th></th>\n","      <th>BUYER_COUNTY</th>\n","      <th>BUYER_STATE</th>\n","      <th>countyfips</th>\n","      <th>STATE</th>\n","      <th>COUNTY</th>\n","      <th>county_name</th>\n","      <th>NAME</th>\n","      <th>variable</th>\n","      <th>year</th>\n","      <th>population</th>\n","      <th>Areaname</th>\n","      <th>LND110210D</th>\n","    </tr>\n","  </thead>\n","  <tbody>\n","    <tr>\n","      <th>0</th>\n","      <td>1</td>\n","      <td>AUTAUGA</td>\n","      <td>AL</td>\n","      <td>01001</td>\n","      <td>1</td>\n","      <td>1</td>\n","      <td>Autauga</td>\n","      <td>Autauga County, Alabama</td>\n","      <td>B01003_001</td>\n","      <td>2006</td>\n","      <td>51328</td>\n","      <td>Autauga, AL</td>\n","      <td>594.44</td>\n","    </tr>\n","    <tr>\n","      <th>1</th>\n","      <td>2</td>\n","      <td>BALDWIN</td>\n","      <td>AL</td>\n","      <td>01003</td>\n","      <td>1</td>\n","      <td>3</td>\n","      <td>Baldwin</td>\n","      <td>Baldwin County, Alabama</td>\n","      <td>B01003_001</td>\n","      <td>2006</td>\n","      <td>168121</td>\n","      <td>Baldwin, AL</td>\n","      <td>1589.78</td>\n","    </tr>\n","    <tr>\n","      <th>2</th>\n","      <td>3</td>\n","      <td>BARBOUR</td>\n","      <td>AL</td>\n","      <td>01005</td>\n","      <td>1</td>\n","      <td>5</td>\n","      <td>Barbour</td>\n","      <td>Barbour County, Alabama</td>\n","      <td>B01003_001</td>\n","      <td>2006</td>\n","      <td>27861</td>\n","      <td>Barbour, AL</td>\n","      <td>884.88</td>\n","    </tr>\n","    <tr>\n","      <th>3</th>\n","      <td>4</td>\n","      <td>BIBB</td>\n","      <td>AL</td>\n","      <td>01007</td>\n","      <td>1</td>\n","      <td>7</td>\n","      <td>Bibb</td>\n","      <td>Bibb County, Alabama</td>\n","      <td>B01003_001</td>\n","      <td>2006</td>\n","      <td>22099</td>\n","      <td>Bibb, AL</td>\n","      <td>622.58</td>\n","    </tr>\n","    <tr>\n","      <th>4</th>\n","      <td>5</td>\n","      <td>BLOUNT</td>\n","      <td>AL</td>\n","      <td>01009</td>\n","      <td>1</td>\n","      <td>9</td>\n","      <td>Blount</td>\n","      <td>Blount County, Alabama</td>\n","      <td>B01003_001</td>\n","      <td>2006</td>\n","      <td>55485</td>\n","      <td>Blount, AL</td>\n","      <td>644.78</td>\n","    </tr>\n","  </tbody>\n","</table>\n","</div>\n","    <div class=\"colab-df-buttons\">\n","\n","  <div class=\"colab-df-container\">\n","    <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-c947a664-b141-463c-9e05-febf9839f065')\"\n","            title=\"Convert this dataframe to an interactive table.\"\n","            style=\"display:none;\">\n","\n","  <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\" viewBox=\"0 -960 960 960\">\n","    <path d=\"M120-120v-720h720v720H120Zm60-500h600v-160H180v160Zm220 220h160v-160H400v160Zm0 220h160v-160H400v160ZM180-400h160v-160H180v160Zm440 0h160v-160H620v160ZM180-180h160v-160H180v160Zm440 0h160v-160H620v160Z\"/>\n","  </svg>\n","    </button>\n","\n","  <style>\n","    .colab-df-container {\n","      display:flex;\n","      gap: 12px;\n","    }\n","\n","    .colab-df-convert {\n","      background-color: #E8F0FE;\n","      border: none;\n","      border-radius: 50%;\n","      cursor: pointer;\n","      display: none;\n","      fill: #1967D2;\n","      height: 32px;\n","      padding: 0 0 0 0;\n","      width: 32px;\n","    }\n","\n","    .colab-df-convert:hover {\n","      background-color: #E2EBFA;\n","      box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n","      fill: #174EA6;\n","    }\n","\n","    .colab-df-buttons div {\n","      margin-bottom: 4px;\n","    }\n","\n","    [theme=dark] .colab-df-convert {\n","      background-color: #3B4455;\n","      fill: #D2E3FC;\n","    }\n","\n","    [theme=dark] .colab-df-convert:hover {\n","      background-color: #434B5C;\n","      box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n","      filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n","      fill: #FFFFFF;\n","    }\n","  </style>\n","\n","    <script>\n","      const buttonEl =\n","        document.querySelector('#df-c947a664-b141-463c-9e05-febf9839f065 button.colab-df-convert');\n","      buttonEl.style.display =\n","        google.colab.kernel.accessAllowed ? 'block' : 'none';\n","\n","      async function convertToInteractive(key) {\n","        const element = document.querySelector('#df-c947a664-b141-463c-9e05-febf9839f065');\n","        const dataTable =\n","          await google.colab.kernel.invokeFunction('convertToInteractive',\n","                                                    [key], {});\n","        if (!dataTable) return;\n","\n","        const docLinkHtml = 'Like what you see? Visit the ' +\n","          '<a target=\"_blank\" href=https://colab.research.google.com/notebooks/data_table.ipynb>data table notebook</a>'\n","          + ' to learn more about interactive tables.';\n","        element.innerHTML = '';\n","        dataTable['output_type'] = 'display_data';\n","        await google.colab.output.renderOutput(dataTable, element);\n","        const docLink = document.createElement('div');\n","        docLink.innerHTML = docLinkHtml;\n","        element.appendChild(docLink);\n","      }\n","    </script>\n","  </div>\n","\n","\n","<div id=\"df-2c73ad81-d04e-4dda-bc8d-e4eca2738484\">\n","  <button class=\"colab-df-quickchart\" onclick=\"quickchart('df-2c73ad81-d04e-4dda-bc8d-e4eca2738484')\"\n","            title=\"Suggest charts\"\n","            style=\"display:none;\">\n","\n","<svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n","     width=\"24px\">\n","    <g>\n","        <path d=\"M19 3H5c-1.1 0-2 .9-2 2v14c0 1.1.9 2 2 2h14c1.1 0 2-.9 2-2V5c0-1.1-.9-2-2-2zM9 17H7v-7h2v7zm4 0h-2V7h2v10zm4 0h-2v-4h2v4z\"/>\n","    </g>\n","</svg>\n","  </button>\n","\n","<style>\n","  .colab-df-quickchart {\n","      --bg-color: #E8F0FE;\n","      --fill-color: #1967D2;\n","      --hover-bg-color: #E2EBFA;\n","      --hover-fill-color: #174EA6;\n","      --disabled-fill-color: #AAA;\n","      --disabled-bg-color: #DDD;\n","  }\n","\n","  [theme=dark] .colab-df-quickchart {\n","      --bg-color: #3B4455;\n","      --fill-color: #D2E3FC;\n","      --hover-bg-color: #434B5C;\n","      --hover-fill-color: #FFFFFF;\n","      --disabled-bg-color: #3B4455;\n","      --disabled-fill-color: #666;\n","  }\n","\n","  .colab-df-quickchart {\n","    background-color: var(--bg-color);\n","    border: none;\n","    border-radius: 50%;\n","    cursor: pointer;\n","    display: none;\n","    fill: var(--fill-color);\n","    height: 32px;\n","    padding: 0;\n","    width: 32px;\n","  }\n","\n","  .colab-df-quickchart:hover {\n","    background-color: var(--hover-bg-color);\n","    box-shadow: 0 1px 2px rgba(60, 64, 67, 0.3), 0 1px 3px 1px rgba(60, 64, 67, 0.15);\n","    fill: var(--button-hover-fill-color);\n","  }\n","\n","  .colab-df-quickchart-complete:disabled,\n","  .colab-df-quickchart-complete:disabled:hover {\n","    background-color: var(--disabled-bg-color);\n","    fill: var(--disabled-fill-color);\n","    box-shadow: none;\n","  }\n","\n","  .colab-df-spinner {\n","    border: 2px solid var(--fill-color);\n","    border-color: transparent;\n","    border-bottom-color: var(--fill-color);\n","    animation:\n","      spin 1s steps(1) infinite;\n","  }\n","\n","  @keyframes spin {\n","    0% {\n","      border-color: transparent;\n","      border-bottom-color: var(--fill-color);\n","      border-left-color: var(--fill-color);\n","    }\n","    20% {\n","      border-color: transparent;\n","      border-left-color: var(--fill-color);\n","      border-top-color: var(--fill-color);\n","    }\n","    30% {\n","      border-color: transparent;\n","      border-left-color: var(--fill-color);\n","      border-top-color: var(--fill-color);\n","      border-right-color: var(--fill-color);\n","    }\n","    40% {\n","      border-color: transparent;\n","      border-right-color: var(--fill-color);\n","      border-top-color: var(--fill-color);\n","    }\n","    60% {\n","      border-color: transparent;\n","      border-right-color: var(--fill-color);\n","    }\n","    80% {\n","      border-color: transparent;\n","      border-right-color: var(--fill-color);\n","      border-bottom-color: var(--fill-color);\n","    }\n","    90% {\n","      border-color: transparent;\n","      border-bottom-color: var(--fill-color);\n","    }\n","  }\n","</style>\n","\n","  <script>\n","    async function quickchart(key) {\n","      const quickchartButtonEl =\n","        document.querySelector('#' + key + ' button');\n","      quickchartButtonEl.disabled = true;  // To prevent multiple clicks.\n","      quickchartButtonEl.classList.add('colab-df-spinner');\n","      try {\n","        const charts = await google.colab.kernel.invokeFunction(\n","            'suggestCharts', [key], {});\n","      } catch (error) {\n","        console.error('Error during call to suggestCharts:', error);\n","      }\n","      quickchartButtonEl.classList.remove('colab-df-spinner');\n","      quickchartButtonEl.classList.add('colab-df-quickchart-complete');\n","    }\n","    (() => {\n","      let quickchartButtonEl =\n","        document.querySelector('#df-2c73ad81-d04e-4dda-bc8d-e4eca2738484 button');\n","      quickchartButtonEl.style.display =\n","        google.colab.kernel.accessAllowed ? 'block' : 'none';\n","    })();\n","  </script>\n","</div>\n","\n","    </div>\n","  </div>\n"],"application/vnd.google.colaboratory.intrinsic+json":{"type":"dataframe","variable_name":"county_info","summary":"{\n  \"name\": \"county_info\",\n  \"rows\": 28265,\n  \"fields\": [\n    {\n      \"column\": \"\",\n      \"properties\": {\n        \"dtype\": \"string\",\n        \"num_unique_values\": 28265,\n        \"samples\": [\n          \"24878\",\n          \"17508\",\n          \"15431\"\n        ],\n        \"semantic_type\": \"\",\n        \"description\": \"\"\n      }\n    },\n    {\n      \"column\": \"BUYER_COUNTY\",\n      \"properties\": {\n        \"dtype\": \"category\",\n        \"num_unique_values\": 1849,\n        \"samples\": [\n          \"ALLEN\",\n          \"CUYAHOGA\",\n          \"CLOUD\"\n        ],\n        \"semantic_type\": \"\",\n        \"description\": \"\"\n      }\n    },\n    {\n      \"column\": \"BUYER_STATE\",\n      \"properties\": {\n        \"dtype\": \"category\",\n        \"num_unique_values\": 51,\n        \"samples\": [\n          \"TX\",\n          \"SC\",\n          \"VA\"\n        ],\n        \"semantic_type\": \"\",\n        \"description\": \"\"\n      }\n    },\n    {\n      \"column\": \"countyfips\",\n      \"properties\": {\n        \"dtype\": \"category\",\n        \"num_unique_values\": 3142,\n        \"samples\": [\n          \"49033\",\n          \"53041\",\n          \"21151\"\n        ],\n        \"semantic_type\": \"\",\n        \"description\": \"\"\n      }\n    },\n    {\n      \"column\": \"STATE\",\n      \"properties\": {\n        \"dtype\": \"category\",\n        \"num_unique_values\": 51,\n        \"samples\": [\n          \"48\",\n          \"45\",\n          \"51\"\n        ],\n        \"semantic_type\": \"\",\n        \"description\": \"\"\n      }\n    },\n    {\n      \"column\": \"COUNTY\",\n      \"properties\": {\n        \"dtype\": \"category\",\n        \"num_unique_values\": 325,\n        \"samples\": [\n          \"409\",\n          \"163\",\n          \"437\"\n        ],\n        \"semantic_type\": \"\",\n        \"description\": \"\"\n      }\n    },\n    {\n      \"column\": \"county_name\",\n      \"properties\": {\n        \"dtype\": \"category\",\n        \"num_unique_values\": 1850,\n        \"samples\": [\n          \"Conejos\",\n          \"Hays\",\n          \"Brown\"\n        ],\n        \"semantic_type\": \"\",\n        \"description\": \"\"\n      }\n    },\n    {\n      \"column\": \"NAME\",\n      \"properties\": {\n        \"dtype\": \"category\",\n        \"num_unique_values\": 3140,\n        \"samples\": [\n          \"Russell County, Virginia\",\n          \"Lewis County, Washington\",\n          \"Marion County, Kentucky\"\n        ],\n        \"semantic_type\": \"\",\n        \"description\": \"\"\n      }\n    },\n    {\n      \"column\": \"variable\",\n      \"properties\": {\n        \"dtype\": \"category\",\n        \"num_unique_values\": 2,\n        \"samples\": [\n          \"NA\",\n          \"B01003_001\"\n        ],\n        \"semantic_type\": \"\",\n        \"description\": \"\"\n      }\n    },\n    {\n      \"column\": \"year\",\n      \"properties\": {\n        \"dtype\": \"object\",\n        \"num_unique_values\": 9,\n        \"samples\": [\n          \"2013\",\n          \"2007\"\n        ],\n        \"semantic_type\": \"\",\n        \"description\": \"\"\n      }\n    },\n    {\n      \"column\": \"population\",\n      \"properties\": {\n        \"dtype\": \"string\",\n        \"num_unique_values\": 24017,\n        \"samples\": [\n          \"8249\",\n          \"3869\"\n        ],\n        \"semantic_type\": \"\",\n        \"description\": \"\"\n      }\n    },\n    {\n      \"column\": \"Areaname\",\n      \"properties\": {\n        \"dtype\": \"category\",\n        \"num_unique_values\": 3136,\n        \"samples\": [\n          \"Catron, NM\",\n          \"Walker, GA\"\n        ],\n        \"semantic_type\": \"\",\n        \"description\": \"\"\n      }\n    },\n    {\n      \"column\": \"LND110210D\",\n      \"properties\": {\n        \"dtype\": \"category\",\n        \"num_unique_values\": 3101,\n        \"samples\": [\n          \"306.02\",\n          \"173.92\"\n        ],\n        \"semantic_type\": \"\",\n        \"description\": \"\"\n      }\n    }\n  ]\n}"}},"metadata":{},"execution_count":2}],"source":["con = sq3.connect(\"opioid.db\")\n","\n","county_info = pd.read_sql_query(\"SELECT * from county_info\", con)\n","\n","# you have to close the connection\n","con.close\n","\n","county_info.head()"]},{"cell_type":"markdown","source":["This should get you started on working with SQL in python. Personally, I find using python easier than directly using the SQL, so the basic approach in this section is how I would suggest that you operate."],"metadata":{"id":"sgzpOy22GiVN"},"id":"sgzpOy22GiVN"}],"metadata":{"kernelspec":{"display_name":"Python 3 (ipykernel)","language":"python","name":"python3"},"language_info":{"codemirror_mode":{"name":"ipython","version":3},"file_extension":".py","mimetype":"text/x-python","name":"python","nbconvert_exporter":"python","pygments_lexer":"ipython3","version":"3.10.13"},"colab":{"provenance":[]}},"nbformat":4,"nbformat_minor":5}