J & J - 정성태의 닷넷 이야기

사용자

메뉴

최근 덧글

[정성태] 그냥 RSS Reader 기능과 약간의 UI 편의성 때문에 사용...[이종효] 오래된 소프트웨어는 보안 위협이 되기도 합니다. 혹시 어떤 기능...[정성태] @Keystroke IEEE의 문서를 소개해 주시다니... +_...[손민수 (Keystroke)] 괜히 듀얼채널 구성할 때 한번에 같은 제품 사라고 하는 것이 아...[정성태] 전각(Full-width)/반각(Half-width) 기능을 토...[정성태] Vector에 대한 내용은 없습니다. Vector가 닷넷 BCL...[orion] 글 읽고 찾아보니 디자인 타임에는 InitializeCompon...[orion] 연휴 전에 재현 프로젝트 올리자 생각해 놓고 여의치 않아서 못 ...[정성태] 아래의 글에 정리했으니 참고하세요. C# - Typed D...[정성태] 간단한 재현 프로젝트라도 있을까요? 저런 식으로 설명만 해...

글쓰기

제목

이름

암호

전자우편

HTML

홈페이지

유형

내용

<div style='display: inline'>
<h1 ad='python' style='font-family: Malgun Gothic, Consolas; font-size: 20pt; color: #006699; text-align: center; font-weight: bold'>파이썬 - sqlite3 예제 코드</h1>

 
파이썬에서 sqlite3으로, 
 
<pre style='margin: 10px 0px 10px 10px; padding: 10px 0px 10px 10px; background-color: #fbedbb; overflow: auto; font-family: Consolas, Verdana;' >
sqlite3 — SQLite 데이터베이스용 DB-API 2.0 인터페이스
; <a target='tab' href='https://docs.python.org/ko/3/library/sqlite3.html'>https://docs.python.org/ko/3/library/sqlite3.html</a>
</pre>
 
로컬 DB 파일을 접근하는 것은 import만 하고 곧바로 사용하면 됩니다. 가령 다음과 같이 connect 함수만 호출해도, 
 
<pre style='margin: 10px 0px 10px 10px; padding: 10px 0px 10px 10px; background-color: #fbedbb; overflow: auto; font-family: Consolas, Verdana;' >
import sqlite3

con = sqlite3.connect('./my_test.db')
</pre>
 
로컬 경로에는 my_test.db 파일이 생성됩니다. C#의 경우 IDbConnection 개체를 얻었으면 그로부터 IDbCommand를 이용해 명령을 실행하지만, 파이썬의 경우에는 sqlite3.Connection 개체로부터 cursor 개체를 얻어 쿼리를 실행하는 식입니다. 또한, SELECT를 제외한 INSERT/UPDATE/DELETE 시에는 commit을 해야 반영이 되는데, 따라서 다음과 같은 식으로 일반적인 SQL 쿼리 수행을 할 수 있습니다. 
 
<pre style='margin: 10px 0px 10px 10px; padding: 10px 0px 10px 10px; background-color: #fbedbb; overflow: auto; font-family: Consolas, Verdana;' >
# sqlite3.Cursor 개체를 받아오고,
cursor = con.cursor()

# (없으면) 테이블 생성
query = "CREATE TABLE IF NOT EXISTS member(ID int primary key not null, Name text, Age int);"
cursor.execute(query)

# 기존 데이터 삭제
query = "DELETE FROM member"
cursor.execute(query)

# 새로운 데이터 추가
for idx in range(1, 5):
    query = "INSERT OR REPLACE INTO member VALUES({0}, 'tester{1}', {2})".format(idx, idx, idx * 10)
    cursor.execute(query)

# 반드시 commit을 호출해야 반영
# INSERT/DELETE/UPDATE 후에는 "my_test.db-journal" 파일이 생성되고, 이 파일은 commit이 되어야 없어짐
con.commit()

# SELECT 문은 commit 없이 exeucte 수행 후 fetch 메서드로 데이터셋 반환
query = "SELECT * FROM member"
cursor.execute(query)
print(cursor.fetchall())
</pre>
 
사용 후에는 당연히 sqlite3.Connection 개체의 close 함수를 호출하는 것으로 정리를 합니다. (connect 시에 sqlite db 파일에 대한 Handle이 열리고, close 시에 파일 핸들이 닫힙니다.) 
 
<pre style='margin: 10px 0px 10px 10px; padding: 10px 0px 10px 10px; background-color: #fbedbb; overflow: auto; font-family: Consolas, Verdana;' >
con.close()
</pre>
 
당연히 sql injection 공격을 예방하기 위해 <a target='tab' href='https://www.sysnet.pe.kr/2/0/1486#pquery'>parameterized query</a>를 사용해야겠죠. ^^ 방법은 query 내에 "?"로 표시를 하고, exeucte 실행 시 대응하는 인자를 튜플로 넣으면 됩니다. 위의 예제 코드라면 다음과 같이 수정하시면 됩니다. 
 
<pre style='margin: 10px 0px 10px 10px; padding: 10px 0px 10px 10px; background-color: #fbedbb; overflow: auto; font-family: Consolas, Verdana;' >
for idx in range(1, 5):
 query = "INSERT OR REPLACE INTO member VALUES(?, ?, ?)"
 cursor.execute(query, (idx, "tester".format(idx), idx * 10))
</pre>
 
<hr style='width: 50%' /> 
<a name='with'></a>
 
C# 개발자라면 SqlConnection과 같은 개체를 사용할 때는 using을 이용해 반드시 Dispose 하던 습관이 있을 것입니다. python의 경우 with 문으로 이것을 대체할 수 있어야 하는데 실제로 해보면 sqlite3의 경우 close 함수 역할을 하지는 않습니다. 가령 아래와 같은 경우, 
 
<pre style='margin: 10px 0px 10px 10px; padding: 10px 0px 10px 10px; background-color: #fbedbb; overflow: auto; font-family: Consolas, Verdana;' >
# 아래의 코드는 close하지 않음.
with sqlite3.connect('./my_test.db') as con:
 pass

cursor = con.cursor()

query = "CREATE TABLE IF NOT EXISTS member(ID int primary key not null, Name text, Age int);"
cursor.execute(query)

# 대신 (<a target='tab' href='https://www.sysnet.pe.kr/2/0/13325#closing'>psycopg2처럼</a>) closing으로 처리해야 함
from contextlib import closing
with closing(sqlite3.connect('./my_test.db')) as con:
 pass
</pre>
 
만약 with 문에서 close 함수가 호출되었다면 이후의 코드에서 오류가 발생했을 텐데 위의 코드는 아무런 오류도 없이 잘 실행이 됩니다. 물론, 명시적으로 close를 호출하면 오류가 발생합니다. 
 
<pre style='margin: 10px 0px 10px 10px; padding: 10px 0px 10px 10px; background-color: #fbedbb; overflow: auto; font-family: Consolas, Verdana;' >
con = sqlite3.connect('./my_test.db')
con.close()

cursor = con.cursor() # 예외 발생: sqlite3.ProgrammingError: Cannot operate on a closed database.
</pre>
 
그러니까, sqlite의 경우 (closing 없이) with 문과 사용해 자원 해제를 할 수는 없습니다. 
 
<pre style='margin: 10px 0px 10px 10px; padding: 10px 0px 10px 10px; background-color: #fbedbb; overflow: auto; font-family: Consolas, Verdana;' >
using sqlite3 in python with "WITH" keyword
; <a target='tab' href='https://stackoverflow.com/questions/19522505/using-sqlite3-in-python-with-with-keyword/50645518'>https://stackoverflow.com/questions/19522505/using-sqlite3-in-python-with-with-keyword/50645518</a>
</pre>
 
<hr style='width: 50%' /> 
 
자, 그럼 이제 마지막으로 sqlite3의 다중 액세스에 대한 의문을 풀어야 합니다. 당연히, sqlite의 특성상 기본적인 file lock에 의한 동기화를 수행할 텐데요, 간단하게 스레드를 이용해 다음과 같이 테스트할 수 있습니다. 
 
<pre style='margin: 10px 0px 10px 10px; padding: 10px 0px 10px 10px; background-color: #fbedbb; overflow: auto; font-family: Consolas, Verdana;' >
def access_db(delay):
 print('access_db.start', delay)
 try:
 con = sqlite3.connect('./my_test.db')

cursor = con.cursor()

query = "CREATE TABLE IF NOT EXISTS member(ID int primary key not null, Name text, Age int);"
        cursor.execute(query)

query = "DELETE FROM member"
        cursor.execute(query)

for idx in range(1, 5):
            # query = "INSERT OR REPLACE INTO member VALUES({0}, 'tester{1}', {2})".format(idx, idx, idx * 10)
            query = "INSERT OR REPLACE INTO member VALUES(?, ?, ?)"
            cursor.execute(query, (idx, "tester".format(idx), idx * 10))

if delay is not None:
 time.sleep(delay)

con.commit()

query = "SELECT * FROM member"
        cursor.execute(query)
        print(cursor.fetchall())
    except Exception as e:
        print('exception occurred:', e)

print('access_db.end', delay)

t = Thread(target=access_db, args=[10])
t.start()

time.sleep(2)

access_db(None)

time.sleep(10) # 프로그램 종료를 지연하기 위해.

""" 출력 결과
access_db.start 10
access_db.start None
exception occurred: database is locked
access_db.end None
[(1, 'tester', 10), (2, 'tester', 20), (3, 'tester', 30), (4, 'tester', 40)]
access_db.end 10
"""
</pre>
 
동일한 access_db 함수를 별도의 스레드에서 수행 후 commit 전 10초 대기시키고 주 스레드에서도 마찬가지로 access_db를 호출해 보았습니다. 당연히 INSERT/UPDATE/DELETE 수행을 했기 때문에 sqlite DB 파일은 lock이 10초 동안 풀리지 않으므로 두 번째 access_db에서 "database is locked"라는 오류가 발생하는 것을 볼 수 있습니다. 
 
반면, time.sleep 수행을 commit 이후로 미루면, 
 
<pre style='margin: 10px 0px 10px 10px; padding: 10px 0px 10px 10px; background-color: #fbedbb; overflow: auto; font-family: Consolas, Verdana;' >
# ...[생략]...

con.commit()

if delay is not None:
 time.sleep(delay)

# ...[생략]...

""" 출력 결과
access_db.start 10
access_db.start None
[(1, 'tester', 10), (2, 'tester', 20), (3, 'tester', 30), (4, 'tester', 40)]
access_db.end None
[(1, 'tester', 10), (2, 'tester', 20), (3, 'tester', 30), (4, 'tester', 40)]
access_db.end 10
"""
</pre>
 
수행이 잘 됩니다. 일단 변경으로 인한 commit이 반영되면 파일 잠금이 풀리고 SELECT 유의 동시 접근에는 문제가 없습니다. 결과가 저렇다는 것은 알겠는데, 이제 문제는 다중 액세스 시 "database is locked"에 대한 해결을 어떻게 해야 하냐는 것이 남습니다. 
 
즉, 저 상황이 되면 try/except로 루프를 돌게 만드는 것은 너무 원시적이라 ^^ 아마도 다른 방법이 있을 것 같은데요, 관련해서 찾아보면 아래의 글이 나옵니다. 
 
<pre style='margin: 10px 0px 10px 10px; padding: 10px 0px 10px 10px; background-color: #fbedbb; overflow: auto; font-family: Consolas, Verdana;' >
SQLite 개념/구조/멀티 DB 실사용기
; <a target='tab' href='https://ehdvudee.tistory.com/23'>https://ehdvudee.tistory.com/23</a>
</pre>
 
따라서, busy_timeout 옵션을 주면 되는데 이 글에서 사용한 예제의 경우 넉넉잡고 11초 정도 대기하도록 설정하면, 
 
<pre style='margin: 10px 0px 10px 10px; padding: 10px 0px 10px 10px; background-color: #fbedbb; overflow: auto; font-family: Consolas, Verdana;' >
con = sqlite3.connect('./my_test.db')
con.execute("<a target='tab' href='https://runebook.dev/ko/docs/sqlite/pragma'>PRAGMA</a> busy_timeout=11000")

cursor = con.cursor()

query = "CREATE TABLE IF NOT EXISTS member(ID int primary key not null, Name text, Age int);"
cursor.execute(query)

query = "DELETE FROM member"
cursor.execute(query)

for idx in range(1, 5):
    # query = "INSERT OR REPLACE INTO member VALUES({0}, 'tester{1}', {2})".format(idx, idx, idx * 10)
    query = "INSERT OR REPLACE INTO member VALUES(?, ?, ?)"
    cursor.execute(query, (idx, "tester".format(idx), idx * 10))

if delay is not None:
 time.sleep(delay)

con.commit()

""" 출력 결과
access_db.start 10
access_db.start None
[(1, 'tester', 10), (2, 'tester', 20), (3, 'tester', 30), (4, 'tester', 40)]
access_db.end 10
[(1, 'tester', 10), (2, 'tester', 20), (3, 'tester', 30), (4, 'tester', 40)]
access_db.end None
"""
</pre>
 
정상적으로 2개의 스레드에서 쿼리 수행이 잘 되는 것을 확인할 수 있습니다. 
 
참고로, docker container에서 <a target='tab' href='https://sqlite.org/cli.html'>sqlite3 db 파일에 대해 SQL 쿼리를 임의로 실행</a>해 보고 싶다면, 다음과 같이 sqlite3 도구를 설치하고 진행할 수 있습니다. 
 
<pre style='margin: 10px 0px 10px 10px; padding: 10px 0px 10px 10px; background-color: #fbedbb; overflow: auto; font-family: Consolas, Verdana;' >
# apt install sqlite3

# sqlite3 test.db
SQLite version 3.31.1 2020-01-27 19:55:54
Enter ".help" for usage hints.
sqlite&gt; .tables
alembic_version answer question user
sqlite&gt; select * from user;
1|tester|pbkdf2:..[생략]...|tester@test.com
sqlite&gt; .quit
</pre>
 
<hr style='width: 50%' /> 
 
sqlite3는 원래 연결 개체가 풀링이 안 되는 것으로 알고 있는데... Flask/SQLAlchemy와 엮이는 경우 일단 근래의 테스트 환경(2.0.6 버전의 sqlalchemy)에서는 풀링되고 있습니다.

<hr />[이 글에 대해서 여러분들과 의견을 공유하고 싶습니다. 틀리거나 미흡한 부분 또는 의문 사항이 있으시면 언제든 댓글 남겨주십시오.]

</div>

첨부파일

스팸 방지용 인증 번호

1337 (왼쪽의 숫자를 입력해야 합니다.)