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 - ALIAS
SQL_QUERY = """
SELECT
Name 국가명,
Continent 대륙명,
Region 지역명
FROM
Country
;
"""
pd.read_sql(SQL_QUERY, db)
Out[3]:
국가명
대륙명
지역명
0
Aruba
North America
Caribbean
1
Afghanistan
Asia
Southern and Central Asia
2
Angola
Africa
Central Africa
3
Anguilla
North America
Caribbean
4
Albania
Europe
Southern Europe
5
Andorra
Europe
Southern Europe
6
Netherlands Antilles
North America
Caribbean
7
United Arab Emirates
Asia
Middle East
8
Argentina
South America
South America
9
Armenia
Asia
Middle East
10
American Samoa
Oceania
Polynesia
11
Antarctica
Antarctica
Antarctica
12
French Southern territories
Antarctica
Antarctica
13
Antigua and Barbuda
North America
Caribbean
14
Australia
Oceania
Australia and New Zealand
15
Austria
Europe
Western Europe
16
Azerbaijan
Asia
Middle East
17
Burundi
Africa
Eastern Africa
18
Belgium
Europe
Western Europe
19
Benin
Africa
Western Africa
20
Burkina Faso
Africa
Western Africa
21
Bangladesh
Asia
Southern and Central Asia
22
Bulgaria
Europe
Eastern Europe
23
Bahrain
Asia
Middle East
24
Bahamas
North America
Caribbean
25
Bosnia and Herzegovina
Europe
Southern Europe
26
Belarus
Europe
Eastern Europe
27
Belize
North America
Central America
28
Bermuda
North America
North America
29
Bolivia
South America
South America
...
...
...
...
209
Tokelau
Oceania
Polynesia
210
Turkmenistan
Asia
Southern and Central Asia
211
East Timor
Asia
Southeast Asia
212
Tonga
Oceania
Polynesia
213
Trinidad and Tobago
North America
Caribbean
214
Tunisia
Africa
Northern Africa
215
Turkey
Asia
Middle East
216
Tuvalu
Oceania
Polynesia
217
Taiwan
Asia
Eastern Asia
218
Tanzania
Africa
Eastern Africa
219
Uganda
Africa
Eastern Africa
220
Ukraine
Europe
Eastern Europe
221
United States Minor Outlying Islands
Oceania
Micronesia/Caribbean
222
Uruguay
South America
South America
223
United States
North America
North America
224
Uzbekistan
Asia
Southern and Central Asia
225
Holy See (Vatican City State)
Europe
Southern Europe
226
Saint Vincent and the Grenadines
North America
Caribbean
227
Venezuela
South America
South America
228
Virgin Islands, British
North America
Caribbean
229
Virgin Islands, U.S.
North America
Caribbean
230
Vietnam
Asia
Southeast Asia
231
Vanuatu
Oceania
Melanesia
232
Wallis and Futuna
Oceania
Polynesia
233
Samoa
Oceania
Polynesia
234
Yemen
Asia
Middle East
235
Yugoslavia
Europe
Southern Europe
236
South Africa
Africa
Southern Africa
237
Zambia
Africa
Eastern Africa
238
Zimbabwe
Africa
Eastern Africa
239 rows × 3 columns
In [4]:
# Pandas Perspective - rename columns
SQL_QUERY = "SELECT * FROM Country;"
df = pd.read_sql(SQL_QUERY, db)
df
Out[4]:
Code
Name
Continent
Region
SurfaceArea
IndepYear
Population
LifeExpectancy
GNP
GNPOld
LocalName
GovernmentForm
HeadOfState
Capital
Code2
0
ABW
Aruba
North America
Caribbean
193.0
NaN
103000
78.4
828.0
793
Aruba
Nonmetropolitan Territory of The Netherlands
Beatrix
129
AW
1
AFG
Afghanistan
Asia
Southern and Central Asia
652090.0
1919
22720000
45.9
5976.0
NaN
Afganistan/Afqanestan
Islamic Emirate
Mohammad Omar
1
AF
2
AGO
Angola
Africa
Central Africa
1246700.0
1975
12878000
38.3
6648.0
7984
Angola
Republic
José Eduardo dos Santos
56
AO
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
4
ALB
Albania
Europe
Southern Europe
28748.0
1912
3401200
71.6
3205.0
2500
Shqipëria
Republic
Rexhep Mejdani
34
AL
5
AND
Andorra
Europe
Southern Europe
468.0
1278
78000
83.5
1630.0
NaN
Andorra
Parliamentary Coprincipality
55
AD
6
ANT
Netherlands Antilles
North America
Caribbean
800.0
NaN
217000
74.7
1941.0
NaN
Nederlandse Antillen
Nonmetropolitan Territory of The Netherlands
Beatrix
33
AN
7
ARE
United Arab Emirates
Asia
Middle East
83600.0
1971
2441000
74.1
37966.0
36846
Al-Imarat al-´Arabiya al-Muttahida
Emirate Federation
Zayid bin Sultan al-Nahayan
65
AE
8
ARG
Argentina
South America
South America
2780400.0
1816
37032000
75.1
340238.0
323310
Argentina
Federal Republic
Fernando de la Rúa
69
AR
9
ARM
Armenia
Asia
Middle East
29800.0
1991
3520000
66.4
1813.0
1627
Hajastan
Republic
Robert Kotšarjan
126
AM
10
ASM
American Samoa
Oceania
Polynesia
199.0
NaN
68000
75.1
334.0
NaN
Amerika Samoa
US Territory
George W. Bush
54
AS
11
ATA
Antarctica
Antarctica
Antarctica
13120000.0
NaN
0
NaN
0.0
NaN
–
Co-administrated
NaN
AQ
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
13
ATG
Antigua and Barbuda
North America
Caribbean
442.0
1981
68000
70.5
612.0
584
Antigua and Barbuda
Constitutional Monarchy
Elisabeth II
63
AG
14
AUS
Australia
Oceania
Australia and New Zealand
7741220.0
1901
18886000
79.8
351182.0
392911
Australia
Constitutional Monarchy, Federation
Elisabeth II
135
AU
15
AUT
Austria
Europe
Western Europe
83859.0
1918
8091800
77.7
211860.0
206025
Österreich
Federal Republic
Thomas Klestil
1523
AT
16
AZE
Azerbaijan
Asia
Middle East
86600.0
1991
7734000
62.9
4127.0
4100
Azärbaycan
Federal Republic
Heydär Äliyev
144
AZ
17
BDI
Burundi
Africa
Eastern Africa
27834.0
1962
6695000
46.2
903.0
982
Burundi/Uburundi
Republic
Pierre Buyoya
552
BI
18
BEL
Belgium
Europe
Western Europe
30518.0
1830
10239000
77.8
249704.0
243948
België/Belgique
Constitutional Monarchy, Federation
Albert II
179
BE
19
BEN
Benin
Africa
Western Africa
112622.0
1960
6097000
50.2
2357.0
2141
Bénin
Republic
Mathieu Kérékou
187
BJ
20
BFA
Burkina Faso
Africa
Western Africa
274000.0
1960
11937000
46.7
2425.0
2201
Burkina Faso
Republic
Blaise Compaoré
549
BF
21
BGD
Bangladesh
Asia
Southern and Central Asia
143998.0
1971
129155000
60.2
32852.0
31966
Bangladesh
Republic
Shahabuddin Ahmad
150
BD
22
BGR
Bulgaria
Europe
Eastern Europe
110994.0
1908
8190900
70.9
12178.0
10169
Balgarija
Republic
Petar Stojanov
539
BG
23
BHR
Bahrain
Asia
Middle East
694.0
1971
617000
73.0
6366.0
6097
Al-Bahrayn
Monarchy (Emirate)
Hamad ibn Isa al-Khalifa
149
BH
24
BHS
Bahamas
North America
Caribbean
13878.0
1973
307000
71.1
3527.0
3347
The Bahamas
Constitutional Monarchy
Elisabeth II
148
BS
25
BIH
Bosnia and Herzegovina
Europe
Southern Europe
51197.0
1992
3972000
71.5
2841.0
NaN
Bosna i Hercegovina
Federal Republic
Ante Jelavic
201
BA
26
BLR
Belarus
Europe
Eastern Europe
207600.0
1991
10236000
68.0
13714.0
NaN
Belarus
Republic
Aljaksandr Lukašenka
3520
BY
27
BLZ
Belize
North America
Central America
22696.0
1981
241000
70.9
630.0
616
Belize
Constitutional Monarchy
Elisabeth II
185
BZ
28
BMU
Bermuda
North America
North America
53.0
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.0
1825
8329000
63.7
8571.0
7967
Bolivia
Republic
Hugo Bánzer Suárez
194
BO
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
209
TKL
Tokelau
Oceania
Polynesia
12.0
NaN
2000
NaN
0.0
NaN
Tokelau
Nonmetropolitan Territory of New Zealand
Elisabeth II
3333
TK
210
TKM
Turkmenistan
Asia
Southern and Central Asia
488100.0
1991
4459000
60.9
4397.0
2000
Türkmenostan
Republic
Saparmurad Nijazov
3419
TM
211
TMP
East Timor
Asia
Southeast Asia
14874.0
NaN
885000
46.0
0.0
NaN
Timor Timur
Administrated by the UN
José Alexandre Gusmão
1522
TP
212
TON
Tonga
Oceania
Polynesia
650.0
1970
99000
67.9
146.0
170
Tonga
Monarchy
Taufa'ahau Tupou IV
3334
TO
213
TTO
Trinidad and Tobago
North America
Caribbean
5130.0
1962
1295000
68.0
6232.0
5867
Trinidad and Tobago
Republic
Arthur N. R. Robinson
3336
TT
214
TUN
Tunisia
Africa
Northern Africa
163610.0
1956
9586000
73.7
20026.0
18898
Tunis/Tunisie
Republic
Zine al-Abidine Ben Ali
3349
TN
215
TUR
Turkey
Asia
Middle East
774815.0
1923
66591000
71.0
210721.0
189122
Türkiye
Republic
Ahmet Necdet Sezer
3358
TR
216
TUV
Tuvalu
Oceania
Polynesia
26.0
1978
12000
66.3
6.0
NaN
Tuvalu
Constitutional Monarchy
Elisabeth II
3424
TV
217
TWN
Taiwan
Asia
Eastern Asia
36188.0
1945
22256000
76.4
256254.0
263451
T’ai-wan
Republic
Chen Shui-bian
3263
TW
218
TZA
Tanzania
Africa
Eastern Africa
883749.0
1961
33517000
52.3
8005.0
7388
Tanzania
Republic
Benjamin William Mkapa
3306
TZ
219
UGA
Uganda
Africa
Eastern Africa
241038.0
1962
21778000
42.9
6313.0
6887
Uganda
Republic
Yoweri Museveni
3425
UG
220
UKR
Ukraine
Europe
Eastern Europe
603700.0
1991
50456000
66.0
42168.0
49677
Ukrajina
Republic
Leonid Kutšma
3426
UA
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
222
URY
Uruguay
South America
South America
175016.0
1828
3337000
75.2
20831.0
19967
Uruguay
Republic
Jorge Batlle Ibáñez
3492
UY
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
224
UZB
Uzbekistan
Asia
Southern and Central Asia
447400.0
1991
24318000
63.7
14194.0
21300
Uzbekiston
Republic
Islam Karimov
3503
UZ
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
226
VCT
Saint Vincent and the Grenadines
North America
Caribbean
388.0
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.0
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.0
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.0
NaN
93000
78.1
0.0
NaN
Virgin Islands of the United States
US Territory
George W. Bush
4067
VI
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
231
VUT
Vanuatu
Oceania
Melanesia
12189.0
1980
190000
60.6
261.0
246
Vanuatu
Republic
John Bani
3537
VU
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
233
WSM
Samoa
Oceania
Polynesia
2831.0
1962
180000
69.2
141.0
157
Samoa
Parlementary Monarchy
Malietoa Tanumafili II
3169
WS
234
YEM
Yemen
Asia
Middle East
527968.0
1918
18112000
59.8
6041.0
5729
Al-Yaman
Republic
Ali Abdallah Salih
1780
YE
235
YUG
Yugoslavia
Europe
Southern Europe
102173.0
1918
10640000
72.4
17000.0
NaN
Jugoslavija
Federal Republic
Vojislav Koštunica
1792
YU
236
ZAF
South Africa
Africa
Southern Africa
1221037.0
1910
40377000
51.1
116729.0
129092
South Africa
Republic
Thabo Mbeki
716
ZA
237
ZMB
Zambia
Africa
Eastern Africa
752618.0
1964
9169000
37.2
3377.0
3922
Zambia
Republic
Frederick Chiluba
3162
ZM
238
ZWE
Zimbabwe
Africa
Eastern Africa
390757.0
1980
11669000
37.8
5951.0
8670
Zimbabwe
Republic
Robert G. Mugabe
4068
ZW
239 rows × 15 columns
In [5]:
# 특정 Column 만 SELECT 하기
new_df = df[["Name", "Continent", "Region"]]
In [6]:
new_df
Out[6]:
Name
Continent
Region
0
Aruba
North America
Caribbean
1
Afghanistan
Asia
Southern and Central Asia
2
Angola
Africa
Central Africa
3
Anguilla
North America
Caribbean
4
Albania
Europe
Southern Europe
5
Andorra
Europe
Southern Europe
6
Netherlands Antilles
North America
Caribbean
7
United Arab Emirates
Asia
Middle East
8
Argentina
South America
South America
9
Armenia
Asia
Middle East
10
American Samoa
Oceania
Polynesia
11
Antarctica
Antarctica
Antarctica
12
French Southern territories
Antarctica
Antarctica
13
Antigua and Barbuda
North America
Caribbean
14
Australia
Oceania
Australia and New Zealand
15
Austria
Europe
Western Europe
16
Azerbaijan
Asia
Middle East
17
Burundi
Africa
Eastern Africa
18
Belgium
Europe
Western Europe
19
Benin
Africa
Western Africa
20
Burkina Faso
Africa
Western Africa
21
Bangladesh
Asia
Southern and Central Asia
22
Bulgaria
Europe
Eastern Europe
23
Bahrain
Asia
Middle East
24
Bahamas
North America
Caribbean
25
Bosnia and Herzegovina
Europe
Southern Europe
26
Belarus
Europe
Eastern Europe
27
Belize
North America
Central America
28
Bermuda
North America
North America
29
Bolivia
South America
South America
...
...
...
...
209
Tokelau
Oceania
Polynesia
210
Turkmenistan
Asia
Southern and Central Asia
211
East Timor
Asia
Southeast Asia
212
Tonga
Oceania
Polynesia
213
Trinidad and Tobago
North America
Caribbean
214
Tunisia
Africa
Northern Africa
215
Turkey
Asia
Middle East
216
Tuvalu
Oceania
Polynesia
217
Taiwan
Asia
Eastern Asia
218
Tanzania
Africa
Eastern Africa
219
Uganda
Africa
Eastern Africa
220
Ukraine
Europe
Eastern Europe
221
United States Minor Outlying Islands
Oceania
Micronesia/Caribbean
222
Uruguay
South America
South America
223
United States
North America
North America
224
Uzbekistan
Asia
Southern and Central Asia
225
Holy See (Vatican City State)
Europe
Southern Europe
226
Saint Vincent and the Grenadines
North America
Caribbean
227
Venezuela
South America
South America
228
Virgin Islands, British
North America
Caribbean
229
Virgin Islands, U.S.
North America
Caribbean
230
Vietnam
Asia
Southeast Asia
231
Vanuatu
Oceania
Melanesia
232
Wallis and Futuna
Oceania
Polynesia
233
Samoa
Oceania
Polynesia
234
Yemen
Asia
Middle East
235
Yugoslavia
Europe
Southern Europe
236
South Africa
Africa
Southern Africa
237
Zambia
Africa
Eastern Africa
238
Zimbabwe
Africa
Eastern Africa
239 rows × 3 columns
In [7]:
# 컬럼명 변경하기
new_df.rename(columns={
"Name": "국가명",
"Continent": "대륙명",
"Region": "지역명",
})
Out[7]:
국가명
대륙명
지역명
0
Aruba
North America
Caribbean
1
Afghanistan
Asia
Southern and Central Asia
2
Angola
Africa
Central Africa
3
Anguilla
North America
Caribbean
4
Albania
Europe
Southern Europe
5
Andorra
Europe
Southern Europe
6
Netherlands Antilles
North America
Caribbean
7
United Arab Emirates
Asia
Middle East
8
Argentina
South America
South America
9
Armenia
Asia
Middle East
10
American Samoa
Oceania
Polynesia
11
Antarctica
Antarctica
Antarctica
12
French Southern territories
Antarctica
Antarctica
13
Antigua and Barbuda
North America
Caribbean
14
Australia
Oceania
Australia and New Zealand
15
Austria
Europe
Western Europe
16
Azerbaijan
Asia
Middle East
17
Burundi
Africa
Eastern Africa
18
Belgium
Europe
Western Europe
19
Benin
Africa
Western Africa
20
Burkina Faso
Africa
Western Africa
21
Bangladesh
Asia
Southern and Central Asia
22
Bulgaria
Europe
Eastern Europe
23
Bahrain
Asia
Middle East
24
Bahamas
North America
Caribbean
25
Bosnia and Herzegovina
Europe
Southern Europe
26
Belarus
Europe
Eastern Europe
27
Belize
North America
Central America
28
Bermuda
North America
North America
29
Bolivia
South America
South America
...
...
...
...
209
Tokelau
Oceania
Polynesia
210
Turkmenistan
Asia
Southern and Central Asia
211
East Timor
Asia
Southeast Asia
212
Tonga
Oceania
Polynesia
213
Trinidad and Tobago
North America
Caribbean
214
Tunisia
Africa
Northern Africa
215
Turkey
Asia
Middle East
216
Tuvalu
Oceania
Polynesia
217
Taiwan
Asia
Eastern Asia
218
Tanzania
Africa
Eastern Africa
219
Uganda
Africa
Eastern Africa
220
Ukraine
Europe
Eastern Europe
221
United States Minor Outlying Islands
Oceania
Micronesia/Caribbean
222
Uruguay
South America
South America
223
United States
North America
North America
224
Uzbekistan
Asia
Southern and Central Asia
225
Holy See (Vatican City State)
Europe
Southern Europe
226
Saint Vincent and the Grenadines
North America
Caribbean
227
Venezuela
South America
South America
228
Virgin Islands, British
North America
Caribbean
229
Virgin Islands, U.S.
North America
Caribbean
230
Vietnam
Asia
Southeast Asia
231
Vanuatu
Oceania
Melanesia
232
Wallis and Futuna
Oceania
Polynesia
233
Samoa
Oceania
Polynesia
234
Yemen
Asia
Middle East
235
Yugoslavia
Europe
Southern Europe
236
South Africa
Africa
Southern Africa
237
Zambia
Africa
Eastern Africa
238
Zimbabwe
Africa
Eastern Africa
239 rows × 3 columns
Content source: dobestan/data-science-school
Similar notebooks: