MySQL 테이블을 생성하고 데이터를 넣기

In [1]:

    

import os

import pandas as pd

import MySQLdb


    

In [2]:

    

DATABASE_HOST = "localhost"  # "localhost" == "127.0.0.1"

DATABASE_USERNAME = os.environ.get("DATABASE_USERNAME", "YOUR_USERNAME")
DATABASE_PASSWORD = os.environ.get("DATABASE_PASSWORD", "YOUR_PASSWORD")

DATABASE_NAME = "fastcampus_data_science_db"


    

In [3]:

    

connection = MySQLdb.connect(
    DATABASE_HOST,
    DATABASE_USERNAME,
    DATABASE_PASSWORD,
    DATABASE_NAME,
    charset='utf8',
)


    

In [4]:

    

connection

cursor = connection.cursor()


    

In [5]:

    

SQL_QUERY = """
    CREATE TABLE IF NOT EXISTS fastroom 
    (
        email varchar(255),
        phonenumber varchar(255),
        address varchar(255),
        deposit int,
        rent int
    );
"""

cursor.execute(SQL_QUERY)


    

    




/Users/dobestan/.pyenv/versions/datascience/lib/python3.5/site-packages/ipykernel/__main__.py:12: Warning: Table 'fastroom' already exists






    
Out[5]:





0

In [6]:

    

SQL_QUERY = """
    SHOW TABLES;
"""

cursor.execute(SQL_QUERY)
cursor.fetchall()


    

    
Out[6]:





(('fastroom',),)

In [7]:

    

SQL_QUERY = """
    SELECT *
    FROM fastroom;
"""

pd.read_sql(SQL_QUERY, connection)


    

    
Out[7]:






  

    

      

      
email
      
phonenumber
      
address
      
deposit
      
rent
    
  
  

  

In [8]:

    

# 지금까지 추가된 row 데이터 제거

# SQL_QUERY
SQL_QUERY = """
    DELETE FROM fastroom;
"""

cursor.execute(SQL_QUERY)


    

    
Out[8]:





0

In [9]:

    

SQL_QUERY = """
    INSERT INTO fastroom (email, phonenumber, address, deposit, rent)
    VALUES (
        "dobestan@gmail.com",
        "010-2220-5736",
        "서울시 강남구 논현1동 2-9 대기빌딩 1,4층",
        1000,
        50
    );
"""

cursor.execute(SQL_QUERY)


    

    
Out[9]:





1

In [10]:

    

SQL_QUERY = """
    SELECT *
    FROM fastroom;
"""

pd.read_sql(SQL_QUERY, connection)


    

    
Out[10]:






  

    

      

      
email
      
phonenumber
      
address
      
deposit
      
rent
    
  
  

    

      
0
      
dobestan@gmail.com
      
010-2220-5736
      
서울시 강남구 논현1동 2-9 대기빌딩 1,4층
      
1000
      
50