lecture22.ipynb (18995B)
1 { 2 "cells": [ 3 { 4 "cell_type": "markdown", 5 "id": "93cbae4c", 6 "metadata": { 7 "slideshow": { 8 "slide_type": "slide" 9 } 10 }, 11 "source": [ 12 "# Lecture 22\n", 13 "## Tuesday, November 15th, 2022\n", 14 "### SQL Database Exercise (I)" 15 ] 16 }, 17 { 18 "cell_type": "markdown", 19 "id": "c4895afc", 20 "metadata": {}, 21 "source": [ 22 "# `SQLite` Exercises\n", 23 "\n", 24 "Today you will work with the `candidates.txt` and `contributors.txt` datasets to\n", 25 "create a database in `python` using `SQLite`. This is a hands-on lecture.\n", 26 "You may do these tasks in a Jupyter notebook.\n", 27 "\n", 28 "The exercises will consist of a sequence of steps to help illustrate basic\n", 29 "commands." 30 ] 31 }, 32 { 33 "cell_type": "markdown", 34 "id": "813913ac", 35 "metadata": { 36 "lines_to_next_cell": 2, 37 "slideshow": { 38 "slide_type": "subslide" 39 } 40 }, 41 "source": [ 42 "<a id='deliverables'></a>\n", 43 "# Exercise Deliverables\n", 44 "\n", 45 "> 1. Copy the Jupyter notebook along with `candidates.txt` and\n", 46 "> `contributors.txt` into `lab/pp12` in your private Git repository and\n", 47 "> commit on your default branch.\n", 48 "> 2. For each step in the exercise notebook, there are instructions labeled\n", 49 "> \"**Do the following:**\" (except for Setup and Interlude). Put all the code\n", 50 "> for those instructions in _code cell(s) immediately following the\n", 51 "> instructions_. The code in those cells should be regular Python code.\n", 52 "> You should place comments where appropriate that describe your intentions.\n", 53 "> **Note:** To get the\n", 54 "> `pandas` tables to display in a cell, use `display()`.\n", 55 "> 3. Save and close your database. Be sure to upload your database in\n", 56 "> `lab/pp12` as well. Please name your database **`lecture22.sqlite`**." 57 ] 58 }, 59 { 60 "cell_type": "markdown", 61 "id": "35b6d1a9", 62 "metadata": {}, 63 "source": [ 64 "## Table of Contents\n", 65 "\n", 66 "[Setup](#setup)\n", 67 "\n", 68 "[Interlude](#interlude): Not required but highly recommended.\n", 69 "\n", 70 "[Step 1](#step_1)\n", 71 "\n", 72 "[Step 2](#step_2)\n", 73 "\n", 74 "[Step 3](#step_3)\n", 75 "\n", 76 "[Step 4](#step_4)\n", 77 "\n", 78 "[Step 5](#step_5)\n", 79 "\n", 80 "[Step 6](#step_6)\n", 81 "\n", 82 "[Step 7](#step_7)\n", 83 "\n", 84 "[Step 8](#step_8)\n" 85 ] 86 }, 87 { 88 "cell_type": "markdown", 89 "id": "3b6570e6", 90 "metadata": {}, 91 "source": [ 92 "<a id='setup'></a>\n", 93 "# Setup\n", 94 "\n", 95 "You should import `sqlite3` again like in the lecture." 96 ] 97 }, 98 { 99 "cell_type": "code", 100 "execution_count": null, 101 "id": "a5e575a5", 102 "metadata": {}, 103 "outputs": [], 104 "source": [ 105 "import sqlite3" 106 ] 107 }, 108 { 109 "cell_type": "markdown", 110 "id": "b0aca704", 111 "metadata": {}, 112 "source": [ 113 "We will also use a basic `pandas` feature to display tables in the database." 114 ] 115 }, 116 { 117 "cell_type": "code", 118 "execution_count": null, 119 "id": "976d7b5f", 120 "metadata": {}, 121 "outputs": [], 122 "source": [ 123 "import pandas as pd\n", 124 "pd.set_option('display.width', 500)\n", 125 "pd.set_option('display.max_rows', None)\n", 126 "pd.set_option('display.max_columns', 100)\n", 127 "pd.set_option('display.notebook_repr_html', True)\n", 128 "\n", 129 "from IPython.display import display" 130 ] 131 }, 132 { 133 "cell_type": "markdown", 134 "id": "082acea0", 135 "metadata": {}, 136 "source": [ 137 "Now we create the tables in the database (similar to lecture)." 138 ] 139 }, 140 { 141 "cell_type": "code", 142 "execution_count": null, 143 "id": "e4a0d354", 144 "metadata": {}, 145 "outputs": [], 146 "source": [ 147 "db = sqlite3.connect('lecture22.sqlite')\n", 148 "cursor = db.cursor()\n", 149 "cursor.execute(\"DROP TABLE IF EXISTS candidates\")\n", 150 "cursor.execute(\"DROP TABLE IF EXISTS contributors\")\n", 151 "cursor.execute(\"PRAGMA foreign_keys=1\")\n", 152 "\n", 153 "cursor.execute('''CREATE TABLE candidates (\n", 154 " id INTEGER PRIMARY KEY NOT NULL, \n", 155 " first_name TEXT, \n", 156 " last_name TEXT, \n", 157 " middle_name TEXT, \n", 158 " party TEXT NOT NULL)''')\n", 159 "\n", 160 "db.commit() # Commit changes to the database\n", 161 "\n", 162 "cursor.execute('''CREATE TABLE contributors (\n", 163 " id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, \n", 164 " last_name TEXT, \n", 165 " first_name TEXT, \n", 166 " middle_name TEXT, \n", 167 " street_1 TEXT, \n", 168 " street_2 TEXT, \n", 169 " city TEXT, \n", 170 " state TEXT, \n", 171 " zip TEXT, \n", 172 " amount REAL, \n", 173 " date DATETIME, \n", 174 " candidate_id INTEGER NOT NULL, \n", 175 " FOREIGN KEY(candidate_id) REFERENCES candidates(id))''')\n", 176 "\n", 177 "db.commit()" 178 ] 179 }, 180 { 181 "cell_type": "markdown", 182 "id": "fb30c3d5", 183 "metadata": {}, 184 "source": [ 185 "Next we load the data for the candidates:" 186 ] 187 }, 188 { 189 "cell_type": "code", 190 "execution_count": null, 191 "id": "3b70dc4e", 192 "metadata": { 193 "lines_to_next_cell": 2 194 }, 195 "outputs": [], 196 "source": [ 197 "with open (\"candidates.txt\") as candidates:\n", 198 " next(candidates) # jump over the header\n", 199 " for line in candidates.readlines():\n", 200 " cid, first_name, last_name, middle_name, party = line.strip().split('|')\n", 201 " vals_to_insert = (int(cid), first_name, last_name, middle_name, party)\n", 202 " cursor.execute('''INSERT INTO candidates \n", 203 " (id, first_name, last_name, middle_name, party)\n", 204 " VALUES (?, ?, ?, ?, ?)''', vals_to_insert)\n", 205 "db.commit()" 206 ] 207 }, 208 { 209 "cell_type": "markdown", 210 "id": "ec092362", 211 "metadata": {}, 212 "source": [ 213 "<a id='interlude'></a>\n", 214 "## Interlude\n", 215 "\n", 216 "Now that you have values in the tables of the database, it would be convenient\n", 217 "to be able to visualize those tables in some way. We'll write a little helper\n", 218 "function to accomplish this." 219 ] 220 }, 221 { 222 "cell_type": "code", 223 "execution_count": null, 224 "id": "885c8e65", 225 "metadata": {}, 226 "outputs": [], 227 "source": [ 228 "def viz_tables(query, *, database=db):\n", 229 " return pd.read_sql_query(query, database)" 230 ] 231 }, 232 { 233 "cell_type": "markdown", 234 "id": "54160eb5", 235 "metadata": {}, 236 "source": [ 237 "Here's how we can use our helper function. It gives a pretty nice visualization\n", 238 "of our table." 239 ] 240 }, 241 { 242 "cell_type": "code", 243 "execution_count": null, 244 "id": "5cb6f058", 245 "metadata": { 246 "lines_to_next_cell": 2 247 }, 248 "outputs": [], 249 "source": [ 250 "viz_tables('''SELECT * FROM candidates''')" 251 ] 252 }, 253 { 254 "cell_type": "markdown", 255 "id": "4ee9ebf0", 256 "metadata": {}, 257 "source": [ 258 "<a id='step_1'></a>\n", 259 "# Step 1\n", 260 "\n", 261 "We still need to load the data in the contributors table." 262 ] 263 }, 264 { 265 "cell_type": "markdown", 266 "id": "55f108e2", 267 "metadata": {}, 268 "source": [ 269 "### Do the following:\n", 270 "\n", 271 "* Load the data in the `contributors` table by loading the data from the\n", 272 " `contributors.txt` file. **You are not allowed to use a `for`-loop in this\n", 273 " task (comprehensions are allowed)**.\n", 274 "* Display the contributors table." 275 ] 276 }, 277 { 278 "cell_type": "markdown", 279 "id": "b9abda82", 280 "metadata": {}, 281 "source": [ 282 "<a id='step_2'></a>\n", 283 "# Step 2: Various Queries\n", 284 "\n", 285 "We can query our database for entries with certain characteristics. For\n", 286 "example, we can query the `candidates` table for entries whose middle name\n", 287 "fields are not empty." 288 ] 289 }, 290 { 291 "cell_type": "code", 292 "execution_count": null, 293 "id": "973f46a1", 294 "metadata": {}, 295 "outputs": [], 296 "source": [ 297 "query = '''SELECT * FROM candidates WHERE middle_name <> \"\"'''\n", 298 "viz_tables(query)" 299 ] 300 }, 301 { 302 "cell_type": "markdown", 303 "id": "60807ada", 304 "metadata": {}, 305 "source": [ 306 "We can also see how many entries satisfy the query:" 307 ] 308 }, 309 { 310 "cell_type": "code", 311 "execution_count": null, 312 "id": "ae2e9d22", 313 "metadata": {}, 314 "outputs": [], 315 "source": [ 316 "print(\"{} candidates have a middle initial.\".format(viz_tables(query).shape[0]))" 317 ] 318 }, 319 { 320 "cell_type": "markdown", 321 "id": "9bcb0e32", 322 "metadata": {}, 323 "source": [ 324 "This used the `shape` method on the returned `pandas` table. You'll get to\n", 325 "practice counting in SQL later." 326 ] 327 }, 328 { 329 "cell_type": "markdown", 330 "id": "7397085b", 331 "metadata": {}, 332 "source": [ 333 "### Do the following queries:\n", 334 "\n", 335 "* Display the contributors where the state is \"PA\"\n", 336 "* Display the contributors where the amount contributed is greater than\n", 337 " $\\$1000.00$.\n", 338 "* Display the contributors from the state \"UT\" where the amount contributed is\n", 339 " greater than $\\$1000.00$.\n", 340 "* Display the contributors who didn't list their state\n", 341 " - **Hint**: Match `state` to the empty string\n", 342 "* Display the contributors from \"WA\" or \"PA\"\n", 343 "* Display the contributors who contributed between $\\$100.00$ and $\\$200.00$.\n", 344 " - **Hint**: You can use the `BETWEEN 100.00 and 200.00` clause." 345 ] 346 }, 347 { 348 "cell_type": "markdown", 349 "id": "79f589bb", 350 "metadata": {}, 351 "source": [ 352 "<a id='step_3'></a>\n", 353 "# Step 3: Sorting\n", 354 "\n", 355 "It could be beneficial to sort by one of the attributes in the database. The\n", 356 "following cell contains a basic sorting demo. Run it and try to understand what\n", 357 "happened." 358 ] 359 }, 360 { 361 "cell_type": "code", 362 "execution_count": null, 363 "id": "8be6c4fb", 364 "metadata": {}, 365 "outputs": [], 366 "source": [ 367 "viz_tables('''SELECT * FROM candidates ORDER BY id DESC''')" 368 ] 369 }, 370 { 371 "cell_type": "markdown", 372 "id": "07fc2df1", 373 "metadata": {}, 374 "source": [ 375 "### Do the following sorts on the specified tables:\n", 376 "\n", 377 "* Sort the `candidates` table by `last_name`.\n", 378 "* Sort the `contributors` table by the `amount` in descending order where `amount`\n", 379 " is restricted to be between $\\$1000.00$ and $\\$5000.00$.\n", 380 " - **Hint:** In your SQL command, start with getting the amount between the\n", 381 " specified range followed by the sort. This will all be done in one line.\n", 382 "* Sort the contributors who donated between $\\$1000.00$ and $\\$5000.00$ by\n", 383 " `candidate_id` and then by `amount` in descending order.\n", 384 " - **Hint**: Multiple orderings can be accomplished by separating requests\n", 385 " after `ORDER BY` with commas.\n", 386 " - e.g. `ORDER BY amount ASC, last_name DESC`" 387 ] 388 }, 389 { 390 "cell_type": "markdown", 391 "id": "b77f01d8", 392 "metadata": {}, 393 "source": [ 394 "<a id='step_4'></a>\n", 395 "# Step 4: Selecting Columns\n", 396 "\n", 397 "So far, we've been selecting all columns from a table (i.e. `SELECT * FROM`).\n", 398 "Often, we just want to select specific columns (e.g. `SELECT amount FROM`)." 399 ] 400 }, 401 { 402 "cell_type": "code", 403 "execution_count": null, 404 "id": "99c985ac", 405 "metadata": {}, 406 "outputs": [], 407 "source": [ 408 "viz_tables('''SELECT last_name, party FROM candidates''')" 409 ] 410 }, 411 { 412 "cell_type": "markdown", 413 "id": "02f44adb", 414 "metadata": {}, 415 "source": [ 416 "Using the `DISTINCT` clause, you remove duplicate rows." 417 ] 418 }, 419 { 420 "cell_type": "code", 421 "execution_count": null, 422 "id": "8384471d", 423 "metadata": {}, 424 "outputs": [], 425 "source": [ 426 "viz_tables('''SELECT DISTINCT party FROM candidates''')" 427 ] 428 }, 429 { 430 "cell_type": "markdown", 431 "id": "9c69c148", 432 "metadata": {}, 433 "source": [ 434 "### Do the following:\n", 435 "\n", 436 "* Get the first and last name of contributors. Make sure each row has distinct\n", 437 " values." 438 ] 439 }, 440 { 441 "cell_type": "markdown", 442 "id": "8d7e1ffb", 443 "metadata": {}, 444 "source": [ 445 "<a id='step_5'></a>\n", 446 "# Step 5: Altering Tables\n", 447 "\n", 448 "The `ALTER` clause allows us to modify tables in our database. Here, we add a\n", 449 "new column to our candidates table called `full_name`." 450 ] 451 }, 452 { 453 "cell_type": "code", 454 "execution_count": null, 455 "id": "5d7eb5c8", 456 "metadata": {}, 457 "outputs": [], 458 "source": [ 459 "cursor.execute('''ALTER TABLE candidates ADD COLUMN full_name TEXT''')\n", 460 "viz_tables('''SELECT * FROM candidates''')" 461 ] 462 }, 463 { 464 "cell_type": "markdown", 465 "id": "b97cbadb", 466 "metadata": {}, 467 "source": [ 468 "What if we want to rename or delete a column? It can't be done with `SQLite`\n", 469 "with a single command. We need to follow some roundabout steps (see [`SQLite`\n", 470 "ALTER TABLE](http://www.sqlitetutorial.net/sqlite-alter-table/)). We won't\n", 471 "consider this case at the moment." 472 ] 473 }, 474 { 475 "cell_type": "markdown", 476 "id": "7d2e3fc3", 477 "metadata": {}, 478 "source": [ 479 "For now, let's put a few commands together to populate the `full_name` column." 480 ] 481 }, 482 { 483 "cell_type": "code", 484 "execution_count": null, 485 "id": "7f2d99fc", 486 "metadata": {}, 487 "outputs": [], 488 "source": [ 489 "query = '''SELECT id, last_name, first_name FROM candidates''' # Select a few columns\n", 490 "full_name_and_id = [(attr[1] + \", \" + attr[2], attr[0]) for attr in cursor.execute(query).fetchall()] # List of tuples: (full_name, id)\n", 491 "\n", 492 "update = '''UPDATE candidates SET full_name = ? WHERE id = ?''' # Update the table\n", 493 "for rows in full_name_and_id:\n", 494 " cursor.execute(update, rows)\n", 495 "\n", 496 "query = '''SELECT * FROM candidates'''\n", 497 "viz_tables(query)" 498 ] 499 }, 500 { 501 "cell_type": "markdown", 502 "id": "11680111", 503 "metadata": {}, 504 "source": [ 505 "Here's another update, this time on an existing column." 506 ] 507 }, 508 { 509 "cell_type": "code", 510 "execution_count": null, 511 "id": "419f8252", 512 "metadata": {}, 513 "outputs": [], 514 "source": [ 515 "update = '''UPDATE candidates SET full_name = \"WINNER\" WHERE last_name = \"Obama\"'''\n", 516 "cursor.execute(update)\n", 517 "update = '''UPDATE candidates SET full_name = \"RUNNER-UP\" WHERE last_name = \"McCain\"'''\n", 518 "cursor.execute(update)\n", 519 "viz_tables(query)" 520 ] 521 }, 522 { 523 "cell_type": "markdown", 524 "id": "c83d645a", 525 "metadata": {}, 526 "source": [ 527 "### Do the following:\n", 528 "\n", 529 "* Add a new column to the contributors table called `full_name`. The value in\n", 530 " that column should be in the form `last_name, first_name`.\n", 531 "* Change the value in the `full_name` column to the string `\"Too Much\"` if\n", 532 " someone donated more than $\\$1000.00$." 533 ] 534 }, 535 { 536 "cell_type": "markdown", 537 "id": "f162d811", 538 "metadata": {}, 539 "source": [ 540 "<a id='step_6'></a>\n", 541 "# Step 6: Aggregation\n", 542 "\n", 543 "You can perform reduction operations on the values in the database. For\n", 544 "example, you can compute the maximum, minimum, sum or the total number from\n", 545 "multiple input values. Here's a little example:" 546 ] 547 }, 548 { 549 "cell_type": "code", 550 "execution_count": null, 551 "id": "becc8cd7", 552 "metadata": {}, 553 "outputs": [], 554 "source": [ 555 "viz_tables('''SELECT *, MAX(amount) AS max_amount FROM contributors''')" 556 ] 557 }, 558 { 559 "cell_type": "markdown", 560 "id": "b5332efb", 561 "metadata": {}, 562 "source": [ 563 "### Do the following:\n", 564 "\n", 565 "* Modify the demo to only output the max amount.\n", 566 " * **Hints:**\n", 567 " - Instead of using `SELECT *, MAX(amount) ...` you can try `SELECT\n", 568 " MAX(amount) ...`\n", 569 " - You will want to use `cursor.execute()` here and then `fetchall()`.\n", 570 " - Do not display your results in a table. It is sufficient to write\n", 571 " the answer out to the screen as a single number.\n", 572 "* Count how many donations there were above $\\$1000.00$.\n", 573 " * **Hint:** There is a `COUNT` function.\n", 574 "* Calculate the average *positive* donation.\n", 575 " * **Hint:** There is an `AVG` function.\n", 576 "* Calculate the average contribution from each state and display in a table.\n", 577 " Restrict to positive values again.\n", 578 " - **Hint**: Use code that looks like: `\"SELECT state,SUM(amount) FROM\n", 579 " contributors GROUP BY state\"`.\n", 580 "\n", 581 "<a id='step_7'></a>\n", 582 "# Step 7: DELETE\n", 583 "\n", 584 "We have already noted that `SQLite` can't drop columns in a straightforward\n", 585 "manner. However, it can delete rows quite simply. Here's the syntax:" 586 ] 587 }, 588 { 589 "cell_type": "code", 590 "execution_count": null, 591 "id": "e50981bb", 592 "metadata": {}, 593 "outputs": [], 594 "source": [ 595 "deletion = '''DELETE FROM table_name WHERE condition'''" 596 ] 597 }, 598 { 599 "cell_type": "markdown", 600 "id": "17bc148a", 601 "metadata": {}, 602 "source": [ 603 "### Do the following:\n", 604 "\n", 605 "* Delete rows in the `contributors` table with last name \"Ahrens\".\n", 606 "\n", 607 "<a id='step_8'></a>\n", 608 "# Step 8: LIMIT\n", 609 "\n", 610 "The `LIMIT` clause offers convenient functionality. It allows you to constrain\n", 611 "the number of rows returned by your query. It shows up in many guises." 612 ] 613 }, 614 { 615 "cell_type": "code", 616 "execution_count": null, 617 "id": "c4ffeb0f", 618 "metadata": {}, 619 "outputs": [], 620 "source": [ 621 "viz_tables('''SELECT * FROM candidates LIMIT 3''')" 622 ] 623 }, 624 { 625 "cell_type": "code", 626 "execution_count": null, 627 "id": "7364af8b", 628 "metadata": {}, 629 "outputs": [], 630 "source": [ 631 "viz_tables('''SELECT * FROM candidates LIMIT 4 OFFSET 5''')" 632 ] 633 }, 634 { 635 "cell_type": "code", 636 "execution_count": null, 637 "id": "863cabe9", 638 "metadata": {}, 639 "outputs": [], 640 "source": [ 641 "viz_tables('''SELECT * FROM candidates ORDER BY last_name LIMIT 4 OFFSET 5''')" 642 ] 643 }, 644 { 645 "cell_type": "markdown", 646 "id": "6dba32f0", 647 "metadata": {}, 648 "source": [ 649 "### Do the following:\n", 650 "\n", 651 "* Query and display the ten most generous donors.\n", 652 "* Query and display the ten least generous donors who donated a positive amount\n", 653 " of money (since the data we have has some negative numbers in it...)." 654 ] 655 }, 656 { 657 "cell_type": "markdown", 658 "id": "caff0ad7", 659 "metadata": {}, 660 "source": [ 661 "# Save\n", 662 "\n", 663 "Don't forget to save all of these changes to your database using `db.commit()`.\n", 664 "Before closing your editor or IDE, be sure to close the database connection with\n", 665 "`db.close()`." 666 ] 667 } 668 ], 669 "metadata": { 670 "jupytext": { 671 "formats": "md,ipynb" 672 }, 673 "kernelspec": { 674 "display_name": "Python 3", 675 "language": "python", 676 "name": "python3" 677 } 678 }, 679 "nbformat": 4, 680 "nbformat_minor": 5 681 }