데이터 분석 part

MariaDB ALTER TABLE / Python과 SQL연동 / 헷갈리는 개념 다시 정리

bleufonce 2025. 2. 13. 16:48

 

MariaDB ALTER TABLE

 

  • MariaDB에서 기존 테이블의 구조를 변경하는 데 사용.
  • 열 추가, 삭제, 수정, 인덱스 추가, 제약 조건 변경 등의 작업을 수행.

 

 

열 추가 (ADD COLUMN)

ALTER TABLE employees ADD COLUMN email VARCHAR(100) AFTER name;
새로운 email 열을 추가.

 

 

 

열 삭제 (DROP COLUMN)

ALTER TABLE employees DROP COLUMN department;
기존 department 열을 삭제.

 

 

 

열 이름 변경 (CHANGE COLUMN)

ALTER TABLE employees CHANGE COLUMN salary monthly_salary DECIMAL(10,2) NOT NULL;
salary 열의 이름을 monthly_salary로 변경.
CHANGE COLUMN을 사용할 때는 기존 데이터 타입을 명시해야 함.

 

 

 

열 데이터 타입 변경 (MODIFY COLUMN)

ALTER TABLE employees MODIFY COLUMN monthly_salary BIGINT NOT NULL;
monthly_salary의 데이터 타입을 BIGINT로 변경
결과는 desc employees; 로 확인

 

 


기본 키 변경 (ADD/DROP PRIMARY KEY)

ALTER TABLE employees MODIFY id INT;  -- AUTO_INCREMENT 제거
ALTER TABLE employees DROP PRIMARY KEY;
ALTER TABLE employees ADD PRIMARY KEY (email);

 

먼저 AUTO INCREMENT 제거 후, PRIMARY KEY 제거 후 수행.
기존 id 기본 키를 제거하고 email을 기본 키로 설정.
기본 키를 변경하려면 email 열이 NOT NULL이어야 함.

 


​
테이블 이름 변경 (RENAME TO)

ALTER TABLE employees RENAME TO staff;
employees 테이블을 staff로 변경

 

인덱스 추가 (ADD INDEX) 

내부적으로 퍼포먼스 인덱스를 증가시킬때 씀. 속도가 빨라짐. 자주는 안씀.

ALTER TABLE staff ADD INDEX idx_name (name);

 

name 열에 걸려있던 인덱스를 추가

 

 

 

인덱스 삭제 (DROP INDEX)

ALTER TABLE staff DROP INDEX idx_name;
name 열에 걸려있던 인덱스를 삭제

 

 

 

Python과 SQL 연동하기 (환경설정)

실습에서 기존 VM 인스턴스와는 다른 새로 GCP에서 VM 인스턴스를 생성하고 새 VM 인스턴스(서버)와 파일을 주고받기 위해 FileZilla (FTP 클라이언트)와 연결하고 그 VM 인스턴스에서 터미널로 들어가 MariaDB를 재설치하고 파이썬을 설치

하였다. 이 과정에서 개념정립이 잘 되지 않아 각 요소들의 역할을 정리해봤다.

 

각 요소의 역할

요소 역할
GCP (Google Cloud Platform) 서버(VM 인스턴스)를 제공하는 클라우드 환경
터미널 (SSH) 원격으로 GCP 서버에 접속하는 창
MariaDB (SQL 데이터베이스) 데이터를 저장하고 관리하는 역할
Python MariaDB 데이터를 다루는 프로그래밍 언어
FileZilla (FTP 클라이언트) GCP 서버와 로컬 PC 간에 파일을 업로드/다운로드

 

Python으로 MariaDB 데이터 다루면 좋은 점

  • 터미널에서 SELECT * FROM users; 같은 SQL 명령어를 직접 실행할 수도 있지만,
    Python을 사용하면 SQL을 더 효율적으로 사용할 수 있고 자동화할 수 있어서 더 편리함!

 

파이썬 설치하기

sudo apt-get update
sudo apt-get install python3-pip

 

 

필요한 라이브러리 설치 

pip install mysql-connector-python pandas

 

 

main.py ( 보기 어렵기 때문에 따로 코드소스를 두고 활용하자. 블럭단위로 보자.)

import mysql.connector
import pandas as pd

# 1. MariaDB 연결 설정
db_config = {
    "host": "localhost",      # MariaDB 서버 주소
    "user": "gabkeun",           # 사용자 이름
    "password": "1234",   # 비밀번호
    "database": "backend"     # 사용할 데이터베이스
}

# MariaDB 연결                # 딕셔너리를 언팩킹
conn = mysql.connector.connect(**db_config)
cursor = conn.cursor()

# 2. 테이블 생성 (이미 존재하면 삭제 후 생성)
# cursor.execute - 파이썬에서 데이터베이스와 상호작용하기위해 쓰는 함수
cursor.execute("DROP TABLE IF EXISTS employees;")
cursor.execute("""
CREATE TABLE employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50),
    age INT,
    department VARCHAR(50),
    salary DECIMAL(10,2)
);
""")

# 3. 샘플 데이터 생성 (pandas DataFrame 활용)       # 키(컬럼명 : 밸류[데이터값])의 딕셔너리 구조
data = {
    "name": ["Alice", "Bob", "Charlie", "David", "Eve"],
    "age": [25, 30, 35, 28, 40],
    "department": ["HR", "IT", "Finance", "Marketing", "IT"],
    "salary": [50000, 70000, 80000, 60000, 90000]
}

df = pd.DataFrame(data)        # 2차원 데이터로 바뀜

# 4. 데이터 삽입                      # 컬럼이름들 써주고 그에 맞게 (%s)개수 잊지말기
insert_query = "INSERT INTO employees (name, age, department, salary) VALUES (%s, %s, %s, %s)"
values = [tuple(row) for row in df.to_numpy()]

cursor.executemany(insert_query, values)
conn.commit()

print("데이터 삽입 완료")

# 5. 데이터 조회 및 pandas DataFrame으로 변환
cursor.execute("SELECT * FROM employees")
rows = cursor.fetchall()

# 컬럼명 가져오기
column_names = [desc[0] for desc in cursor.description]
df_result = pd.DataFrame(rows, columns=column_names)

# 6. 결과 출력 (pandas 기본 출력 방식 사용)
print("\n=== Employees Table Data ===")
print(df_result.to_string(index=False))  # 인덱스 없이 출력

# CSV로 저장 (필요한 경우)
df_result.to_csv("employees_data.csv", index=False, encoding="utf-8-sig")
print("데이터를 'employees_data.csv' 파일로 저장하였습니다.")

# 연결 종료
cursor.close()
conn.close()

 

 

쉽게 실습하기 위해 상기 코드를 Sublime Text 에 붙여넣고 수정해가며 작업했다.

이것을 파이썬 코드 파일(.py)로 저장하고 FileZilla를 통해 업로드하고 다시 mariaDB에서 불러왔다. 

 

불러오는 방법은

MariaDB에 들어가기 전, 터미널 창에서

python3 main. py 입력하기.  (파이썬 앞글자 대문자가 아닌 소문자임에 주의)

 

파이썬 코드를 mariaDB로 불러오는 과정에서 생소함 때문에 어려움이 많았는데 강사님께서 감사하게도 내용을 정리해주셨다.

 

 

 

 

 

 

 

 

 

헷갈리는 개념들 다시 정리

 

 

< GCP, MariaDB(MySQL), 터미널, FileZila (FTP 클라이언트) 와의 연관관계 이해 >

 

웹 서버의 구조

- 웹 서버의 구조는 클라이언트(사용자)와 서버 간의 통신을 기반으로 동작한다.

 

1. 전체적인 연관관계 이해하기

GCP에서 MariaDB를 사용하고, 터미널과 FileZilla를 이용하는 과정은 웹 서버 운영과 데이터 관리의 일부이다.

 

* 관계 *

 

1. GCP에서 VM 인스턴스(서버)를 생성
2. VM 인스턴스 내에서 MariaDB를 실행 (SQL 데이터베이스 사용)
3. 터미널(SSH)로 서버에 접속하여 MariaDB 관리 (SQL 명령어 실행)
4. FileZilla(FTP 클라이언트)로 서버와 파일 전송 (코드/데이터 파일 관리)

  • 터미널(SSH) → 서버에 직접 접속해서 명령어를 입력하는 도구
  • MariaDB(SQL) → 데이터 저장/관리하는 데이터베이스
  • FileZilla(FTP) → 서버와 파일을 주고받는 도구

 

2. 개별 요소 설명

1) Google Cloud Platform (GCP)

  • 클라우드 서버를 제공하는 플랫폼
  • VM 인스턴스(가상 서버)를 생성해서 운영 가능

2) VM 인스턴스 (서버)

  • GCP에서 만든 가상 서버 (리눅스 서버인 경우가 많음)
  • 여기서 MariaDB를 설치하고 실행함

3) 터미널 (SSH 접속)

  • GCP의 VM 서버에 접속할 때 사용하는 방법
  • 명령어를 입력해서 서버를 제어하고, MariaDB에 SQL 명령어를 실행할 수 있음

4) MariaDB (SQL 데이터베이스)

  • VM 인스턴스에 설치된 데이터베이스
  • SQL을 이용해 데이터를 저장하고, 조회하고, 수정할 수 있음

5) FileZilla (FTP 클라이언트)

  • VM 인스턴스와 파일을 주고받을 때 사용하는 프로그램
  • HTML, PHP, Python, SQL 백업 파일 등을 서버에 업로드하거나 다운로드 가능

 

3. 전체 구조 흐름 예시

 

1) GCP에서 VM 인스턴스 생성

2) 터미널(SSH)로 서버에 접속

3) MariaDB에서 SQL 실행

4) FileZilla(FTP)로 파일 업로드/다운로드

 

 

※ SSH 접속이란?

SSH(Secure Shell)는 원격 서버에 접속하는 방법.
GCP에서 만든 VM 인스턴스(서버)에 접속하려면 SSH를 사용해야 함.
터미널을 통해 내 컴퓨터에서 원격 서버로 로그인한다고 생각하면 됨.

 

 

SSH 사용방법

 

1. 서버의 IP 주소 확인

 

1) GCP에 로그인

2) "Compute Engine" → "VM 인스턴스"로 이동

3) 외부 IP 주소를 확인 (예: 34.125.67.89)

 

2. SSH 접속하기 (로컬 컴퓨터에서 터미널 실행) - Windows에서

 

Window에서 GCP 서버에 SSH 접속하는 방법은 두가지이다.

(실습에서는 PuTTY 사용)

1) Windows 내장 터미널 사용 (cmd 또는 PowerShell)

2) PuTTY 같은 외부 프로그램 사용.

 

 

 

 

리눅스 시스템에서 사용되는 주요 파일 및 디렉터리 관리 명령어

  • cd : change directory (작업 디렉토리 변경)
  • pwd : print working directory (현재 작업 디렉토리 위치 출력)
  • mkdir : make directory (디렉토리 생성 = [윈도우에서] 폴더 생성)
  • ls : list(목록 출력) → 자주 쓰는 옵션 ('ls -li 또는 ls-al 또는 ll)
  • chmod : change mode (디렉토리 및 파일 권한 변경)
  • sudo : superuser do (다른 명령어와 같이 사용하여, 해당 명령어가 관리자 권한(root)으로 실행)
  • rm : remove (삭제) - 주로 rm -rf로 강제 전체 삭제하는 명령어

 

 

gm-lab1이라는 디렉토리 생성 : sudo mkdir gm lab1

 

gm lab1 : gm lab1에 읽기,쓰기, 실행 권한명령을 부여 : sudo chmod -R 777 gm lab1

 

MariaDB root 계정으로 접속  : sudo mysql -u root

 

만들어둔 기존 계정으로 접속 : mysql -u '계정명' -p

 

데이터 베이스 생성 : CREATE DATABASE mydb;

 

* CREATE DATABASE backend default CHARACTER SET UTF8; : backend라는 새 데이터 생성하는데 기본 문자 인코딩을 UTF-8로 설정한다.

 

데이터 베이스 삭제 : DROP DATABASE mydb;

 

사용자 계정 생성 및 비밀번호 설정 : CREATE USER 'user1'@'localhost' IDENTIFIED BY '1234';

- user1 은 계정 이름이고 localhost 는 서버위치.

 

사용자 삭제 : DROP USER 'user1'@'localhost';

 

root 계정에서 특정 사용자의 비밀번호 변경 : ALTER USER 'user1'@'localhost' IDENTIFIED BY 'newpassword123';

 

현재 로그인한 사용자가 자신의 비밀번호 변경 : SET PASSWORD = PASSWORD('newpassword123');

 

특정 데이터베이스에 대한 모든 권한 부여 : GRANT ALL PRIVILEGES ON mydb.* TO 'user1'@'localhost';

 

특정 권한만 부여 (예: 읽기 및 쓰기 권한) : GRANT SELECT, INSERT, UPDATE ON mydb.* TO 'user1'@'localhost';

- user1은 SELECT, INSERT, UPDATE 명령어만 사용가능.

 

특정 권한 회수 : REVOKE INSERT, UPDATE ON mydb.* FROM 'user1'@'localhost';

 

권한 변경 사항 적용 : FLUSH PRIVILEGES;

 

사용자 계정 목록 조회 : SELECT User, Host FROM mysql.user;

 

 

 

 

 

SQL 제약조건 키

 

 

  • PRIMARY KEY
    • 정의: 테이블에서 각 행을 고유하게 식별할 수 있는 컬럼(또는 컬럼들의 조합).
    • 특징: 기본 키는 중복될 수 없고, NULL 값을 가질 수 없다. 데이터베이스에서 하나의 테이블당 하나의 PRIMARY KEY만 존재할 수 있다.
    • 사용 예시: 사용자 테이블에서 user_id가 PRIMARY KEY라면, user_id는 고유하고 NULL 값이 없어야 한다.
  • AUTO INCREMENT
    • 정의: 숫자 값을 자동으로 증가시키는 기능. 주로 PRIMARY KEY로 설정된 컬럼에서 사용.
    • 특징: 값을 하나씩 자동으로 증가시켜 데이터가 추가될 때마다 이전 값보다 1만큼 큰 값이 자동으로 들어감.
    • 사용 예시: 사용자 테이블에서 user_id를 AUTO INCREMENT로 설정하면, 새로운 사용자가 추가될 때마다 자동으로 1씩 증가하는 값이 할당.
  • UNIQUE
    • 정의: 컬럼의 값이 중복될 수 없도록 보장하는 제약.
    • 특징: 중복된 값이 들어가면 에러가 발생한다. NULL은 중복이 가능하지만, NULL 값을 제외한 모든 값은 유일해야 한다.
    • 사용 예시: 이메일 주소를 저장하는 컬럼에 UNIQUE 제약을 두면, 두 명 이상의 사용자가 동일한 이메일을 가질 수 없다.
  • CHECK
    • 정의: 특정 조건을 만족하는 값만 들어가도록 제한하는 제약.
    • 특징: 컬럼에 들어갈 값에 대해 조건을 지정할 수 있다. 예를 들어, 숫자가 일정 범위 내에 있도록 하거나, 특정 문자열만 허용할 수 있음.
    • 사용 예시: 나이 컬럼에 CHECK (age >= 18)을 설정하면, 나이는 18세 이상만 입력될 수 있다.

 

 

 

 

실습 과정 오류 대안

  • 초기 MariaDB실습때부터 그랬지만 사용자계정 로그인이 되었다가 안되었다가 하는 경우가 잦았다. 보조강사님께서 제안하신 오른쪽 숫자키보드 대신 상단 숫자키보드를 쓰니 로그인이 되었다. 이것이 핵심 원인인지는 아직 파악되지 않았지만, 오른쪽 숫자키를 쓰면 키보드 설정 오류때문에 종종 오류가 나는 듯하다. 
  • MySQL에서 csv파일을 받아서 엑셀파일로 열어서 확인했을때 한글이 깨지는 경우가 있었다. 인터넷에서는 해결방법이 메모장으로 열어서 형식을 UTF-8로 설정하고 다른이름으로 저장하라고 했지만 나의 경우에는 되지 않았다. 그래서 한번 형식을 UTF 8 (BOM)로 바꾸어서 저장했더니 이번 건에서는 출력이 잘 된다. 이게 정확한 해결법인지는 모르겠지만 종종 시도해봄직하다.

 

 

 

오늘 회고

나에게 생소하고 어려운 내용을 많은 양으로 배우려면 시간이 정말 많이 든다. 오늘은 시간을 효율적으로 관리하는 법을 연구해봐야겠다는 생각을 했다. 하루의 수업이 끝나면 밀린 개인적 용무를 처리할 일도 많고, 불가피한 일정이나 약속이 생기기도 한다. 주어진 시간을 최대한 효율적으로 쓰는 방법을 고민해봐야겠다.

 

 

 

 

 

 

 

저작자표시 비영리 변경금지