[MySQL] python으로 mysql 접근하기 with mysql.connector
이번 포스팅은 python으로 mysql에 접근(select, insert..)하는 방법을 게시하겠습니다.
필자는 MySQL 문법에 익숙하지만 python으로 활용하는 방법은 모르기 때문에 내용이 부실해도 양해 부탁드립니다.
mysql.connector
파이썬에서 mysql을 사용하기 위해서 mysql.connector.python, mysql.connector을 사용합니다.
import mysql.connector
데이터베이스 생성
mysql.connector.connect : connect 메서드를 사용하여 연결하고자 하는 DB 정보를 입력합니다.
mydb.cursor(buffered=True) : cursor는 특정 테이블에 대해서 각 로우에 대한 특정 처리를 위해 사용합니다. 데이터 양이 많은 경우 buffered 설정을 True로 변경합니다.
mycursor.execute : execute는 괄호 안에 있는 명령에 맞게 cursor로 실행합니다.
def create_database():
mydb = mysql.connector.connect(
host="localhost",
user="root",
password={password},
auth_plugin='mysql_native_password'
)
# 읽어올 데이터 양이 많은 경우 cursor 생성 시 buffer 설정
mycursor = mydb.cursor(buffered=True)
mycursor.execute("CREATE DATABASE python_SQL")
테이블 생성
데이터베이스 생성과 마찬가지로 execute 안의 쿼리문만 변경되었습니다.
def crete_table():
mydb = mysql.connector.connect(
host="localhost",
user="root",
password={password},
database="python_SQL"
)
cursor = mydb.cursor(buffered=True)
cursor.execute("CREATE TABLE customers (name VARCHAR(255), address VARCHAR(255))")
단일 데이터 삽입
단일 데이터 삽입 시 sql 쿼리 문과 value 값을 지정해서 execute 함수로 실행시킵니다.
def insert_table():
mydb = mysql.connector.connect(
host="localhost",
user="root",
password={password},
database="python_SQL"
)
my_cursor = mydb.cursor(buffered=True)
sql = "INSERT INTO customers (name, address) VALUES (%s, %s)"
val = ("John", "Highway 21")
my_cursor.execute(sql, val)
mydb.commit()
다중 데이터 삽입
단일 데이터가 아닌 다중 데이터일 경우 execute 보다는 executemany 함수를 사용하면 더 빠르게 데이터를 삽입할 수 있습니다. 대부분의 데이터는 다중 데이터이기 때문에 executemany를 사용합니다.
def multi_insert_table():
mydb = mysql.connector.connect(
host="localhost",
user="root",
password={password},
database="python_SQL"
)
my_cursor = mydb.cursor(buffered=True)
sql = "INSERT INTO customers (name, address) VALUES (%s, %s)"
val = [
('Peter', 'Lowstreet 4'),
('Amy', 'Apple st 652'),
('Hannah', 'Mountain 21'),
('Michael', 'Valley 345'),
('Sandy', 'Ocean blvd 2'),
('Betty', 'Green Grass 1'),
('Richard', 'Sky st 331'),
('Susan', 'One way 98'),
('Vicky', 'Yellow Garden 2'),
('Ben', 'Park Lane 38'),
('William', 'Central st 954'),
('Chuck', 'Main Road 989'),
('Viola', 'Sideway 1633')
]
my_cursor.executemany(sql, val)
mydb.commit()
print(my_cursor.rowcount, "was inserted.")
모든 테이블 검색
검색은 파이썬 환경에서 확인을 해야 되니깐 fetchall 함수를 사용합니다. fetchall 함수는 해당 쿼리 문의 내용을 전체 검색합니다.
mydb = mysql.connector.connect(
host="localhost",
user="root",
password={password},
database="python_SQL"
)
my_cursor = mydb.cursor(buffered=True)
my_cursor.execute("SELECT * FROM customers")
my_result = my_cursor.fetchall()
for result in my_result:
print(result)
하나의 데이터 검색
fetchone 함수를 사용하여 맨 처음 데이터를 검색합니다.
def select_one_table():
mydb = mysql.connector.connect(
host="localhost",
user="root",
password={password},
database="python_SQL"
)
my_cursor = mydb.cursor(buffered=True)
my_cursor.execute("SELECT * FROM customers")
my_result = my_cursor.fetchone()
print(my_result)
그 외
delete, update 쿼리문이 있지만, 사용법은 위와 같습니다.
db 연결 -> 커서 생성 -> sql 쿼리문 작성 -> execute/executemany 실행 -> commit(insert, update, delete) -> 확인
COMMIT
위 과정을 따라 하신 분들은 commit에 대한 의문점이 생기실 겁니다. insert의 execute 뒤에 commit을 하는 경우도 있고, select는 commit이 없습니다. 왜 그럴까요?
DML 언어를 사용할 때는 항상 COMMIT도 함께 해줘야 한다.
(select도 DML이지만 직접적인 트랜잭션 대상이 아닙니다. )
예를 들어, 50개의 데이터가 있을 때 20개를 삭제하고 30개를 다시 입력하면 60개의 데이터가 나와야 합니다.
하지만, COMMIT을 하지 않을 경우에 기존 50개의 데이터만 나오고 50개 중 20개는 새로운 데이터로 나옵니다.
하나의 트랜잭션이 종료되지 않으면 추가된 10개의 데이터는 인식하지 못하는 상황이 발생합니다.
COMMIT 정의
- 모든 작업을 정상적으로 처리하겠다고 확정하는 명령어이다.
- 트랜젝션의 처리 과정을 데이터베이스에 반영하기 위해서, 변경된 내용을 모두 영구 저장한다.
- COMMIT 수행하면, 하나의 트랜젝션 과정을 종료하게 된다.
- TRANSACTION(INSERT, UPDATE, DELETE) 작업 내용을 실제 DB에 저장한다.
- 이전 데이터가 완전히 UPDATE 된다.
- 모든 사용자가 변경한 데이터의 결과를 볼 수 있다.