10.4 Database programming with Python

Connections

이 섹션에서는 Connector/Python을 사용하여 Python과 MySQL을 활용한 데이터베이스 프로그래밍을 설명한다.
Connector/Python은 Python Software Foundation의 DB-API 표준을 기반으로 한 MySQL 구성 요소이다.
이 섹션은 Python 언어에 대한 기본적인 지식을 전제로 한다.

 

Connector/Python 설치

Connector/Python 패키지는 Python 프로그램이 실행되는 컴퓨터에 설치되어야 한다.

Connector/Python은 mysql.com에서 다운로드한 후 설치 프로그램을 실행하여 설치할 수 있다.

Windows 또는 Mac 명령줄에서 pip를 사용하여 설치할 수 있다:

pip install mysql-connector-python

Python 프로그램은 mysql.connector 모듈을 import하여 Connector/Python을 사용한다.

 

Python 프로그램은 쿼리를 실행하기 전에 데이터베이스에 연결해야 한다.

연결은 mysql.connector.connect() 함수를 사용하여 생성되는 MySQLConnection 클래스의 객체이다.

이 함수는 데이터베이스 서버 주소, 데이터베이스 이름, 로그인 사용자 이름, 비밀번호를 지정하여 연결을 생성한다.

 

데이터베이스를 찾을 수 없거나 로그인 자격 증명이 유효하지 않으면 연결 생성이 실패한다.
따라서 연결은 일반적으로 try-except 문의 try 블록 내에서 생성된다.

연결이 실패하면 except 블록이 실행되며, 일반적으로 오류 메시지를 출력한다.

Python 프로그램은 더 이상 연결이 필요하지 않을 때 connection.close() 메서드를 사용하여 연결을 해제한다.

Creating a connection

1. import 문은 Connector/Python 코드와 errorcode 모듈을 프로그램에서 사용할 수 있도록 한다.
2. mysql.connector.connect 함수는 reservationConnection이라는 MySQLConnection 객체를 생성한다.
3. 연결이 실패하면 except 블록이 실행되고 오류 메시지를 출력한다.
4. 연결이 성공하면 else 블록이 실행된다. 이후 쿼리는 reservationConnection 객체를 사용한다.
5. 데이터베이스 처리가 완료되면 close 메서드를 호출하여 연결을 해제한다.

---

Cursors

커서는 MySQLCursor 클래스의 객체로, SQL 문을 실행하고 결과를 저장한다. 커서는 connection.cursor() 메서드를 사용하여 생성되며, 항상 특정 데이터베이스와 연관된다.

 

커서의 주요 기능

1. SQL 문 실행
   • SQL 문은 cursor.execute() 메서드로 컴파일되고 실행된다.
   • cursor.execute() 메서드는 하나의 필수 매개변수를 가지며, 이는 SQL 문을 포함하는 문자열이다.
2. 다중 SQL 문 사용
   • 하나의 커서는 한 연결에 연관되지만, 여러 SQL 문에서 재사용할 수 있다.
   • 연결을 닫기 전에 cursor.close() 메서드로 커서를 해제해야 한다.
3. 트랜잭션 처리
   • Connector/Python을 MySQL과 InnoDB 스토리지 엔진과 함께 사용할 때, INSERT, UPDATE, DELETE 문의 결과는 자동으로 데이터베이스에 저장되지 않는다.
   • connection.commit() 메서드는 모든 변경 사항을 저장하며, connection.rollback() 메서드는 모든 변경 사항을 취소한다.
   • commit() 및 rollback() 메서드는 변경 사항을 실행한 커서를 해제하기 전에 호출되어야 한다.

MySQLCursor의 추가 메서드 및 속성

1. cursor.rowcount() 속성
   • 쿼리에 의해 반환되거나 변경된 행의 수를 나타낸다.
2. cursor.column_names() 속성
   • 쿼리 결과의 열 이름 목록을 포함한다.
3. cursor.fetchwarnings() 메서드
   • 쿼리에 의해 생성된 경고 목록을 반환한다.

Using cursors

1. cursor 메서드는 Reservation 데이터베이스와 연관된 flightCursor라는 커서를 생성한다.
2. execute 메서드는 Flight 테이블을 생성한다.
3. 다음 execute 메서드는 Flight 테이블에 행을 삽입한다.
4. commit 메서드는 커서가 해제되기 전에 삽입 내용을 데이터베이스에 저장한다.

---

Query parameters

cursor.execute() 메서드는 쿼리 매개변수를 사용하여 Python 값을 SQL 문에 대체할 수 있다:

• SQL 문은 placeholder 문자인 %s를 사용하여 쿼리 값을 나타낸다.
• placeholder에 할당할 값을 포함하는 튜플을 cursor.execute() 메서드의 두 번째 인수로 전달한다.

SQL 문이 실행될 때 튜플의 값들이 순서대로 %s placeholder에 매핑된다.
Python 데이터 타입은 MySQL 데이터 타입으로 자동 변환된다.

 

데이터베이스 프로그래머는 입력 데이터를 SQL 문에 삽입할 때 주의해야 한다.

SQL injection attack은 사용자가 SQL 문 의도를 변경하려는 값을 고의로 입력하는 경우를 말한다.

cursor.execute() 메서드는 값들을 placeholder에 할당할 때 SQL injection attack을 방지한다.

---

airline과 flightNum 값이 사용자로부터 입력됐다고 가정할 때, 다음 코드에서 어떤 것이 잘못되었는가?

flightQuery = ('UPDATE Flight SET AirlineName = "' + airline + 
               '" WHERE FlightNumber = ' + flightNum)
flightCursor.execute(flightQuery)

▼View solution

airline과 flightNum을 문자열로 연결하여 SQL 문에 포함하면, 공격자가 SQL 문을 변경하는 데이터를 입력할 수 있는 취약점이 생긴다.
Ex: flight 값으로 '101 OR TRUE'를 입력하면 WHERE 절이 Flight 테이블의 모든 행과 일치하게 된다.

query 매개변수를 사용하여 execute 메서드를 호출하면 이러한 SQL 삽입 공격을 방지할 수 있다:

flightQuery = ('UPDATE Flight '
               'SET AirlineName = %s '
               'WHERE FlightNumber = %s')
flightCursor.execute(flightQuery, (airline, flightNum))

---

Fetching values

cursor.execute() 메서드가 SELECT 문을 실행하면, 쿼리 결과는 다음과 같은 fetch 메서드를 사용하여 접근할 수 있다:

1. cursor.fetchone()
   • 단일 결과 행을 포함하는 튜플을 반환하거나, 선택된 행이 없으면 None을 반환한다.
   • 쿼리가 여러 행을 반환하면, cursor.fetchone을 반복적으로 호출하여 None이 반환될 때까지 실행할 수 있다.
2. cursor.fetchall()
   • 모든 결과 행을 포함하는 튜플의 리스트를 반환한다.
   • 튜플 리스트는 루프에서 처리할 수 있다.
     Ex: for rowTuple in cursor.fetchall() 은 각 행을 rowTuple에 할당하며, 모든 행이 처리되면 종료된다.

1. flightCursor는 SELECT 쿼리를 실행한다.
2. fetchall 메서드는 튜플의 집합을 반환한다. 각 튜플은 결과 테이블의 한 행을 나타낸다.
3. for 문은 집합 내의 각 튜플을 처리한다. row[0]은 첫 번째 열의 데이터를 포함하고, row[1]은 두 번째 열의 데이터를 포함한다.

---

flightQuery = ('SELECT AirlineName, DepartureTime, AirportCode FROM Flight '
 'WHERE FlightNumber = %s')
flightData = (140,) # Comma required for single value tuple
flightCursor.execute(flightQuery, flightData)
 
row = flightCursor.___A___()
if row is ___B___:
 print("Flight not found")
else:
 print('Flight departs from', ___C___)

flightCursor.close()

A,B,C ?

순서대로

fetchone

None

row[2]

---

Stored procedure calls

저장 프로시저는 MySQL 절차적 SQL의 일부로, 데이터베이스에 저장되며 Python과 같은 범용 프로그래밍 언어에서 자주 호출된다.

저장 프로시저는 cursor.callproc() 메서드를 사용하여 호출되며, 두 개의 매개변수를 가진다:

 

• 저장 프로시저의 이름
• 저장 프로시저 매개변수의 값을 포함하는 입력 튜플

callproc() 메서드는 출력 튜플을 반환하며, 이 튜플은 저장 프로시저의 각 매개변수에 대해 하나의 값을 포함한다.

저장 프로시저의 매개변수는 IN, OUT, 또는 INOUT으로 지정된다. 이는 매개변수가 입력, 출력, 또는 둘 다를 위한 것임을 나타낸다.

• OUT 매개변수: 입력 튜플의 해당 값은 무시된다.
• IN 매개변수: 출력 튜플의 해당 값은 입력 값과 동일하다.

1. FlightCount는 특정 항공사의 항공편 수를 계산한다.
2. flightData는 항공사 이름과 quantity 매개변수를 위한 placeholder 0을 포함한다.
3. callproc() 메서드는 flightData 입력 튜플을 사용하여 FlightCount를 호출하고, 출력 튜플을 result에 반환한다.
4. result[1]은 출력 튜플의 두 번째 항목이다.

A stored procedure parameter: quantity

A method of the MySQLConnection class: cursor()

A method of the MySQLCursor class: callproc()

A Python input tuple: flightData

A Python output tuple: result

---

▼View answers

from mysql import connector  # load connector functions
connection = connector.connect(  # A
    user='Blaise',
    password='Pascal',
    host='localhost',
    database='college'
)

rowcursor = connection.cursor()  # B
query = 'SELECT * FROM Room'
rowcursor.execute(query)  # C, D
for row in rowcursor.fetchall():  # E
    print(row)

rowcursor.close()  # F
connection.close()  # G

(A) connect (B) cursor (C) rowcursor (D) query (E) fetchall (F) rowcursor (G) close