[사용 툴] - Jupyter notebook(웹 기반 대화형 코딩 환경) - MariaDB(오픈 소스 관계형 데이터베이스 관리 시스템)
· 데이터를 데이터베이스에 저장하는 방법
데이터를 데이터베이스의 테이블로 저장할 수 있다. 이 경우, SQL과 같은 데이터베이스 관리 시스템(DBMS)을 사용해서 데이터를 저장하게 된다.
데이터베이스 연결 생성
먼저, 데이터베이스에 연결해야 한다. 이를 위해 필요한 정보는 데이터베이스 서버의 주소, 사용자 이름, 비밀번호 등이다. 파이썬에서는 'sqlalchemy', 'pymysql' 라이브러리를 사용하여 데이터베이스에 연결하는 것도 가능하다.
'sqlalchemy' 라이브러리는 ORM(Object Relational Mapping) 라이브러리 중 하나로, SQL 쿼리를 직접 작성하지 않고도 데이터베이스 작업을 수행할 수 있게 해 준다.
'pymysql' 라이브러리는 파이썬에서 MySQL 기반의 데이터베이스(MariaDB)에 연결하고 작업을 수행하기 위한 라이브러리이다. 이 라이브러리를 사용하면 MySQL 데이터베이스에 직접 접근하여 데이터를 생성, 조회, 업데이트, 삭제(CRUD)하는 작업을 수행할 수 있다.
아나콘다 명령 실행 프롬프트를 열어 가상환경으로 들어가 해당 라이브러리들을 설치하여 준다. - sqlalchemy 라이브러리 설치 명령어 : pip install sqlalchemy - pymysql 라이브러리 설치 명령어 : pip install pymysql 또는 conda install -c conda-forge pymysql (가장 최신 버전 설치)
sqlalchemy 라이브러리 설치pymysql 라이브러리 설치 설치가 완료되었다면, 'import'하고 데이터베이스와 연결하도록 하자.
from sqlalchemy import create_engine
# 연결정보 생성
db_connection_info = "mysql+pymysql://gjuser:dbdb@localhost:3306/gjdb"
# 데이터베이스 연결 생성
db_connection = create_engine(db_connection_info)
# 데이터베이스에 연결
db_connection
'create_engine'은 SQLAlchemy 라이브러리에서 데이터베이스와 연결을 생성하는 핵심 함수이다. 이 함수는 데이터베이스와의 연결을 관리하고 SQL 쿼리를 실행하는 엔진 객체를 생성한다. 'mysql + pymysql'은 사용할 데이터베이스 드라이버와 라이브러리를 나타낸다. 드라이버는 파이썬에서 사용하는 표준 API를 MySQL에 특화된 코드로 자동 변환하여 준다. 따라서 MySQL에 대해 신경 쓸 필요 없이 파이썬 코드만 작성하면 된다. 여기서는 MySQL 데이터베이스에 pymysql 라이브러리를 사용해 접속하겠다는 의미이다.
이렇게 생성된 엔진 객체를 사용하면, 데이터베이스에 SQL 쿼리를 실행하거나 데이터베이스와의 연결을 관리하는 등의 작업을 수행할 수 있다.
데이터베이스 연결
테이블 생성
데이터를 저장하기 위한 테이블을 생성한다. 테이블 생성은 SQL의 'Create Table' 문을 사용한다. 테이블을 생성할 때에는 각 열의 이름과 데이터 타입을 지정해야 한다.
본인은 '테이블 정의서'와 '테이블 생성 스크립트'를 통해 SQL에서 테이블을 생성하였다.
테이블 생성조건_테이블 정의서 테이블 생성조건_테이블 생성 스크립트 테이블 생성_SQL
데이터 삽입
이제 데이터를 테이블에 삽입할 수 있다. 본인은 한국전력거래소의 "시간별 전력수요량" 공공데이터를 담은 'new_data.csv' 파일을 데이터프레임(df)에 저장할 것이다.데이터프레임_컬럼명(변경전) 데이터프레임의 컬럼명은 '년도, 시간, 전력량'이고, 생성한 테이블의 컬럼명은 'ymd, time, power'이다. 컬럼명을 일치시켜 주도록 하자.
데이터프레임_컬럼명_변경 데이터프레임(df)에 'columns' 속성을 적용하여, 리스트 형태로 데이터프레임의 모든 열 이름을 추출하고, 테이블의 컬럼명인 'ymd, time, power'로 값을 덮어씌워, 컬렴명을 일치시켜 준다. 이로써 저장을 위한 준비는 끝났다.
데이터 저장하기
판다스의 'to_sql' 함수를 사용하여 데이터프레임(df) 객체를 SQL 데이터베이스의 테이블에 저장하는 기능을 수행한다. 이 메서드의 각 매개변수는 다음과 같은 의미를 가진다.
데이터프레임의 데이터를 SQL의 테이블에 저장 - name : SQL 데이터베이스에 저장할 때 사용할 테이블 이름을 지정한다.
- con : SQLAlchemy 엔진을 사용하여 데이터베이스 연결을 지정한다.
- index : 데이터프레임(df)의 인덱스를 데이터베이스에 같이 저장할지 여부를 지정한다. "index = False"로 지정하면, 인덱스는 저장되지 않는다.
- if_exists : 대상 테이블이 이미 존재할 때 어떤 동작을 수행할지 지정한다. 여기서는 "append"를 지정하였으므로, 대상 테이블이 이미 존재할 경우 새로운 데이터를 기존 테이블에 추가한다. 만약 "replace"를 지정하면 기존 테이블을 삭제하고 새 테이블을 생성하며, "fail"을 지정하면 아무 동작도 수행하지 않고 에러를 발생시킨다.
따라서 이 코드는 'df'라는 데이터프레임 객체의 데이터를 'db_connection'이라는 SQLALchemy 엔진을 통해 "time_power_demand"라는 이름의 SQL 테이블에 저장하며, 이때 데이터프레임의 인덱스는 저장하지 않고, 대상 테이블이 이미 존재할 경우 새로운 데이터를 기존 테이블에 추가하는 동작을 수행한다.
만약, 'Incorrect String Value' 오류가 발생한다면, 아래의 순서로 해결하기길 바란다.
1_(1) C드라이브\Program Files\MariaDB 10.11\data\my.ini 파일 메모장으로 실행 Incorrect String Value_오류 해결_11_(2) [Mysqld] 아래에 'character-set-sever=utf8mb4' 입력 및 저장 후 프로그램 재실행
위의 방법으로도 해결이 안된다면, 직접 생성된 테이블의 속성을 수정하여 주도록 하자.
2_테이블의 속성 중 '조합'을 'utf8mb4_general_ci'로 수정 및 저장 (utf8mb4_general_ci : SQL 데이터베이스에서 문자열을 비교 및 정렬하기 위해 사용되는 문자열 인코딩 및 정렬 규칙) Incorrect String Value_오류 해결_2 아래는 오류가 해결되어 데이터들이 모두 저장된 모습이다. 데이터 저장 성공
데이터베이스 접속 끊기
데이터베이스로 데이터를 저장하였으니, 데이터베이스와의 접속을 종료해 주어야 한다. 'dispose()' 메서드는 SQLAlchemy의 Engine 객체에서 사용되며, 엔진에 연결된 모든 데이터베이스 연결을 명시적으로 닫고, 연결 풀을 해제하는 작업을 수행한다. 데이터베이스 연결을 유지하는 것은 시스템 리소스를 사용하므로, 더 이상 필요하지 않은 경우 연결을 종료해야 한다.
db_connection.dispose()
특정 데이터베이스에 접속하기 위해 필요한 연결 객체를 생성한다. 이때, 데이터베이스 서버의 주소, 사용자 이름, 비밀번호 등의 정보가 필요하며, 'SQLAlchemy'와 'pymySQL' 라이브러리를 사용하여 데이터베이스와의 연결을 설정하고 관리한다.
이후 'create_engine' 함수를 사용하여 데이터베이스 연결을 생성한다.
데이터베이스 테이블에 데이터를 저장하기 위해서는 데이터프레임의 열 이름과 테이블의 열 이름이 일치해야 한다.
'to_sql' 함수를 사용하여 데이터프레임의 데이터를 데이터베이스 테이블에 저장한다.
더 이상 데이터 베이스를 사용할 필요가 없을 때는 'close()' 또는 'dispost()' 메서드를 사용하여 데이터베이스와의 연결을 종료해야 한다. 만약 종료하지 않으면 시스템의 리소스가 낭비된다.
이번 글에서는 "데이터를 데이터베이스에 저장하는 방법"에 대해 알아보았다. 다음 글에서는 "파일 및 데이터베이스에 저장된 데이터 조회"에 관하여 다루도록 하겠다.
