파이썬 - sqlite3 예제 코드
파이썬에서 sqlite3으로,
sqlite3 — SQLite 데이터베이스용 DB-API 2.0 인터페이스
; https://docs.python.org/ko/3/library/sqlite3.html
로컬 DB 파일을 접근하는 것은 import만 하고 곧바로 사용하면 됩니다. 가령 다음과 같이 connect 함수만 호출해도,
import sqlite3
con = sqlite3.connect('./my_test.db')
로컬 경로에는 my_test.db 파일이 생성됩니다. C#의 경우 IDbConnection 개체를 얻었으면 그로부터 IDbCommand를 이용해 명령을 실행하지만, 파이썬의 경우에는 sqlite3.Connection 개체로부터 cursor 개체를 얻어 쿼리를 실행하는 식입니다. 또한, SELECT를 제외한 INSERT/UPDATE/DELETE 시에는 commit을 해야 반영이 되는데, 따라서 다음과 같은 식으로 일반적인 SQL 쿼리 수행을 할 수 있습니다.
# 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())
사용 후에는 당연히 sqlite3.Connection 개체의 close 함수를 호출하는 것으로 정리를 합니다. (connect 시에 sqlite db 파일에 대한 Handle이 열리고, close 시에 파일 핸들이 닫힙니다.)
con.close()
당연히 sql injection 공격을 예방하기 위해
parameterized query를 사용해야겠죠. ^^ 방법은 query 내에 "?"로 표시를 하고, exeucte 실행 시 대응하는 인자를 튜플로 넣으면 됩니다. 위의 예제 코드라면 다음과 같이 수정하시면 됩니다.
for idx in range(1, 5):
query = "INSERT OR REPLACE INTO member VALUES(?, ?, ?)"
cursor.execute(query, (idx, "tester".format(idx), idx * 10))
C# 개발자라면 SqlConnection과 같은 개체를 사용할 때는 using을 이용해 반드시 Dispose 하던 습관이 있을 것입니다. python의 경우 with 문으로 이것을 대체할 수 있어야 하는데 실제로 해보면 sqlite3의 경우 close 함수 역할을 하지는 않습니다. 가령 아래와 같은 경우,
# 아래의 코드는 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)
# 대신 (psycopg2처럼) closing으로 처리해야 함
from contextlib import closing
with closing(sqlite3.connect('./my_test.db')) as con:
pass
만약 with 문에서 close 함수가 호출되었다면 이후의 코드에서 오류가 발생했을 텐데 위의 코드는 아무런 오류도 없이 잘 실행이 됩니다. 물론, 명시적으로 close를 호출하면 오류가 발생합니다.
con = sqlite3.connect('./my_test.db')
con.close()
cursor = con.cursor() # 예외 발생: sqlite3.ProgrammingError: Cannot operate on a closed database.
그러니까, sqlite의 경우 (closing 없이) with 문과 사용해 자원 해제를 할 수는 없습니다.
using sqlite3 in python with "WITH" keyword
; https://stackoverflow.com/questions/19522505/using-sqlite3-in-python-with-with-keyword/50645518
자, 그럼 이제 마지막으로 sqlite3의 다중 액세스에 대한 의문을 풀어야 합니다. 당연히, sqlite의 특성상 기본적인 file lock에 의한 동기화를 수행할 텐데요, 간단하게 스레드를 이용해 다음과 같이 테스트할 수 있습니다.
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
"""
동일한 access_db 함수를 별도의 스레드에서 수행 후 commit 전 10초 대기시키고 주 스레드에서도 마찬가지로 access_db를 호출해 보았습니다. 당연히 INSERT/UPDATE/DELETE 수행을 했기 때문에 sqlite DB 파일은 lock이 10초 동안 풀리지 않으므로 두 번째 access_db에서 "database is locked"라는 오류가 발생하는 것을 볼 수 있습니다.
반면, time.sleep 수행을 commit 이후로 미루면,
# ...[생략]...
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
"""
수행이 잘 됩니다. 일단 변경으로 인한 commit이 반영되면 파일 잠금이 풀리고 SELECT 유의 동시 접근에는 문제가 없습니다. 결과가 저렇다는 것은 알겠는데, 이제 문제는 다중 액세스 시 "database is locked"에 대한 해결을 어떻게 해야 하냐는 것이 남습니다.
즉, 저 상황이 되면 try/except로 루프를 돌게 만드는 것은 너무 원시적이라 ^^ 아마도 다른 방법이 있을 것 같은데요, 관련해서 찾아보면 아래의 글이 나옵니다.
SQLite 개념/구조/멀티 DB 실사용기
; https://ehdvudee.tistory.com/23
따라서, busy_timeout 옵션을 주면 되는데 이 글에서 사용한 예제의 경우 넉넉잡고 11초 정도 대기하도록 설정하면,
con = sqlite3.connect('./my_test.db')
con.execute("PRAGMA 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
"""
정상적으로 2개의 스레드에서 쿼리 수행이 잘 되는 것을 확인할 수 있습니다.
참고로, docker container에서
sqlite3 db 파일에 대해 SQL 쿼리를 임의로 실행해 보고 싶다면, 다음과 같이 sqlite3 도구를 설치하고 진행할 수 있습니다.
# apt install sqlite3
# sqlite3 test.db
SQLite version 3.31.1 2020-01-27 19:55:54
Enter ".help" for usage hints.
sqlite> .tables
alembic_version answer question user
sqlite> select * from user;
1|tester|pbkdf2:..[생략]...|tester@test.com
sqlite> .quit
sqlite3는 원래 연결 개체가 풀링이 안 되는 것으로 알고 있는데... Flask/SQLAlchemy와 엮이는 경우 일단 근래의 테스트 환경(2.0.6 버전의 sqlalchemy)에서는 풀링되고 있습니다.
[이 글에 대해서 여러분들과 의견을 공유하고 싶습니다. 틀리거나 미흡한 부분 또는 의문 사항이 있으시면 언제든 댓글 남겨주십시오.]