In [1]:
import MySQLdb
db = MySQLdb.connect(
"db.fastcamp.us",
"root",
"dkstncks",
"world",
charset='utf8',
)
cursor = db.cursor()
In [2]:
import pandas as pd
# SQL Perspective - ORDER BY
SQL_QUERY = """
SELECT *
FROM Country
ORDER BY Population DESC
;
"""
pd.read_sql(SQL_QUERY, db)
Out[2]:
Code
Name
Continent
Region
SurfaceArea
IndepYear
Population
LifeExpectancy
GNP
GNPOld
LocalName
GovernmentForm
HeadOfState
Capital
Code2
0
CHN
China
Asia
Eastern Asia
9572900.0
-1523
1277558000
71.4
982268.0
917719
Zhongquo
People'sRepublic
Jiang Zemin
1891
CN
1
IND
India
Asia
Southern and Central Asia
3287263.0
1947
1013662000
62.5
447114.0
430572
Bharat/India
Federal Republic
Kocheril Raman Narayanan
1109
IN
2
USA
United States
North America
North America
9363520.0
1776
278357000
77.1
8510700.0
8110900
United States
Federal Republic
George W. Bush
3813
US
3
IDN
Indonesia
Asia
Southeast Asia
1904569.0
1945
212107000
68.0
84982.0
215002
Indonesia
Republic
Abdurrahman Wahid
939
ID
4
BRA
Brazil
South America
South America
8547403.0
1822
170115000
62.9
776739.0
804108
Brasil
Federal Republic
Fernando Henrique Cardoso
211
BR
5
PAK
Pakistan
Asia
Southern and Central Asia
796095.0
1947
156483000
61.1
61289.0
58549
Pakistan
Republic
Mohammad Rafiq Tarar
2831
PK
6
RUS
Russian Federation
Europe
Eastern Europe
17075400.0
1991
146934000
67.2
276608.0
442989
Rossija
Federal Republic
Vladimir Putin
3580
RU
7
BGD
Bangladesh
Asia
Southern and Central Asia
143998.0
1971
129155000
60.2
32852.0
31966
Bangladesh
Republic
Shahabuddin Ahmad
150
BD
8
JPN
Japan
Asia
Eastern Asia
377829.0
-660
126714000
80.7
3787042.0
4192638
Nihon/Nippon
Constitutional Monarchy
Akihito
1532
JP
9
NGA
Nigeria
Africa
Western Africa
923768.0
1960
111506000
51.6
65707.0
58623
Nigeria
Federal Republic
Olusegun Obasanjo
2754
NG
10
MEX
Mexico
North America
Central America
1958201.0
1810
98881000
71.5
414972.0
401461
México
Federal Republic
Vicente Fox Quesada
2515
MX
11
DEU
Germany
Europe
Western Europe
357022.0
1955
82164700
77.4
2133367.0
2102826
Deutschland
Federal Republic
Johannes Rau
3068
DE
12
VNM
Vietnam
Asia
Southeast Asia
331689.0
1945
79832000
69.3
21929.0
22834
Viêt Nam
Socialistic Republic
Trân Duc Luong
3770
VN
13
PHL
Philippines
Asia
Southeast Asia
300000.0
1946
75967000
67.5
65107.0
82239
Pilipinas
Republic
Gloria Macapagal-Arroyo
766
PH
14
EGY
Egypt
Africa
Northern Africa
1001449.0
1922
68470000
63.3
82710.0
75617
Misr
Republic
Hosni Mubarak
608
EG
15
IRN
Iran
Asia
Southern and Central Asia
1648195.0
1906
67702000
69.7
195746.0
160151
Iran
Islamic Republic
Ali Mohammad Khatami-Ardakani
1380
IR
16
TUR
Turkey
Asia
Middle East
774815.0
1923
66591000
71.0
210721.0
189122
Türkiye
Republic
Ahmet Necdet Sezer
3358
TR
17
ETH
Ethiopia
Africa
Eastern Africa
1104300.0
-1000
62565000
45.2
6353.0
6180
YeItyop´iya
Republic
Negasso Gidada
756
ET
18
THA
Thailand
Asia
Southeast Asia
513115.0
1350
61399000
68.6
116416.0
153907
Prathet Thai
Constitutional Monarchy
Bhumibol Adulyadej
3320
TH
19
GBR
United Kingdom
Europe
British Islands
242900.0
1066
59623400
77.7
1378330.0
1296830
United Kingdom
Constitutional Monarchy
Elisabeth II
456
GB
20
FRA
France
Europe
Western Europe
551500.0
843
59225700
78.8
1424285.0
1392448
France
Republic
Jacques Chirac
2974
FR
21
ITA
Italy
Europe
Southern Europe
301316.0
1861
57680000
79.0
1161755.0
1145372
Italia
Republic
Carlo Azeglio Ciampi
1464
IT
22
COD
Congo, The Democratic Republic of the
Africa
Central Africa
2344858.0
1960
51654000
48.8
6964.0
2474
République Démocratique du Congo
Republic
Joseph Kabila
2298
CD
23
UKR
Ukraine
Europe
Eastern Europe
603700.0
1991
50456000
66.0
42168.0
49677
Ukrajina
Republic
Leonid Kutšma
3426
UA
24
KOR
South Korea
Asia
Eastern Asia
99434.0
1948
46844000
74.4
320749.0
442544
Taehan Min’guk (Namhan)
Republic
Kim Dae-jung
2331
KR
25
MMR
Myanmar
Asia
Southeast Asia
676578.0
1948
45611000
54.9
180375.0
171028
Myanma Pye
Republic
kenraali Than Shwe
2710
MM
26
COL
Colombia
South America
South America
1138914.0
1810
42321000
70.3
102896.0
105116
Colombia
Republic
Andrés Pastrana Arango
2257
CO
27
ZAF
South Africa
Africa
Southern Africa
1221037.0
1910
40377000
51.1
116729.0
129092
South Africa
Republic
Thabo Mbeki
716
ZA
28
ESP
Spain
Europe
Southern Europe
505992.0
1492
39441700
78.8
553233.0
532031
España
Constitutional Monarchy
Juan Carlos I
653
ES
29
POL
Poland
Europe
Eastern Europe
323250.0
1918
38653600
73.2
151697.0
135636
Polska
Republic
Aleksander Kwasniewski
2928
PL
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
209
LIE
Liechtenstein
Europe
Western Europe
160.0
1806
32300
78.8
1119.0
1084
Liechtenstein
Constitutional Monarchy
Hans-Adam II
2446
LI
210
SMR
San Marino
Europe
Southern Europe
61.0
885
27000
81.1
510.0
NaN
San Marino
Republic
None
3171
SM
211
GIB
Gibraltar
Europe
Southern Europe
6.0
NaN
25000
79.0
258.0
NaN
Gibraltar
Dependent Territory of the UK
Elisabeth II
915
GI
212
VGB
Virgin Islands, British
North America
Caribbean
151.0
NaN
21000
75.4
612.0
573
British Virgin Islands
Dependent Territory of the UK
Elisabeth II
537
VG
213
COK
Cook Islands
Oceania
Polynesia
236.0
NaN
20000
71.1
100.0
NaN
The Cook Islands
Nonmetropolitan Territory of New Zealand
Elisabeth II
583
CK
214
PLW
Palau
Oceania
Micronesia
459.0
1994
19000
68.6
105.0
NaN
Belau/Palau
Republic
Kuniwo Nakamura
2881
PW
215
TCA
Turks and Caicos Islands
North America
Caribbean
430.0
NaN
17000
73.3
96.0
NaN
The Turks and Caicos Islands
Dependent Territory of the UK
Elisabeth II
3423
TC
216
WLF
Wallis and Futuna
Oceania
Polynesia
200.0
NaN
15000
NaN
0.0
NaN
Wallis-et-Futuna
Nonmetropolitan Territory of France
Jacques Chirac
3536
WF
217
NRU
Nauru
Oceania
Micronesia
21.0
1968
12000
60.8
197.0
NaN
Naoero/Nauru
Republic
Bernard Dowiyogo
2728
NR
218
TUV
Tuvalu
Oceania
Polynesia
26.0
1978
12000
66.3
6.0
NaN
Tuvalu
Constitutional Monarchy
Elisabeth II
3424
TV
219
MSR
Montserrat
North America
Caribbean
102.0
NaN
11000
78.0
109.0
NaN
Montserrat
Dependent Territory of the UK
Elisabeth II
2697
MS
220
AIA
Anguilla
North America
Caribbean
96.0
NaN
8000
76.1
63.2
NaN
Anguilla
Dependent Territory of the UK
Elisabeth II
62
AI
221
SPM
Saint Pierre and Miquelon
North America
North America
242.0
NaN
7000
77.6
0.0
NaN
Saint-Pierre-et-Miquelon
Territorial Collectivity of France
Jacques Chirac
3067
PM
222
SHN
Saint Helena
Africa
Western Africa
314.0
NaN
6000
76.8
0.0
NaN
Saint Helena
Dependent Territory of the UK
Elisabeth II
3063
SH
223
SJM
Svalbard and Jan Mayen
Europe
Nordic Countries
62422.0
NaN
3200
NaN
0.0
NaN
Svalbard og Jan Mayen
Dependent Territory of Norway
Harald V
938
SJ
224
CXR
Christmas Island
Oceania
Australia and New Zealand
135.0
NaN
2500
NaN
0.0
NaN
Christmas Island
Territory of Australia
Elisabeth II
1791
CX
225
TKL
Tokelau
Oceania
Polynesia
12.0
NaN
2000
NaN
0.0
NaN
Tokelau
Nonmetropolitan Territory of New Zealand
Elisabeth II
3333
TK
226
NFK
Norfolk Island
Oceania
Australia and New Zealand
36.0
NaN
2000
NaN
0.0
NaN
Norfolk Island
Territory of Australia
Elisabeth II
2806
NF
227
NIU
Niue
Oceania
Polynesia
260.0
NaN
2000
NaN
0.0
NaN
Niue
Nonmetropolitan Territory of New Zealand
Elisabeth II
2805
NU
228
FLK
Falkland Islands
South America
South America
12173.0
NaN
2000
NaN
0.0
NaN
Falkland Islands
Dependent Territory of the UK
Elisabeth II
763
FK
229
VAT
Holy See (Vatican City State)
Europe
Southern Europe
0.4
1929
1000
NaN
9.0
NaN
Santa Sede/Città del Vaticano
Independent Church State
Johannes Paavali II
3538
VA
230
CCK
Cocos (Keeling) Islands
Oceania
Australia and New Zealand
14.0
NaN
600
NaN
0.0
NaN
Cocos (Keeling) Islands
Territory of Australia
Elisabeth II
2317
CC
231
PCN
Pitcairn
Oceania
Polynesia
49.0
NaN
50
NaN
0.0
NaN
Pitcairn
Dependent Territory of the UK
Elisabeth II
2912
PN
232
BVT
Bouvet Island
Antarctica
Antarctica
59.0
NaN
0
NaN
0.0
NaN
Bouvetøya
Dependent Territory of Norway
Harald V
NaN
BV
233
IOT
British Indian Ocean Territory
Africa
Eastern Africa
78.0
NaN
0
NaN
0.0
NaN
British Indian Ocean Territory
Dependent Territory of the UK
Elisabeth II
NaN
IO
234
SGS
South Georgia and the South Sandwich Islands
Antarctica
Antarctica
3903.0
NaN
0
NaN
0.0
NaN
South Georgia and the South Sandwich Islands
Dependent Territory of the UK
Elisabeth II
NaN
GS
235
UMI
United States Minor Outlying Islands
Oceania
Micronesia/Caribbean
16.0
NaN
0
NaN
0.0
NaN
United States Minor Outlying Islands
Dependent Territory of the US
George W. Bush
NaN
UM
236
HMD
Heard Island and McDonald Islands
Antarctica
Antarctica
359.0
NaN
0
NaN
0.0
NaN
Heard and McDonald Islands
Territory of Australia
Elisabeth II
NaN
HM
237
ATF
French Southern territories
Antarctica
Antarctica
7780.0
NaN
0
NaN
0.0
NaN
Terres australes françaises
Nonmetropolitan Territory of France
Jacques Chirac
NaN
TF
238
ATA
Antarctica
Antarctica
Antarctica
13120000.0
NaN
0
NaN
0.0
NaN
–
Co-administrated
NaN
AQ
239 rows × 15 columns
In [3]:
# Pandas Perspective - sort()
SQL_QUERY = """
SELECT *
FROM Country
;
"""
df = pd.read_sql(SQL_QUERY, db)
In [4]:
df.sort_values(by="Population", ascending=False)
Out[4]:
Code
Name
Continent
Region
SurfaceArea
IndepYear
Population
LifeExpectancy
GNP
GNPOld
LocalName
GovernmentForm
HeadOfState
Capital
Code2
41
CHN
China
Asia
Eastern Asia
9572900.0
-1523
1277558000
71.4
982268.0
917719
Zhongquo
People'sRepublic
Jiang Zemin
1891
CN
99
IND
India
Asia
Southern and Central Asia
3287263.0
1947
1013662000
62.5
447114.0
430572
Bharat/India
Federal Republic
Kocheril Raman Narayanan
1109
IN
223
USA
United States
North America
North America
9363520.0
1776
278357000
77.1
8510700.0
8110900
United States
Federal Republic
George W. Bush
3813
US
98
IDN
Indonesia
Asia
Southeast Asia
1904569.0
1945
212107000
68.0
84982.0
215002
Indonesia
Republic
Abdurrahman Wahid
939
ID
30
BRA
Brazil
South America
South America
8547403.0
1822
170115000
62.9
776739.0
804108
Brasil
Federal Republic
Fernando Henrique Cardoso
211
BR
164
PAK
Pakistan
Asia
Southern and Central Asia
796095.0
1947
156483000
61.1
61289.0
58549
Pakistan
Republic
Mohammad Rafiq Tarar
2831
PK
181
RUS
Russian Federation
Europe
Eastern Europe
17075400.0
1991
146934000
67.2
276608.0
442989
Rossija
Federal Republic
Vladimir Putin
3580
RU
21
BGD
Bangladesh
Asia
Southern and Central Asia
143998.0
1971
129155000
60.2
32852.0
31966
Bangladesh
Republic
Shahabuddin Ahmad
150
BD
109
JPN
Japan
Asia
Eastern Asia
377829.0
-660
126714000
80.7
3787042.0
4192638
Nihon/Nippon
Constitutional Monarchy
Akihito
1532
JP
155
NGA
Nigeria
Africa
Western Africa
923768.0
1960
111506000
51.6
65707.0
58623
Nigeria
Federal Republic
Olusegun Obasanjo
2754
NG
135
MEX
Mexico
North America
Central America
1958201.0
1810
98881000
71.5
414972.0
401461
México
Federal Republic
Vicente Fox Quesada
2515
MX
56
DEU
Germany
Europe
Western Europe
357022.0
1955
82164700
77.4
2133367.0
2102826
Deutschland
Federal Republic
Johannes Rau
3068
DE
230
VNM
Vietnam
Asia
Southeast Asia
331689.0
1945
79832000
69.3
21929.0
22834
Viêt Nam
Socialistic Republic
Trân Duc Luong
3770
VN
168
PHL
Philippines
Asia
Southeast Asia
300000.0
1946
75967000
67.5
65107.0
82239
Pilipinas
Republic
Gloria Macapagal-Arroyo
766
PH
63
EGY
Egypt
Africa
Northern Africa
1001449.0
1922
68470000
63.3
82710.0
75617
Misr
Republic
Hosni Mubarak
608
EG
102
IRN
Iran
Asia
Southern and Central Asia
1648195.0
1906
67702000
69.7
195746.0
160151
Iran
Islamic Republic
Ali Mohammad Khatami-Ardakani
1380
IR
215
TUR
Turkey
Asia
Middle East
774815.0
1923
66591000
71.0
210721.0
189122
Türkiye
Republic
Ahmet Necdet Sezer
3358
TR
68
ETH
Ethiopia
Africa
Eastern Africa
1104300.0
-1000
62565000
45.2
6353.0
6180
YeItyop´iya
Republic
Negasso Gidada
756
ET
207
THA
Thailand
Asia
Southeast Asia
513115.0
1350
61399000
68.6
116416.0
153907
Prathet Thai
Constitutional Monarchy
Bhumibol Adulyadej
3320
TH
76
GBR
United Kingdom
Europe
British Islands
242900.0
1066
59623400
77.7
1378330.0
1296830
United Kingdom
Constitutional Monarchy
Elisabeth II
456
GB
72
FRA
France
Europe
Western Europe
551500.0
843
59225700
78.8
1424285.0
1392448
France
Republic
Jacques Chirac
2974
FR
106
ITA
Italy
Europe
Southern Europe
301316.0
1861
57680000
79.0
1161755.0
1145372
Italia
Republic
Carlo Azeglio Ciampi
1464
IT
44
COD
Congo, The Democratic Republic of the
Africa
Central Africa
2344858.0
1960
51654000
48.8
6964.0
2474
République Démocratique du Congo
Republic
Joseph Kabila
2298
CD
220
UKR
Ukraine
Europe
Eastern Europe
603700.0
1991
50456000
66.0
42168.0
49677
Ukrajina
Republic
Leonid Kutšma
3426
UA
116
KOR
South Korea
Asia
Eastern Asia
99434.0
1948
46844000
74.4
320749.0
442544
Taehan Min’guk (Namhan)
Republic
Kim Dae-jung
2331
KR
140
MMR
Myanmar
Asia
Southeast Asia
676578.0
1948
45611000
54.9
180375.0
171028
Myanma Pye
Republic
kenraali Than Shwe
2710
MM
47
COL
Colombia
South America
South America
1138914.0
1810
42321000
70.3
102896.0
105116
Colombia
Republic
Andrés Pastrana Arango
2257
CO
236
ZAF
South Africa
Africa
Southern Africa
1221037.0
1910
40377000
51.1
116729.0
129092
South Africa
Republic
Thabo Mbeki
716
ZA
66
ESP
Spain
Europe
Southern Europe
505992.0
1492
39441700
78.8
553233.0
532031
España
Constitutional Monarchy
Juan Carlos I
653
ES
171
POL
Poland
Europe
Eastern Europe
323250.0
1918
38653600
73.2
151697.0
135636
Polska
Republic
Aleksander Kwasniewski
2928
PL
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
123
LIE
Liechtenstein
Europe
Western Europe
160.0
1806
32300
78.8
1119.0
1084
Liechtenstein
Constitutional Monarchy
Hans-Adam II
2446
LI
193
SMR
San Marino
Europe
Southern Europe
61.0
885
27000
81.1
510.0
NaN
San Marino
Republic
None
3171
SM
79
GIB
Gibraltar
Europe
Southern Europe
6.0
NaN
25000
79.0
258.0
NaN
Gibraltar
Dependent Territory of the UK
Elisabeth II
915
GI
228
VGB
Virgin Islands, British
North America
Caribbean
151.0
NaN
21000
75.4
612.0
573
British Virgin Islands
Dependent Territory of the UK
Elisabeth II
537
VG
46
COK
Cook Islands
Oceania
Polynesia
236.0
NaN
20000
71.1
100.0
NaN
The Cook Islands
Nonmetropolitan Territory of New Zealand
Elisabeth II
583
CK
169
PLW
Palau
Oceania
Micronesia
459.0
1994
19000
68.6
105.0
NaN
Belau/Palau
Republic
Kuniwo Nakamura
2881
PW
204
TCA
Turks and Caicos Islands
North America
Caribbean
430.0
NaN
17000
73.3
96.0
NaN
The Turks and Caicos Islands
Dependent Territory of the UK
Elisabeth II
3423
TC
232
WLF
Wallis and Futuna
Oceania
Polynesia
200.0
NaN
15000
NaN
0.0
NaN
Wallis-et-Futuna
Nonmetropolitan Territory of France
Jacques Chirac
3536
WF
216
TUV
Tuvalu
Oceania
Polynesia
26.0
1978
12000
66.3
6.0
NaN
Tuvalu
Constitutional Monarchy
Elisabeth II
3424
TV
161
NRU
Nauru
Oceania
Micronesia
21.0
1968
12000
60.8
197.0
NaN
Naoero/Nauru
Republic
Bernard Dowiyogo
2728
NR
145
MSR
Montserrat
North America
Caribbean
102.0
NaN
11000
78.0
109.0
NaN
Montserrat
Dependent Territory of the UK
Elisabeth II
2697
MS
3
AIA
Anguilla
North America
Caribbean
96.0
NaN
8000
76.1
63.2
NaN
Anguilla
Dependent Territory of the UK
Elisabeth II
62
AI
195
SPM
Saint Pierre and Miquelon
North America
North America
242.0
NaN
7000
77.6
0.0
NaN
Saint-Pierre-et-Miquelon
Territorial Collectivity of France
Jacques Chirac
3067
PM
188
SHN
Saint Helena
Africa
Western Africa
314.0
NaN
6000
76.8
0.0
NaN
Saint Helena
Dependent Territory of the UK
Elisabeth II
3063
SH
189
SJM
Svalbard and Jan Mayen
Europe
Nordic Countries
62422.0
NaN
3200
NaN
0.0
NaN
Svalbard og Jan Mayen
Dependent Territory of Norway
Harald V
938
SJ
52
CXR
Christmas Island
Oceania
Australia and New Zealand
135.0
NaN
2500
NaN
0.0
NaN
Christmas Island
Territory of Australia
Elisabeth II
1791
CX
71
FLK
Falkland Islands
South America
South America
12173.0
NaN
2000
NaN
0.0
NaN
Falkland Islands
Dependent Territory of the UK
Elisabeth II
763
FK
209
TKL
Tokelau
Oceania
Polynesia
12.0
NaN
2000
NaN
0.0
NaN
Tokelau
Nonmetropolitan Territory of New Zealand
Elisabeth II
3333
TK
157
NIU
Niue
Oceania
Polynesia
260.0
NaN
2000
NaN
0.0
NaN
Niue
Nonmetropolitan Territory of New Zealand
Elisabeth II
2805
NU
154
NFK
Norfolk Island
Oceania
Australia and New Zealand
36.0
NaN
2000
NaN
0.0
NaN
Norfolk Island
Territory of Australia
Elisabeth II
2806
NF
225
VAT
Holy See (Vatican City State)
Europe
Southern Europe
0.4
1929
1000
NaN
9.0
NaN
Santa Sede/Città del Vaticano
Independent Church State
Johannes Paavali II
3538
VA
38
CCK
Cocos (Keeling) Islands
Oceania
Australia and New Zealand
14.0
NaN
600
NaN
0.0
NaN
Cocos (Keeling) Islands
Territory of Australia
Elisabeth II
2317
CC
166
PCN
Pitcairn
Oceania
Polynesia
49.0
NaN
50
NaN
0.0
NaN
Pitcairn
Dependent Territory of the UK
Elisabeth II
2912
PN
11
ATA
Antarctica
Antarctica
Antarctica
13120000.0
NaN
0
NaN
0.0
NaN
–
Co-administrated
NaN
AQ
34
BVT
Bouvet Island
Antarctica
Antarctica
59.0
NaN
0
NaN
0.0
NaN
Bouvetøya
Dependent Territory of Norway
Harald V
NaN
BV
221
UMI
United States Minor Outlying Islands
Oceania
Micronesia/Caribbean
16.0
NaN
0
NaN
0.0
NaN
United States Minor Outlying Islands
Dependent Territory of the US
George W. Bush
NaN
UM
93
HMD
Heard Island and McDonald Islands
Antarctica
Antarctica
359.0
NaN
0
NaN
0.0
NaN
Heard and McDonald Islands
Territory of Australia
Elisabeth II
NaN
HM
100
IOT
British Indian Ocean Territory
Africa
Eastern Africa
78.0
NaN
0
NaN
0.0
NaN
British Indian Ocean Territory
Dependent Territory of the UK
Elisabeth II
NaN
IO
187
SGS
South Georgia and the South Sandwich Islands
Antarctica
Antarctica
3903.0
NaN
0
NaN
0.0
NaN
South Georgia and the South Sandwich Islands
Dependent Territory of the UK
Elisabeth II
NaN
GS
12
ATF
French Southern territories
Antarctica
Antarctica
7780.0
NaN
0
NaN
0.0
NaN
Terres australes françaises
Nonmetropolitan Territory of France
Jacques Chirac
NaN
TF
239 rows × 15 columns
Content source: dobestan/data-science-school
Similar notebooks: