1. SQL 연결, 실행 순서
데이터베이스와 연동을 하기 위해서는 데이터베이스와 연결을 해야 한다. 이때 connect() 메서드를 사용한다.
연결이 되면 연결 객체가 만들어지는데 객체 안에는 커서라는 게 있다. 커서란 애플리캐이션 하고 데이터베이스 쪽에 서로 SQL 문을 전달하고 데이터베이스에서 그 SQL문을 실행한 결과를 받아오는 역할을 한다. SQL 문과 실행결과를 실어 나르는 객체다.
SQL 문을 실행을 전달할 때는 커서의 execute 함수를 사용한다.
commit()은 앞에서 했던 작업들을 DB에 반영되도록 하는 함수
close() 연결을 닫아 준다.
2. SQL 유형
| 명령어 종류 | 명령어 | 설명 |
| 데이터 조작어 (DML : Data Manipulation Language) |
SELECT | 데이터베이스에 들어 있는 데이터를 조회하거나 검색하기 위한 명령어를 말하는 것으로 RETRIEVE라고도 함 |
| INSERT UPDATE DELETE |
데이터베이스의 테이블에 들어 있는 데이터에 변형을 가하는 종류(데이터 삽입, 수정, 삭제)의 명령어들을 말함. | |
| 데이터 정의어 (DDL : Data Definition Language) |
CREATE ALTER DROP RENAME TRUNCATE |
테이블과 같은 데이터 구조를 정의하는 데 사용되는 명령어들로 (생성, 변경, 삭제, 이름변경) 데이터 구조와 관련된 명령어들을 말함. |
| 데이터 제어어 (DCL : Data Control Language) |
GRANT REVOKE |
데이터베이스에 접근하고 객체들을 사용하도록 권한을 주고 회수하는 명령어 |
| 트랜잭션 제어어 (TCL : Transaction Control Language) |
COMMIT ROLLBACK SAVEPOINT |
논리적인 작업의 단위를 묶어서 DML에 의해 조작된 결과를 작업단위(트랜잭션) 별로 제어하는 명령어 |
- DML은 저장되어 있는 데이터를 조작
- DDL은 데이터가 저장되는 공간을 조작
- DCL은 데이터베이스 자체를 제어
- TCL은 하나의 단위 업무로서 처리해야 하는 것
3. 데이터 조회
import pymysql
# 데이터베이스에 연결을 생성
try:
with pymysql.connect(host="localhost", port=3306, user="springboot",
passwd="qwert12345", db="sampledb") as conn:
# 조회 쿼리를 작성
query = "select member_id, member_name, member_age, member_email from members"
# 커서를 생성해서 조회 쿼리를 실행
curr = conn.cursor()
curr.execute(query)
# 조회 결과를 출력
for c in curr:
print(c)
except pymysql.MySQLError as e:
print(e)
- 출력 : (1, '홍길동', 23, 'hong@test.com')
- SQL 실습 환경 구성에서 넣었던 테스트 데이터가 출력
- cursorclass를 지정하지 않으면 SELECT 결과를 값으로 구성된 튜플 타입으로 리턴
import pymysql
# 데이터베이스에 연결을 생성
try:
with pymysql.connect(host="localhost", port=3306, user="springboot",
passwd="qwert12345", db="sampledb",
cursorclass=pymysql.cursors.DictCursor) as conn:
# 조회 쿼리를 작성
query = "select member_id, member_name, member_age, member_email from members"
# 커서를 생성해서 조회 쿼리를 실행
curr = conn.cursor()
curr.execute(query)
# 조회 결과를 출력
for c in curr:
print(c)
except pymysql.MySQLError as e:
print(e)
- 출력 : {'member_id': 1, 'member_name': '홍길동', 'member_age': 23, 'member_email': 'hong@test.com'}
- cursorclass를 pymysql.cursors.DictCursor으로 설정
- cursorclass를 설정하면 SELECT 결과를 컬럼 이름과 값으로 구성된 딕셔너리를 반환
4. 데이터베이스 접속
import pymysql
pymysql.connect(host="localhost", # 데이터베이스 주소
port=3306, # 데이터베이스 서비스 포트 (MySQL 기본 포트: 3306)
user="springboot", # 데이터베이스 접속 계정
passwd="qwert12345", # 데이터베이스 사용자 패스워드
db="sampledb" # 사용할 데이터베이스/스키마
autocommit=True|False # INSERT, UPDATE, DELETE 구문 실행 결과를 자동으로 반영할지 여부를 설정
cursorclass=pymysql.cursors.DictCursor # SELECT 결과를 컬럼명과 값으로 구성된 dict 타입으로 반환
)
5. 데이터 추가
1) Faker 데이터 추가
import pymysql
from faker import Faker
try:
with pymysql.connect(host="localhost", port=3306, user="springboot", passwd="qwert12345",
db="sampledb", cursorclass=pymysql.cursors.DictCursor) as conn:
curr = conn.cursor()
fake = Faker('ko-KR')
for i in range(10): # Faker 데이터를 10개 입력 반복문
query = f"insert into members (member_name, member_age, member_email) values \
('{fake.name()}', {fake.pyint(10, 30)}, '{fake.email()}')"
print(query)
curr.execute(query)
conn.commit() # 10개의 insert 구문의 실행 결과를 DB에 반영
except pymysql.MySQLError as e:
print(e)
2) autocommit = True
import pymysql
from faker import Faker
# 데이터베이스 연결 시 autocommit을 True로 설정
try:
with pymysql.connect(host="localhost", port=3306, user="springboot", passwd="qwert12345",
db="sampledb", cursorclass=pymysql.cursors.DictCursor,
autocommit=True) as conn:
curr = conn.cursor()
fake = Faker('ko-KR')
for i in range(10):
query = f"insert into members (member_name, member_age, member_email) values \
('{fake.name()}', {fake.pyint(10, 30)}, '{fake.email()}')"
print(query)
curr.execute(query)
# conn.commit()
except pymysql.MySQLError as e:
print(e)
위 코드 connect에서 auto commit = True로 하면 commit() 함수를 호출하지 않아도 변경사항이 DB에 반영되는 것을 확인할 수 있다.
- 트렌젝션 관리가 필요한 경우 connect에 autocommit=False(기본값)으로 설정해 사용하지 않는 것이 좋다 않다.
- 관리가 필요하지 않은 경우에는 connect에 autocommit=True(기본값)으로 설정해 사용해서 자동으로 등록되는 것을 볼 수 있다.
3) 데이터 추가 후 조회
(1) fetchone()
- 커서로부터 하나의 행(레코드)을 리턴
import pymysql
from faker import Faker
# 데이터 추가 후 조회하도록 수정
# fetchone() - 커서로부터 하나의 행(레코드)을 반환
try:
with pymysql.connect(host="localhost", port=3306, user="springboot", passwd="qwert12345",
db="sampledb", cursorclass=pymysql.cursors.DictCursor) as conn:
curr = conn.cursor()
fake = Faker('ko-KR')
for i in range(10):
query = f"insert into members (member_name, member_age, member_email) values \
('{fake.name()}', {fake.pyint(10, 30)}, '{fake.email()}')"
print(query)
curr.execute(query)
conn.commit()
query = "select * from members"
curr.execute(query)
# 조회 결과를 하나씩 가져오기
while True :
result = curr.fetchone()
if result == None : break
print(result)
except pymysql.MySQLError as e:
print(e)
(2) fetchall()
- 커서로부터 모든 행(레코드)을 반환
import pymysql
from faker import Faker
# # 데이터 추가 후 조회하도록 수정
# fetchall() - 커서로부터 모든 행(레코드)을 반환
# 데이터베이스에 연결을 생성
try:
with pymysql.connect(host="localhost", port=3306, user="springboot", passwd="qwert12345",
db="sampledb", cursorclass=pymysql.cursors.DictCursor) as conn:
curr = conn.cursor()
fake = Faker('ko-KR')
for i in range(10):
query = f"insert into members (member_name, member_age, member_email) values \
('{fake.name()}', {fake.pyint(10, 30)}, '{fake.email()}')"
print(query)
curr.execute(query)
conn.commit()
query = "select * from members"
curr.execute(query)
# 커서로부터 모든 행(레코드) 조회 결과를 반환
results = curr.fetchall()
for result in results:
print(result)
except pymysql.MySQLError as e:
print(e)
(3) fetchmany(n)
- 커서로부터 n개의 행(레코드)을 반환
import pymysql
from faker import Faker
# 데이터 추가 후 조회하도록 수정
# fetchmany(n) - 커서로부터 n개의 행(레코드)을 반환
# 데이터베이스에 연결을 생성
try:
with pymysql.connect(host="localhost", port=3306, user="springboot", passwd="qwert12345",
db="sampledb", cursorclass=pymysql.cursors.DictCursor) as conn:
curr = conn.cursor()
fake = Faker('ko-KR')
for i in range(10):
query = f"insert into members (member_name, member_age, member_email) values \
('{fake.name()}', {fake.pyint(10, 30)}, '{fake.email()}')"
print(query)
curr.execute(query)
conn.commit()
query = "select * from members"
curr.execute(query)
# 커서로부터 조회 결과의 일부를 반환
results = curr.fetchmany(10) # 조회 결과의 첫 부분 10개를 가져와서 반환
for result in results:
print(result)
print("*" * 10)
results = curr.fetchmany(10) # 앞에서 가져온 다음(11번째)부터 데이터를 반환
for result in results:
print(result)
except pymysql.MySQLError as e:
print(e)
6. 데이터 조회
1) 입력한 내용화 일치하는 데이터 조회
import pymysql
# 입력한 내용과 일치하는 데이터를 조회
try:
with pymysql.connect(host="localhost", port=3306, user="springboot",
passwd="qwert12345", db="sampledb",
cursorclass=pymysql.cursors.DictCursor) as conn:
curr = conn.cursor()
name = input("검색할 이름을 입력하세요 : ")
query = f"select * from members where member_name = '{name}'"
curr.execute(query)
results = curr.fetchall()
for result in results:
print(f"ID : {result['member_id']}") # 컬럼의 이름으로 값을 추출하는 것이 가능
print(f"이름 : {result['member_name']}")
print(f"나이 : {result['member_age']}")
print(f"이메일 : {result['member_email']}")
except pymysql.MySQLError as e:
print(e)
2) 입력한 내용을 포함하고 있는 데이터 조회
import pymysql
# 입력한 내용을 포함하고 있는 데이터를 조회
try:
with pymysql.connect(host="localhost", port=3306, user="springboot",
passwd="qwert12345", db="sampledb",
cursorclass=pymysql.cursors.DictCursor) as conn:
curr = conn.cursor()
name = input("검색할 이름을 입력하세요 : ")
query = f"select * from members where member_name like '%{name}%'"
count = curr.execute(query)
print(f"총 {count}건을 조회했습니다.")
# SELECT 구문인 경우 조회 결과 개수를 반환
# INSERT, UPDATE, DELETE 구문인 경우 등록, 수정, 삭제된 행의 개수를 반환
results = curr.fetchall()
print(f"총 {len(results)}건을 조회했습니다.")
for result in results:
print(f"ID : {result['member_id']}")
print(f"이름 : {result['member_name']}")
print(f"나이 : {result['member_age']}")
print(f"이메일 : {result['member_email']}")
except pymysql.MySQLError as e:
print(e)
- LIKE문은 검색하는 내용이 포함되어 있는 데이터를 검색해 준다.
LIKE 문을 위 코드와 같이 직접적으로 사용하면 보안에 취약해 SQL 인젝션 공격을 할 수 있다.
검색할 이름을 입력할 때 a' or 'a' = 'a' or 'a를 입력하면 SQL 인젝션 공격이 되어서 모든 데이터를 조회할 수 있다.
이 문제를 막기 위해서는 구조화된 쿼리를 실행하는 형태로 변경해야 한다.
(1) 구조화된 쿼리를 실행하는 형태
import pymysql
# 구조화된 쿼리를 실행하는 형태로 변경 SQL injection
# 입력한 내용을 포함하고 있는 데이터를 조회
try:
with pymysql.connect(host="localhost", port=3306, user="springboot",
passwd="qwert12345", db="sampledb",
cursorclass=pymysql.cursors.DictCursor) as conn:
curr = conn.cursor()
name = input("검색할 이름을 입력하세요 : ")
query = "select * from members where member_name like %s"
count = curr.execute(query, ('%'+name+'%',))
print(f"총 {count}건을 조회했습니다.")
results = curr.fetchall()
print(f"총 {len(results)}건을 조회했습니다.")
for result in results:
print(f"ID : {result['member_id']}")
print(f"이름 : {result['member_name']}")
print(f"나이 : {result['member_age']}")
print(f"이메일 : {result['member_email']}")
except pymysql.MySQLError as e:
print(e)
- %s 문자열 데이터 쿼리문의 구조를 정의
- execute() 함수의 두 번째 인자에 쿼리 실행에 필요한 값을 튜플 또는 딕셔너리를 통해 전달
- execute() 함수가 값을 안전한 형태로 변경해서 쿼리를 생성, 실행
위 코드와 같이 구조화된 코드로 작성하면 a' or 'a' = 'a' or 'a를 입력해도 데이터를 조회할 수 없다.
'SK Shieldus Rookies 19th > 인프라 활용을 위한 파이썬' 카테고리의 다른 글
| [SK shieldus Rookies 19기][Django] - 실습 환경 구성 (0) | 2024.03.20 |
|---|---|
| [SK shieldus Rookies 19기][Python, SQL] - 실습 3 (0) | 2024.03.20 |
| [SK shieldus Rookies 19기][SQL] - 실습 환경 구성 (1) | 2024.03.19 |
| [SK shieldus Rookies 19기][Python] - 정규 표현식 (3) | 2024.03.19 |
| [SK shieldus Rookies 19기][Python] - 내장 함수, 표준 라이브러리, 외부 라이브러리 Faker (0) | 2024.03.18 |
댓글