pyodbc를 이용하여 Tibero DB에 대한 query문 작성하기

이전 시간에는 Ubuntu(WSL) 환경에서 ODBC를 통해 Tibero DB와 Python을 연동해보았다.

이번에는 ODBC를 통해 데이터베이스와 연결할 수 있도록 도와주는 파이썬 라이브러리인 pyodbc를 사용하여

select, insert등의 query문을 작성하고 Tibero DB로부터 값을 받아오는 작업들을 정리해보고자 한다.

 

 

자세한 내용은 아래 pyodbc 공식 문서 참고

https://github.com/mkleehammer/pyodbc/wiki/Getting-started

Getting started

 

 

query문 정리에 앞서서 조회, 수정, 삭제를 수행할 테이블은 아래와 같다.

 

Select

pyodbc에서 fetchone(), fetchall(), fetchmany() 메서드들을 사용하여 쿼리 결과를 가져올 수 있다.

찾고자 하는 테이블의 행이 존재하지 않는다면 fetchone()은 None을 반환하고,

fetchall()과 fetchmany()는 모두 빈 list를 반환한다고 한다. 

 

fetchone()

• fetchon() 메서드는 쿼리 결과에서 첫 번째 행(레코드)을 가져온다.
• 가져온 결과는 튜플(tuple) 형태로 반환된다.
• 데이터베이스 결과 집합의 커서(cursor)가 다음 행으로 이동된다. 이후 fetchone()을 호출하면 다음 행을 가져온다.

# 예시

import pyodbc

try:
    user = 'sys'
    passwd = 'tibero'

    sql = 'select * from s_dept;'
    conn = pyodbc.connect('DSN=tibero;UID='+user+';PWD='+passwd)
    conn.setdecoding(pyodbc.SQL_CHAR, encoding='utf-8')
    conn.setdecoding(pyodbc.SQL_WCHAR, encoding='utf-8')
    conn.setdecoding(pyodbc.SQL_WMETADATA, encoding='utf-32le')
    conn.setencoding(encoding='utf-8')
    cursor = conn.cursor()
    cursor.execute(sql)

    row = cursor.fetchone()
    if row:
        print(row)

    conn.close()

except Exception as ex:
    print(ex)

 

 

해당 테이블의 column명으로 접근할 수도 있다.

# 예시
import pyodbc

try:
    user = 'sys'
    passwd = 'tibero'

    sql = 'select * from s_dept;'
    conn = pyodbc.connect('DSN=tibero;UID='+user+';PWD='+passwd)
    conn.setdecoding(pyodbc.SQL_CHAR, encoding='utf-8')
    conn.setdecoding(pyodbc.SQL_WCHAR, encoding='utf-8')
    conn.setdecoding(pyodbc.SQL_WMETADATA, encoding='utf-32le')
    conn.setencoding(encoding='utf-8')
    cursor = conn.cursor()
    cursor.execute(sql)

    row = cursor.fetchone()
    print('Deptno:', row[0])
    print('Deptno:', row.DEPTNO) # DEPTNO column에 접근
    print('Dname:', row[1])
    print('Dname:', row.DNAME)	# DNAME column에 접근
    #if row:
       # print(row)

    conn.close()

except Exception as ex:
    print(ex)

 

fetchall()

• fetchall() 메서드는 쿼리 결과에서 모든 행(레코드)을 가져와 리스트 형태로 반환한다.
• 결과 집합의 모든 행을 가져온 후에는 커서가 결과 집합의 끝에 도달하며, 그 다음 fetch 메서드 호출은 빈 리스트를 반환한다.
• 결과 집합이 매우 큰 경우, 모든 행을 한 번에 가져오므로 메모리 사용에 주의하도록!

# 예시
import pyodbc

try:
    user = 'sys'
    passwd = 'tibero'

    sql = 'select * from s_dept;'
    conn = pyodbc.connect('DSN=tibero;UID='+user+';PWD='+passwd)
    conn.setdecoding(pyodbc.SQL_CHAR, encoding='utf-8')
    conn.setdecoding(pyodbc.SQL_WCHAR, encoding='utf-8')
    conn.setdecoding(pyodbc.SQL_WMETADATA, encoding='utf-32le')
    conn.setencoding(encoding='utf-8')
    cursor = conn.cursor()
    cursor.execute(sql)

    rows = cursor.fetchall()
    for row in rows:
        print(row.DEPTNO, row.DNAME, row.LOC)

    conn.close()

except Exception as ex:
    print(ex)

 

fetchmany()

• 지정된 개수의 행(레코드)를 가져온다.
• 메서드를 호출할 때 가져올 행의 개수를 인자로 설정해야한다.
• 결과는 리스트로 반환되며, 커서는 가져온 행의 수만큼 이동한다.
• 결과 집합에 더 이상의 행이 없거나 fetchmany()에 지정한 개수보다 적은 행만 남아있으면, 반환되는 리스트의 크기는 실제로 가져온 행의 수에 맞게 조정된다.

# 예시
...
    cursor = conn.cursor()
    cursor.execute(sql)

    rows = cursor.fetchmany(3)
    for row in rows:
        print(row.DEPTNO, row.DNAME, row.LOC)
...

 

 

Parameters

pyodbc는 SQL 쿼리를 실행할 때 파라미터를 사용할 수 있는 기능을 제공한다.

파라미터는 동적 쿼리를 작성하고 실행하는 데 도움이 되며,

SQL 인젝션 공격을 방지하는 데 도움이 된다.

 

파라미터를 사용하려면 SQL 쿼리 문자열 안에 파라미터를 나타내는 물음표를 삽입한다.

이 물음표는 실제 파라미터 값으로 대체될 자리 표시자 역할을 한다.

# 예시
import pyodbc

try:
    user = 'sys'
    passwd = 'tibero'

    conn = pyodbc.connect('DSN=tibero;UID='+user+';PWD='+passwd)
    conn.setdecoding(pyodbc.SQL_CHAR, encoding='utf-8')
    conn.setdecoding(pyodbc.SQL_WCHAR, encoding='utf-8')
    conn.setdecoding(pyodbc.SQL_WMETADATA, encoding='utf-32le')
    conn.setencoding(encoding='utf-8')
    cursor = conn.cursor()
    cursor.execute("""select deptno, dname, loc
                      from s_dept
                      where deptno < ?
                            and dname LIKE ?
                   ;""", 50, '%S')

    rows = cursor.fetchall()
    for row in rows:
        print(row.DEPTNO, row.DNAME, row.LOC)

    conn.close()

except Exception as ex:
    print(ex)

 

 

Insert

데이터를 삽입하려 할 때, 위의 파라미터 기능을 활용하여 insert문을 작성할 수 있다. 

주의할 점은 Insert문이나 delete문 작성 후, commit을 아래와 같이 호출하거나

연결 시 autocommit을 True 설정해야 한다. 그렇지 않으면 변경 내용이 손실될 수 있다!!!!

# 예시
cursor.execute("insert into s_dept(deptno, dname, loc) values (70, 'DEVELOPER', 'SEOUL')")
conn.commit()

cursor.execute("insert into s_dept(deptno, dname, loc) values (?, ?, ?)", 70, 'DEVELOPER', 'SEOUL')
conn.commit()

 

 

Delete

업데이트 및 삭제는 동일한 방식으로 작동하므로 SQL을 전달하여 실행한다.

업데이트 및 삭제 시 영향을 받은 레코드 수가 얼마나 되는지 알고 싶을 때,

Cursor rowcount 속성을 사용하여 해결할 수 있다.

# 예시
import pyodbc

try:
    user = 'sys'
    passwd = 'tibero'

    sql = 'delete from s_dept where deptno = 70'
    conn = pyodbc.connect('DSN=tibero;UID='+user+';PWD='+passwd)
    conn.setdecoding(pyodbc.SQL_CHAR, encoding='utf-8')
    conn.setdecoding(pyodbc.SQL_WCHAR, encoding='utf-8')
    conn.setdecoding(pyodbc.SQL_WMETADATA, encoding='utf-32le')
    conn.setencoding(encoding='utf-8')
    curs = conn.cursor()
    curs.execute(sql)

    print(curs.rowcount, 'deleted')
    conn.commit()

    conn.close()

except Exception as ex:
    print(ex)