Python과 SQL 연동 과정과 실습기록

오늘 드디어 어제부터 예고로만 들었던 SQL과 마리아DB를 연결시키는 수업을 받았다!

솔직히 나날이 코드가 좀 재밌어지고있다 헿.. 에너지레벨 '■'

 

Python 설치 코드

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

요렇게 완료!

필요한 라이브러리 설치

pip install mysql-connector-python pandas

 

그리고 "main.py"이라는 이름의 파일을 서브라임텍스트로 생성해 주었다.

아래는 파이선을 마리아DB로 연결해주는 코드는 분석해 보았다. (꽤 기니 참고해서 넘겨보시길)

 

 

구성은 아래와 같이 8단계로 이루워져있다.

① MariaDB 연결 설정, MariaDB 연결 ② 테이블 생성 (이미 존재하면 삭제 후 생성)

③ 샘플 데이터 생성 (pandas DataFrame 활용) ④ 데이터 삽입 ⑤ 데이터 조회 및 지표 계산

⑥ 그로스 마케팅 지표 계산 (CTR, CVR, ROAS) ⑦ 결과 출력 (pandas 기본 출력) ,CSV로 저장 (필요한 경우)

⑧연결 종료

 

 

① MariaDB 연결 설정

db_config = {
    "host": "localhost",      # MariaDB 서버 주소
    "user": "내가 마리아DB에서 만든 사용자 이름",
    "password": "내가 설정한 비밀번호",
    "database": "backend"     # 사용할 데이터베이스 선택
}

# MariaDB 연결
conn = mysql.connector.connect(**db_config)
cursor = conn.cursor()

 

② 테이블 생성 (이미 존재하면 삭제 후 생성)

# 2. 테이블 생성
cursor.execute("DROP TABLE IF EXISTS growth_marketing;")
# 만약 'growth_marketing' 테이블이 존재한다면 삭제합니다.
cursor.execute("""

CREATE TABLE growth_marketing (
    id INT AUTO_INCREMENT PRIMARY KEY,
    # 캠페인 이름을 저장할 VARCHAR 타입의 컬럼을 정의AUTO_INCREMENT과 PRIMARY KEY를 줬습니다.
    campaign_name VARCHAR(100),
    date DATE,
    impressions INT,
    clicks INT,
    conversions INT,
    cost DECIMAL(10,2),
    revenue DECIMAL(10,2)
);
""") #여러줄을 하나의 변수에 저장할 수 있다. 여기서는 멀티라인X

(""")의 용도:

1. 여러 줄 문자열: Python에서 삼중 따옴표는 여러 줄에 걸친 문자열을 정의할 때 사용됩니다.
2. SQL 쿼리 작성: 복잡한 SQL 쿼리를 여러 줄에 걸쳐 가독성 좋게 작성할 수 있게 해줍니다.
3. 코드 포맷팅 유지: SQL 쿼리의 들여쓰기와 줄 바꿈을 그대로 유지할 수 있어, SQL 문의 구조를 시각적으로 명확하게 표현할 수 있습니다.
4. 문자열 이스케이프 간소화: 따옴표나 특수 문자를 이스케이프 처리 없이 그대로 사용할 수 있어 편리합니다.

이렇게 삼중 따옴표를 사용하면 복잡한 SQL 문을 보다 읽기 쉽고 관리하기 쉬운 형태로 작성할 수 있습니다.

 

위에 코드에서는 1번의 용도인 여러준 문자열로 써주었다.

 

그리고 업로드하다보면 에러도 나고 코드가 이상할수도있는데 IF EXISTS이 부분이 "만약 존재한다면"이라는 조건을 담당해주어서 몇번이나 재업로드해도 마리아에 들어가서 드롭테이블 계속 해주지 않아서 너무 쾌적하게 작업이 가능해서 좋았다..✨

 

③ 샘플 데이터 생성 (pandas DataFrame 활용)

data = {
    "campaign_name": ["Google Ads", "Facebook Ads", "Instagram Ads", "YouTube Ads", "LinkedIn Ads"],
    "date": ["2025-02-01", "2025-02-02", "2025-02-03", "2025-02-04", "2025-02-05"],
    "impressions": [10000, 15000, 12000, 18000, 11000],
    "clicks": [500, 750, 600, 900, 550],
    "conversions": [50, 70, 60, 80, 55],
    "cost": [100000, 150000, 120000, 180000, 110000],
    "revenue": [500000, 750000, 600000, 900000, 550000]
}

 요부분이 우리가 다 아는 파이썬의 딕셔너리 형태로 작성된 데이터를 드디어 여기에 쓰는구나..!

파이썬에서 배웠던거 기억나시죠??

④ 데이터 삽입

# 4. 데이터 삽입
# SQL 쿼리 정의: 여러 줄의 문자열을 정의할 때 """ 사용
insert_query = """
INSERT INTO growth_marketing (campaign_name, date, impressions, clicks, conversions, cost, revenue)
VALUES (%s, %s, %s, %s, %s, %s, %s)
"""

# pandas DataFrame을 2D numpy 배열로 변환 후 각 행(row)을 튜플로 변환
# 이 패턴은 pandas DataFrame을 SQL insertion에 적합한 형태로 변환할 때 자주 사용됨
values = [tuple(row) for row in df.to_numpy()]

# executemany: 여러 행의 데이터를 한 번에 삽입. 대량 삽입 시 효율적
cursor.executemany(insert_query, values)

# 변경사항 커밋: DB 변경을 확정. 트랜잭션 관리에 중요
conn.commit()

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

 

마리아 DB는 Tuple만 받게끔 형식이 고정되어있어서 튜플로 변환해주는것이다. 그리고 파이썬에서는 배열이 없기때문에 pandas의 데이터프레인을 SQL insertion에 적합한 형태로 변환할 때 df.to_numpy() 이렇게 사용해준다고 한다.

 

 

⑤ 데이터 조회 및 지표 계산

# SQL 쿼리 실행: 테이블의 모든 데이터 조회
cursor.execute("SELECT * FROM growth_marketing")

# fetchall: 모든 결과 행을 한 번에 가져옴. 대량의 데이터 처리 시 주의 필요
rows = cursor.fetchall()

# cursor.description을 이용해 컬럼명 추출. SQL 결과를 DataFrame으로 변환 시 유용
column_names = [desc[0] for desc in cursor.description]

# SQL 결과를 pandas DataFrame으로 변환. 데이터 분석 작업을 위한 일반적인 패턴
df_result = pd.DataFrame(rows, columns=column_names)

# DataFrame을 이용한 계산: 새로운 컬럼 추가. 벡터화 연산으로 효율적
df_result["CTR (%)"] = (df_result["clicks"] / df_result["impressions"]) * 100
df_result["CVR (%)"] = (df_result["conversions"] / df_result["clicks"]) * 100
df_result["ROAS (%)"] = (df_result["revenue"] / df_result["cost"]) * 100

 

⑥ 그로스 마케팅 지표 계산 (CTR, CVR, ROAS)

# DataFrame을 이용한 계산: 새로운 컬럼 추가. 벡터화 연산으로 효율적
df_result["CTR (%)"] = (df_result["clicks"] / df_result["impressions"]) * 100
df_result["CVR (%)"] = (df_result["conversions"] / df_result["clicks"]) * 100
df_result["ROAS (%)"] = (df_result["revenue"] / df_result["cost"]) * 100

 

⑦ 결과 출력 (pandas 기본 출력) ,CSV로 저장 (필요한 경우)

# DataFrame 출력: to_string 메서드로 포맷팅. index=False로 인덱스 제외
print("\n=== Growth Marketing Performance Metrics ===")
print(df_result[["campaign_name", "date", "CTR (%)", "CVR (%)", "ROAS (%)"]].to_string(index=False))

 

⑧연결 종료

# DataFrame을 CSV로 저장. encoding 지정은 한글 처리 시 중요
df_result.to_csv("growth_marketing_metrics.csv", index=False, encoding="utf-8")
print("지표 데이터를 'growth_marketing_metrics.csv' 파일로 저장하였습니다.")

# 리소스 정리: cursor와 connection 종료. 메모리 누수 방지를 위해 중요
cursor.close()
conn.close()

---

 

그다음에는 저번에 깔아줬던 FTP로 파일을 VM서버에 올려줘야한다.

 

그리고 동굴(터미널)로 가준후 ① 경로 설정 (cd /var/gm-lab3 요렇게 경로를 한번에 이동가능)

python3 main1.py -- main1.py는 파일이름.속성

 

이렇게 쳐주면 파일이 들어간다.

오 신난다.

 

마리아DB를 신나게 접속해준후

show tables;
select * from growth_marketing;

요렇게 데이터를 확인해볼수있다!!! ( 뿌듯...!)

 

 

총정리

1. 파이썬 코드 작성 (파일명.py) 
2. 파일질라(FTP)를 이용해 파이썬 코드 업로드(/경로설정_디렉토리/) - 권한 설정이 되어 있어야 함(/var$ sudo chmod -R 777 디렉토리명) 
3. 파이썬 실행 (/디렉토리명 /python3 파일명.py ) 
4. CSV파일 생성 및 maria db 테이블, 데이터생성 확인 
4.1 CSV파일 생성 (/ 디렉토리명 /ls -li) 
4.2 maria db 테이블 확인 (마리아 데이터 베이스 로그인 후 데이터베이스 선택, 'show tables;' 명령으로 확인) 
4.3 maria db 데이터생성 확인(select * from '테이블명' 으로 조회 확인)