{ "cells": [ { "cell_type": "markdown", "metadata": {}, "source": [ "# A gentle introduction to SQL\n", "\n", "\n", "This notebook introduces some of the basic commands for querying and modifying a database using the Structured Query Language, SQL. \n", "\n", "Part One retrieves data in the form of some csv files, from elsewhere in the internet. Then we're going to use a particular handy tool to turn this data into a database. \n", "\n", "In Part two, we'll walk through some basic SQL commands for exploring and transforming this data. The amphitheatre data is courtesy Sebastian Heath, [https://doi.org/10.5281/zenodo.596149](https://doi.org/10.5281/zenodo.596149). The aqueduct data is from [Pelagios Commons](http://commons.pelagios.org).\n", "\n", "## Part One" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "We've already obtained the amphitheatre data from Sebastian Heath using the `curl` command, like so:\n", "\n", "`!curl https://raw.githubusercontent.com/sfsheath/roman-amphitheaters/master/roman-amphitheaters.csv > amphi.csv`" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "We also downloaded information about roman aqueducts from the [peripleo api](http://peripleo.pelagios.org/peripleo/search?query=roman+AND+aqueduct&prettyprint=true) as json, and converted to csv using [this tool](https://github.com/zemirco/json2csv).\n", "\n", "Finally, we want to turn these two csv files into a single database containing two tables. We will use the command line tool '[sqlitebiter](https://github.com/thombashi/sqlitebiter)' by Tsuyoshi Hombashi to do this, which we have already installed." ] }, { "cell_type": "code", "execution_count": 1, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "\u001b[37m[INFO] sqlitebiter file: convert 'amphi.csv' to 'amphi' table\u001b[39;49;00m\n", "\u001b[37m[INFO] sqlitebiter file: convert 'aqua.csv' to 'aqua' table\u001b[39;49;00m\n", "\u001b[37m[INFO] sqlitebiter file: number of created tables: 2\u001b[39;49;00m\n", "\u001b[37m[INFO] sqlitebiter file: database path: /home/jovyan/roman.db\u001b[39;49;00m\n", "\u001b[0m" ] } ], "source": [ "!sqlitebiter -o roman.db file \"amphi.csv\" \"aqua.csv\"\n" ] }, { "cell_type": "code", "execution_count": 80, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "CREATE TABLE IF NOT EXISTS '_source_info_' (source_id INTEGER PRIMARY KEY AUTOINCREMENT, dir_name TEXT, base_name TEXT NOT NULL, format TEXT NOT NULL, size INTEGER, mtime INTEGER);\r\n", "CREATE TABLE sqlite_sequence(name,seq);\r\n", "CREATE TABLE IF NOT EXISTS 'amphi' (id TEXT, title TEXT, label TEXT, latintoponym TEXT, pleiades TEXT, welchid INTEGER, golvinid INTEGER, buildingtype TEXT, chronogroup TEXT, secondcentury INTEGER, capacity INTEGER, modcountry TEXT, romanregion TEXT, arenamajor REAL, arenaminor REAL, extmajor REAL, extminor REAL, exteriorheight REAL, longitude REAL, latitude REAL, elevation INTEGER);\r\n", "CREATE TABLE IF NOT EXISTS 'aqua' (identifier TEXT, title TEXT, \"object_type\" TEXT, \"dataset_path\" TEXT, \"geo_bounds\" TEXT, names TEXT, \"temporal_bounds\" TEXT, depictions TEXT, matches TEXT);\r\n" ] } ], "source": [ "!sqlite3 roman.db .schema .exit" ] }, { "cell_type": "code", "execution_count": 81, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "_source_info_ amphi aqua \r\n" ] } ], "source": [ "!sqlite3 roman.db .tables .exit" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Part Two\n", "\n", "Now that we have a database, we'll bring it into python so that we can query it. Once a database is in python, we can do a wide variety of data science type visualizations or explorations, although these are beyond the remit of the current notebook.\n", "\n", "The first thing we're going to do is create a function that opens a connection to the database, and allows us to build queries. After we create the function, we can create query objects, and then `run_query`. Students might also want to consult [this tutorial](https://www.dataquest.io/blog/sql-basics/)." ] }, { "cell_type": "code", "execution_count": 82, "metadata": {}, "outputs": [], "source": [ "# create a function for querying the database\n", "import sqlite3\n", "import pandas as pd\n", "\n", "db = sqlite3.connect('roman.db')\n", "\n", "def run_query(query):\n", " return pd.read_sql_query(query,db)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Let's give it a try. We're going to build a query that asks, 'show us every column in the amphi table, but only for the first five rows.'\n" ] }, { "cell_type": "code", "execution_count": 18, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
idtitlelabellatintoponympleiadeswelchidgolvinidbuildingtypechronogroupsecondcentury...modcountryromanregionarenamajorarenaminorextmajorextminorexteriorheightlongitudelatitudeelevation
0duraEuroposAmphitheaterAmphitheater at Dura EuroposDuraDura Europushttps://pleiades.stoa.org/places/893989129amphitheaterseveran0...Syriasyria3125504440.72892634.749855223
1arlesAmphitheaterAmphitheater at ArlesArlesArelatehttps://pleiades.stoa.org/places/148217154amphitheaterflavian1...Francenarbonensis47321361074.63111143.67777821
2lyonAmphitheaterAmphitheater at LyonLyonLugdunumhttps://pleiades.stoa.org/places/167717amphitheatersecond-century1...Francelugdunensis67.6421054.83055645.770556206
3ludusMagnusArenaLudus Magnus ArenaLudus MagnusLudus Magnushttps://pleiades.stoa.org/places/423025practice-arenaimperial0...Italyregio-i12.49491341.88995022
4romeFlavianAmphitheaterFlavian Amphitheater at RomeColosseumhttps://pleiades.stoa.org/places/423025152amphitheaterflavian1...Italyregio-i83481891565212.49226941.89016922
\n", "

5 rows × 21 columns

\n", "
" ], "text/plain": [ " id title label \\\n", "0 duraEuroposAmphitheater Amphitheater at Dura Europos Dura \n", "1 arlesAmphitheater Amphitheater at Arles Arles \n", "2 lyonAmphitheater Amphitheater at Lyon Lyon \n", "3 ludusMagnusArena Ludus Magnus Arena Ludus Magnus \n", "4 romeFlavianAmphitheater Flavian Amphitheater at Rome Colosseum \n", "\n", " latintoponym pleiades welchid golvinid \\\n", "0 Dura Europus https://pleiades.stoa.org/places/893989 129 \n", "1 Arelate https://pleiades.stoa.org/places/148217 154 \n", "2 Lugdunum https://pleiades.stoa.org/places/167717 \n", "3 Ludus Magnus https://pleiades.stoa.org/places/423025 \n", "4 https://pleiades.stoa.org/places/423025 152 \n", "\n", " buildingtype chronogroup secondcentury ... modcountry \\\n", "0 amphitheater severan 0 ... Syria \n", "1 amphitheater flavian 1 ... France \n", "2 amphitheater second-century 1 ... France \n", "3 practice-arena imperial 0 ... Italy \n", "4 amphitheater flavian 1 ... Italy \n", "\n", " romanregion arenamajor arenaminor extmajor extminor exteriorheight \\\n", "0 syria 31 25 50 44 \n", "1 narbonensis 47 32 136 107 \n", "2 lugdunensis 67.6 42 105 \n", "3 regio-i \n", "4 regio-i 83 48 189 156 52 \n", "\n", " longitude latitude elevation \n", "0 40.728926 34.749855 223 \n", "1 4.631111 43.677778 21 \n", "2 4.830556 45.770556 206 \n", "3 12.494913 41.889950 22 \n", "4 12.492269 41.890169 22 \n", "\n", "[5 rows x 21 columns]" ] }, "execution_count": 18, "metadata": {}, "output_type": "execute_result" } ], "source": [ "query = 'SELECT * FROM amphi LIMIT 5;'\n", "run_query(query)" ] }, { "cell_type": "code", "execution_count": 19, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
identifiertitleobject_typedataset_pathgeo_boundsnamestemporal_boundsdepictionsmatches
0http://dare.ht.lu.se/places/43530Roman aqueduct, Mitiline, LesbosPlace[{\"title\":\"dare-20160328\",\"id\":\"dare-20160328\"}]{\"min_lon\":26.514763,\"max_lon\":26.514763,\"min_...[\"Roman aqueduct, Mitiline, Lesbos\"]
1http://pleiades.stoa.org/places/738817254Ansignan aqueductPlace[{\"title\":\"pleiades\",\"id\":\"pleiades\"}]{\"min_lon\":2.5140266,\"max_lon\":2.5140266,\"min_...
2http://pleiades.stoa.org/places/403927Caldaccoli aqueductPlace[{\"title\":\"pleiades\",\"id\":\"pleiades\"}]{\"min_lon\":10.4367702,\"max_lon\":10.4367702,\"mi...
3http://topostext.org/place/352254BTeiLyttos aqueduct (Lasithi)Place[{\"title\":\"ToposText Places\",\"id\":\"ToposText P...{\"min_lon\":25.3818,\"max_lon\":25.3818,\"min_lat\"...{\"start\":-750,\"end\":640}
4http://pleiades.stoa.org/places/246891Albarracín-Cella Roman aqueductPlace[{\"title\":\"pleiades\",\"id\":\"pleiades\"}]{\"min_lon\":-1.5,\"max_lon\":-1.5,\"min_lat\":40.5,...
\n", "
" ], "text/plain": [ " identifier \\\n", "0 http://dare.ht.lu.se/places/43530 \n", "1 http://pleiades.stoa.org/places/738817254 \n", "2 http://pleiades.stoa.org/places/403927 \n", "3 http://topostext.org/place/352254BTei \n", "4 http://pleiades.stoa.org/places/246891 \n", "\n", " title object_type \\\n", "0 Roman aqueduct, Mitiline, Lesbos Place \n", "1 Ansignan aqueduct Place \n", "2 Caldaccoli aqueduct Place \n", "3 Lyttos aqueduct (Lasithi) Place \n", "4 Albarracín-Cella Roman aqueduct Place \n", "\n", " dataset_path \\\n", "0 [{\"title\":\"dare-20160328\",\"id\":\"dare-20160328\"}] \n", "1 [{\"title\":\"pleiades\",\"id\":\"pleiades\"}] \n", "2 [{\"title\":\"pleiades\",\"id\":\"pleiades\"}] \n", "3 [{\"title\":\"ToposText Places\",\"id\":\"ToposText P... \n", "4 [{\"title\":\"pleiades\",\"id\":\"pleiades\"}] \n", "\n", " geo_bounds \\\n", "0 {\"min_lon\":26.514763,\"max_lon\":26.514763,\"min_... \n", "1 {\"min_lon\":2.5140266,\"max_lon\":2.5140266,\"min_... \n", "2 {\"min_lon\":10.4367702,\"max_lon\":10.4367702,\"mi... \n", "3 {\"min_lon\":25.3818,\"max_lon\":25.3818,\"min_lat\"... \n", "4 {\"min_lon\":-1.5,\"max_lon\":-1.5,\"min_lat\":40.5,... \n", "\n", " names temporal_bounds depictions \\\n", "0 [\"Roman aqueduct, Mitiline, Lesbos\"] \n", "1 \n", "2 \n", "3 {\"start\":-750,\"end\":640} \n", "4 \n", "\n", " matches \n", "0 \n", "1 \n", "2 \n", "3 \n", "4 " ] }, "execution_count": 19, "metadata": {}, "output_type": "execute_result" } ], "source": [ "# just check that the aquaduct table is in there too\n", "query = 'SELECT * FROM aqua LIMIT 5;'\n", "run_query(query)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Basic Query Commands\n", "\n", "SELECT, LIMIT, ORDER BY : using these, we can ask, 'Which amphitheatre is at the highest elevation?'\n", "\n", "Use SELECT to retrieve the id and elevation columns FROM the amphi table\n", "\n", "Use ORDER BY to sort the elevation column and use the DESC keyword to specify that you want to sort in descending order\n", "\n", "Use LIMIT to restrict the output to 1 row" ] }, { "cell_type": "code", "execution_count": 30, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
idelevation
0lambaesisAmphitheater1170
\n", "
" ], "text/plain": [ " id elevation\n", "0 lambaesisAmphitheater 1170" ] }, "execution_count": 30, "metadata": {}, "output_type": "execute_result" } ], "source": [ "query = '''\n", "SELECT id, elevation \n", "FROM amphi\n", "ORDER BY elevation DESC\n", "LIMIT 1;\n", "'''\n", "\n", "run_query(query)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Let's get the top 10 now" ] }, { "cell_type": "code", "execution_count": 31, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
idelevation
0lambaesisAmphitheater1170
1albaFucensAmphitheater977
2mactarisAmphitheater911
3tebessaAmphitheater877
4leonAmphitheater846
5bostraAmphitheater845
6segobrigaAmphitheater817
7siccaVeneriaAmphitheater748
8sanBenedettoDeiMarsiAmphitheater683
9sanVittorinoAmphitheater672
\n", "
" ], "text/plain": [ " id elevation\n", "0 lambaesisAmphitheater 1170\n", "1 albaFucensAmphitheater 977\n", "2 mactarisAmphitheater 911\n", "3 tebessaAmphitheater 877\n", "4 leonAmphitheater 846\n", "5 bostraAmphitheater 845\n", "6 segobrigaAmphitheater 817\n", "7 siccaVeneriaAmphitheater 748\n", "8 sanBenedettoDeiMarsiAmphitheater 683\n", "9 sanVittorinoAmphitheater 672" ] }, "execution_count": 31, "metadata": {}, "output_type": "execute_result" } ], "source": [ "query = '''\n", "SELECT id, elevation \n", "FROM amphi\n", "ORDER BY elevation DESC\n", "LIMIT 10;\n", "'''\n", "\n", "run_query(query)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Following this pattern, can you create a query that also provides the geographic coordinates? In the block below see if you can construct and run that query." ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Querying with Conditions\n", "\n", "Now let's create a query that creates a subset of data using a logical operator. We need the 'WHERE' command." ] }, { "cell_type": "code", "execution_count": 32, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
idtitlelabellatintoponympleiadeswelchidgolvinidbuildingtypechronogroupsecondcentury...modcountryromanregionarenamajorarenaminorextmajorextminorexteriorheightlongitudelatitudeelevation
0segobrigaAmphitheaterAmphitheater at SegobrigaSegobrigaSegobrigahttps://pleiades.stoa.org/places/26603688amphitheaterflavian1...Spaintarraconensis47317564-2.81394439.886018817
1cyreneAmphitheaterAmphitheater at CyreneCyrenehttps://pleiades.stoa.org/places/373778amphitheaterimperial1...Libyacrete-et-cyrenaica32.728.821.85080832.824526550
2grumentoAmphitheaterAmphitheater at GrumentoGrumentoGrumentumhttps://pleiades.stoa.org/places/44260392amphitheaterimperial1...Italyregio-iii6040907015.91157940.287640579
3albaFucensAmphitheaterAmphitheater at Alba FucensAlba FucensAlba Fucenshttps://pleiades.stoa.org/places/413005amphitheaterjulio-claudian1...Italyregio-iv64371037613.41228942.077200977
4lambaesisAmphitheaterAmphitheater at LambaesisLambaesishttps://pleiades.stoa.org/places/334570amphitheatersecond-century1...Algeriamauretania6855104.6946.25993535.4892471170
5mactarisAmphitheaterAmphitheater at MactarisMactarishttps://pleiades.stoa.org/places/324774119amphitheatersecond-century1...Tunisiaproconsularis38.424.863.249.69.20667335.855628911
6sbeitlaAmphitheaterAmphitheater at SbeitlaSbeitlaSufetulahttps://pleiades.stoa.org/places/324816113amphitheatersecond-century1...Tunisiaproconsularis473772609.11458235.243617556
7tebessaAmphitheaterAmphitheater at ThevesteTebessaThevestehttps://pleiades.stoa.org/places/324831amphitheaterflavian1...Algeriaproconsularis52.839.583708.12380935.401171877
8susaAmphitheaterAmphitheater at SusaSusaSegusiohttps://pleiades.stoa.org/places/16791915amphitheaterimperial1...Italyalpes-cottiae443660527.04497045.133185546
9sophiaAmphitheaterAmphitheater at SophiaSophiaUlpia Serdicahttps://pleiades.stoa.org/places/207439amphitheaterpost-severan0...Bulgariathracia60.54323.32865042.697178554
10rodezAmphitheaterAmphitheater at RodezRodezSegodunumhttps://pleiades.stoa.org/places/13857922amphitheaterimperial1...Franceaquitania2.57116244.353698611
11sanBenedettoDeiMarsiAmphitheaterAmphitheater at San Benedetto dei MarsiSan BenedettoMarruviumhttps://pleiades.stoa.org/places/432927amphitheaterimperial1...Italyregio-iv10113.62750542.006504683
12sanVittorinoAmphitheaterAmphitheater at San VittorinoSan VittorinoAmiternumhttps://pleiades.stoa.org/places/413013amphitheaterfirst-century1...Italyregio-iv6442906813.30602742.400531672
13sisapoAmphitheaterAmphitheater at SisapoSisapohttps://pleiades.stoa.org/places/266043amphitheaterimperial1...Spainbaetica-4.51666738.645833659
14leonAmphitheaterAmphitheater at LeónLeónLegiohttps://pleiades.stoa.org/places/236512amphitheaterimperial1...Spaintarraconensis-5.56694442.598889846
15bernAmphitheaterAmphitheater at BernBernBremodorumhttps://pleiades.stoa.org/places/17747142amphitheaterimperial1...Switzerlandgermania-superior27.623.542.638.57.45117946.975715547
16bostraAmphitheaterAmphitheater at BosraBosraNova Trajana Bostrahttps://pleiades.stoa.org/places/678073amphitheaterimperial1...Syriasyria36.47984432.517923845
17siccaVeneriaAmphitheaterAmphitheater at Sicca VeneriaSicca VeneriaSicca Veneriahttps://pleiades.stoa.org/places/315152amphitheaterimperial1...Tunisiaproconsularis7050100808.71530236.185361748
18aostaAmphitheaterAmphitheater at AostaAostaAugusta Praetoriahttps://pleiades.stoa.org/places/383579amphitheaterjulio-claudian1...Italyregio-xi1501157.32348145.740914595
19aphrodisiasAmphitheaterLate Roman Amphitheater at AphrodisasAphrodisiasAphrodisiashttps://pleiades.stoa.org/places/638753arena-in-stadiumfourth-century0...Turkeyasia593028.72306137.712553526
20tusculumAmphitheaterTusculum AmphitheaterTusculumhttps://pleiades.stoa.org/places/423108amphitheatersecond-century1...Italyregio-i4930735412.70210341.798372582
21compsaAmphitheaterAmphitheater at CompsaCompsaCompsahttps://pleiades.stoa.org/places/442550amphitheaterrepublican1...Italyregio-ii15.33055640.870278572
\n", "

22 rows × 21 columns

\n", "
" ], "text/plain": [ " id title \\\n", "0 segobrigaAmphitheater Amphitheater at Segobriga \n", "1 cyreneAmphitheater Amphitheater at Cyrene \n", "2 grumentoAmphitheater Amphitheater at Grumento \n", "3 albaFucensAmphitheater Amphitheater at Alba Fucens \n", "4 lambaesisAmphitheater Amphitheater at Lambaesis \n", "5 mactarisAmphitheater Amphitheater at Mactaris \n", "6 sbeitlaAmphitheater Amphitheater at Sbeitla \n", "7 tebessaAmphitheater Amphitheater at Theveste \n", "8 susaAmphitheater Amphitheater at Susa \n", "9 sophiaAmphitheater Amphitheater at Sophia \n", "10 rodezAmphitheater Amphitheater at Rodez \n", "11 sanBenedettoDeiMarsiAmphitheater Amphitheater at San Benedetto dei Marsi \n", "12 sanVittorinoAmphitheater Amphitheater at San Vittorino \n", "13 sisapoAmphitheater Amphitheater at Sisapo \n", "14 leonAmphitheater Amphitheater at León \n", "15 bernAmphitheater Amphitheater at Bern \n", "16 bostraAmphitheater Amphitheater at Bosra \n", "17 siccaVeneriaAmphitheater Amphitheater at Sicca Veneria \n", "18 aostaAmphitheater Amphitheater at Aosta \n", "19 aphrodisiasAmphitheater Late Roman Amphitheater at Aphrodisas \n", "20 tusculumAmphitheater Tusculum Amphitheater \n", "21 compsaAmphitheater Amphitheater at Compsa \n", "\n", " label latintoponym \\\n", "0 Segobriga Segobriga \n", "1 Cyrene \n", "2 Grumento Grumentum \n", "3 Alba Fucens Alba Fucens \n", "4 Lambaesis \n", "5 Mactaris \n", "6 Sbeitla Sufetula \n", "7 Tebessa Theveste \n", "8 Susa Segusio \n", "9 Sophia Ulpia Serdica \n", "10 Rodez Segodunum \n", "11 San Benedetto Marruvium \n", "12 San Vittorino Amiternum \n", "13 Sisapo \n", "14 León Legio \n", "15 Bern Bremodorum \n", "16 Bosra Nova Trajana Bostra \n", "17 Sicca Veneria Sicca Veneria \n", "18 Aosta Augusta Praetoria \n", "19 Aphrodisias Aphrodisias \n", "20 Tusculum \n", "21 Compsa Compsa \n", "\n", " pleiades welchid golvinid \\\n", "0 https://pleiades.stoa.org/places/266036 88 \n", "1 https://pleiades.stoa.org/places/373778 \n", "2 https://pleiades.stoa.org/places/442603 92 \n", "3 https://pleiades.stoa.org/places/413005 \n", "4 https://pleiades.stoa.org/places/334570 \n", "5 https://pleiades.stoa.org/places/324774 119 \n", "6 https://pleiades.stoa.org/places/324816 113 \n", "7 https://pleiades.stoa.org/places/324831 \n", "8 https://pleiades.stoa.org/places/167919 15 \n", "9 https://pleiades.stoa.org/places/207439 \n", "10 https://pleiades.stoa.org/places/138579 22 \n", "11 https://pleiades.stoa.org/places/432927 \n", "12 https://pleiades.stoa.org/places/413013 \n", "13 https://pleiades.stoa.org/places/266043 \n", "14 https://pleiades.stoa.org/places/236512 \n", "15 https://pleiades.stoa.org/places/177471 42 \n", "16 https://pleiades.stoa.org/places/678073 \n", "17 https://pleiades.stoa.org/places/315152 \n", "18 https://pleiades.stoa.org/places/383579 \n", "19 https://pleiades.stoa.org/places/638753 \n", "20 https://pleiades.stoa.org/places/423108 \n", "21 https://pleiades.stoa.org/places/442550 \n", "\n", " buildingtype chronogroup secondcentury ... modcountry \\\n", "0 amphitheater flavian 1 ... Spain \n", "1 amphitheater imperial 1 ... Libya \n", "2 amphitheater imperial 1 ... Italy \n", "3 amphitheater julio-claudian 1 ... Italy \n", "4 amphitheater second-century 1 ... Algeria \n", "5 amphitheater second-century 1 ... Tunisia \n", "6 amphitheater second-century 1 ... Tunisia \n", "7 amphitheater flavian 1 ... Algeria \n", "8 amphitheater imperial 1 ... Italy \n", "9 amphitheater post-severan 0 ... Bulgaria \n", "10 amphitheater imperial 1 ... France \n", "11 amphitheater imperial 1 ... Italy \n", "12 amphitheater first-century 1 ... Italy \n", "13 amphitheater imperial 1 ... Spain \n", "14 amphitheater imperial 1 ... Spain \n", "15 amphitheater imperial 1 ... Switzerland \n", "16 amphitheater imperial 1 ... Syria \n", "17 amphitheater imperial 1 ... Tunisia \n", "18 amphitheater julio-claudian 1 ... Italy \n", "19 arena-in-stadium fourth-century 0 ... Turkey \n", "20 amphitheater second-century 1 ... Italy \n", "21 amphitheater republican 1 ... Italy \n", "\n", " romanregion arenamajor arenaminor extmajor extminor exteriorheight \\\n", "0 tarraconensis 47 31 75 64 \n", "1 crete-et-cyrenaica 32.7 28.8 \n", "2 regio-iii 60 40 90 70 \n", "3 regio-iv 64 37 103 76 \n", "4 mauretania 68 55 104.6 94 \n", "5 proconsularis 38.4 24.8 63.2 49.6 \n", "6 proconsularis 47 37 72 60 \n", "7 proconsularis 52.8 39.5 83 70 \n", "8 alpes-cottiae 44 36 60 52 \n", "9 thracia 60.5 43 \n", "10 aquitania \n", "11 regio-iv 101 \n", "12 regio-iv 64 42 90 68 \n", "13 baetica \n", "14 tarraconensis \n", "15 germania-superior 27.6 23.5 42.6 38.5 \n", "16 syria \n", "17 proconsularis 70 50 100 80 \n", "18 regio-xi 150 115 \n", "19 asia 59 30 \n", "20 regio-i 49 30 73 54 \n", "21 regio-ii \n", "\n", " longitude latitude elevation \n", "0 -2.813944 39.886018 817 \n", "1 21.850808 32.824526 550 \n", "2 15.911579 40.287640 579 \n", "3 13.412289 42.077200 977 \n", "4 6.259935 35.489247 1170 \n", "5 9.206673 35.855628 911 \n", "6 9.114582 35.243617 556 \n", "7 8.123809 35.401171 877 \n", "8 7.044970 45.133185 546 \n", "9 23.328650 42.697178 554 \n", "10 2.571162 44.353698 611 \n", "11 13.627505 42.006504 683 \n", "12 13.306027 42.400531 672 \n", "13 -4.516667 38.645833 659 \n", "14 -5.566944 42.598889 846 \n", "15 7.451179 46.975715 547 \n", "16 36.479844 32.517923 845 \n", "17 8.715302 36.185361 748 \n", "18 7.323481 45.740914 595 \n", "19 28.723061 37.712553 526 \n", "20 12.702103 41.798372 582 \n", "21 15.330556 40.870278 572 \n", "\n", "[22 rows x 21 columns]" ] }, "execution_count": 32, "metadata": {}, "output_type": "execute_result" } ], "source": [ "query = '''\n", "SELECT * \n", "FROM amphi\n", "WHERE elevation > 500;\n", "'''\n", "\n", "run_query(query)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Our condition can be string data too; in which case we put the string in quotation marks:" ] }, { "cell_type": "code", "execution_count": 33, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
idtitlelabellatintoponympleiadeswelchidgolvinidbuildingtypechronogroupsecondcentury...modcountryromanregionarenamajorarenaminorextmajorextminorexteriorheightlongitudelatitudeelevation
0arlesAmphitheaterAmphitheater at ArlesArlesArelatehttps://pleiades.stoa.org/places/148217154amphitheaterflavian1...Francenarbonensis47321361074.63111143.67777821
1romeFlavianAmphitheaterFlavian Amphitheater at RomeColosseumhttps://pleiades.stoa.org/places/423025152amphitheaterflavian1...Italyregio-i83481891565212.49226941.89016922
2newsteadAmphitheaterAmphitheater at NewsteadNewsteadTrimontiumhttps://pleiades.stoa.org/places/89304amphitheaterflavian1...United Kingdombritannia37237060-2.69190055.60260083
3pozzuoliFlavianAmphitheaterFlavian Amphitheater at PozzuoliPozzuoli (Flavian)Puteolihttps://pleiades.stoa.org/places/432815153amphitheaterflavian1...Italyregio-i74.84214911614.12531540.82592941
4segobrigaAmphitheaterAmphitheater at SegobrigaSegobrigaSegobrigahttps://pleiades.stoa.org/places/26603688amphitheaterflavian1...Spaintarraconensis47317564-2.81394439.886018817
5parisAmphitheaterAmphitheater at ParisParisLutetia Parisiorumhttps://pleiades.stoa.org/places/109126gallo-roman-amphitheaterflavian1...Francelugdunensis2.35285548.84509746
6nimesAmphitheaterAmphitheater at NimesNimesNemausushttps://pleiades.stoa.org/places/148142amphitheaterflavian1...Francenarbonensis69.138.4133.4101.44.35959943.83487650
7chesterAmphitheaterAmphitheater at ChesterChesterDeva Victrixhttps://pleiades.stoa.org/places/79420amphitheaterflavian1...United Kingdombritannia56.748.395.586.6-2.88692853.18907923
8arezzoAmphitheaterAmphitheater at ArezzoArezzoArretiumhttps://pleiades.stoa.org/places/413032amphitheaterflavian1...Italyregio-vii71.942.7109.48011.88037043.460491263
9londonAmphitheaterAmphitheater at LondonLondonLondiniumhttps://pleiades.stoa.org/places/79574amphitheaterflavian1...United Kingdombritannia604010085-0.09213651.51552229
10urbisagliaAmphitheaterAmphitheater at Urbs SalviaUrbs SalviaUrbs Sagliahttps://pleiades.stoa.org/places/413364amphitheaterflavian1...Italyregio-v5935987113.38722443.200877219
11vindonissaAmphitheaterAmphitheater at VindonissaVindonissahttps://pleiades.stoa.org/places/17766117amphitheaterflavian1...Switzerlandgermania-superior6451112988.21360547.476290361
12frejusAmphitheaterAmphitheater FréjusFréjusForum Juliihttps://pleiades.stoa.org/places/157836amphitheaterflavian1...Francenarbonensis67.739.7113.785.76.72871043.43446012
13tebessaAmphitheaterAmphitheater at ThevesteTebessaThevestehttps://pleiades.stoa.org/places/324831amphitheaterflavian1...Algeriaproconsularis52.839.583708.12380935.401171877
14aquileiaAmphitheaterAmphitheater at AquileiaAquileiaAquileiahttps://pleiades.stoa.org/places/187290amphitheaterflavian1...Italyregio-x724614211813.37071345.7682392
15terminiImereseAmphitheaterAmphitheater at Termini ImereseTerminiThermae Himeraehttps://pleiades.stoa.org/places/462513164amphitheaterflavian1...Italysicilia5330987513.69518637.98369676
16bolsenaAmphitheaterAmphitheater at BolsenaBolsenaVolsinii Novihttps://pleiades.stoa.org/places/413389amphitheaterflavian1...Italyregio-vii11.99015842.650243417
17narbonneAmphitheaterAmphitheater at NarbonneNarbonneNarbohttps://pleiades.stoa.org/places/246347amphitheaterflavian1...Francenarbonensis7546.6121.693.23.01018143.18487314
18capuaImperialAmphitheaterImperial Amphitheater at CapuaCapua IICapuahttps://pleiades.stoa.org/places/432754amphitheaterflavian1...Italyregio-i76.1245.8316513514.25008941.08593534
19dorchesterAmphitheaterAmphitheater at Dorchester / Maumbury HengeMaumburyDurnovariahttps://pleiades.stoa.org/places/79431amphitheaterflavian1...United Kingdombritannia58478077-2.44024650.70804478
20autunAmphitheaterAmphitheater at AutunAutunAugustodunumhttps://pleiades.stoa.org/places/177460157amphitheaterflavian1...Francelugdunensis74491541304.31085646.953003317
21bobadelaAmphitheaterAmphitheater at BobadelaBobadelaElbocorishttps://pleiades.stoa.org/places/236458amphitheaterflavian1...Portugallusitania5040-7.89357240.361088383
22chichesterAmphitheaterAmphitheater at ChichesterChichesterNoviomagus Reginorumhttps://pleiades.stoa.org/places/79622amphitheaterflavian1...United Kingdombritannia7060-0.77118650.83539414
\n", "

23 rows × 21 columns

\n", "
" ], "text/plain": [ " id title \\\n", "0 arlesAmphitheater Amphitheater at Arles \n", "1 romeFlavianAmphitheater Flavian Amphitheater at Rome \n", "2 newsteadAmphitheater Amphitheater at Newstead \n", "3 pozzuoliFlavianAmphitheater Flavian Amphitheater at Pozzuoli \n", "4 segobrigaAmphitheater Amphitheater at Segobriga \n", "5 parisAmphitheater Amphitheater at Paris \n", "6 nimesAmphitheater Amphitheater at Nimes \n", "7 chesterAmphitheater Amphitheater at Chester \n", "8 arezzoAmphitheater Amphitheater at Arezzo \n", "9 londonAmphitheater Amphitheater at London \n", "10 urbisagliaAmphitheater Amphitheater at Urbs Salvia \n", "11 vindonissaAmphitheater Amphitheater at Vindonissa \n", "12 frejusAmphitheater Amphitheater Fréjus \n", "13 tebessaAmphitheater Amphitheater at Theveste \n", "14 aquileiaAmphitheater Amphitheater at Aquileia \n", "15 terminiImereseAmphitheater Amphitheater at Termini Imerese \n", "16 bolsenaAmphitheater Amphitheater at Bolsena \n", "17 narbonneAmphitheater Amphitheater at Narbonne \n", "18 capuaImperialAmphitheater Imperial Amphitheater at Capua \n", "19 dorchesterAmphitheater Amphitheater at Dorchester / Maumbury Henge \n", "20 autunAmphitheater Amphitheater at Autun \n", "21 bobadelaAmphitheater Amphitheater at Bobadela \n", "22 chichesterAmphitheater Amphitheater at Chichester \n", "\n", " label latintoponym \\\n", "0 Arles Arelate \n", "1 Colosseum \n", "2 Newstead Trimontium \n", "3 Pozzuoli (Flavian) Puteoli \n", "4 Segobriga Segobriga \n", "5 Paris Lutetia Parisiorum \n", "6 Nimes Nemausus \n", "7 Chester Deva Victrix \n", "8 Arezzo Arretium \n", "9 London Londinium \n", "10 Urbs Salvia Urbs Saglia \n", "11 Vindonissa \n", "12 Fréjus Forum Julii \n", "13 Tebessa Theveste \n", "14 Aquileia Aquileia \n", "15 Termini Thermae Himerae \n", "16 Bolsena Volsinii Novi \n", "17 Narbonne Narbo \n", "18 Capua II Capua \n", "19 Maumbury Durnovaria \n", "20 Autun Augustodunum \n", "21 Bobadela Elbocoris \n", "22 Chichester Noviomagus Reginorum \n", "\n", " pleiades welchid golvinid \\\n", "0 https://pleiades.stoa.org/places/148217 154 \n", "1 https://pleiades.stoa.org/places/423025 152 \n", "2 https://pleiades.stoa.org/places/89304 \n", "3 https://pleiades.stoa.org/places/432815 153 \n", "4 https://pleiades.stoa.org/places/266036 88 \n", "5 https://pleiades.stoa.org/places/109126 \n", "6 https://pleiades.stoa.org/places/148142 \n", "7 https://pleiades.stoa.org/places/79420 \n", "8 https://pleiades.stoa.org/places/413032 \n", "9 https://pleiades.stoa.org/places/79574 \n", "10 https://pleiades.stoa.org/places/413364 \n", "11 https://pleiades.stoa.org/places/177661 17 \n", "12 https://pleiades.stoa.org/places/157836 \n", "13 https://pleiades.stoa.org/places/324831 \n", "14 https://pleiades.stoa.org/places/187290 \n", "15 https://pleiades.stoa.org/places/462513 164 \n", "16 https://pleiades.stoa.org/places/413389 \n", "17 https://pleiades.stoa.org/places/246347 \n", "18 https://pleiades.stoa.org/places/432754 \n", "19 https://pleiades.stoa.org/places/79431 \n", "20 https://pleiades.stoa.org/places/177460 157 \n", "21 https://pleiades.stoa.org/places/236458 \n", "22 https://pleiades.stoa.org/places/79622 \n", "\n", " buildingtype chronogroup secondcentury ... \\\n", "0 amphitheater flavian 1 ... \n", "1 amphitheater flavian 1 ... \n", "2 amphitheater flavian 1 ... \n", "3 amphitheater flavian 1 ... \n", "4 amphitheater flavian 1 ... \n", "5 gallo-roman-amphitheater flavian 1 ... \n", "6 amphitheater flavian 1 ... \n", "7 amphitheater flavian 1 ... \n", "8 amphitheater flavian 1 ... \n", "9 amphitheater flavian 1 ... \n", "10 amphitheater flavian 1 ... \n", "11 amphitheater flavian 1 ... \n", "12 amphitheater flavian 1 ... \n", "13 amphitheater flavian 1 ... \n", "14 amphitheater flavian 1 ... \n", "15 amphitheater flavian 1 ... \n", "16 amphitheater flavian 1 ... \n", "17 amphitheater flavian 1 ... \n", "18 amphitheater flavian 1 ... \n", "19 amphitheater flavian 1 ... \n", "20 amphitheater flavian 1 ... \n", "21 amphitheater flavian 1 ... \n", "22 amphitheater flavian 1 ... \n", "\n", " modcountry romanregion arenamajor arenaminor extmajor extminor \\\n", "0 France narbonensis 47 32 136 107 \n", "1 Italy regio-i 83 48 189 156 \n", "2 United Kingdom britannia 37 23 70 60 \n", "3 Italy regio-i 74.8 42 149 116 \n", "4 Spain tarraconensis 47 31 75 64 \n", "5 France lugdunensis \n", "6 France narbonensis 69.1 38.4 133.4 101.4 \n", "7 United Kingdom britannia 56.7 48.3 95.5 86.6 \n", "8 Italy regio-vii 71.9 42.7 109.4 80 \n", "9 United Kingdom britannia 60 40 100 85 \n", "10 Italy regio-v 59 35 98 71 \n", "11 Switzerland germania-superior 64 51 112 98 \n", "12 France narbonensis 67.7 39.7 113.7 85.7 \n", "13 Algeria proconsularis 52.8 39.5 83 70 \n", "14 Italy regio-x 72 46 142 118 \n", "15 Italy sicilia 53 30 98 75 \n", "16 Italy regio-vii \n", "17 France narbonensis 75 46.6 121.6 93.2 \n", "18 Italy regio-i 76.12 45.83 165 135 \n", "19 United Kingdom britannia 58 47 80 77 \n", "20 France lugdunensis 74 49 154 130 \n", "21 Portugal lusitania 50 40 \n", "22 United Kingdom britannia 70 60 \n", "\n", " exteriorheight longitude latitude elevation \n", "0 4.631111 43.677778 21 \n", "1 52 12.492269 41.890169 22 \n", "2 -2.691900 55.602600 83 \n", "3 14.125315 40.825929 41 \n", "4 -2.813944 39.886018 817 \n", "5 2.352855 48.845097 46 \n", "6 4.359599 43.834876 50 \n", "7 -2.886928 53.189079 23 \n", "8 11.880370 43.460491 263 \n", "9 -0.092136 51.515522 29 \n", "10 13.387224 43.200877 219 \n", "11 8.213605 47.476290 361 \n", "12 6.728710 43.434460 12 \n", "13 8.123809 35.401171 877 \n", "14 13.370713 45.768239 2 \n", "15 13.695186 37.983696 76 \n", "16 11.990158 42.650243 417 \n", "17 3.010181 43.184873 14 \n", "18 14.250089 41.085935 34 \n", "19 -2.440246 50.708044 78 \n", "20 4.310856 46.953003 317 \n", "21 -7.893572 40.361088 383 \n", "22 -0.771186 50.835394 14 \n", "\n", "[23 rows x 21 columns]" ] }, "execution_count": 33, "metadata": {}, "output_type": "execute_result" } ], "source": [ "query = '''\n", "SELECT * \n", "FROM amphi\n", "WHERE chronogroup = \"flavian\";\n", "'''\n", "\n", "run_query(query)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Can you write a query that pulls only the Flavian amphitheatres in France? Hint: you'll need the AND command." ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Adding some maths\n", "\n", "How many such amphitheatres are there? This is where you'd use the COUNT command. Let's count up the number of amphitheatres from the second century." ] }, { "cell_type": "code", "execution_count": 35, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
COUNT(*)
050
\n", "
" ], "text/plain": [ " COUNT(*)\n", "0 50" ] }, "execution_count": 35, "metadata": {}, "output_type": "execute_result" } ], "source": [ "query = '''\n", "SELECT COUNT(*)\n", "FROM amphi\n", "WHERE chronogroup = \"second-century\";\n", "'''\n", "\n", "run_query(query)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "We can rename that result like so:" ] }, { "cell_type": "code", "execution_count": 36, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
Total Count of Second Century Amphitheatres in the DB
050
\n", "
" ], "text/plain": [ " Total Count of Second Century Amphitheatres in the DB\n", "0 50 " ] }, "execution_count": 36, "metadata": {}, "output_type": "execute_result" } ], "source": [ "query = '''\n", "SELECT COUNT(*) AS \"Total Count of Second Century Amphitheatres in the DB\"\n", "FROM amphi\n", "WHERE chronogroup = \"second-century\";\n", "'''\n", "\n", "run_query(query)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ " SUM, AVG, MIN and MAX \n", " \n", " What was the average capacity?" ] }, { "cell_type": "code", "execution_count": 37, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
Average Capacity
05791.476923
\n", "
" ], "text/plain": [ " Average Capacity\n", "0 5791.476923" ] }, "execution_count": 37, "metadata": {}, "output_type": "execute_result" } ], "source": [ "query = '''\n", "SELECT AVG(capacity) AS \"Average Capacity\"\n", "FROM amphi;\n", "'''\n", "\n", "run_query(query)" ] }, { "cell_type": "code", "execution_count": 85, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
Average Length
032.982759
\n", "
" ], "text/plain": [ " Average Length\n", "0 32.982759" ] }, "execution_count": 85, "metadata": {}, "output_type": "execute_result" } ], "source": [ "query = '''\n", "SELECT AVG(arenamajor) AS \"Average Length\"\n", "FROM amphi;\n", "'''\n", "\n", "run_query(query)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "We can group rows by one value versus another to see how they compare. Is there a difference in average length of the long axis in Julio-Claudian versus Flavian amphitheatres?" ] }, { "cell_type": "code", "execution_count": 45, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
chronogroupAverage Length
0post-severan62.750000
1fourth-century59.000000
2neronian57.000000
3augustan56.125000
4flavian54.266087
5late-second-century46.333333
6hadrianic45.626667
7third-century43.500000
8julio-claudian43.273571
9second-century42.856800
10severan42.450000
11first-century42.227778
12imperial22.010092
13caesarean19.333333
14republican10.250000
15late-1st-early-2nd0.000000
\n", "
" ], "text/plain": [ " chronogroup Average Length\n", "0 post-severan 62.750000\n", "1 fourth-century 59.000000\n", "2 neronian 57.000000\n", "3 augustan 56.125000\n", "4 flavian 54.266087\n", "5 late-second-century 46.333333\n", "6 hadrianic 45.626667\n", "7 third-century 43.500000\n", "8 julio-claudian 43.273571\n", "9 second-century 42.856800\n", "10 severan 42.450000\n", "11 first-century 42.227778\n", "12 imperial 22.010092\n", "13 caesarean 19.333333\n", "14 republican 10.250000\n", "15 late-1st-early-2nd 0.000000" ] }, "execution_count": 45, "metadata": {}, "output_type": "execute_result" } ], "source": [ "query = '''\n", "SELECT chronogroup, AVG(arenamajor) AS \"Average Length\"\n", "FROM amphi\n", "GROUP BY chronogroup\n", "ORDER BY \"Average Length\" DESC;\n", "'''\n", "\n", "run_query(query)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## JOIN\n", "\n", "Now let's tell the database how the two tables are joined together. The `label` field in the `amphi` table contains the modern day description of the location of amphitheatres, and the `title` field in the `aqua` table contains a description of the modern day location of the aqueducts. Normally, when we join two tables, we want to perform the join on columns that are keyed together. In a sales database for instance there might a table of `orders` and another for `shipping address`, and each one contains a `customer_id` column. In such a case, we use `=` to say \n", "\n", "```\n", "FROM orders \n", "INNER JOIN shipping_address \n", "ON orders.customer_id = shipping_addres.customer_id\n", "```\n", "\n", "But archaeological data is rarely so straightforward. In our two tables here, we have to pattern match in order to make the two fields join up - there is no 'primary key' to help us know that a row in one table is talking about the same thing in another table. Instead of `=` we're going to use the [LIKE command](http://www.sqlitetutorial.net/sqlite-like/). LIKE uses two different kinds of wildcards, `%` and `_`. \n", "\n", "+ % matches any sequence of zero or more characters\n", "+ _ matches any single character.\n", "\n", "If we said, `LIKE 'Arl%'` we would find matches on Arles, Arlate and so on. Placing the `%` on either side would find strings that _contain_ Arl. In our case, we want to find instances in the `aqua` table's `title` column that contain strings from the `amphi` table's `label` column.\n", "\n", "To join to our first table all matching rows from our second, we do an '[inner join](http://www.sqlitetutorial.net/sqlite-inner-join/)'. The syntax generally is:\n", "\n", "```\n", "SELECT relevant-columns # these will be the columns displayed in your result\n", "FROM tableA # the table to join\n", "INNER JOIN tableB # with this table\n", "ON tableA.title = tableB.label # by these criteria\n", "```\n", "\n", "The query below displays the result of joining the `aqua` table to the `amphi` table using the `labels` column data as the middle piece in a wildcard: `%string%`, but uses the || characters to indicate we want the string values, not the literal characters amphi.label.\n" ] }, { "cell_type": "code", "execution_count": 84, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
idlabelidentifier
0mitilineAmphitheaterMitilinehttp://dare.ht.lu.se/places/43530
\n", "
" ], "text/plain": [ " id label identifier\n", "0 mitilineAmphitheater Mitiline http://dare.ht.lu.se/places/43530" ] }, "execution_count": 84, "metadata": {}, "output_type": "execute_result" } ], "source": [ "query = '''\n", "SELECT amphi.id, amphi.label, aqua.identifier\n", "FROM aqua \n", "INNER JOIN amphi\n", "ON aqua.title LIKE '%' || amphi.label || '%';\n", "'''\n", "\n", "\n", "run_query(query)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "* true confession: There is not an amphitheatre at Mitilene to our knowledge. We added one row to the table manually so that this join example would work properly. (When we retrieved the data from the Pelagios api, we only downloaded the first page of results, in order to keep the notebook light)." ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "We've also created a small notebook that shows how to import a database into R, and to build queries for it. Once you've done that, you can pass the results as a dataframe and use the full power of R to analyze. The notebook [is here](SQLite Database and R.ipynb).\n", "\n", "This is also possible in python, of course, and we have an example [notebook here](visualizing%20results%20of%20sql%20query%20in%20python.ipynb)" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [] } ], "metadata": { "kernelspec": { "display_name": "Python 3", "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.6.5" } }, "nbformat": 4, "nbformat_minor": 2 }