-
-
Save ischurov/f68b62a1e0edba96a0129c108ea3b480 to your computer and use it in GitHub Desktop.
Lesson23.ipynb
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| { | |
| "cells": [ | |
| { | |
| "metadata": {}, | |
| "id": "naval-confidentiality", | |
| "cell_type": "markdown", | |
| "source": "## SQL " | |
| }, | |
| { | |
| "metadata": { | |
| "trusted": true | |
| }, | |
| "id": "general-diana", | |
| "cell_type": "code", | |
| "source": "import sqlite3", | |
| "execution_count": 1, | |
| "outputs": [] | |
| }, | |
| { | |
| "metadata": { | |
| "trusted": true | |
| }, | |
| "id": "superior-surgeon", | |
| "cell_type": "code", | |
| "source": "conn = sqlite3.connect(\"database.sqlite\")", | |
| "execution_count": 2, | |
| "outputs": [] | |
| }, | |
| { | |
| "metadata": { | |
| "trusted": true | |
| }, | |
| "id": "abroad-performance", | |
| "cell_type": "code", | |
| "source": "c = conn.cursor()", | |
| "execution_count": 3, | |
| "outputs": [] | |
| }, | |
| { | |
| "metadata": { | |
| "trusted": true | |
| }, | |
| "id": "tutorial-unemployment", | |
| "cell_type": "code", | |
| "source": "c.execute(\n \"\"\"\nCREATE TABLE gradebook (\nid integer PRIMARY KEY,\nfirst_name text,\nlast_name text,\ngrade integer\n)\n\"\"\"\n)", | |
| "execution_count": 4, | |
| "outputs": [ | |
| { | |
| "output_type": "execute_result", | |
| "execution_count": 4, | |
| "data": { | |
| "text/plain": "<sqlite3.Cursor at 0x1066167c0>" | |
| }, | |
| "metadata": {} | |
| } | |
| ] | |
| }, | |
| { | |
| "metadata": { | |
| "trusted": true | |
| }, | |
| "id": "limiting-gross", | |
| "cell_type": "code", | |
| "source": "c.execute(\n \"\"\"\nINSERT INTO gradebook VALUES\n(1, \"Alice\", \"Smith\", 4),\n(2, \"Bob\", \"Smith\", 5)\n\"\"\"\n)", | |
| "execution_count": 5, | |
| "outputs": [ | |
| { | |
| "output_type": "execute_result", | |
| "execution_count": 5, | |
| "data": { | |
| "text/plain": "<sqlite3.Cursor at 0x1066167c0>" | |
| }, | |
| "metadata": {} | |
| } | |
| ] | |
| }, | |
| { | |
| "metadata": { | |
| "trusted": true | |
| }, | |
| "id": "pregnant-collector", | |
| "cell_type": "code", | |
| "source": "c.execute(\n \"\"\"\nSELECT * FROM gradebook;\n\"\"\"\n).fetchall()", | |
| "execution_count": 6, | |
| "outputs": [ | |
| { | |
| "output_type": "execute_result", | |
| "execution_count": 6, | |
| "data": { | |
| "text/plain": "[(1, 'Alice', 'Smith', 4), (2, 'Bob', 'Smith', 5)]" | |
| }, | |
| "metadata": {} | |
| } | |
| ] | |
| }, | |
| { | |
| "metadata": { | |
| "trusted": true | |
| }, | |
| "id": "steady-lafayette", | |
| "cell_type": "code", | |
| "source": "c.execute(\n \"\"\"\nINSERT INTO gradebook (first_name, last_name, grade) \nVALUES\n(\"Claudia\", \"Johnson\", 3)\n\"\"\"\n)", | |
| "execution_count": 7, | |
| "outputs": [ | |
| { | |
| "output_type": "execute_result", | |
| "execution_count": 7, | |
| "data": { | |
| "text/plain": "<sqlite3.Cursor at 0x1066167c0>" | |
| }, | |
| "metadata": {} | |
| } | |
| ] | |
| }, | |
| { | |
| "metadata": { | |
| "trusted": true | |
| }, | |
| "id": "according-lightning", | |
| "cell_type": "code", | |
| "source": "c.execute(\n \"\"\"\nSELECT * FROM gradebook;\n\"\"\"\n).fetchall()", | |
| "execution_count": 8, | |
| "outputs": [ | |
| { | |
| "output_type": "execute_result", | |
| "execution_count": 8, | |
| "data": { | |
| "text/plain": "[(1, 'Alice', 'Smith', 4),\n (2, 'Bob', 'Smith', 5),\n (3, 'Claudia', 'Johnson', 3)]" | |
| }, | |
| "metadata": {} | |
| } | |
| ] | |
| }, | |
| { | |
| "metadata": { | |
| "trusted": true | |
| }, | |
| "id": "purple-deposit", | |
| "cell_type": "code", | |
| "source": "import pandas as pd", | |
| "execution_count": 9, | |
| "outputs": [] | |
| }, | |
| { | |
| "metadata": { | |
| "trusted": true | |
| }, | |
| "id": "subject-scratch", | |
| "cell_type": "code", | |
| "source": "pd.read_sql(\n \"\"\"\nSELECT * FROM gradebook;\n\"\"\",\n conn,\n)", | |
| "execution_count": 10, | |
| "outputs": [ | |
| { | |
| "output_type": "execute_result", | |
| "execution_count": 10, | |
| "data": { | |
| "text/plain": " id first_name last_name grade\n0 1 Alice Smith 4\n1 2 Bob Smith 5\n2 3 Claudia Johnson 3", | |
| "text/html": "<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>id</th>\n <th>first_name</th>\n <th>last_name</th>\n <th>grade</th>\n </tr>\n </thead>\n <tbody>\n <tr>\n <th>0</th>\n <td>1</td>\n <td>Alice</td>\n <td>Smith</td>\n <td>4</td>\n </tr>\n <tr>\n <th>1</th>\n <td>2</td>\n <td>Bob</td>\n <td>Smith</td>\n <td>5</td>\n </tr>\n <tr>\n <th>2</th>\n <td>3</td>\n <td>Claudia</td>\n <td>Johnson</td>\n <td>3</td>\n </tr>\n </tbody>\n</table>\n</div>" | |
| }, | |
| "metadata": {} | |
| } | |
| ] | |
| }, | |
| { | |
| "metadata": { | |
| "trusted": true | |
| }, | |
| "id": "olympic-straight", | |
| "cell_type": "code", | |
| "source": "df = pd.read_csv(\n \"https://github.com/devashishpatel/IMDB-Top-5000/raw/master/movie_metadata.csv\"\n)", | |
| "execution_count": 11, | |
| "outputs": [] | |
| }, | |
| { | |
| "metadata": { | |
| "trusted": true | |
| }, | |
| "id": "opposite-israeli", | |
| "cell_type": "code", | |
| "source": "df.to_sql(\"movies\", conn)", | |
| "execution_count": 12, | |
| "outputs": [] | |
| }, | |
| { | |
| "metadata": { | |
| "trusted": true | |
| }, | |
| "id": "cognitive-electronics", | |
| "cell_type": "code", | |
| "source": "c.execute(\n \"\"\"\nSELECT * FROM movies\nLIMIT 1\n\"\"\"\n).fetchall()", | |
| "execution_count": 13, | |
| "outputs": [ | |
| { | |
| "output_type": "execute_result", | |
| "execution_count": 13, | |
| "data": { | |
| "text/plain": "[(0,\n 'Color',\n 'James Cameron',\n 723.0,\n 178.0,\n 0.0,\n 855.0,\n 'Joel David Moore',\n 1000.0,\n 760505847.0,\n 'Action|Adventure|Fantasy|Sci-Fi',\n 'CCH Pounder',\n 'Avatar\\xa0',\n 886204,\n 4834,\n 'Wes Studi',\n 0.0,\n 'avatar|future|marine|native|paraplegic',\n 'http://www.imdb.com/title/tt0499549/?ref_=fn_tt_tt_1',\n 3054.0,\n 'English',\n 'USA',\n 'PG-13',\n 237000000.0,\n 2009.0,\n 936.0,\n 7.9,\n 1.78,\n 33000)]" | |
| }, | |
| "metadata": {} | |
| } | |
| ] | |
| }, | |
| { | |
| "metadata": { | |
| "trusted": true | |
| }, | |
| "id": "experienced-passage", | |
| "cell_type": "code", | |
| "source": "c.execute(\n \"\"\"\nSELECT * FROM sqlite_master WHERE type='table';\n\"\"\"\n).fetchall()", | |
| "execution_count": 14, | |
| "outputs": [ | |
| { | |
| "output_type": "execute_result", | |
| "execution_count": 14, | |
| "data": { | |
| "text/plain": "[('table',\n 'gradebook',\n 'gradebook',\n 2,\n 'CREATE TABLE gradebook (\\nid integer PRIMARY KEY,\\nfirst_name text,\\nlast_name text,\\ngrade integer\\n)'),\n ('table',\n 'movies',\n 'movies',\n 3,\n 'CREATE TABLE \"movies\" (\\n\"index\" INTEGER,\\n \"color\" TEXT,\\n \"director_name\" TEXT,\\n \"num_critic_for_reviews\" REAL,\\n \"duration\" REAL,\\n \"director_facebook_likes\" REAL,\\n \"actor_3_facebook_likes\" REAL,\\n \"actor_2_name\" TEXT,\\n \"actor_1_facebook_likes\" REAL,\\n \"gross\" REAL,\\n \"genres\" TEXT,\\n \"actor_1_name\" TEXT,\\n \"movie_title\" TEXT,\\n \"num_voted_users\" INTEGER,\\n \"cast_total_facebook_likes\" INTEGER,\\n \"actor_3_name\" TEXT,\\n \"facenumber_in_poster\" REAL,\\n \"plot_keywords\" TEXT,\\n \"movie_imdb_link\" TEXT,\\n \"num_user_for_reviews\" REAL,\\n \"language\" TEXT,\\n \"country\" TEXT,\\n \"content_rating\" TEXT,\\n \"budget\" REAL,\\n \"title_year\" REAL,\\n \"actor_2_facebook_likes\" REAL,\\n \"imdb_score\" REAL,\\n \"aspect_ratio\" REAL,\\n \"movie_facebook_likes\" INTEGER\\n)')]" | |
| }, | |
| "metadata": {} | |
| } | |
| ] | |
| }, | |
| { | |
| "metadata": { | |
| "trusted": true | |
| }, | |
| "id": "intense-grass", | |
| "cell_type": "code", | |
| "source": "c.execute(\n \"\"\"\nSELECT director_name, movie_title FROM movies\nLIMIT 1\n\"\"\"\n).fetchall()", | |
| "execution_count": 15, | |
| "outputs": [ | |
| { | |
| "output_type": "execute_result", | |
| "execution_count": 15, | |
| "data": { | |
| "text/plain": "[('James Cameron', 'Avatar\\xa0')]" | |
| }, | |
| "metadata": {} | |
| } | |
| ] | |
| }, | |
| { | |
| "metadata": { | |
| "trusted": true | |
| }, | |
| "id": "french-eating", | |
| "cell_type": "code", | |
| "source": "df[[\"director_name\", \"movie_title\"]].iloc[:1]", | |
| "execution_count": 16, | |
| "outputs": [ | |
| { | |
| "output_type": "execute_result", | |
| "execution_count": 16, | |
| "data": { | |
| "text/plain": " director_name movie_title\n0 James Cameron Avatar ", | |
| "text/html": "<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>director_name</th>\n <th>movie_title</th>\n </tr>\n </thead>\n <tbody>\n <tr>\n <th>0</th>\n <td>James Cameron</td>\n <td>Avatar</td>\n </tr>\n </tbody>\n</table>\n</div>" | |
| }, | |
| "metadata": {} | |
| } | |
| ] | |
| }, | |
| { | |
| "metadata": { | |
| "trusted": true | |
| }, | |
| "id": "breathing-mistress", | |
| "cell_type": "code", | |
| "source": "pd.read_sql(\n \"\"\"\nSeLeCt DIRECTOR_NAME, MoViE_TitlE FROM movies\nLIMIT 1\n\"\"\",\n conn,\n)", | |
| "execution_count": 17, | |
| "outputs": [ | |
| { | |
| "output_type": "execute_result", | |
| "execution_count": 17, | |
| "data": { | |
| "text/plain": " director_name movie_title\n0 James Cameron Avatar ", | |
| "text/html": "<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>director_name</th>\n <th>movie_title</th>\n </tr>\n </thead>\n <tbody>\n <tr>\n <th>0</th>\n <td>James Cameron</td>\n <td>Avatar</td>\n </tr>\n </tbody>\n</table>\n</div>" | |
| }, | |
| "metadata": {} | |
| } | |
| ] | |
| }, | |
| { | |
| "metadata": { | |
| "trusted": true | |
| }, | |
| "id": "emotional-aviation", | |
| "cell_type": "code", | |
| "source": "pd.read_sql(\n \"\"\"\nSELECT count(*) FROM movies\n\"\"\",\n conn,\n)", | |
| "execution_count": 18, | |
| "outputs": [ | |
| { | |
| "output_type": "execute_result", | |
| "execution_count": 18, | |
| "data": { | |
| "text/plain": " count(*)\n0 5043", | |
| "text/html": "<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>count(*)</th>\n </tr>\n </thead>\n <tbody>\n <tr>\n <th>0</th>\n <td>5043</td>\n </tr>\n </tbody>\n</table>\n</div>" | |
| }, | |
| "metadata": {} | |
| } | |
| ] | |
| }, | |
| { | |
| "metadata": { | |
| "trusted": true | |
| }, | |
| "id": "appreciated-halifax", | |
| "cell_type": "code", | |
| "source": "pd.read_sql(\n \"\"\"\nSELECT count(*), avg(duration), max(director_facebook_likes) FROM movies\n\"\"\",\n conn,\n)", | |
| "execution_count": 19, | |
| "outputs": [ | |
| { | |
| "output_type": "execute_result", | |
| "execution_count": 19, | |
| "data": { | |
| "text/plain": " count(*) avg(duration) max(director_facebook_likes)\n0 5043 107.201074 23000.0", | |
| "text/html": "<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>count(*)</th>\n <th>avg(duration)</th>\n <th>max(director_facebook_likes)</th>\n </tr>\n </thead>\n <tbody>\n <tr>\n <th>0</th>\n <td>5043</td>\n <td>107.201074</td>\n <td>23000.0</td>\n </tr>\n </tbody>\n</table>\n</div>" | |
| }, | |
| "metadata": {} | |
| } | |
| ] | |
| }, | |
| { | |
| "metadata": { | |
| "trusted": true | |
| }, | |
| "id": "distinct-theology", | |
| "cell_type": "code", | |
| "source": "pd.read_sql(\n \"\"\"\nSELECT movie_title, director_name FROM movies\nWHERE director_name like \"%Cameron\"\n\"\"\",\n conn,\n)", | |
| "execution_count": 20, | |
| "outputs": [ | |
| { | |
| "output_type": "execute_result", | |
| "execution_count": 20, | |
| "data": { | |
| "text/plain": " movie_title director_name\n0 Avatar James Cameron\n1 Titanic James Cameron\n2 Terminator 2: Judgment Day James Cameron\n3 True Lies James Cameron\n4 Cloudy with a Chance of Meatballs 2 Cody Cameron\n5 The Abyss James Cameron\n6 Aliens James Cameron\n7 The Terminator James Cameron", | |
| "text/html": "<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>movie_title</th>\n <th>director_name</th>\n </tr>\n </thead>\n <tbody>\n <tr>\n <th>0</th>\n <td>Avatar</td>\n <td>James Cameron</td>\n </tr>\n <tr>\n <th>1</th>\n <td>Titanic</td>\n <td>James Cameron</td>\n </tr>\n <tr>\n <th>2</th>\n <td>Terminator 2: Judgment Day</td>\n <td>James Cameron</td>\n </tr>\n <tr>\n <th>3</th>\n <td>True Lies</td>\n <td>James Cameron</td>\n </tr>\n <tr>\n <th>4</th>\n <td>Cloudy with a Chance of Meatballs 2</td>\n <td>Cody Cameron</td>\n </tr>\n <tr>\n <th>5</th>\n <td>The Abyss</td>\n <td>James Cameron</td>\n </tr>\n <tr>\n <th>6</th>\n <td>Aliens</td>\n <td>James Cameron</td>\n </tr>\n <tr>\n <th>7</th>\n <td>The Terminator</td>\n <td>James Cameron</td>\n </tr>\n </tbody>\n</table>\n</div>" | |
| }, | |
| "metadata": {} | |
| } | |
| ] | |
| }, | |
| { | |
| "metadata": { | |
| "trusted": true | |
| }, | |
| "id": "refined-junior", | |
| "cell_type": "code", | |
| "source": "pd.read_sql(\n \"\"\"\nSELECT avg(duration) FROM movies\nWHERE director_name like \"%Cameron\"\n\"\"\",\n conn,\n)", | |
| "execution_count": 21, | |
| "outputs": [ | |
| { | |
| "output_type": "execute_result", | |
| "execution_count": 21, | |
| "data": { | |
| "text/plain": " avg(duration)\n0 149.125", | |
| "text/html": "<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>avg(duration)</th>\n </tr>\n </thead>\n <tbody>\n <tr>\n <th>0</th>\n <td>149.125</td>\n </tr>\n </tbody>\n</table>\n</div>" | |
| }, | |
| "metadata": {} | |
| } | |
| ] | |
| }, | |
| { | |
| "metadata": { | |
| "trusted": true | |
| }, | |
| "id": "still-relay", | |
| "cell_type": "code", | |
| "source": "(df[lambda x: x[\"director_name\"].fillna(\"\").str.endswith(\"Cameron\")]\n [\"duration\"].mean())", | |
| "execution_count": 22, | |
| "outputs": [ | |
| { | |
| "output_type": "execute_result", | |
| "execution_count": 22, | |
| "data": { | |
| "text/plain": "149.125" | |
| }, | |
| "metadata": {} | |
| } | |
| ] | |
| }, | |
| { | |
| "metadata": { | |
| "trusted": true | |
| }, | |
| "id": "terminal-shark", | |
| "cell_type": "code", | |
| "source": "pd.read_sql(\n \"\"\"\nSELECT movie_title, duration FROM movies\nORDER BY duration DESC\nLIMIT 10\n\"\"\",\n conn,\n)", | |
| "execution_count": 23, | |
| "outputs": [ | |
| { | |
| "output_type": "execute_result", | |
| "execution_count": 23, | |
| "data": { | |
| "text/plain": " movie_title duration\n0 Trapped 511.0\n1 Carlos 334.0\n2 Blood In, Blood Out 330.0\n3 Heaven's Gate 325.0\n4 The Legend of Suriyothai 300.0\n5 Das Boot 293.0\n6 Apocalypse Now 289.0\n7 The Company 286.0\n8 Gods and Generals 280.0\n9 Gettysburg 271.0", | |
| "text/html": "<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>movie_title</th>\n <th>duration</th>\n </tr>\n </thead>\n <tbody>\n <tr>\n <th>0</th>\n <td>Trapped</td>\n <td>511.0</td>\n </tr>\n <tr>\n <th>1</th>\n <td>Carlos</td>\n <td>334.0</td>\n </tr>\n <tr>\n <th>2</th>\n <td>Blood In, Blood Out</td>\n <td>330.0</td>\n </tr>\n <tr>\n <th>3</th>\n <td>Heaven's Gate</td>\n <td>325.0</td>\n </tr>\n <tr>\n <th>4</th>\n <td>The Legend of Suriyothai</td>\n <td>300.0</td>\n </tr>\n <tr>\n <th>5</th>\n <td>Das Boot</td>\n <td>293.0</td>\n </tr>\n <tr>\n <th>6</th>\n <td>Apocalypse Now</td>\n <td>289.0</td>\n </tr>\n <tr>\n <th>7</th>\n <td>The Company</td>\n <td>286.0</td>\n </tr>\n <tr>\n <th>8</th>\n <td>Gods and Generals</td>\n <td>280.0</td>\n </tr>\n <tr>\n <th>9</th>\n <td>Gettysburg</td>\n <td>271.0</td>\n </tr>\n </tbody>\n</table>\n</div>" | |
| }, | |
| "metadata": {} | |
| } | |
| ] | |
| }, | |
| { | |
| "metadata": { | |
| "trusted": true | |
| }, | |
| "id": "silver-california", | |
| "cell_type": "code", | |
| "source": "pd.read_sql(\n \"\"\"\nSELECT country, avg(imdb_score) as avg_score FROM movies\nGROUP BY country\nORDER BY avg_score DESC\nLIMIT 10\n\"\"\",\n conn,\n)", | |
| "execution_count": 24, | |
| "outputs": [ | |
| { | |
| "output_type": "execute_result", | |
| "execution_count": 24, | |
| "data": { | |
| "text/plain": " country avg_score\n0 Kyrgyzstan 8.700000\n1 Libya 8.400000\n2 United Arab Emirates 8.200000\n3 Soviet Union 8.100000\n4 Egypt 8.100000\n5 Iran 7.725000\n6 Poland 7.620000\n7 Indonesia 7.600000\n8 Israel 7.525000\n9 Sweden 7.516667", | |
| "text/html": "<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>country</th>\n <th>avg_score</th>\n </tr>\n </thead>\n <tbody>\n <tr>\n <th>0</th>\n <td>Kyrgyzstan</td>\n <td>8.700000</td>\n </tr>\n <tr>\n <th>1</th>\n <td>Libya</td>\n <td>8.400000</td>\n </tr>\n <tr>\n <th>2</th>\n <td>United Arab Emirates</td>\n <td>8.200000</td>\n </tr>\n <tr>\n <th>3</th>\n <td>Soviet Union</td>\n <td>8.100000</td>\n </tr>\n <tr>\n <th>4</th>\n <td>Egypt</td>\n <td>8.100000</td>\n </tr>\n <tr>\n <th>5</th>\n <td>Iran</td>\n <td>7.725000</td>\n </tr>\n <tr>\n <th>6</th>\n <td>Poland</td>\n <td>7.620000</td>\n </tr>\n <tr>\n <th>7</th>\n <td>Indonesia</td>\n <td>7.600000</td>\n </tr>\n <tr>\n <th>8</th>\n <td>Israel</td>\n <td>7.525000</td>\n </tr>\n <tr>\n <th>9</th>\n <td>Sweden</td>\n <td>7.516667</td>\n </tr>\n </tbody>\n</table>\n</div>" | |
| }, | |
| "metadata": {} | |
| } | |
| ] | |
| }, | |
| { | |
| "metadata": { | |
| "trusted": true | |
| }, | |
| "id": "complex-sunglasses", | |
| "cell_type": "code", | |
| "source": "pd.read_sql(\n \"\"\"\nSELECT country, avg(imdb_score) as avg_score FROM movies\nGROUP BY country\nHAVING avg_score > 8\nORDER BY avg_score DESC\n\"\"\",\n conn,\n)", | |
| "execution_count": 26, | |
| "outputs": [ | |
| { | |
| "output_type": "execute_result", | |
| "execution_count": 26, | |
| "data": { | |
| "text/plain": " country avg_score\n0 Kyrgyzstan 8.7\n1 Libya 8.4\n2 United Arab Emirates 8.2\n3 Soviet Union 8.1\n4 Egypt 8.1", | |
| "text/html": "<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>country</th>\n <th>avg_score</th>\n </tr>\n </thead>\n <tbody>\n <tr>\n <th>0</th>\n <td>Kyrgyzstan</td>\n <td>8.7</td>\n </tr>\n <tr>\n <th>1</th>\n <td>Libya</td>\n <td>8.4</td>\n </tr>\n <tr>\n <th>2</th>\n <td>United Arab Emirates</td>\n <td>8.2</td>\n </tr>\n <tr>\n <th>3</th>\n <td>Soviet Union</td>\n <td>8.1</td>\n </tr>\n <tr>\n <th>4</th>\n <td>Egypt</td>\n <td>8.1</td>\n </tr>\n </tbody>\n</table>\n</div>" | |
| }, | |
| "metadata": {} | |
| } | |
| ] | |
| }, | |
| { | |
| "metadata": { | |
| "trusted": true | |
| }, | |
| "id": "loved-survivor", | |
| "cell_type": "code", | |
| "source": "c.execute(\"\"\"\nSELECT * FROM movies LIMIT 2;\n\"\"\").fetchall()", | |
| "execution_count": 27, | |
| "outputs": [ | |
| { | |
| "output_type": "execute_result", | |
| "execution_count": 27, | |
| "data": { | |
| "text/plain": "[(0,\n 'Color',\n 'James Cameron',\n 723.0,\n 178.0,\n 0.0,\n 855.0,\n 'Joel David Moore',\n 1000.0,\n 760505847.0,\n 'Action|Adventure|Fantasy|Sci-Fi',\n 'CCH Pounder',\n 'Avatar\\xa0',\n 886204,\n 4834,\n 'Wes Studi',\n 0.0,\n 'avatar|future|marine|native|paraplegic',\n 'http://www.imdb.com/title/tt0499549/?ref_=fn_tt_tt_1',\n 3054.0,\n 'English',\n 'USA',\n 'PG-13',\n 237000000.0,\n 2009.0,\n 936.0,\n 7.9,\n 1.78,\n 33000),\n (1,\n 'Color',\n 'Gore Verbinski',\n 302.0,\n 169.0,\n 563.0,\n 1000.0,\n 'Orlando Bloom',\n 40000.0,\n 309404152.0,\n 'Action|Adventure|Fantasy',\n 'Johnny Depp',\n \"Pirates of the Caribbean: At World's End\\xa0\",\n 471220,\n 48350,\n 'Jack Davenport',\n 0.0,\n 'goddess|marriage ceremony|marriage proposal|pirate|singapore',\n 'http://www.imdb.com/title/tt0449088/?ref_=fn_tt_tt_1',\n 1238.0,\n 'English',\n 'USA',\n 'PG-13',\n 300000000.0,\n 2007.0,\n 5000.0,\n 7.1,\n 2.35,\n 0)]" | |
| }, | |
| "metadata": {} | |
| } | |
| ] | |
| }, | |
| { | |
| "metadata": { | |
| "trusted": true | |
| }, | |
| "id": "stopped-hierarchy", | |
| "cell_type": "code", | |
| "source": "c.execute(\"\"\"\nSELECT * FROM movies where [index] = 1\n\"\"\").fetchall()", | |
| "execution_count": 28, | |
| "outputs": [ | |
| { | |
| "output_type": "execute_result", | |
| "execution_count": 28, | |
| "data": { | |
| "text/plain": "[(1,\n 'Color',\n 'Gore Verbinski',\n 302.0,\n 169.0,\n 563.0,\n 1000.0,\n 'Orlando Bloom',\n 40000.0,\n 309404152.0,\n 'Action|Adventure|Fantasy',\n 'Johnny Depp',\n \"Pirates of the Caribbean: At World's End\\xa0\",\n 471220,\n 48350,\n 'Jack Davenport',\n 0.0,\n 'goddess|marriage ceremony|marriage proposal|pirate|singapore',\n 'http://www.imdb.com/title/tt0449088/?ref_=fn_tt_tt_1',\n 1238.0,\n 'English',\n 'USA',\n 'PG-13',\n 300000000.0,\n 2007.0,\n 5000.0,\n 7.1,\n 2.35,\n 0)]" | |
| }, | |
| "metadata": {} | |
| } | |
| ] | |
| }, | |
| { | |
| "metadata": { | |
| "trusted": true | |
| }, | |
| "id": "blocked-corruption", | |
| "cell_type": "code", | |
| "source": "c.execute(\"\"\"\nUPDATE movies \nSET movie_title = \"Something\",\nduration = -1\nwhere [index] = 1\n\"\"\").fetchall()", | |
| "execution_count": 29, | |
| "outputs": [ | |
| { | |
| "output_type": "execute_result", | |
| "execution_count": 29, | |
| "data": { | |
| "text/plain": "[]" | |
| }, | |
| "metadata": {} | |
| } | |
| ] | |
| }, | |
| { | |
| "metadata": { | |
| "trusted": true | |
| }, | |
| "id": "urban-ghost", | |
| "cell_type": "code", | |
| "source": "c.execute(\"\"\"\nSELECT * FROM movies where [index] = 1\n\"\"\").fetchall()", | |
| "execution_count": 30, | |
| "outputs": [ | |
| { | |
| "output_type": "execute_result", | |
| "execution_count": 30, | |
| "data": { | |
| "text/plain": "[(1,\n 'Color',\n 'Gore Verbinski',\n 302.0,\n -1.0,\n 563.0,\n 1000.0,\n 'Orlando Bloom',\n 40000.0,\n 309404152.0,\n 'Action|Adventure|Fantasy',\n 'Johnny Depp',\n 'Something',\n 471220,\n 48350,\n 'Jack Davenport',\n 0.0,\n 'goddess|marriage ceremony|marriage proposal|pirate|singapore',\n 'http://www.imdb.com/title/tt0449088/?ref_=fn_tt_tt_1',\n 1238.0,\n 'English',\n 'USA',\n 'PG-13',\n 300000000.0,\n 2007.0,\n 5000.0,\n 7.1,\n 2.35,\n 0)]" | |
| }, | |
| "metadata": {} | |
| } | |
| ] | |
| }, | |
| { | |
| "metadata": { | |
| "trusted": true | |
| }, | |
| "id": "needed-movement", | |
| "cell_type": "code", | |
| "source": "pd.read_sql(\"\"\"\nSELECT * FROM movies where [index] = 1\n\"\"\", conn)", | |
| "execution_count": 31, | |
| "outputs": [ | |
| { | |
| "output_type": "execute_result", | |
| "execution_count": 31, | |
| "data": { | |
| "text/plain": " index color director_name num_critic_for_reviews duration \\\n0 1 Color Gore Verbinski 302.0 -1.0 \n\n director_facebook_likes actor_3_facebook_likes actor_2_name \\\n0 563.0 1000.0 Orlando Bloom \n\n actor_1_facebook_likes gross ... num_user_for_reviews language \\\n0 40000.0 309404152.0 ... 1238.0 English \n\n country content_rating budget title_year actor_2_facebook_likes \\\n0 USA PG-13 300000000.0 2007.0 5000.0 \n\n imdb_score aspect_ratio movie_facebook_likes \n0 7.1 2.35 0 \n\n[1 rows x 29 columns]", | |
| "text/html": "<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>index</th>\n <th>color</th>\n <th>director_name</th>\n <th>num_critic_for_reviews</th>\n <th>duration</th>\n <th>director_facebook_likes</th>\n <th>actor_3_facebook_likes</th>\n <th>actor_2_name</th>\n <th>actor_1_facebook_likes</th>\n <th>gross</th>\n <th>...</th>\n <th>num_user_for_reviews</th>\n <th>language</th>\n <th>country</th>\n <th>content_rating</th>\n <th>budget</th>\n <th>title_year</th>\n <th>actor_2_facebook_likes</th>\n <th>imdb_score</th>\n <th>aspect_ratio</th>\n <th>movie_facebook_likes</th>\n </tr>\n </thead>\n <tbody>\n <tr>\n <th>0</th>\n <td>1</td>\n <td>Color</td>\n <td>Gore Verbinski</td>\n <td>302.0</td>\n <td>-1.0</td>\n <td>563.0</td>\n <td>1000.0</td>\n <td>Orlando Bloom</td>\n <td>40000.0</td>\n <td>309404152.0</td>\n <td>...</td>\n <td>1238.0</td>\n <td>English</td>\n <td>USA</td>\n <td>PG-13</td>\n <td>300000000.0</td>\n <td>2007.0</td>\n <td>5000.0</td>\n <td>7.1</td>\n <td>2.35</td>\n <td>0</td>\n </tr>\n </tbody>\n</table>\n<p>1 rows × 29 columns</p>\n</div>" | |
| }, | |
| "metadata": {} | |
| } | |
| ] | |
| }, | |
| { | |
| "metadata": { | |
| "trusted": true | |
| }, | |
| "id": "rising-judge", | |
| "cell_type": "code", | |
| "source": "import requests\nr = requests.get(\"http://math-info.hse.ru/f/2018-19/nes-ds/gradebook.sqlite\")\nwith open('gradebook.sqlite', \"wb\") as f:\n f.write(r.content)", | |
| "execution_count": 32, | |
| "outputs": [] | |
| }, | |
| { | |
| "metadata": { | |
| "trusted": true | |
| }, | |
| "id": "robust-forty", | |
| "cell_type": "code", | |
| "source": "conn.close()", | |
| "execution_count": 33, | |
| "outputs": [] | |
| }, | |
| { | |
| "metadata": { | |
| "trusted": true | |
| }, | |
| "id": "composite-friendship", | |
| "cell_type": "code", | |
| "source": "conn = sqlite3.connect(\"gradebook.sqlite\")\n", | |
| "execution_count": 34, | |
| "outputs": [] | |
| }, | |
| { | |
| "metadata": { | |
| "trusted": true | |
| }, | |
| "cell_type": "code", | |
| "source": "c = conn.cursor()", | |
| "execution_count": 35, | |
| "outputs": [] | |
| }, | |
| { | |
| "metadata": { | |
| "trusted": true | |
| }, | |
| "id": "bottom-sitting", | |
| "cell_type": "code", | |
| "source": "c.execute(\n \"\"\"\nSELECT * FROM sqlite_master WHERE type='table';\n\"\"\"\n).fetchall()", | |
| "execution_count": 36, | |
| "outputs": [ | |
| { | |
| "output_type": "execute_result", | |
| "execution_count": 36, | |
| "data": { | |
| "text/plain": "[('table',\n 'courses',\n 'courses',\n 2,\n 'CREATE TABLE \"courses\" (\\n\"id\" INTEGER,\\n \"code\" TEXT,\\n \"year\" INTEGER,\\n \"active\" INTEGER\\n)'),\n ('table',\n 'submissions',\n 'submissions',\n 4,\n 'CREATE TABLE \"submissions\" (\\n\"id\" INTEGER,\\n \"assignment_id\" INTEGER,\\n \"user_id\" INTEGER,\\n \"grade\" REAL\\n)'),\n ('table',\n 'users',\n 'users',\n 5,\n 'CREATE TABLE \"users\" (\\n\"id\" INTEGER,\\n \"first_name\" TEXT,\\n \"last_name\" TEXT,\\n \"email\" TEXT\\n)'),\n ('table',\n 'course_user',\n 'course_user',\n 6,\n 'CREATE TABLE \"course_user\" (\\n\"course_id\" INTEGER,\\n \"user_id\" INTEGER\\n)'),\n ('table',\n 'assignments',\n 'assignments',\n 7,\n 'CREATE TABLE \"assignments\" (\\n\"id\" INTEGER,\\n \"course_id\" INTEGER,\\n \"code\" TEXT,\\n \"name\" TEXT,\\n \"max_grade\" INTEGER\\n)')]" | |
| }, | |
| "metadata": {} | |
| } | |
| ] | |
| }, | |
| { | |
| "metadata": { | |
| "trusted": true | |
| }, | |
| "cell_type": "code", | |
| "source": "pd.read_sql(\"\"\"\nSELECT * FROM \ncourses JOIN assignments \nON courses.id = assignments.course_id;\n\"\"\", conn)", | |
| "execution_count": 37, | |
| "outputs": [ | |
| { | |
| "output_type": "execute_result", | |
| "execution_count": 37, | |
| "data": { | |
| "text/plain": " id code year active id course_id code name \\\n0 1 nes-ds 2017 0 1 1 ps01 Intro to Python \n1 1 nes-ds 2017 0 4 1 ps01 Python, part 1 \n2 2 nes-ds 2018 1 2 2 ps01 Intro \n3 2 nes-ds 2018 1 3 2 ps02 Functions \n4 2 nes-ds 2018 1 5 2 ps03 Pandas \n5 2 nes-ds 2018 1 7 2 ps04 Advanced Python \n6 3 dj-prog 2018 1 6 3 ps03 Classes \n7 3 dj-prog 2018 1 8 3 ps01 Introduction \n8 4 python-all 2015 0 9 4 ps01 First class \n9 5 python-icef 2018 1 10 5 ps01 Welcome to Python \n\n max_grade \n0 10 \n1 22 \n2 12 \n3 8 \n4 10 \n5 32 \n6 15 \n7 10 \n8 7 \n9 8 ", | |
| "text/html": "<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>id</th>\n <th>code</th>\n <th>year</th>\n <th>active</th>\n <th>id</th>\n <th>course_id</th>\n <th>code</th>\n <th>name</th>\n <th>max_grade</th>\n </tr>\n </thead>\n <tbody>\n <tr>\n <th>0</th>\n <td>1</td>\n <td>nes-ds</td>\n <td>2017</td>\n <td>0</td>\n <td>1</td>\n <td>1</td>\n <td>ps01</td>\n <td>Intro to Python</td>\n <td>10</td>\n </tr>\n <tr>\n <th>1</th>\n <td>1</td>\n <td>nes-ds</td>\n <td>2017</td>\n <td>0</td>\n <td>4</td>\n <td>1</td>\n <td>ps01</td>\n <td>Python, part 1</td>\n <td>22</td>\n </tr>\n <tr>\n <th>2</th>\n <td>2</td>\n <td>nes-ds</td>\n <td>2018</td>\n <td>1</td>\n <td>2</td>\n <td>2</td>\n <td>ps01</td>\n <td>Intro</td>\n <td>12</td>\n </tr>\n <tr>\n <th>3</th>\n <td>2</td>\n <td>nes-ds</td>\n <td>2018</td>\n <td>1</td>\n <td>3</td>\n <td>2</td>\n <td>ps02</td>\n <td>Functions</td>\n <td>8</td>\n </tr>\n <tr>\n <th>4</th>\n <td>2</td>\n <td>nes-ds</td>\n <td>2018</td>\n <td>1</td>\n <td>5</td>\n <td>2</td>\n <td>ps03</td>\n <td>Pandas</td>\n <td>10</td>\n </tr>\n <tr>\n <th>5</th>\n <td>2</td>\n <td>nes-ds</td>\n <td>2018</td>\n <td>1</td>\n <td>7</td>\n <td>2</td>\n <td>ps04</td>\n <td>Advanced Python</td>\n <td>32</td>\n </tr>\n <tr>\n <th>6</th>\n <td>3</td>\n <td>dj-prog</td>\n <td>2018</td>\n <td>1</td>\n <td>6</td>\n <td>3</td>\n <td>ps03</td>\n <td>Classes</td>\n <td>15</td>\n </tr>\n <tr>\n <th>7</th>\n <td>3</td>\n <td>dj-prog</td>\n <td>2018</td>\n <td>1</td>\n <td>8</td>\n <td>3</td>\n <td>ps01</td>\n <td>Introduction</td>\n <td>10</td>\n </tr>\n <tr>\n <th>8</th>\n <td>4</td>\n <td>python-all</td>\n <td>2015</td>\n <td>0</td>\n <td>9</td>\n <td>4</td>\n <td>ps01</td>\n <td>First class</td>\n <td>7</td>\n </tr>\n <tr>\n <th>9</th>\n <td>5</td>\n <td>python-icef</td>\n <td>2018</td>\n <td>1</td>\n <td>10</td>\n <td>5</td>\n <td>ps01</td>\n <td>Welcome to Python</td>\n <td>8</td>\n </tr>\n </tbody>\n</table>\n</div>" | |
| }, | |
| "metadata": {} | |
| } | |
| ] | |
| } | |
| ], | |
| "metadata": { | |
| "kernelspec": { | |
| "name": "py3.10", | |
| "display_name": "Python 3.10", | |
| "language": "python" | |
| }, | |
| "language_info": { | |
| "name": "python", | |
| "version": "3.10.0", | |
| "mimetype": "text/x-python", | |
| "codemirror_mode": { | |
| "name": "ipython", | |
| "version": 3 | |
| }, | |
| "pygments_lexer": "ipython3", | |
| "nbconvert_exporter": "python", | |
| "file_extension": ".py" | |
| }, | |
| "@webio": { | |
| "lastKernelId": null, | |
| "lastCommId": null | |
| }, | |
| "gist": { | |
| "id": "", | |
| "data": { | |
| "description": "Lesson23.ipynb", | |
| "public": false | |
| } | |
| } | |
| }, | |
| "nbformat": 4, | |
| "nbformat_minor": 5 | |
| } |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment