In [1]:
import MySQLdb
db = MySQLdb.connect(
"db.fastcamp.us",
"root",
"dkstncks",
"world",
charset='utf8',
)
cursor = db.cursor()
In [2]:
import pandas as pd
In [3]:
# SQL Perspective
SQL_QUERY = """
SELECT *
FROM Country
WHERE
Continent IN ("North America", "South America")
;
"""
pd.read_sql(SQL_QUERY, db)
Out[3]:
Code
Name
Continent
Region
SurfaceArea
IndepYear
Population
LifeExpectancy
GNP
GNPOld
LocalName
GovernmentForm
HeadOfState
Capital
Code2
0
ABW
Aruba
North America
Caribbean
193
NaN
103000
78.4
828.0
793
Aruba
Nonmetropolitan Territory of The Netherlands
Beatrix
129
AW
1
AIA
Anguilla
North America
Caribbean
96
NaN
8000
76.1
63.2
NaN
Anguilla
Dependent Territory of the UK
Elisabeth II
62
AI
2
ANT
Netherlands Antilles
North America
Caribbean
800
NaN
217000
74.7
1941.0
NaN
Nederlandse Antillen
Nonmetropolitan Territory of The Netherlands
Beatrix
33
AN
3
ARG
Argentina
South America
South America
2780400
1816
37032000
75.1
340238.0
323310
Argentina
Federal Republic
Fernando de la Rúa
69
AR
4
ATG
Antigua and Barbuda
North America
Caribbean
442
1981
68000
70.5
612.0
584
Antigua and Barbuda
Constitutional Monarchy
Elisabeth II
63
AG
5
BHS
Bahamas
North America
Caribbean
13878
1973
307000
71.1
3527.0
3347
The Bahamas
Constitutional Monarchy
Elisabeth II
148
BS
6
BLZ
Belize
North America
Central America
22696
1981
241000
70.9
630.0
616
Belize
Constitutional Monarchy
Elisabeth II
185
BZ
7
BMU
Bermuda
North America
North America
53
NaN
65000
76.9
2328.0
2190
Bermuda
Dependent Territory of the UK
Elisabeth II
191
BM
8
BOL
Bolivia
South America
South America
1098581
1825
8329000
63.7
8571.0
7967
Bolivia
Republic
Hugo Bánzer Suárez
194
BO
9
BRA
Brazil
South America
South America
8547403
1822
170115000
62.9
776739.0
804108
Brasil
Federal Republic
Fernando Henrique Cardoso
211
BR
10
BRB
Barbados
North America
Caribbean
430
1966
270000
73.0
2223.0
2186
Barbados
Constitutional Monarchy
Elisabeth II
174
BB
11
CAN
Canada
North America
North America
9970610
1867
31147000
79.4
598862.0
625626
Canada
Constitutional Monarchy, Federation
Elisabeth II
1822
CA
12
CHL
Chile
South America
South America
756626
1810
15211000
75.7
72949.0
75780
Chile
Republic
Ricardo Lagos Escobar
554
CL
13
COL
Colombia
South America
South America
1138914
1810
42321000
70.3
102896.0
105116
Colombia
Republic
Andrés Pastrana Arango
2257
CO
14
CRI
Costa Rica
North America
Central America
51100
1821
4023000
75.8
10226.0
9757
Costa Rica
Republic
Miguel Ángel Rodríguez Echeverría
584
CR
15
CUB
Cuba
North America
Caribbean
110861
1902
11201000
76.2
17843.0
18862
Cuba
Socialistic Republic
Fidel Castro Ruz
2413
CU
16
CYM
Cayman Islands
North America
Caribbean
264
NaN
38000
78.9
1263.0
1186
Cayman Islands
Dependent Territory of the UK
Elisabeth II
553
KY
17
DMA
Dominica
North America
Caribbean
751
1978
71000
73.4
256.0
243
Dominica
Republic
Vernon Shaw
586
DM
18
DOM
Dominican Republic
North America
Caribbean
48511
1844
8495000
73.2
15846.0
15076
República Dominicana
Republic
Hipólito Mejía Domínguez
587
DO
19
ECU
Ecuador
South America
South America
283561
1822
12646000
71.1
19770.0
19769
Ecuador
Republic
Gustavo Noboa Bejarano
594
EC
20
FLK
Falkland Islands
South America
South America
12173
NaN
2000
NaN
0.0
NaN
Falkland Islands
Dependent Territory of the UK
Elisabeth II
763
FK
21
GLP
Guadeloupe
North America
Caribbean
1705
NaN
456000
77.0
3501.0
NaN
Guadeloupe
Overseas Department of France
Jacques Chirac
919
GP
22
GRD
Grenada
North America
Caribbean
344
1974
94000
64.5
318.0
NaN
Grenada
Constitutional Monarchy
Elisabeth II
916
GD
23
GRL
Greenland
North America
North America
2166090
NaN
56000
68.1
0.0
NaN
Kalaallit Nunaat/Grønland
Part of Denmark
Margrethe II
917
GL
24
GTM
Guatemala
North America
Central America
108889
1821
11385000
66.2
19008.0
17797
Guatemala
Republic
Alfonso Portillo Cabrera
922
GT
25
GUF
French Guiana
South America
South America
90000
NaN
181000
76.1
681.0
NaN
Guyane française
Overseas Department of France
Jacques Chirac
3014
GF
26
GUY
Guyana
South America
South America
214969
1966
861000
64.0
722.0
743
Guyana
Republic
Bharrat Jagdeo
928
GY
27
HND
Honduras
North America
Central America
112088
1838
6485000
69.9
5333.0
4697
Honduras
Republic
Carlos Roberto Flores Facussé
933
HN
28
HTI
Haiti
North America
Caribbean
27750
1804
8222000
49.2
3459.0
3107
Haïti/Dayti
Republic
Jean-Bertrand Aristide
929
HT
29
JAM
Jamaica
North America
Caribbean
10990
1962
2583000
75.2
6871.0
6722
Jamaica
Constitutional Monarchy
Elisabeth II
1530
JM
30
KNA
Saint Kitts and Nevis
North America
Caribbean
261
1983
38000
70.7
299.0
NaN
Saint Kitts and Nevis
Constitutional Monarchy
Elisabeth II
3064
KN
31
LCA
Saint Lucia
North America
Caribbean
622
1979
154000
72.3
571.0
NaN
Saint Lucia
Constitutional Monarchy
Elisabeth II
3065
LC
32
MEX
Mexico
North America
Central America
1958201
1810
98881000
71.5
414972.0
401461
México
Federal Republic
Vicente Fox Quesada
2515
MX
33
MSR
Montserrat
North America
Caribbean
102
NaN
11000
78.0
109.0
NaN
Montserrat
Dependent Territory of the UK
Elisabeth II
2697
MS
34
MTQ
Martinique
North America
Caribbean
1102
NaN
395000
78.3
2731.0
2559
Martinique
Overseas Department of France
Jacques Chirac
2508
MQ
35
NIC
Nicaragua
North America
Central America
130000
1838
5074000
68.7
1988.0
2023
Nicaragua
Republic
Arnoldo Alemán Lacayo
2734
NI
36
PAN
Panama
North America
Central America
75517
1903
2856000
75.5
9131.0
8700
Panamá
Republic
Mireya Elisa Moscoso Rodríguez
2882
PA
37
PER
Peru
South America
South America
1285216
1821
25662000
70.0
64140.0
65186
Perú/Piruw
Republic
Valentin Paniagua Corazao
2890
PE
38
PRI
Puerto Rico
North America
Caribbean
8875
NaN
3869000
75.6
34100.0
32100
Puerto Rico
Commonwealth of the US
George W. Bush
2919
PR
39
PRY
Paraguay
South America
South America
406752
1811
5496000
73.7
8444.0
9555
Paraguay
Republic
Luis Ángel González Macchi
2885
PY
40
SLV
El Salvador
North America
Central America
21041
1841
6276000
69.7
11863.0
11203
El Salvador
Republic
Francisco Guillermo Flores Pérez
645
SV
41
SPM
Saint Pierre and Miquelon
North America
North America
242
NaN
7000
77.6
0.0
NaN
Saint-Pierre-et-Miquelon
Territorial Collectivity of France
Jacques Chirac
3067
PM
42
SUR
Suriname
South America
South America
163265
1975
417000
71.4
870.0
706
Suriname
Republic
Ronald Venetiaan
3243
SR
43
TCA
Turks and Caicos Islands
North America
Caribbean
430
NaN
17000
73.3
96.0
NaN
The Turks and Caicos Islands
Dependent Territory of the UK
Elisabeth II
3423
TC
44
TTO
Trinidad and Tobago
North America
Caribbean
5130
1962
1295000
68.0
6232.0
5867
Trinidad and Tobago
Republic
Arthur N. R. Robinson
3336
TT
45
URY
Uruguay
South America
South America
175016
1828
3337000
75.2
20831.0
19967
Uruguay
Republic
Jorge Batlle Ibáñez
3492
UY
46
USA
United States
North America
North America
9363520
1776
278357000
77.1
8510700.0
8110900
United States
Federal Republic
George W. Bush
3813
US
47
VCT
Saint Vincent and the Grenadines
North America
Caribbean
388
1979
114000
72.3
285.0
NaN
Saint Vincent and the Grenadines
Constitutional Monarchy
Elisabeth II
3066
VC
48
VEN
Venezuela
South America
South America
912050
1811
24170000
73.1
95023.0
88434
Venezuela
Federal Republic
Hugo Chávez Frías
3539
VE
49
VGB
Virgin Islands, British
North America
Caribbean
151
NaN
21000
75.4
612.0
573
British Virgin Islands
Dependent Territory of the UK
Elisabeth II
537
VG
50
VIR
Virgin Islands, U.S.
North America
Caribbean
347
NaN
93000
78.1
0.0
NaN
Virgin Islands of the United States
US Territory
George W. Bush
4067
VI
In [4]:
# Pandas Perspective
SQL_QUERY = """
SELECT *
FROM Country
"""
df = pd.read_sql(SQL_QUERY, db)
In [5]:
df["Continent"].isin(["North America", "South America"])
Out[5]:
0 True
1 False
2 False
3 True
4 False
5 False
6 True
7 False
8 True
9 False
10 False
11 False
12 False
13 True
14 False
15 False
16 False
17 False
18 False
19 False
20 False
21 False
22 False
23 False
24 True
25 False
26 False
27 True
28 True
29 True
...
209 False
210 False
211 False
212 False
213 True
214 False
215 False
216 False
217 False
218 False
219 False
220 False
221 False
222 True
223 True
224 False
225 False
226 True
227 True
228 True
229 True
230 False
231 False
232 False
233 False
234 False
235 False
236 False
237 False
238 False
Name: Continent, dtype: bool
In [6]:
df[df["Continent"].isin(["North America", "South America"])]
Out[6]:
Code
Name
Continent
Region
SurfaceArea
IndepYear
Population
LifeExpectancy
GNP
GNPOld
LocalName
GovernmentForm
HeadOfState
Capital
Code2
0
ABW
Aruba
North America
Caribbean
193
NaN
103000
78.4
828.0
793
Aruba
Nonmetropolitan Territory of The Netherlands
Beatrix
129
AW
3
AIA
Anguilla
North America
Caribbean
96
NaN
8000
76.1
63.2
NaN
Anguilla
Dependent Territory of the UK
Elisabeth II
62
AI
6
ANT
Netherlands Antilles
North America
Caribbean
800
NaN
217000
74.7
1941.0
NaN
Nederlandse Antillen
Nonmetropolitan Territory of The Netherlands
Beatrix
33
AN
8
ARG
Argentina
South America
South America
2780400
1816
37032000
75.1
340238.0
323310
Argentina
Federal Republic
Fernando de la Rúa
69
AR
13
ATG
Antigua and Barbuda
North America
Caribbean
442
1981
68000
70.5
612.0
584
Antigua and Barbuda
Constitutional Monarchy
Elisabeth II
63
AG
24
BHS
Bahamas
North America
Caribbean
13878
1973
307000
71.1
3527.0
3347
The Bahamas
Constitutional Monarchy
Elisabeth II
148
BS
27
BLZ
Belize
North America
Central America
22696
1981
241000
70.9
630.0
616
Belize
Constitutional Monarchy
Elisabeth II
185
BZ
28
BMU
Bermuda
North America
North America
53
NaN
65000
76.9
2328.0
2190
Bermuda
Dependent Territory of the UK
Elisabeth II
191
BM
29
BOL
Bolivia
South America
South America
1098581
1825
8329000
63.7
8571.0
7967
Bolivia
Republic
Hugo Bánzer Suárez
194
BO
30
BRA
Brazil
South America
South America
8547403
1822
170115000
62.9
776739.0
804108
Brasil
Federal Republic
Fernando Henrique Cardoso
211
BR
31
BRB
Barbados
North America
Caribbean
430
1966
270000
73.0
2223.0
2186
Barbados
Constitutional Monarchy
Elisabeth II
174
BB
37
CAN
Canada
North America
North America
9970610
1867
31147000
79.4
598862.0
625626
Canada
Constitutional Monarchy, Federation
Elisabeth II
1822
CA
40
CHL
Chile
South America
South America
756626
1810
15211000
75.7
72949.0
75780
Chile
Republic
Ricardo Lagos Escobar
554
CL
47
COL
Colombia
South America
South America
1138914
1810
42321000
70.3
102896.0
105116
Colombia
Republic
Andrés Pastrana Arango
2257
CO
50
CRI
Costa Rica
North America
Central America
51100
1821
4023000
75.8
10226.0
9757
Costa Rica
Republic
Miguel Ángel Rodríguez Echeverría
584
CR
51
CUB
Cuba
North America
Caribbean
110861
1902
11201000
76.2
17843.0
18862
Cuba
Socialistic Republic
Fidel Castro Ruz
2413
CU
53
CYM
Cayman Islands
North America
Caribbean
264
NaN
38000
78.9
1263.0
1186
Cayman Islands
Dependent Territory of the UK
Elisabeth II
553
KY
58
DMA
Dominica
North America
Caribbean
751
1978
71000
73.4
256.0
243
Dominica
Republic
Vernon Shaw
586
DM
60
DOM
Dominican Republic
North America
Caribbean
48511
1844
8495000
73.2
15846.0
15076
República Dominicana
Republic
Hipólito Mejía Domínguez
587
DO
62
ECU
Ecuador
South America
South America
283561
1822
12646000
71.1
19770.0
19769
Ecuador
Republic
Gustavo Noboa Bejarano
594
EC
71
FLK
Falkland Islands
South America
South America
12173
NaN
2000
NaN
0.0
NaN
Falkland Islands
Dependent Territory of the UK
Elisabeth II
763
FK
81
GLP
Guadeloupe
North America
Caribbean
1705
NaN
456000
77.0
3501.0
NaN
Guadeloupe
Overseas Department of France
Jacques Chirac
919
GP
86
GRD
Grenada
North America
Caribbean
344
1974
94000
64.5
318.0
NaN
Grenada
Constitutional Monarchy
Elisabeth II
916
GD
87
GRL
Greenland
North America
North America
2166090
NaN
56000
68.1
0.0
NaN
Kalaallit Nunaat/Grønland
Part of Denmark
Margrethe II
917
GL
88
GTM
Guatemala
North America
Central America
108889
1821
11385000
66.2
19008.0
17797
Guatemala
Republic
Alfonso Portillo Cabrera
922
GT
89
GUF
French Guiana
South America
South America
90000
NaN
181000
76.1
681.0
NaN
Guyane française
Overseas Department of France
Jacques Chirac
3014
GF
91
GUY
Guyana
South America
South America
214969
1966
861000
64.0
722.0
743
Guyana
Republic
Bharrat Jagdeo
928
GY
94
HND
Honduras
North America
Central America
112088
1838
6485000
69.9
5333.0
4697
Honduras
Republic
Carlos Roberto Flores Facussé
933
HN
96
HTI
Haiti
North America
Caribbean
27750
1804
8222000
49.2
3459.0
3107
Haïti/Dayti
Republic
Jean-Bertrand Aristide
929
HT
107
JAM
Jamaica
North America
Caribbean
10990
1962
2583000
75.2
6871.0
6722
Jamaica
Constitutional Monarchy
Elisabeth II
1530
JM
115
KNA
Saint Kitts and Nevis
North America
Caribbean
261
1983
38000
70.7
299.0
NaN
Saint Kitts and Nevis
Constitutional Monarchy
Elisabeth II
3064
KN
122
LCA
Saint Lucia
North America
Caribbean
622
1979
154000
72.3
571.0
NaN
Saint Lucia
Constitutional Monarchy
Elisabeth II
3065
LC
135
MEX
Mexico
North America
Central America
1958201
1810
98881000
71.5
414972.0
401461
México
Federal Republic
Vicente Fox Quesada
2515
MX
145
MSR
Montserrat
North America
Caribbean
102
NaN
11000
78.0
109.0
NaN
Montserrat
Dependent Territory of the UK
Elisabeth II
2697
MS
146
MTQ
Martinique
North America
Caribbean
1102
NaN
395000
78.3
2731.0
2559
Martinique
Overseas Department of France
Jacques Chirac
2508
MQ
156
NIC
Nicaragua
North America
Central America
130000
1838
5074000
68.7
1988.0
2023
Nicaragua
Republic
Arnoldo Alemán Lacayo
2734
NI
165
PAN
Panama
North America
Central America
75517
1903
2856000
75.5
9131.0
8700
Panamá
Republic
Mireya Elisa Moscoso Rodríguez
2882
PA
167
PER
Peru
South America
South America
1285216
1821
25662000
70.0
64140.0
65186
Perú/Piruw
Republic
Valentin Paniagua Corazao
2890
PE
172
PRI
Puerto Rico
North America
Caribbean
8875
NaN
3869000
75.6
34100.0
32100
Puerto Rico
Commonwealth of the US
George W. Bush
2919
PR
175
PRY
Paraguay
South America
South America
406752
1811
5496000
73.7
8444.0
9555
Paraguay
Republic
Luis Ángel González Macchi
2885
PY
192
SLV
El Salvador
North America
Central America
21041
1841
6276000
69.7
11863.0
11203
El Salvador
Republic
Francisco Guillermo Flores Pérez
645
SV
195
SPM
Saint Pierre and Miquelon
North America
North America
242
NaN
7000
77.6
0.0
NaN
Saint-Pierre-et-Miquelon
Territorial Collectivity of France
Jacques Chirac
3067
PM
197
SUR
Suriname
South America
South America
163265
1975
417000
71.4
870.0
706
Suriname
Republic
Ronald Venetiaan
3243
SR
204
TCA
Turks and Caicos Islands
North America
Caribbean
430
NaN
17000
73.3
96.0
NaN
The Turks and Caicos Islands
Dependent Territory of the UK
Elisabeth II
3423
TC
213
TTO
Trinidad and Tobago
North America
Caribbean
5130
1962
1295000
68.0
6232.0
5867
Trinidad and Tobago
Republic
Arthur N. R. Robinson
3336
TT
222
URY
Uruguay
South America
South America
175016
1828
3337000
75.2
20831.0
19967
Uruguay
Republic
Jorge Batlle Ibáñez
3492
UY
223
USA
United States
North America
North America
9363520
1776
278357000
77.1
8510700.0
8110900
United States
Federal Republic
George W. Bush
3813
US
226
VCT
Saint Vincent and the Grenadines
North America
Caribbean
388
1979
114000
72.3
285.0
NaN
Saint Vincent and the Grenadines
Constitutional Monarchy
Elisabeth II
3066
VC
227
VEN
Venezuela
South America
South America
912050
1811
24170000
73.1
95023.0
88434
Venezuela
Federal Republic
Hugo Chávez Frías
3539
VE
228
VGB
Virgin Islands, British
North America
Caribbean
151
NaN
21000
75.4
612.0
573
British Virgin Islands
Dependent Territory of the UK
Elisabeth II
537
VG
229
VIR
Virgin Islands, U.S.
North America
Caribbean
347
NaN
93000
78.1
0.0
NaN
Virgin Islands of the United States
US Territory
George W. Bush
4067
VI
Content source: dobestan/data-science-school
Similar notebooks: