-
Notifications
You must be signed in to change notification settings - Fork 0
/
sql-primer.txt
128 lines (113 loc) · 4.15 KB
/
sql-primer.txt
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
#sql tutorial
First download and install MySQL for your OS
Download and install the Python connector (driver) for MYSQL
Create a folder for all the superbowl related database file
#copy/paste and save the text below in an ascii file titled cr_sb_tb.sql
#####################################################
DROP TABLE IF EXISTS supbowl;
CREATE TABLE supbowl
(
year int,
romannbr varchar(8),
winner varchar(12),
wscore int,
loser varchar(12),
losescore int,
conf varchar(3)
);
#####################################################
#copy/paste and save the text below in an ascii file titled ld_sb_tb.sql
#####################################################
# empty the table
DELETE FROM supbowl;
# load new records into it
LOAD DATA LOCAL INFILE 'sb.csv' INTO TABLE supbowl
columns terminated by ','
OPTIONALLY ENCLOSED BY '"'
;
#####################################################
#copy/paste and save the text below in an ascii file titled sb.csv
#####################################################
1967,"I","Packers",35,"Chiefs",10,"NFC"
1968,"II","Packers",33,"Raiders",14,"NFC"
1969,"III","Jets",16,"Colts",7,"AFC"
1970,"IV","Chiefs",23,"Vikings",7,"AFC"
1971,"V","Colts",16,"Cowboys",13,"AFC"
1972,"VI","Cowboys",24,"Dolphins",3,"NFC"
1973,"VII","Dolphins",14,"Redskins",7,"AFC"
1974,"VIII","Dolphins",24,"Vikings",7,"AFC"
1975,"IX","Steelers",16,"Vikings",6,"AFC"
1976,"X","Steelers",21,"Cowboys",17,"AFC"
1977,"XI","Raiders",32,"Vikings",14,"AFC"
1978,"XII","Cowboys",27,"Broncos",10,"NFC"
1979,"XIII","Steelers",35,"Cowboys",31,"AFC"
1980,"XIV","Steelers",31,"Rams",19,"AFC"
1981,"XV","Raiders",27,"Eagles",10,"AFC"
1982,"XVI","49ers",26,"Bengals",21,"NFC"
1983,"XVII","Redskins",27,"Dolphins",17,"NFC"
1984,"XVIII","Raiders",38,"Redskins",9,"AFC"
1985,"XIX","49ers",38,"Dolphins",16,"NFC"
1986,"XX","Bears",46,"Patriots",10,"NFC"
1987,"XXI","Giants",39,"Broncos",20,"NFC"
1988,"XXII","Redskins",42,"Broncos",10,"NFC"
1989,"XXIII","49ers",20,"Bengals",16,"NFC"
1990,"XXIV","49ers",55,"Broncos",10,"NFC"
1991,"XXV","Giants",20,"Bills",19,"NFC"
1992,"XXVI","Redskins",37,"Bills",24,"NFC"
1993,"XXVII","Cowboys",52,"Bills",17,"NFC"
1994,"XXVIII","Cowboys",30,"Bills",13,"NFC"
1995,"XXIX","49ers",49,"Chargers",26,"NFC"
1996,"XXX","Cowboys",27,"Steelers",17,"NFC"
1997,"XXXI","Packers",35,"Patriots",21,"NFC"
1998,"XXXII","Broncos",31,"Packers",24,"AFC"
1999,"XXXIII","Broncos",34,"Falcons",19,"AFC"
2000,"XXXIV","Rams",23,"Titans",16,"NFC"
2001,"XXXV","Ravens",34,"Giants",7,"AFC"
2002,"XXXVI","Patriots",20,"Rams",17,"AFC"
2003,"XXXVII","Buccaneers",48,"Raiders",21,"NFC"
2004,"XXXVIII","Patriots",32,"Panthers",29,"AFC"
2005,"XXXIX","Patriots",24,"Eagles",21,"AFC"
2006,"XL","Steelers",21,"Seahawks",10,"AFC"
2007,"XLI","Colts",29,"Bears",17,"AFC"
2008,"XLII","Giants",17,"Patriots",14,"NFC"
2009,"XLIII","Steelers",27,"Cardinals",23,"AFC"
2010,"XLIV","Saints",31,"Colts",17,"NFC"
2011,"XLV","Packers",31,"Steelers",25,"NFC"
2012,"XLVI","Giants",21,"Patriots",17,"NFC"
2013,"XLVII","Ravens",34,"49ers",31,"AFC"
2014,"XLVIII","Seahawks",43,"Broncos",8,"NFC"
###################################################
from your OS command line start MySQL (e.g. Linux: "sudo mysql")
once in MySQL do the following:
#delete the database if exists
drop database sbowl;
#create the database
create database sbowl;
#switch to the sbowldatabase
use sbowl;
#run the script to create the table
source cr_sb_tb.sql;
#run the script to load the data
source ld_sb_tb.sql;
#add one more year worth of data using the insert command
insert into supbowl values(2015,'XLIX','Patriots',28,'Seahawks',24,'AFC');
#query the table
select * from supbowl;
To use Python with this database:
1) set up a user and password (myuser, mypassword)
2) cut and paste the text below in an asscii file called pysql.py
and then execute from command line (e.g. python3 pysql.py)
#####################################################
import mysql.connector
mydb = mysql.connector.connect(
host="localhost",
user="myuser",
passwd="mypassword",
database="sbowl"
)
mycursor = mydb.cursor()
mycursor.execute("SELECT * FROM supbowl")
myresult = mycursor.fetchall()
for x in myresult:
print(x)
#####################################################