
from zipfile import ZipFile


with ZipFile("newsgroups.zip", 'r') as zipped:
    print(zipped.namelist())

['newsgroups.txt']


with ZipFile("newsgroups.zip") as zipped:
    txt = zipped.read('newsgroups.txt').decode(encoding='utf8', errors='ignore')


print(txt[:2000])

Newsgroup: rec.autos
From: gwm@spl1.spl.loral.com (Gary W. Mahan)
Subject: Re: Are BMW's worth the price?            


>sure sounds like they got a ringer.  the 325is i drove was definitely
>faster than that.  if you want to quote numbers, my AW AutoFile shows
>0-60 in 7.4, 1/4 mile in 15.9.  it quotes Car and Driver's figures
>of 6.9 and 15.3.  oh, BTW, these numbers are for the 325i.

Car and Driver rated the 325is (1988) at 7.2 0-60 , 1/4 at 15.2 (after 30k miles) last time I checked 8#}.

Automobile magazine rated new 325is 1/4 mile@16.2.

Gee , aint quotiin funner than the dickens!

Sounds like we need a race.  I'll let you have the newer version.  Can someone out there lend me a 1988 325is for a day 8-].  I wont hurt it, I promise.

>i don't know how the addition of variable valve timing for 1993 affects it.
>but don't take my word for it.  go drive it.

Actually I will take your word on it.  I refuse to test it (new 325is) because
I love BMW's and would probably want to buy it.  Problem is, my income just doesn't support that.  


Newsgroup: sci.med
From: davec@ecst.csuchico.edu (Dave Childs)
Subject: Dental Fillings question


I have been hearing bad thing about amalgam dental fillings.  Some say
the lead/ mercury leeches into your system and this is bad.  And I have
recently heard that there is some suspicion that the mercury is a breeding
ground for bacteria that will be resistant to antibiotics.   

My dentist wants to use an amalgam filling for me in a place where I have
two cavaties in one tooth and wants to use one filling to cover both.
He says that composite filling don't hold up well when they are large.
So, I would like to know if there are any other choices besides amalgam
and composite.  And, should I really even be worried about amalgam?  I
heard that some scandanavian country does not even use them any more- 
is this true.

Any information you can give me will be greatly appreciated.


Thanks!

Dave Childs


Newsgroup: alt.atheism
From: "Robe


import numpy as np


labels = np.random.choice(['auto', 'med', 'rel'], size=10)
labels

array(['rel', 'rel', 'auto', 'rel', 'med', 'auto', 'med', 'med', 'rel',
       'rel'], dtype='<U4')


predictions = np.random.choice(['auto', 'med', 'rel'], size=10)
predictions

array(['rel', 'auto', 'med', 'rel', 'med', 'auto', 'med', 'rel', 'med',
       'med'], dtype='<U4')


import pandas as pd

ctab = pd.crosstab(labels, predictions)
ctab


%pip install ipython-sql

Requirement already satisfied: ipython-sql in /Users/bb/opt/anaconda3/lib/python3.8/site-packages (0.4.0)
Requirement already satisfied: ipython-genutils>=0.1.0 in /Users/bb/opt/anaconda3/lib/python3.8/site-packages (from ipython-sql) (0.2.0)
Requirement already satisfied: prettytable<1 in /Users/bb/opt/anaconda3/lib/python3.8/site-packages (from ipython-sql) (0.7.2)
Requirement already satisfied: sqlparse in /Users/bb/opt/anaconda3/lib/python3.8/site-packages (from ipython-sql) (0.4.1)
Requirement already satisfied: six in /Users/bb/opt/anaconda3/lib/python3.8/site-packages (from ipython-sql) (1.16.0)
Requirement already satisfied: ipython>=1.0 in /Users/bb/opt/anaconda3/lib/python3.8/site-packages (from ipython-sql) (7.29.0)
Requirement already satisfied: sqlalchemy>=0.6.7 in /Users/bb/opt/anaconda3/lib/python3.8/site-packages (from ipython-sql) (1.3.24)
Requirement already satisfied: traitlets>=4.2 in /Users/bb/opt/anaconda3/lib/python3.8/site-packages (from ipython>=1.0->ipython-sql) (5.1.1)
Requirement already satisfied: decorator in /Users/bb/opt/anaconda3/lib/python3.8/site-packages (from ipython>=1.0->ipython-sql) (5.1.0)
Requirement already satisfied: pickleshare in /Users/bb/opt/anaconda3/lib/python3.8/site-packages (from ipython>=1.0->ipython-sql) (0.7.5)
Requirement already satisfied: jedi>=0.16 in /Users/bb/opt/anaconda3/lib/python3.8/site-packages (from ipython>=1.0->ipython-sql) (0.18.0)
Requirement already satisfied: prompt-toolkit!=3.0.0,!=3.0.1,<3.1.0,>=2.0.0 in /Users/bb/opt/anaconda3/lib/python3.8/site-packages (from ipython>=1.0->ipython-sql) (3.0.20)
Requirement already satisfied: setuptools>=18.5 in /Users/bb/opt/anaconda3/lib/python3.8/site-packages (from ipython>=1.0->ipython-sql) (58.0.4)
Requirement already satisfied: appnope in /Users/bb/opt/anaconda3/lib/python3.8/site-packages (from ipython>=1.0->ipython-sql) (0.1.2)
Requirement already satisfied: pexpect>4.3 in /Users/bb/opt/anaconda3/lib/python3.8/site-packages (from ipython>=1.0->ipython-sql) (4.8.0)
Requirement already satisfied: backcall in /Users/bb/opt/anaconda3/lib/python3.8/site-packages (from ipython>=1.0->ipython-sql) (0.2.0)
Requirement already satisfied: pygments in /Users/bb/opt/anaconda3/lib/python3.8/site-packages (from ipython>=1.0->ipython-sql) (2.10.0)
Requirement already satisfied: matplotlib-inline in /Users/bb/opt/anaconda3/lib/python3.8/site-packages (from ipython>=1.0->ipython-sql) (0.1.2)
Requirement already satisfied: parso<0.9.0,>=0.8.0 in /Users/bb/opt/anaconda3/lib/python3.8/site-packages (from jedi>=0.16->ipython>=1.0->ipython-sql) (0.8.3)
Requirement already satisfied: ptyprocess>=0.5 in /Users/bb/opt/anaconda3/lib/python3.8/site-packages (from pexpect>4.3->ipython>=1.0->ipython-sql) (0.7.0)
Requirement already satisfied: wcwidth in /Users/bb/opt/anaconda3/lib/python3.8/site-packages (from prompt-toolkit!=3.0.0,!=3.0.1,<3.1.0,>=2.0.0->ipython>=1.0->ipython-sql) (0.2.5)
Note: you may need to restart the kernel to use updated packages.


%load_ext sql


%%sql
sqlite:///tiny.sqlite


%%sql
SELECT *
FROM sqlite_master

 * sqlite:///tiny.sqlite
Done.


%%sql 
CREATE TABLE courses(
course_id TEXT NOT NULL PRIMARY KEY,
course_name TEXT,
credits INT
);

 * sqlite:///tiny.sqlite
(sqlite3.OperationalError) table courses already exists
[SQL: CREATE TABLE courses( course_id TEXT NOT NULL PRIMARY KEY,
course_name TEXT,
credits INT
);]
(Background on this error at: http://sqlalche.me/e/13/e3q8)


%%sql
SELECT * 
FROM sqlite_master

 * sqlite:///tiny.sqlite
Done.


%%sql
SELECT * 
FROM courses

 * sqlite:///tiny.sqlite
Done.


%%sql
INSERT INTO courses (course_id, course_name, credits)
VALUES ('MTH 141', 'Calculus I', 4)

 * sqlite:///tiny.sqlite
1 rows affected.

[]


%%sql 
SELECT *
FROM courses

 * sqlite:///tiny.sqlite
Done.


%%sql
INSERT INTO courses (course_id, course_name)
VALUES ('MTH 309', 'Linear Algebra');

SELECT *
FROM courses

 * sqlite:///tiny.sqlite
1 rows affected.
Done.


%%sql 
INSERT INTO courses (course_id, course_name, credits)
VALUES ('MTH 141', 'Calculus 1A', 3)

 * sqlite:///tiny.sqlite

---------------------------------------------------------------------------
IntegrityError                            Traceback (most recent call last)
~/opt/anaconda3/lib/python3.8/site-packages/sqlalchemy/engine/base.py in _execute_context(self, dialect, constructor, statement, parameters, *args)
   1275                 if not evt_handled:
-> 1276                     self.dialect.do_execute(
   1277                         cursor, statement, parameters, context

~/opt/anaconda3/lib/python3.8/site-packages/sqlalchemy/engine/default.py in do_execute(self, cursor, statement, parameters, context)
    607     def do_execute(self, cursor, statement, parameters, context=None):
--> 608         cursor.execute(statement, parameters)
    609 

IntegrityError: UNIQUE constraint failed: courses.course_id

The above exception was the direct cause of the following exception:

IntegrityError                            Traceback (most recent call last)
/var/folders/vd/9gpvwb493r52y4sgtl_fvtvm0000gn/T/ipykernel_42485/3691693528.py in <module>
----> 1 get_ipython().run_cell_magic('sql', '', "INSERT INTO courses (course_id, course_name, credits)\nVALUES ('MTH 141', 'Calculus 1A', 3)\n")

~/opt/anaconda3/lib/python3.8/site-packages/IPython/core/interactiveshell.py in run_cell_magic(self, magic_name, line, cell)
   2404             with self.builtin_trap:
   2405                 args = (magic_arg_s, cell)
-> 2406                 result = fn(*args, **kwargs)
   2407             return result
   2408 

~/opt/anaconda3/lib/python3.8/site-packages/decorator.py in fun(*args, **kw)
    230             if not kwsyntax:
    231                 args, kw = fix(args, kw, sig)
--> 232             return caller(func, *(extras + args), **kw)
    233     fun.__name__ = func.__name__
    234     fun.__doc__ = func.__doc__

~/opt/anaconda3/lib/python3.8/site-packages/IPython/core/magic.py in <lambda>(f, *a, **k)
    185     # but it's overkill for just that one bit of state.
    186     def magic_deco(arg):
--> 187         call = lambda f, *a, **k: f(*a, **k)
    188 
    189         if callable(arg):

~/opt/anaconda3/lib/python3.8/site-packages/decorator.py in fun(*args, **kw)
    230             if not kwsyntax:
    231                 args, kw = fix(args, kw, sig)
--> 232             return caller(func, *(extras + args), **kw)
    233     fun.__name__ = func.__name__
    234     fun.__doc__ = func.__doc__

~/opt/anaconda3/lib/python3.8/site-packages/IPython/core/magic.py in <lambda>(f, *a, **k)
    185     # but it's overkill for just that one bit of state.
    186     def magic_deco(arg):
--> 187         call = lambda f, *a, **k: f(*a, **k)
    188 
    189         if callable(arg):

~/opt/anaconda3/lib/python3.8/site-packages/sql/magic.py in execute(self, line, cell, local_ns)
    215 
    216         try:
--> 217             result = sql.run.run(conn, parsed["sql"], self, user_ns)
    218 
    219             if (

~/opt/anaconda3/lib/python3.8/site-packages/sql/run.py in run(conn, sql, config, user_namespace)
    365             else:
    366                 txt = sqlalchemy.sql.text(statement)
--> 367                 result = conn.session.execute(txt, user_namespace)
    368             _commit(conn=conn, config=config)
    369             if result and config.feedback:

~/opt/anaconda3/lib/python3.8/site-packages/sqlalchemy/engine/base.py in execute(self, object_, *multiparams, **params)
   1009             )
   1010         else:
-> 1011             return meth(self, multiparams, params)
   1012 
   1013     def _execute_function(self, func, multiparams, params):

~/opt/anaconda3/lib/python3.8/site-packages/sqlalchemy/sql/elements.py in _execute_on_connection(self, connection, multiparams, params)
    296     def _execute_on_connection(self, connection, multiparams, params):
    297         if self.supports_execution:
--> 298             return connection._execute_clauseelement(self, multiparams, params)
    299         else:
    300             raise exc.ObjectNotExecutableError(self)

~/opt/anaconda3/lib/python3.8/site-packages/sqlalchemy/engine/base.py in _execute_clauseelement(self, elem, multiparams, params)
   1122             )
   1123 
-> 1124         ret = self._execute_context(
   1125             dialect,
   1126             dialect.execution_ctx_cls._init_compiled,

~/opt/anaconda3/lib/python3.8/site-packages/sqlalchemy/engine/base.py in _execute_context(self, dialect, constructor, statement, parameters, *args)
   1314 
   1315         except BaseException as e:
-> 1316             self._handle_dbapi_exception(
   1317                 e, statement, parameters, cursor, context
   1318             )

~/opt/anaconda3/lib/python3.8/site-packages/sqlalchemy/engine/base.py in _handle_dbapi_exception(self, e, statement, parameters, cursor, context)
   1508                 util.raise_(newraise, with_traceback=exc_info[2], from_=e)
   1509             elif should_wrap:
-> 1510                 util.raise_(
   1511                     sqlalchemy_exception, with_traceback=exc_info[2], from_=e
   1512                 )

~/opt/anaconda3/lib/python3.8/site-packages/sqlalchemy/util/compat.py in raise_(***failed resolving arguments***)
    180 
    181         try:
--> 182             raise exception
    183         finally:
    184             # credit to

~/opt/anaconda3/lib/python3.8/site-packages/sqlalchemy/engine/base.py in _execute_context(self, dialect, constructor, statement, parameters, *args)
   1274                             break
   1275                 if not evt_handled:
-> 1276                     self.dialect.do_execute(
   1277                         cursor, statement, parameters, context
   1278                     )

~/opt/anaconda3/lib/python3.8/site-packages/sqlalchemy/engine/default.py in do_execute(self, cursor, statement, parameters, context)
    606 
    607     def do_execute(self, cursor, statement, parameters, context=None):
--> 608         cursor.execute(statement, parameters)
    609 
    610     def do_execute_no_params(self, cursor, statement, context=None):

IntegrityError: (sqlite3.IntegrityError) UNIQUE constraint failed: courses.course_id
[SQL: INSERT INTO courses (course_id, course_name, credits)
VALUES ('MTH 141', 'Calculus 1A', 3)]
(Background on this error at: http://sqlalche.me/e/13/gkpj)


%%sql
INSERT INTO courses (course_id, course_name, credits)
VALUES
('MTH 142', 'Calculus II', 4),
('MTH 448', 'Data Oriented Computing', 3),
('MTH 462', 'Topics in Analysis', 2);

SELECT *
FROM courses

 * sqlite:///tiny.sqlite
3 rows affected.
Done.


%%sql
UPDATE courses
SET credits = 4
WHERE course_id = 'MTH 309';

SELECT *
FROM courses

 * sqlite:///tiny.sqlite
1 rows affected.
Done.


%%sql
DELETE FROM courses
WHERE credits < 4;

SELECT *
FROM courses

 * sqlite:///tiny.sqlite
2 rows affected.
Done.


%%sql 
ALTER TABLE courses
ADD COLUMN semester;

SELECT *
FROM courses

 * sqlite:///tiny.sqlite
Done.
Done.


%%sql 
UPDATE courses
SET semester = 'Fall';

SELECT *
FROM courses;

 * sqlite:///tiny.sqlite
3 rows affected.
Done.


%%sql 
DROP TABLE courses

 * sqlite:///tiny.sqlite
Done.

[]


%%sql
SELECT *
FROM sqlite_master

 * sqlite:///tiny.sqlite
Done.


%%sql 
DROP TABLE courses

 * sqlite:///tiny.sqlite
(sqlite3.OperationalError) no such table: courses
[SQL: DROP TABLE courses]
(Background on this error at: http://sqlalche.me/e/13/e3q8)


%%sql 
DROP TABLE IF EXISTS course;

 * sqlite:///tiny.sqlite
Done.

[]


%%sql 
sqlite:///gradebook_data.sqlite


%%sql
SELECT *
FROM sqlite_master

 * sqlite:///gradebook_data.sqlite
   sqlite:///tiny.sqlite
Done.


%%sql 
SELECT *
FROM gradebook

 * sqlite:///gradebook_data.sqlite
   sqlite:///tiny.sqlite
Done.


%%sql 
SELECT first_name, last_name, exam3
FROM gradebook

 * sqlite:///gradebook_data.sqlite
   sqlite:///tiny.sqlite
Done.


%%sql
SELECT first_name AS FName, last_name AS LName, exam3 AS Last_Exam
FROM gradebook

 * sqlite:///gradebook_data.sqlite
   sqlite:///tiny.sqlite
Done.


%%sql
SELECT first_name, last_name, (exam1 + exam2 + exam3) AS exam_total
FROM gradebook

 * sqlite:///gradebook_data.sqlite
   sqlite:///tiny.sqlite
Done.


%%sql 
SELECT first_name, last_name, exam1, COALESCE(exam1, 0) AS exam1_modified
FROM gradebook

 * sqlite:///gradebook_data.sqlite
   sqlite:///tiny.sqlite
Done.


%%sql 
SELECT first_name, 
       last_name, 
       COALESCE(exam1, 0) + COALESCE(exam2, 0) + COALESCE(exam3, 0) AS exam_total
FROM gradebook

 * sqlite:///gradebook_data.sqlite
   sqlite:///tiny.sqlite
Done.


%%sql 
SELECT last_name || ', ' || first_name AS name, exam1, exam2, exam3
FROM gradebook

 * sqlite:///gradebook_data.sqlite
   sqlite:///tiny.sqlite
Done.


%%sql 
SELECT first_name, last_name, exam3, 
CASE 
    WHEN exam3 > 90 THEN 'A'
    WHEN exam3 > 80 THEN 'B'
    WHEN exam3 > 70 THEN 'C'
    WHEN exam3 > 60 THEN 'D'
    ELSE 'F'
END AS grade
FROM gradebook

 * sqlite:///gradebook_data.sqlite
   sqlite:///tiny.sqlite
Done.


%%sql 
SELECT *
FROM gradebook
WHERE exam1 > 70

 * sqlite:///gradebook_data.sqlite
   sqlite:///tiny.sqlite
Done.


%%sql 
SELECT *
FROM gradebook
WHERE exam1 > 70 AND NOT exam3 > 70

 * sqlite:///gradebook_data.sqlite
   sqlite:///tiny.sqlite
Done.


%%sql 
SELECT *
FROM gradebook
WHERE exam1 = NULL

 * sqlite:///gradebook_data.sqlite
   sqlite:///tiny.sqlite
Done.


%%sql 
SELECT *
FROM gradebook 
WHERE exam1 IS NULL

 * sqlite:///gradebook_data.sqlite
   sqlite:///tiny.sqlite
Done.


%%sql 
SELECT * 
FROM gradebook
WHERE exam1 IS NOT NULL

 * sqlite:///gradebook_data.sqlite
   sqlite:///tiny.sqlite
Done.


%%sql 
SELECT *
FROM gradebook
WHERE last_name GLOB '[HR]*i*'

 * sqlite:///gradebook_data.sqlite
   sqlite:///tiny.sqlite
Done.


%%sql 
SELECT *
FROM gradebook
WHERE last_name GLOB '[HR]*i*' AND exam1 > 50

 * sqlite:///gradebook_data.sqlite
   sqlite:///tiny.sqlite
Done.

student_id	first_name	last_name	major	exam1	exam2	exam3
2712	Starr	Bachand	computer science	None	27	85
3548	Marcia	Hinkel	biology	79	10	41
3877	Chu	Flax	mathematics	72	None	29
5108	Shawnna	Coate	computer science	79	96	61
5775	Rosina	Hovey	biology	96	13	99
5787	Tenisha	Rippel	computer science	27	54	79
6245	Adelina	Brummitt	mathematics	None	67	99
7138	Fran	Mount	biology	71	5	80
7256	Rosalinda	Trippe	computer science	None	None	82
8911	Lottie	Champine	computer science	None	None	62
9165	Ludivina	Hoskin	mathematics	27	55	None
9346	Stanton	Mcgaugh	computer science	6	60	40
9667	Jonnie	Hillock	mathematics	56	24	72

first_name	last_name	exam3
Starr	Bachand	85
Marcia	Hinkel	41
Chu	Flax	29
Shawnna	Coate	61
Rosina	Hovey	99
Tenisha	Rippel	79
Adelina	Brummitt	99
Fran	Mount	80
Rosalinda	Trippe	82
Lottie	Champine	62
Ludivina	Hoskin	None
Stanton	Mcgaugh	40
Jonnie	Hillock	72

FName	LName	Last_Exam
Starr	Bachand	85
Marcia	Hinkel	41
Chu	Flax	29
Shawnna	Coate	61
Rosina	Hovey	99
Tenisha	Rippel	79
Adelina	Brummitt	99
Fran	Mount	80
Rosalinda	Trippe	82
Lottie	Champine	62
Ludivina	Hoskin	None
Stanton	Mcgaugh	40
Jonnie	Hillock	72

first_name	last_name	exam_total
Starr	Bachand	None
Marcia	Hinkel	130
Chu	Flax	None
Shawnna	Coate	236
Rosina	Hovey	208
Tenisha	Rippel	160
Adelina	Brummitt	None
Fran	Mount	156
Rosalinda	Trippe	None
Lottie	Champine	None
Ludivina	Hoskin	None
Stanton	Mcgaugh	106
Jonnie	Hillock	152

first_name	last_name	exam1	exam1_modified
Starr	Bachand	None	0
Marcia	Hinkel	79	79
Chu	Flax	72	72
Shawnna	Coate	79	79
Rosina	Hovey	96	96
Tenisha	Rippel	27	27
Adelina	Brummitt	None	0
Fran	Mount	71	71
Rosalinda	Trippe	None	0
Lottie	Champine	None	0
Ludivina	Hoskin	27	27
Stanton	Mcgaugh	6	6
Jonnie	Hillock	56	56

Week 13¶

SQL¶

SQL Queries¶

type	name	tbl_name	rootpage	sql
table	courses	courses	2	CREATE TABLE courses( course_id TEXT NOT NULL PRIMARY KEY, course_name TEXT, credits INT )
index	sqlite_autoindex_courses_1	courses	3	None

course_id	course_name	credits
MTH 141	Calculus I	4
MTH 309	Linear Algebra	None
MTH 142	Calculus II	4
MTH 448	Data Oriented Computing	3
MTH 462	Topics in Analysis	2

course_id	course_name	credits	semester
MTH 141	Calculus I	4	Fall
MTH 309	Linear Algebra	4	Fall
MTH 142	Calculus II	4	Fall

name	exam1	exam2	exam3
Bachand, Starr	None	27	85
Hinkel, Marcia	79	10	41
Flax, Chu	72	None	29
Coate, Shawnna	79	96	61
Hovey, Rosina	96	13	99
Rippel, Tenisha	27	54	79
Brummitt, Adelina	None	67	99
Mount, Fran	71	5	80
Trippe, Rosalinda	None	None	82
Champine, Lottie	None	None	62
Hoskin, Ludivina	27	55	None
Mcgaugh, Stanton	6	60	40
Hillock, Jonnie	56	24	72

col_0	auto	med	rel
row_0
auto	1	1	0
med	0	2	1
rel	1	2	2