notebook.community

Edit and run

MySQL 데이터베이스에 연결하기





In [1]:



    


import os



    











In [2]:



    


import pandas as pd


# 이 경우에는 `pip install MySQLdb` 라고 설치하지 않았는데, 왜 import 가 가능할까요?
# pypi에 올라가 있는 패키지명은 mysqlclient 지만, 모듈명이 MySQLdb 라서 이렇게 호출을 해주셔야 합니다.

# 단, 이 경우를 제외하고 수업 시간에 다룰 대부분의 패키지는 패키지명과 모듈명이 동일해서
# 설치한 패키지명으로 import를 해주시면 됩니다.
import MySQLdb



    











In [3]:



    


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 = "sakila"



    











In [4]:



    


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



    











In [5]:



    


connection



    


















    
Out[5]:








<_mysql.connection open to 'localhost' at 7fcfcd1c0a18>

















In [6]:



    


SQL_QUERY = "SELECT * FROM customer;"

customer_df = pd.read_sql(SQL_QUERY, connection)



    











In [7]:



    


customer_df



    


















    
Out[7]:










  
    

      
      customer_id
      store_id
      first_name
      last_name
      email
      address_id
      active
      create_date
      last_update
    

  
  
    

      0
      1
      1
      MARY
      SMITH
      MARY.SMITH@sakilacustomer.org
      5
      1
      2006-02-14 22:04:36
      2006-02-15 04:57:20
    

    

      1
      2
      1
      PATRICIA
      JOHNSON
      PATRICIA.JOHNSON@sakilacustomer.org
      6
      1
      2006-02-14 22:04:36
      2006-02-15 04:57:20
    

    

      2
      3
      1
      LINDA
      WILLIAMS
      LINDA.WILLIAMS@sakilacustomer.org
      7
      1
      2006-02-14 22:04:36
      2006-02-15 04:57:20
    

    

      3
      4
      2
      BARBARA
      JONES
      BARBARA.JONES@sakilacustomer.org
      8
      1
      2006-02-14 22:04:36
      2006-02-15 04:57:20
    

    

      4
      5
      1
      ELIZABETH
      BROWN
      ELIZABETH.BROWN@sakilacustomer.org
      9
      1
      2006-02-14 22:04:36
      2006-02-15 04:57:20
    

    

      5
      6
      2
      JENNIFER
      DAVIS
      JENNIFER.DAVIS@sakilacustomer.org
      10
      1
      2006-02-14 22:04:36
      2006-02-15 04:57:20
    

    

      6
      7
      1
      MARIA
      MILLER
      MARIA.MILLER@sakilacustomer.org
      11
      1
      2006-02-14 22:04:36
      2006-02-15 04:57:20
    

    

      7
      8
      2
      SUSAN
      WILSON
      SUSAN.WILSON@sakilacustomer.org
      12
      1
      2006-02-14 22:04:36
      2006-02-15 04:57:20
    

    

      8
      9
      2
      MARGARET
      MOORE
      MARGARET.MOORE@sakilacustomer.org
      13
      1
      2006-02-14 22:04:36
      2006-02-15 04:57:20
    

    

      9
      10
      1
      DOROTHY
      TAYLOR
      DOROTHY.TAYLOR@sakilacustomer.org
      14
      1
      2006-02-14 22:04:36
      2006-02-15 04:57:20
    

    

      10
      11
      2
      LISA
      ANDERSON
      LISA.ANDERSON@sakilacustomer.org
      15
      1
      2006-02-14 22:04:36
      2006-02-15 04:57:20
    

    

      11
      12
      1
      NANCY
      THOMAS
      NANCY.THOMAS@sakilacustomer.org
      16
      1
      2006-02-14 22:04:36
      2006-02-15 04:57:20
    

    

      12
      13
      2
      KAREN
      JACKSON
      KAREN.JACKSON@sakilacustomer.org
      17
      1
      2006-02-14 22:04:36
      2006-02-15 04:57:20
    

    

      13
      14
      2
      BETTY
      WHITE
      BETTY.WHITE@sakilacustomer.org
      18
      1
      2006-02-14 22:04:36
      2006-02-15 04:57:20
    

    

      14
      15
      1
      HELEN
      HARRIS
      HELEN.HARRIS@sakilacustomer.org
      19
      1
      2006-02-14 22:04:36
      2006-02-15 04:57:20
    

    

      15
      16
      2
      SANDRA
      MARTIN
      SANDRA.MARTIN@sakilacustomer.org
      20
      0
      2006-02-14 22:04:36
      2006-02-15 04:57:20
    

    

      16
      17
      1
      DONNA
      THOMPSON
      DONNA.THOMPSON@sakilacustomer.org
      21
      1
      2006-02-14 22:04:36
      2006-02-15 04:57:20
    

    

      17
      18
      2
      CAROL
      GARCIA
      CAROL.GARCIA@sakilacustomer.org
      22
      1
      2006-02-14 22:04:36
      2006-02-15 04:57:20
    

    

      18
      19
      1
      RUTH
      MARTINEZ
      RUTH.MARTINEZ@sakilacustomer.org
      23
      1
      2006-02-14 22:04:36
      2006-02-15 04:57:20
    

    

      19
      20
      2
      SHARON
      ROBINSON
      SHARON.ROBINSON@sakilacustomer.org
      24
      1
      2006-02-14 22:04:36
      2006-02-15 04:57:20
    

    

      20
      21
      1
      MICHELLE
      CLARK
      MICHELLE.CLARK@sakilacustomer.org
      25
      1
      2006-02-14 22:04:36
      2006-02-15 04:57:20
    

    

      21
      22
      1
      LAURA
      RODRIGUEZ
      LAURA.RODRIGUEZ@sakilacustomer.org
      26
      1
      2006-02-14 22:04:36
      2006-02-15 04:57:20
    

    

      22
      23
      2
      SARAH
      LEWIS
      SARAH.LEWIS@sakilacustomer.org
      27
      1
      2006-02-14 22:04:36
      2006-02-15 04:57:20
    

    

      23
      24
      2
      KIMBERLY
      LEE
      KIMBERLY.LEE@sakilacustomer.org
      28
      1
      2006-02-14 22:04:36
      2006-02-15 04:57:20
    

    

      24
      25
      1
      DEBORAH
      WALKER
      DEBORAH.WALKER@sakilacustomer.org
      29
      1
      2006-02-14 22:04:36
      2006-02-15 04:57:20
    

    

      25
      26
      2
      JESSICA
      HALL
      JESSICA.HALL@sakilacustomer.org
      30
      1
      2006-02-14 22:04:36
      2006-02-15 04:57:20
    

    

      26
      27
      2
      SHIRLEY
      ALLEN
      SHIRLEY.ALLEN@sakilacustomer.org
      31
      1
      2006-02-14 22:04:36
      2006-02-15 04:57:20
    

    

      27
      28
      1
      CYNTHIA
      YOUNG
      CYNTHIA.YOUNG@sakilacustomer.org
      32
      1
      2006-02-14 22:04:36
      2006-02-15 04:57:20
    

    

      28
      29
      2
      ANGELA
      HERNANDEZ
      ANGELA.HERNANDEZ@sakilacustomer.org
      33
      1
      2006-02-14 22:04:36
      2006-02-15 04:57:20
    

    

      29
      30
      1
      MELISSA
      KING
      MELISSA.KING@sakilacustomer.org
      34
      1
      2006-02-14 22:04:36
      2006-02-15 04:57:20
    

    

      ...
      ...
      ...
      ...
      ...
      ...
      ...
      ...
      ...
      ...
    

    

      569
      570
      2
      IVAN
      CROMWELL
      IVAN.CROMWELL@sakilacustomer.org
      576
      1
      2006-02-14 22:04:37
      2006-02-15 04:57:20
    

    

      570
      571
      2
      JOHNNIE
      CHISHOLM
      JOHNNIE.CHISHOLM@sakilacustomer.org
      577
      1
      2006-02-14 22:04:37
      2006-02-15 04:57:20
    

    

      571
      572
      1
      SIDNEY
      BURLESON
      SIDNEY.BURLESON@sakilacustomer.org
      578
      1
      2006-02-14 22:04:37
      2006-02-15 04:57:20
    

    

      572
      573
      1
      BYRON
      BOX
      BYRON.BOX@sakilacustomer.org
      579
      1
      2006-02-14 22:04:37
      2006-02-15 04:57:20
    

    

      573
      574
      2
      JULIAN
      VEST
      JULIAN.VEST@sakilacustomer.org
      580
      1
      2006-02-14 22:04:37
      2006-02-15 04:57:20
    

    

      574
      575
      2
      ISAAC
      OGLESBY
      ISAAC.OGLESBY@sakilacustomer.org
      581
      1
      2006-02-14 22:04:37
      2006-02-15 04:57:20
    

    

      575
      576
      2
      MORRIS
      MCCARTER
      MORRIS.MCCARTER@sakilacustomer.org
      582
      1
      2006-02-14 22:04:37
      2006-02-15 04:57:20
    

    

      576
      577
      2
      CLIFTON
      MALCOLM
      CLIFTON.MALCOLM@sakilacustomer.org
      583
      1
      2006-02-14 22:04:37
      2006-02-15 04:57:20
    

    

      577
      578
      2
      WILLARD
      LUMPKIN
      WILLARD.LUMPKIN@sakilacustomer.org
      584
      1
      2006-02-14 22:04:37
      2006-02-15 04:57:20
    

    

      578
      579
      2
      DARYL
      LARUE
      DARYL.LARUE@sakilacustomer.org
      585
      1
      2006-02-14 22:04:37
      2006-02-15 04:57:20
    

    

      579
      580
      1
      ROSS
      GREY
      ROSS.GREY@sakilacustomer.org
      586
      1
      2006-02-14 22:04:37
      2006-02-15 04:57:20
    

    

      580
      581
      1
      VIRGIL
      WOFFORD
      VIRGIL.WOFFORD@sakilacustomer.org
      587
      1
      2006-02-14 22:04:37
      2006-02-15 04:57:20
    

    

      581
      582
      2
      ANDY
      VANHORN
      ANDY.VANHORN@sakilacustomer.org
      588
      1
      2006-02-14 22:04:37
      2006-02-15 04:57:20
    

    

      582
      583
      1
      MARSHALL
      THORN
      MARSHALL.THORN@sakilacustomer.org
      589
      1
      2006-02-14 22:04:37
      2006-02-15 04:57:20
    

    

      583
      584
      2
      SALVADOR
      TEEL
      SALVADOR.TEEL@sakilacustomer.org
      590
      1
      2006-02-14 22:04:37
      2006-02-15 04:57:20
    

    

      584
      585
      1
      PERRY
      SWAFFORD
      PERRY.SWAFFORD@sakilacustomer.org
      591
      1
      2006-02-14 22:04:37
      2006-02-15 04:57:20
    

    

      585
      586
      1
      KIRK
      STCLAIR
      KIRK.STCLAIR@sakilacustomer.org
      592
      1
      2006-02-14 22:04:37
      2006-02-15 04:57:20
    

    

      586
      587
      1
      SERGIO
      STANFIELD
      SERGIO.STANFIELD@sakilacustomer.org
      593
      1
      2006-02-14 22:04:37
      2006-02-15 04:57:20
    

    

      587
      588
      1
      MARION
      OCAMPO
      MARION.OCAMPO@sakilacustomer.org
      594
      1
      2006-02-14 22:04:37
      2006-02-15 04:57:20
    

    

      588
      589
      1
      TRACY
      HERRMANN
      TRACY.HERRMANN@sakilacustomer.org
      595
      1
      2006-02-14 22:04:37
      2006-02-15 04:57:20
    

    

      589
      590
      2
      SETH
      HANNON
      SETH.HANNON@sakilacustomer.org
      596
      1
      2006-02-14 22:04:37
      2006-02-15 04:57:20
    

    

      590
      591
      1
      KENT
      ARSENAULT
      KENT.ARSENAULT@sakilacustomer.org
      597
      1
      2006-02-14 22:04:37
      2006-02-15 04:57:20
    

    

      591
      592
      1
      TERRANCE
      ROUSH
      TERRANCE.ROUSH@sakilacustomer.org
      598
      0
      2006-02-14 22:04:37
      2006-02-15 04:57:20
    

    

      592
      593
      2
      RENE
      MCALISTER
      RENE.MCALISTER@sakilacustomer.org
      599
      1
      2006-02-14 22:04:37
      2006-02-15 04:57:20
    

    

      593
      594
      1
      EDUARDO
      HIATT
      EDUARDO.HIATT@sakilacustomer.org
      600
      1
      2006-02-14 22:04:37
      2006-02-15 04:57:20
    

    

      594
      595
      1
      TERRENCE
      GUNDERSON
      TERRENCE.GUNDERSON@sakilacustomer.org
      601
      1
      2006-02-14 22:04:37
      2006-02-15 04:57:20
    

    

      595
      596
      1
      ENRIQUE
      FORSYTHE
      ENRIQUE.FORSYTHE@sakilacustomer.org
      602
      1
      2006-02-14 22:04:37
      2006-02-15 04:57:20
    

    

      596
      597
      1
      FREDDIE
      DUGGAN
      FREDDIE.DUGGAN@sakilacustomer.org
      603
      1
      2006-02-14 22:04:37
      2006-02-15 04:57:20
    

    

      597
      598
      1
      WADE
      DELVALLE
      WADE.DELVALLE@sakilacustomer.org
      604
      1
      2006-02-14 22:04:37
      2006-02-15 04:57:20
    

    

      598
      599
      2
      AUSTIN
      CINTRON
      AUSTIN.CINTRON@sakilacustomer.org
      605
      1
      2006-02-14 22:04:37
      2006-02-15 04:57:20
    

  



599 rows × 9 columns

Content source: dobestan/data-science-school

Similar notebooks:

notebook.community | gallery | about