In [1]:

    

import pandas as pd
import MySQLdb


    

In [47]:

    

server = 'ec2-54-149-163-97.us-west-2.compute.amazonaws.com'


    

In [52]:

    

db = MySQLdb.connect(server, 'root','test1234', 'sakila')


    

    




---------------------------------------------------------------------------
OperationalError                          Traceback (most recent call last)
<ipython-input-52-f393f8ed1e27> in <module>()
----> 1 db = MySQLdb.connect(server, 'root','test1234', 'sakila')

c:\python27\lib\site-packages\MySQLdb\__init__.pyc in Connect(*args, **kwargs)
     79     """Factory function for connections.Connection."""
     80     from MySQLdb.connections import Connection
---> 81     return Connection(*args, **kwargs)
     82 
     83 connect = Connection = Connect

c:\python27\lib\site-packages\MySQLdb\connections.pyc in __init__(self, *args, **kwargs)
    191         autocommit = kwargs2.pop('autocommit', False)
    192 
--> 193         super(Connection, self).__init__(*args, **kwargs2)
    194         self.cursorclass = cursorclass
    195         self.encoders = dict([ (k, v) for k, v in conv.items()

OperationalError: (1049, "Unknown database 'sakila'")

In [50]:

    

country_df = pd.read_sql("SELECT * FROM country;", db)
city_df = pd.read_sql("SELECT * FROM city;", db)


    

In [14]:

    

country_df.columns


    

    
Out[14]:





Index([u'Code', u'Name', u'Continent', u'Region', u'SurfaceArea', u'IndepYear',
       u'Population', u'LifeExpectancy', u'GNP', u'GNPOld', u'LocalName',
       u'GovernmentForm', u'HeadOfState', u'Capital', u'Code2'],
      dtype='object')

In [15]:

    

city_df.columns


    

    
Out[15]:





Index([u'ID', u'Name', u'CountryCode', u'District', u'Population'], dtype='object')

In [28]:

    

# countrycode 와 code 
city_df.merge(country_df, left_on = "CountryCode", right_on = "Code")[["Name_x","Name_y"]].head()


    

    
Out[28]:






  

    

      

      
Name_x
      
Name_y
    
  
  

    

      
0
      
Kabul
      
Afghanistan
    
    

      
1
      
Qandahar
      
Afghanistan
    
    

      
2
      
Herat
      
Afghanistan
    
    

      
3
      
Mazar-e-Sharif
      
Afghanistan
    
    

      
4
      
Amsterdam
      
Netherlands
    
  

In [32]:

    

SQL_QUERY = '''
    SELECT co.Name 'country name', ci.Name 'city name'
    FROM country co, city ci
    WHERE co.Code = ci.CountryCode
    ;
'''
pd.read_sql(SQL_QUERY, db).head()


    

    
Out[32]:






  

    

      

      
country name
      
city name
    
  
  

    

      
0
      
Aruba
      
Oranjestad
    
    

      
1
      
Afghanistan
      
Kabul
    
    

      
2
      
Afghanistan
      
Qandahar
    
    

      
3
      
Afghanistan
      
Herat
    
    

      
4
      
Afghanistan
      
Mazar-e-Sharif
    
  

In [33]:

    

# 위에랑 똑같지만 DB에 부담을 덜주면서 연산이 가능하다
SQL_QUERY = '''
    SELECT co.Name 'country name', ci.Name 'city name'
    FROM country co
        JOIN city ci
        ON co.Code = ci.CountryCode
    ;
'''
pd.read_sql(SQL_QUERY, db).head()


    

    
Out[33]:






  

    

      

      
country name
      
city name
    
  
  

    

      
0
      
Aruba
      
Oranjestad
    
    

      
1
      
Afghanistan
      
Kabul
    
    

      
2
      
Afghanistan
      
Qandahar
    
    

      
3
      
Afghanistan
      
Herat
    
    

      
4
      
Afghanistan
      
Mazar-e-Sharif
    
  

In [34]:

    

import os


    

In [40]:

    

# file 가져오기
[
    file_name
    for file_name
    in os.listdir("../")
    if file_name.endswith(".ipynb")
]


    

    
Out[40]:





[]

In [42]:

    

os.path.join("baseball_players_salary_pred", "baseball.csv")


    

    
Out[42]:





'baseball_players_salary_pred\\baseball.csv'

In [41]:

    

# os.curdir 현재 
# os.path.join 
# os.listdir


    

    
Out[41]:





'.'

In [ ]:

    




    

In [ ]: