[스터디 노트] Week9_2일차 [심화_3 ~ 4] - SQL

1.  DB 접속

!pip install mysql-connector-python

---

📍 localhost 접속

import mysql.connector

local = mysql.connector.connect(
    host = "localhost",
    user = "root",
    password = "password"
)

local.close()

# 특정 데이터베이스 바로 접속
local = mysql.connector.connect(
    host = "localhost",
    user = "root",
    password = "password",
    database = "zerobase"
)
# 접속 끊기
local.close()

---

📍 AWS RDS 접속

# AWS RDS 접속
remote = mysql.connector.connect(
    host = "엔드포인트",
    port = 3306,
    user = "admin",
    password = "password"
)
remote.close()

# 특정 데이터베이스 바로 접속
remote = mysql.connector.connect(
    host = "엔드포인트",
    port = 3306,
    user = "admin",
    password = "password",
    database = "zerobase"
)
remote.close()

---

📍 Query를 실행하기 위한 코드

# Query를 실행하기 위한 코드
import mysql.connector

mydb = mysql.connector.connect(
    host = "localhost",
    user = "root",
    password = "password",
    database = "zerobase"
)

mycursor = mydb.cursor()
mycursor.execute(<query>);

# 테이블 생성
remote = mysql.connector.connect(
    host = "엔드포인트",
    port = 3306,
    user = "admin",
    password = "password",
    database = "zerobase"
)
cur = remote.cursor()
cur.execute("CREATE TABLE sql_file(id INT, filename VARCHAR(16))")

remote.close()

# 테이블 삭제
remote = mysql.connector.connect(
    host = "엔드포인트",
    port = 3306,
    user = "admin",
    password = "password",
    database = "zerobase"
)

cur = remote.cursor()
cur.execute("DROP TABLE sql_file")

remote.close()

---

📍 SQL FILE 을 실행하기 위한 코드

mydb = mysql.connector.connect(
    host = "localhost",
    user = "root",
    password = "password",
    database = "zerobase"
)

cur = mydb.cursor()
sql = open("test03.sql").read()

cur.execute(sql)

mydb.close()

 

---

📍 SQL FILE 내에 Query가 여러개 존재하는 경우

# multi=True
mydb = mysql.connector.connect(
    host = "localhost",
    user = "root",
    password = "password",
    database = "zerobase"
)

cur = mydb.cursor()
sql = open("test04.sql").read()

for result_iterator in cur.execute(sql, multi=True):
    if result_iterator.with_rows:
        print(result_iterator.fetchall())
    else:
        print(result_iterator.statement)
        
mydb.commit()
mydb.close()
>>>
INSERT INTO sql_file VALUES(1, "test01.sql")
INSERT INTO sql_file VALUES(2, "test02.sql")
INSERT INTO sql_file VALUES(3, "test03.sql")
INSERT INTO sql_file VALUES(4, "test04.sql")

🔌 코드에 대한 추가 설명

• 파이썬 코드에서 multi=True 옵션을 사용하면, 이러한 여러 INSERT INTO 문을 한 번에 실행할 수 있습니다.
• 이렇게 하면, 각 INSERT INTO 문이 데이터베이스에 적용되고, 트랜잭션이 커밋되기 전까지는 변경사항이 데이터베이스에 영구적으로 저장되지 않습니다.
• mydb.commit( )을 호출하면 모든 변경사항이 데이터베이스에 적용됩니다.
• 이러한 INSERT INTO 문들은 일반적으로 with_rows 속성이 False로 설정되므로, result_iterator.statement를 출력하는 부분이 실행될 것입니다.
• 이는 각 INSERT INTO 문을 출력해 줍니다.

---

📍 Fetch All

• fetchall( ) 메서드는 SQL 쿼리로부터 반환된 모든 행을 가져오는 데 사용됩니다.
• 이 메서드는 보통 execute( ) 메서드로 SQL 쿼리를 실행한 후에 호출됩니다.

❗️주의

• fetchall( )은 쿼리의 모든 결과를 메모리에 로드하기 때문에, 결과가 매우 큰 경우에는 메모리 문제가 발생할 수 있습니다.
• 이런 경우에는 fetchone( ) 또는 fetchmany(size)를 사용할 수 있습니다.

# 데이터베이스 연결
mydb = mysql.connector.connect(
  host="localhost",
  user="yourusername",
  password="yourpassword",
  database="yourdatabase"
)

# 커서 객체 생성
mycursor = mydb.cursor()

# SQL 쿼리 실행
mycursor.execute("SELECT * FROM your_table")

# 모든 행 가져오기
result = mycursor.fetchall()

# 결과 출력
for row in result:
  print(row)

---

📍 sql_file 테이블 조회( 읽어올 데이터 양이 많은 경우 buffered=True)

mydb = mysql.connector.connect(
    host = "localhost",
    user = "root",
    password = "password",
    database = "zerobase"
);

cur = mydb.cursor(buffered=True)
cur.execute("SELECT * FROM sql_file")

result = cur.fetchall()

for result_iterator in result:
    print(result_iterator)
    
mydb.close()
>>>
(1, 'test01.sql')
(2, 'test02.sql')
(3, 'test03.sql')
(4, 'test04.sql')

---

📍 buffered=True

• buffered=True 옵션은 MySQL 커넥터의 cursor( ) 메서드에서 사용할 수 있습니다.
• 이 옵션을 설정하면, SQL 쿼리가 실행된 후 결과가 즉시 클라이언트로 가져와져 내부 버퍼에 저장됩니다.
• 이렇게 하면 커서를 여러 번 반복할 수 있고, 같은 결과셋을 여러 번 사용할 수 있습니다.

# buffered=True 없이
mycursor = mydb.cursor()
mycursor.execute("SELECT * FROM table_name")

for row in mycursor:
    print(row)

# 이 시점에서 커서는 결과셋을 완전히 소진했으므로, 다시 반복할 수 없습니다.

# buffered=True 사용
mycursor = mydb.cursor(buffered=True)
mycursor.execute("SELECT * FROM table_name")

for row in mycursor:
    print(row)

# buffered=True 때문에, 같은 결과셋을 다시 반복할 수 있습니다.
for row in mycursor:
    print(row)

❗️장점과 단점

• 장점
  • 결과셋을 여러 번 반복할 수 있습니다.
  • 결과가 크더라도, 서버에서 결과를 한 번에 하나씩 가져오는 것이 아니라 전체 결과를 한 번에 가져옵니다.
  • 이로 인해 네트워크 지연이 줄어듭니다.
• 단점
  • 결과셋이 매우 큰 경우, 모든 데이터를 클라이언트의 메모리에 로드해야 하므로 메모리 문제가 발생할 수 있습니다.
  • buffered=True는 특정 상황에서 유용할 수 있지만, 결과셋이 크거나 메모리 사용에 민감한 애플리케이션에서는 주의가 필요합니다.

⚡️ 검색결과를 Pandas 로 읽기

import pandas as pd

df = pd.DataFrame(result)
df

---

2. Python with CSV

---

📍 CSV 에 있는 데이터를 Python 으로 INSERT

df = pd.read_csv('./police_station.csv')
df.head()

 

⚡️ INSERT INTO

remote = mysql.connector.connect(
    host = "엔드포인트",
    port = 3306,
    user = "admin",
    password = "password",
    database = "zerobase"
)
# cursor 생성
cur = remote.cursor(buffered=True)
# INSERT 문 만들기
sql = "INSERT INTO police_station VALUES(%s, %s)"

for i, row in df.iterrows():
    cur.execute(sql, tuple(row))
    print(tuple(row))
    remote.commit() # commit() 은 database 에 적용하기 위한 명령

⚡️ 결과 확인

cur.execute("SELECT * FROM police_station")

result = cur.fetchall()
for row in result:
    print(row)
>>>
('서울중부경찰서', '서울특별시 중구 수표로 27')
('서울종로경찰서', '서울특별시 종로구 율곡로 46')
('서울남대문경찰서', '서울특별시 중구 한강대로 410')
('서울서대문경찰서', '서울특별시 서대문구 통일로 113')
.
.
.
('서울수서경찰서', '서울특별시 강남구 개포로 617')
('서울은평경찰서', '서울특별시 은평구 연서로 365')

df = pd.DataFrame(result)
df.head()

---

3. 혼자 해보기

---

📍cctv 파일에서 데이터를 Pandas 로 읽어옵니다.

import pandas as pd

df_cc = pd.read_csv("./Seoul_CCTV.csv", encoding="utf-8")
df_cc.head()

 

---

📍 AWS RDS(database-1) zerobase 에 접속합니다.

import mysql
conn = mysql.connector.connect(
    host = "database-1.cicfmbqntnlk.ap-southeast-2.rds.amazonaws.com",
    port = 3306,
    user = "admin",
    password = "admin1234",
    database = "zerobase"
)

cursor = conn.cursor(buffered=True)

---

📍 cctv Table 을 생성합니다.

sql = "CREATE TABLE cctv (`기관명` VARCHAR(16), `소계` INT, `2013년도 이전` INT, `2014년` INT, `2015년` INT, `2016년` INT)"
cursor.execute(sql)

---

📍데이터를 cctv 테이블에 INSERT 합니다.

sql = "INSERT INTO cctv VALUES(%s, %s, %s, %s, %s, %s)"

for i, row in df_cc.iterrows():
    cursor.execute(sql, tuple(row))
    print(tuple(row))
    conn.commit()
>>>
('강남구', 3238, 1292, 430, 584, 932)
('강동구', 1010, 379, 99, 155, 377)
('강북구', 831, 369, 120, 138, 204)
.
.
('종로구', 1619, 464, 314, 211, 630)
('중구', 1023, 413, 190, 72, 348)
('중랑구', 916, 509, 121, 177, 109)

---

📍 cctv 테이블의 데이터 조회하여 확인합니다.

cursor.execute("SELECT * FROM cctv")

result = cursor.fetchall()
for row in result:
    print(row)
>>>
('은평구', '2108', '1138', '224', '278', '468')
('종로구', '1619', '464', '314', '211', '630')
('중구', '1023', '413', '190', '72', '348')
('중랑구', '916', '509', '121', '177', '109')

---

📍 조회된 데이터를 Pandas 로 변환하여 출력합니다.

df_result = pd.DataFrame(result)
df.head()