이번에는 지난번에 언급한대로 python에서 mariadb 혹은 mysql과 연동하는 방법에 대해서 알아보겠습니다. 물론 여러가지 방법이 존재하겠지만, 그 중에서 2가지에 대해서 알아보겠습니다.
- mysql connector를 사용한 일반적인 db사용법
- pandas에서 제공하는 메서드를 이용한 자동 db 연동법
일반적으로 대부분의 application은 소소하거나 방대한 내용의 데이터를 다루고, 하물며 자체 코드나 메시지 관리를 위해서라도 database를 사용하게 됩니다. 결국 어떤언어든 databae와 연동하여 작업을 하기위한 수단을 제공합니다. 이 수단을 통해서 흔히 사용하는 DML / DDL을 수행하게 되겠죠??
- DML (Data Manipulation Language : 데이터 조작어) - SELECT , INSERT, UPDATE, DELETE
- DDL (Data Definition Language : 데이터 정의어) - CREATE , ALTER , DROP
그럼 첫번째로 mysql connector를 사용한 일반적인 db사용법에 대해서 알아보겠습니다.
1. package 설치
python에서 사용이 가능한 package를 찾아보려면, 아래의 경로에서 확인합니다. 이곳은 maven이나 npm의 repository를 찾는 사이트 처럼 python에서 사용가능한 package를 찾는 사이트라고 생각하면 됩니다.
PyPI · The Python Package Index
The Python Package Index (PyPI) is a repository of software for the Python programming language.
pypi.org
이곳에서 아래과 같이 입력하고 검색을 해보면... 가장 상단에 mysql-connector-python 이라고 결과가 나옵니다. 이제 이 것을 이전에 anaconda를 통해서 가상 환경으로 분리한 x32의 python 영역에 설치를 해줍니다.
[anaconda에서 가상환경 이동]
activate python37_32
[pip 를 이용한 설치]
pip install mysql-connector-python
정상적으로 설치가 되었습니다.
2. DB연결 및 사용
[DB연결]
연결하고자 하는 DB와는 설치한 package에서 제공하는 connect( )메서드를 사용해서 객체를 생성해야 합니다. 객체를 생성하기 위한 parameter는 host, user, password, database를 입력합니다. 이렇게 문제없이 connect객체가 생성되었다면, DBMS에 SQL구문을 호출해서 데이터를 조작하기 위한 cursor 객체를 생성합니다. 이는 connect( )메서드로 생성한 객체 내에서 제공하는 cursor( )메서드를 사용합니다.
import mysql.connector as mariadb
mariadb_conn = mariadb.connect(host='localhost', user='root', password='<password입력>', database='at_project')
mariadb_cur = mariadb_conn.cursor()
[Create Table]
sql = '''
CREATE TABLE TT(
IDSEQ VARCHAR(50) PRIMARY KEY,
ID VARCHAR(50),
UPDATE_DATE DATETIME)
'''
mariadb_cur.execute(sql)다음과 같이 sql문을 작성해서 테이블을 생성해 줍니다. 이때, cursor의 execute메서드를 사용하고 인자로 sql문을 넣어줍니다. 그 결과는... 아래와 같이 해당 database에 TT table이 생성되었고, 해당 table은 3개의 컬럼으로 구성됩니다.
[Insert Table]
sqlInsert = "INSERT INTO TT (IDSEQ, ID) values (%s, %s)"
valueInsert1 = ('2', 'ayotera')
valueInsert2 = ('3', 'ayotera')
mariadb_cur.execute(sqlInsert, valueInsert1)
mariadb_cur.execute(sqlInsert, valueInsert2)
mariadb_conn.commit()다음과 같이 sql문을 작성하고, values에 들어갈 변수는 varchar로 필드를 생성했기 때문에, %s로 지정해서 넣어줍니다. 그리고 실제로 들어가는 값을 tuple형식으로 넣어주면 됩니다. 이때 insert에 대한 cursor작업이 다 끝나면, 실제 DB에 넣기 위해서 connect 객체의 commit( )메서드를 호출해야 합니다.
그 결과, 총 3개의 데이터가 정상적으로 들어갔습니다.
[Select Table]
sqlSelect = "SELECT * FROM TT WHERE ID=%s"
valueSelect = ('ayotera',)
mariadb_cur.execute(sqlSelect, valueSelect)
a = mariadb_cur.fetchall()
print(a)
print(a[1])
print(a[2][0])
############################################
[('1', 'ayotera', None), ('2', 'ayotera', None), ('3', 'ayotera', None)]
('2', 'ayotera', None)
3다음과 같이 sql문을 작성하고, 검색조건에 대해서도 역시 varchar이기 때문에 %s로 지정하여 완성합니다. 실제 조건은 INSERT와 동일하게 tuple로 작성하여 최종 처리하지만, 여기서 중요한 점이 있습니다. 그건 바로 아래 이야기 하겠습니다. 이 cursor를 실행하게 되면, 결과가 리턴이 되고 그 결과를 cursor의 fetch 관련 메서드를 통해서 받아옵니다.
이번에는 전체를 한번에 받아오는 fetchall( )메서드로 구현하였습니다. fetchall( )메서드로 가져오면 결과와 같이 list형식으로 받아오게 되고, 개별 원소는 tuple로 되어있습니다. 따라서 자체적인 수정은 불가능하게 됩니다. list와 tuple의 조합이기 때문에, 원소의 위치에 직접적인 접근이 가능합니다.
[주의사항]
mysql.connector.errors.ProgrammingError: 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '%s' at line 1
위가 같은 에러가 발생하는 경우가 생기는데, 이때는 sql에 조건을 작성할때 tuple로 넣게 되는데... 대상이 1개가 될지라도 뒤에 ( , )를 추가해야 합니다. 아래 예시의 valueSelect의 차이점을 한번 보시면 됩니다.
[Error]
sqlSelect = "SELECT * FROM TT WHERE ID=%s"
valueSelect = ('ayotera')
[Success]
sqlSelect = "SELECT * FROM TT WHERE ID=%s"
valueSelect = ('ayotera', )
3. Trapping Errors
발생하는 error에 대해서 예외처리가 가능하기 때문에 이를 처리하는 방식을 구현하겠습니다. 타 언어와 마찬가지로 try~except문을 사용하면 됩니다.
sqlSelect = "SELECT * FROM TT WHERE ID=%s"
valueSelect1 = ('ayotera')
valueSelect2 = ('ayotera',)
try:
mariadb_cur.execute(sqlSelect, valueSelect1)
a = mariadb_cur.fetchall()
print(a)
print(a[1])
print(a[2][0])
except mariadb.Error as error:
print("Error : {0}".format(error))
mariadb_cur.execute(sqlSelect, valueSelect2)
a = mariadb_cur.fetchall()
print(a)
print(a[1])
print(a[2][0])try안에는 문제될 만한 내용을 넣고, Error가 발생하면 해당내용을 결과에 출력을 해주고 뒤의 나머지 코드는 정상적으로 동작시키게 됩니다.
Error : 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '%s' at line 1
[('1', 'ayotera', None), ('2', 'ayotera', None), ('3', 'ayotera', None)]
('2', 'ayotera', None)
3해당 결과로 오류로 인한 결과 1건과, 정상적인 select 결과 1건을 출력하게 됩니다. 다음에는 정석적인 sql connection외에 python의 다른 package에서 제공하는 다른 방법도 알아보겠습니다.
-Ayotera Lab-
댓글