python - mysql 연결

SQL ALCHEMY

• python에서 사요하는 대표적인 ORM
  ORM: Object Relational Mapping
  • SQL 데이터베이스와 파이썬의 상호작용을 지원하는 강력한 라이브러리! 
  • 데이터베이스를 객체화시켜 데이터베이스에 있는 데이터를 CRUD
  • 사용하는 DBSMS가 변경되면 엔진만 바꿔주면 된다.
  • 쿼리 대신 함수 형태(메소드) CRUD를 할 수 있다.
     
• 설치

pip install --upgrade 'sqlalchemy<2.0'

 

Prerequisite

import pandas as pd
from sqlalchemy import create_engine

• create_engine 의 기능
  •  함수는 데이터베이스와의 연결을 설정하고 관리하는 데 사용된다.
  • 데이터베이스에 대한 연결 설정: 함수에 데이터베이스의 종류, 사용자 이름, 비밀번호, 호스트 주소 및 데이터베이스 이름과 같은 연결 정보를 제공하여 데이터베이스와의 연결을 설정.
  • 데이터베이스 엔진 생성: 함수는 지정한 연결 정보를 기반으로 데이터베이스 엔진을 생성한다. 데이터베이스 엔진은 SQLAlchemy를 통해 데이터베이스와 상호 작용하는 데 사용된다.
  • 연결 관리: 생성된 데이터베이스 엔진을 사용하여 데이터베이스와의 연결을 관리하고 SQL 쿼리를 실행한다. 연결 관리와 쿼리 실행은 SQLAlchemy의 ORM(Object-Relational Mapping) 및 Core 기능을 사용하여 수행된다.

 

 

1. SQL DB - python 연결하기

• engine 만들기

# MySQL 연결 정보 설정
username = '입력하세요'
password = '입력하세요'
hostname = '입력하세요'
database_name = '입력하세요'


# DB 정보를 활용해서 Connection String 만들기
conn = f'mysql://{username}:{password}@{hostname}/{database_name}?charset=utf8'

# 엔진 생성
engine = create_engine(conn)

# 엔진 연결 끊기
engine.dispose()

내가 헷갈려서 써놓는 추가 설명.

MySQL을 들어가면 첫 화면에 위의 사진과 같이 connection들이 보인다.

 

여기서 hostname는 로컬에서 사용한다면 'localhost'  (또는 위의 사진에서 root 아래에 있는 127.0.~~ 이걸 사용하면 된다. 단, port 번호는 빼고)를 입력하면 된다.

username는 위의 사진에서 사람 아이콘 옆에 있는 'root'이고 (이는 connection을 만들 때 다르게 설정 가능하다.)

password는 connection을 생성할 때 설정했던 비밀번호를 입력하면 된다.

database_name은 connection 내에 생성된 db 중 연결하고 싶은 db의 이름을 쓰면 된다.

 

 

2. SQL db의 테이블을 python 환경에서 불러오기

engine 생성까지는 1번 과정과 동일하다.

엔진을 생성한 후 pd.read_sql()을 통해 query와 engine을 함께 넣고 데이터베이스의 테이블을 불러온다.

# MySQL 연결 정보 설정
username = '입력하세요'
password = '입력하세요'
hostname = '입력하세요'
database_name = '입력하세요'


# DB 정보를 활용해서 Connection String 만들기
conn = f'mysql://{username}:{password}@{hostname}/{database_name}?charset=utf8'

# 엔진 생성
engine = create_engine(conn)

# SQL 쿼리를 사용하여 테이블 데이터를 읽어옵니다.
query = "SELECT * FROM sql_analyze.tb_popltn_data"

# Pandas의 read_sql_query 함수를 사용하여 데이터를 읽어옵니다.
df = pd.read_sql_query(query, engine)
df

 

함수화

def sql_table_import(username, password, host_name, database_name, query):
    import pandas as pd
    from sqlalchemy import create_engine

    conn = f'mysql://{username}:{password}@{host_name}/{database_name}?charset=utf8'

    # 엔진 생성
    engine = create_engine(conn)

    query = query
    df = pd.read_sql_query(query, engine)
    engine.dispose()
    return df

 

3. python의 데이터프레임을 SQL db에 적재하기

# MySQL 연결 정보 설정
username = '입력하세요'
password = '입력하세요'
hostname = '입력하세요'
database_name = '입력하세요'


# DB 정보를 활용해서 Connection String 만들기
conn = f'mysql://{username}:{password}@{hostname}/{database_name}?charset=utf8'

# 엔진 생성
engine = create_engine(conn)


# 데이터프레임을 MySQL 테이블로 적재
table_name = 'db에 적재될 테이블 명을 입력하세요.'  # 새로운 테이블 이름
df.to_sql(table_name, con=engine, if_exists='replace', index=False)

# 연결 종료
engine.dispose()

engine까지는 1번 과정과 동일하다.

여기서 df는 pandas의 데이터 프레임이다.

이 때 df의 column명에 마침표나 콤마 등의 기호가 없는지 잘 확인할 것.

 

함수화

def df_to_sql(username, password, host_name, database_name, df,db_table_name):
    import pandas as pd
    from sqlalchemy import create_engine

    conn = f'mysql://{username}:{password}@{host_name}/{database_name}?charset=utf8'

    # 엔진 생성
    engine = create_engine(conn)

    table_name = db_table_name
    df.to_sql(table_name, con=engine, if_exists='replace',index = False)

    engine.dispose()
    return

 

---