데이터를 python에서 DBeaver로 보내고 데이터를 입력하는 방법

사전 설정 코드📚

우선 DBeaver에 python데이터를 보내기위한 맵핑과 사전 테이블을 생성해보겠습니다.

from sqlalchemy import create_engine, MetaData, Table, Column
from sqlalchemy import text 
from sqlalchemy import Integer, String, Float, DateTime
from sqlalchemy.engine import URL
import pandas as pd

# MySQL 연결할때 변수 세팅 
DB_SERVER_HOST = 'sparta-rds-mysql.cveowakiit1u.ap-northeast-2.rds.amazonaws.com'
DB_USERNAME = 'tableau_student'
DB_PASSWORD = 'student'
DB_DATABASE = 'student' # DB 이름 
DB_PORT =  3306 

# MySQL 연결 URL 만들기 
connection_url = URL.create(
    drivername="mysql+pymysql",
    username=DB_USERNAME,
    password=DB_PASSWORD,
    host=DB_SERVER_HOST,
    port=DB_PORT,
    database=DB_DATABASE,
)
 
# MySQL 연결 
engine = create_engine(connection_url)

# 테이블에 대한 Metadata 설정 
table_name = 'prediction_value' # 테이블 명 (qcc.customer -> qcc (db명) customer 테이블 명 )
metadata = MetaData()
table = Table(
    table_name, metadata,
    Column('userid_DI', String(100)), # User 값 
    Column('prediction_grade', Float), # Float 값 
)

# CREATE 
metadata.create_all(engine)

부분 부분 설명해보겠습니다.😎

라이브러리

from sqlalchemy import create_engine, MetaData, Table, Column
from sqlalchemy import text 
from sqlalchemy import Integer, String, Float, DateTime
from sqlalchemy.engine import URL
import pandas as pd

• sqlalchemy: SQLAlchemy는 파이썬용 SQL Toolkit으로, 데이터베이스 연결 및 쿼리를 작성하기 위한 라이브러리입니다.
• pandas: 데이터 조작 및 분석을 위한 라이브러리입니다.
• create_engine, MetaData, Table, Column: SQLAlchemy에서 데이터베이스 연결 및 테이블 생성을 위한 클래스들입니다.
• URL: 데이터베이스 URL을 생성하기 위한 유틸리티입니다.

MySQL연결 정보 정의

DB_SERVER_HOST = 'sparta-rds-mysql.cveowakiit1u.ap-northeast-2.rds.amazonaws.com'
DB_USERNAME = 'tableau_student'
DB_PASSWORD = 'student'
DB_DATABASE = 'student' # DB 이름 
DB_PORT =  3306

• 데이터베이스 접속을 위한 호스트명, 사용자명, 비밀번호, 포트, 데이터베이스 이름을 변수로 저장합니다.

MySQL 연결 URL 생성

connection_url = URL.create(
    drivername="mysql+pymysql",
    username=DB_USERNAME,
    password=DB_PASSWORD,
    host=DB_SERVER_HOST,
    port=DB_PORT,
    database=DB_DATABASE,
)

• URL.create를 사용하여 데이터베이스 연결 URL을 생성합니다.

• mysql+pymysql: MySQL에 PyMySQL 드라이버를 사용해 연결합니다.

• username, password, host, port, database: 데이터베이스 접속에 필요한 정보를 전달합니다.

  MySQL 연결 엔진 생성

 table_name = 'prediction_value' 
metadata = MetaData()

• table_name: 생성할 테이블 이름을 정의합니다 (prediction_value).
• MetaData: 테이블과 데이터베이스 간의 메타데이터를 관리하는 객체입니다.

테이블 구조 정의

table = Table(
     table_name, metadata,
     Column('userid_DI', String(100)), # User 값 
     Column('prediction_grade', Float), # Float 값 
)

• Table: 생성할 테이블의 구조를 정의합니다.
• userid_DI: 길이 100의 문자열로 구성된 사용자 ID 열.
• prediction_grade: Float 타입의 예측 성적 열.

테이블 생성

metadata.create_all(engine)

• create_all: engine을 사용하여 데이터베이스에 metadata에 정의된 테이블을 생성합니다.
• 테이블 이름은 prediction_value이며, 위에서 정의한 구조(userid_DI, prediction_grade)를 따릅니다.

데이터 베이스에 입력하기📚

이제 실제 데이터를 넣어볼까요?

import pandas as pd
from sqlalchemy import create_engine
from sqlalchemy.engine import URL

# 실제 데이터 넣는 테이블 이름
table_name = 'prediction_value'

# MySQL 연결 설정
DB_SERVER_HOST = 'sparta-rds-mysql.cveowakiit1u.ap-northeast-2.rds.amazonaws.com'
DB_USERNAME = 'tableau_student'
DB_PASSWORD = 'student'
DB_DATABASE = 'student'  # DB 이름
DB_PORT = 3306

# MySQL 연결 URL 만들기
connection_url = URL.create(
    drivername="mysql+pymysql",
    username=DB_USERNAME,
    password=DB_PASSWORD,
    host=DB_SERVER_HOST,
    port=DB_PORT,
    database=DB_DATABASE,
)

# MySQL 연결
engine = create_engine(connection_url)

# 예측 결과를 가진 데이터프레임 (df2_TEST_grade_drop)
# 'userid_DI'와 'predicted_grade' 컬럼만 유지
upload_df = df2_TEST_grade_drop[['index','userid_DI', 'predicted_grade']].copy()

# MySQL 데이터 업로드
upload_df.to_sql(name=table_name, con=engine, if_exists='replace', index=False)

부분 설명 들어갑니다. 😎

라이브러리

import pandas as pd
from sqlalchemy import create_engine
from sqlalchemy.engine import URL

• pandas: 데이터 조작 및 관리를 위한 라이브러리입니다.
• sqlalchemy.create_engine: MySQL과 같은 데이터베이스와 연결하기 위해 사용됩니다.
• sqlalchemy.engine.URL: 데이터베이스 연결 URL을 생성하기 위한 유틸리티입니다.

MySQL 연결 정보 설정

DB_SERVER_HOST = 'sparta-rds-mysql.cveowakiit1u.ap-northeast-2.rds.amazonaws.com'
DB_USERNAME = 'tableau_student'
DB_PASSWORD = 'student'
DB_DATABASE = 'student'  # DB 이름
DB_PORT = 3306

• DB_SERVER_HOST: 데이터베이스 서버의 호스트 주소.
• DB_USERNAME: 데이터베이스 사용자 이름.
• DB_PASSWORD: 데이터베이스 비밀번호.
• DB_DATABASE: 사용할 데이터베이스 이름.
• DB_PORT: 데이터베이스 포트(기본적으로 MySQL은 3306번 포트를 사용).

데이터베이스 연결 URL 생성

connection_url = URL.create(
    drivername="mysql+pymysql",
    username=DB_USERNAME,
    password=DB_PASSWORD,
    host=DB_SERVER_HOST,
    port=DB_PORT,
    database=DB_DATABASE,
)

• URL.create는 데이터베이스 연결에 필요한 URL을 생성합니다.
• 이 URL은 MySQL 드라이버(mysql+pymysql)와 사용자 정보를 포함하여 데이터베이스에 연결할 수 있도록 구성됩니다.

MySQL 연결 엔진 생성

engine = create_engine(connection_url)

• create_engine: 데이터베이스와의 연결을 설정하는 SQLAlchemy 엔진 객체를 생성합니다.
• 이 엔진 객체를 사용하여 데이터베이스 작업(테이블 생성, 데이터 삽입 등)을 수행할 수 있습니다.

업로드할 데이터프레임 준비

upload_df = df2_TEST_grade_drop[['index','userid_DI', 'predicted_grade']].copy()

• df2_TEST_grade_drop: 예측 결과가 포함된 데이터프레임입니다.
• [['index','userid_DI', 'predicted_grade']]: 데이터프레임에서 필요한 세 개의 열만 선택합니다.
  - index: 각 행의 고유 식별자.
  - userid_DI: 사용자 ID.
  - predicted_grade: 예측된 점수.
• .copy(): 원본 데이터프레임을 변경하지 않도록 복사본을 생성합니다.

데이터베이스에 데이터 업로드🤩핵심 기능🤩

upload_df.to_sql(name=table_name, con=engine, if_exists='replace', index=False)

• to_sql: Pandas 데이터프레임을 MySQL 테이블에 저장하는 메서드입니다.
• name=table_name: 테이블 이름을 설정합니다. 여기서는 'prediction_value'로 설정.
• con=engine: 데이터베이스 연결 객체를 지정합니다.
• if_exists='replace':
  - 테이블이 이미 존재하면 삭제하고 새로 생성합니다.(덮어쓰기)
  - 기존 데이터를 유지하려면 'append'를 사용합니다.
• index=False: 데이터프레임의 인덱스 값은 저장하지 않습니다.