[멋쟁이사자처럼 그로스마케팅 부트캠프] 9일차 회고

오늘은 ALTER TABLE과 SQL과 Pandas의 연동에 대해 배웠고, 아쉽게도 flask로 웹을 만들어보는 건 내일로 밀렸다. 아쉽긴해도 진도가 뒤쳐지는 동기가 없도록 교수님께서 잘 대처하신것도 같고, 오히려 자습할 수 있는 시간이 생겼다는 건 좋은거 아닐까 싶다. 다들 어려움이 있어도 포기하지 않고 질문하고 해결해내는 모습들이 정말 멋지신 것 같다!

ALTER TABLE

ALTER TABLE문은 기존 테이블의 구조를 변경하는 데 사용되며, 아래와 같은 기능을 할 수 있다.

기능	SQL 문
열 추가	ALTER TABLE 테이블명 ADD COLUMN 컬럼명 데이터타입 AFTER 기존컬럼;
열 삭제	ALTER TABLE 테이블명 DROP COLUMN 컬럼명;
열 이름 변경	ALTER TABLE 테이블명 CHANGE COLUMN 컬럼명 변경할컬럼명 데이터타입;
열 데이터 타입 변경	ALTER TABLE 테이블명 MODIFY COLUMN 컬럼명 변경할데이터타입;
기본 키 변경	ALTER TABLE 테이블명 ADD PRIMARY KEY(컬럼명);
테이블 이름 변경	ALTER TABLE 테이블명 RENAME TO 변경할테이블명;
인덱스 추가 및 삭제	ALTER TABLE 테이블명 ADD INDEX 키이름(컬럼명);
	ALTER TABLE 테이블명 DROP INDEX 키이름;
제약조건 변경	ALTER TABLE 테이블명 ADD CONSTRAINT 제약조건명 컬럼명 제약조건;

Python과 DB 연동

Python 설치하는 법

아래 명령어를 통해 설치 가능한 패키지들을 최신 버전으로 업데이트하고, python3버전과 라이브러리 설치를 위한 pip를 설치한다.

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

라이브러리 설치

DB를 연동하기 위한 mysql-connector-python과 데이터 분석을 위한 pandas라이브러리를 설치한다.

pip install mysql-connector-python pandas

예제 소스를 통한 연동 방법

1. 데이터베이스 서버 연결

db_config 변수를 통해 디비 서버에 대한 정보를 입력한 후 mysql.connector 라이브러리를 통해 서버에 연결한다.

import mysql.connector
import pandas as pd

db_config = {
    "host": "서버 주소",
    "user": "사용자 이름",
    "password": "비밀번호",
    "database": "데이터베이스"
}

conn = mysql.connector.connect(**db_config)
cursor = conn.cursor()

• **db_config: 딕셔너리 변수인 db_config를 키워드 인수로 풀어서 아래와 같이 변환한다.
  mysql.connector.connect(host="서버 주소", user="사용자 이름", password="비밀번호", database="데이터베이스")
• cursor = conn.cursor(): SQL 쿼리를 실행하고 결과를 가져올 수 있게해주는 객체이다.

2. 테이블 생성 (CREATE TABLE)

아래와 같이 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)
);
""")

위 소스를 실행하면 employees 테이블이 존재하면 테이블을 제거하고, employees 테이블을 생성한다.

3. 샘플 데이터 생성

서버에 데이터를 삽입하기 위해 data 변수를 선언해주고, 데이터프레임으로 변환해준다.

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)

번외) 필요에 따라 numpy와 random 라이브러리를 활용해 아래와 같이 랜덤으로 데이터를 생성할 수도 있다

# 생성할 데이터 개수
num_samples = 5

# 이름
names = ["Alice", "Bob", "Charlie", "David", "Eve", "Griezmann", "Llorente", "Barrios", "Alvarez", "Simeone"]
random_names = random.choices(names, k=num_samples)

# 나이
random_ages = np.random.randint(20, 60, size=num_samples)

# 부서
departments = ["HR", "IT", "Finance", "Marketing", "Sales", "Operations"]
random_departments = random.choices(departments, k=num_samples)

# 급여
random_salaries = np.random.randint(30000, 100000, size=num_samples)

data = {
    "name": random_names,
    "age": random_ages,
    "department": random_departments,
    "salary": random_salaries
}

df = pd.DataFrame(data)

• random.choices(names, k=num_samples): 주어진 리스트에서 n개의 데이터를 추출해 리스트로 반환한다.
• np.random.randint(20, 60, size=num_samples): 20 ~ 60사이의 정수를 랜덤으로 n개 생성해 배열로 반환한다.

4. 데이터 삽입

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("데이터 삽입 완료")

• [tuple(row) for row in df.to_numpy()]: 데이터프레임을 numpy 배열로 변환 후 각 행을 튜플로 변환하여 리스트로 반환한다.
• [ ('Alice', 25, 'HR', 50000), ('Bob', 30, 'IT', 70000), ('Charlie', 35, 'Finance', 80000), ('David', 28, 'Marketing', 60000), ('Eve', 40, 'IT', 90000) ]
• executemany(insert_query, values): 여러 번의 SQL 쿼리 실행을 위해 사용하며 첫번째 인수로는 SQL 쿼리문인 insert_query, 두번째 인수로는 튜플을 담은 리스트인 values를 넣어준다. 이러면 여러 개의 데이터를 한 번에 삽입할 수 있다.

5. 데이터 조회

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)

print("\n=== Employees Table Data ===")
print(df_result.to_string(index=False))  # 인덱스 없이 출력

• cursor.fetchall(): SQL 쿼리의 실행 결과로 반환되는 데이터를 모두 조회하여 가져온다.
• [desc[0] for desc in cursor.description]: cursor.description은 컬럼 정보가 들어있는 튜플로 이루어진 리스트이고, desc[0]은 컬럼명을 가져온다.

6. CSV 파일로 저장

필요에 따라 pandas의 to_csv 기능으로 csv 파일로 저장할 수 있다.

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

7. 데이터베이스 연결 종료

서버의 자원 낭비를 방지하기 위해 모든 작업이 끝나면 cursor와 conn를 종료시켜줘야한다.

cursor.close()
conn.close()

번외) 맥에서 이미지 속 텍스트 추출하는 방법

아래와 같은 이미지가 있을 때 숫자를 세거나 문자를 하나하나 타이핑해서 입력하는게 번거로울 때가 있다.
물론 처음부터 표로 데이터를 제공받으면 좋겠지만 언제나 만약을 대비하는 건 좋으니까..!

맥에서는 "라이브 텍스트" 기능을 활용하면 간편하게 이미지에서 텍스트를 추출할 수 있다.
(윈도우에도 사진에서 텍스트를 추출하는 기능이 있는 걸로 알고 있다.)

1. 라이브 텍스트 기능 활성화

만약 라이브 텍스트 기능이 꺼져있다면 설정에서 "라이브 텍스트"를 활성화를 해줘야 한다.
시스템 설정" > 일반 > 언어 및 지역 > 라이브 텍스트

2. 이미지 속 텍스트 추출하기

이제 이미지를 다운받고 미리보기에 들어가보면 이미지 속 텍스트를 드래그하여 복사할 수 있다!

channel	budget	impressions	clicks	conversions	start_date
Google Ads	8000000	300000	20000	4000	2024-06-01
Facebook Ads	7000000	250000	18000	3500	2024-07-01
Instagram Ads	6500000	230000	17000	3400	2024-08-01

불필요한 타이핑 시간을 줄이고, 빠르게 데이터를 정리하는데 조금이라도 도움이 되었길 바랍니다!

마무리하며

벌써 내일이면 2주차 마지막 수업이다..
내일은 flask를 활용한 웹 개발 그리고 정적·동적 크롤링을 배우는 날인데, 예전에 해본 적은 있지만 워낙 오랜만에 하는거라 새롭게 배우는 기분이 드는 것 같다.
다들 오늘도 고생 많으셨고 내일도 힘내서 2주차 마지막 수업 즐겁게 배워보자구요!! 🔥