Microsoft MVP성태의 닷넷 이야기
스크립트: 32. 파이썬 - sqlite3 기본 예제 코드 [링크 복사], [링크+제목 복사],
조회: 9658
글쓴 사람
정성태 (techsharer at outlook.com)
홈페이지
첨부 파일
 
(연관된 글이 2개 있습니다.)

파이썬 - 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)에서는 풀링되고 있습니다.




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

[연관 글]






[최초 등록일: ]
[최종 수정일: 6/30/2023]

Creative Commons License
이 저작물은 크리에이티브 커먼즈 코리아 저작자표시-비영리-변경금지 2.0 대한민국 라이센스에 따라 이용하실 수 있습니다.
by SeongTae Jeong, mailto:techsharer at outlook.com

비밀번호

댓글 작성자
 



2023-03-20 10시31분
파이썬에서 SQLite 사용 방법
; https://blog.naver.com/cjinnnn/223047230959

파이썬 SQLite DB 의 Table 유무 확인 방법
; https://blog.naver.com/cjinnnn/223047238829

파이썬 SQLite DB 의 컬럼 유무 확인 방법
; https://blog.naver.com/cjinnnn/223047246368
정성태

[1]  2  3  4  5  6  7  8  9  10  11  12  13  14  15  ...
NoWriterDateCnt.TitleFile(s)
13650정성태6/18/202452개발 환경 구성: 713. "WSL --debug-shell"로 살펴보는 WSL 2 VM의 리눅스 환경
13649정성태6/18/2024249오류 유형: 910. windbg - !py 확장 명령어 실행 시 "failed to find python interpreter" (2)
13648정성태6/17/2024260오류 유형: 909. C# - DynamicMethod 사용 시 System.TypeAccessException
13647정성태6/16/2024369개발 환경 구성: 712. Windows - WSL 2의 네트워크 통신 방법 - 세 번째 이야기 (같은 IP를 공유하는 WSL 2 인스턴스)
13646정성태6/14/2024389오류 유형: 908. Process Explorer - "Error configuring dump resources: The system cannot find the file specified."
13645정성태6/13/2024659개발 환경 구성: 711. Visual Studio로 개발 시 기본 등록하는 dev tag 이미지로 Docker Desktop k8s에서 실행하는 방법
13644정성태6/12/2024654닷넷: 2265. C# - System.Text.Json의 기본적인 (한글 등에서의) escape 처리
13643정성태6/12/2024695오류 유형: 907. MySqlConnector 사용 시 System.IO.FileLoadException 오류
13642정성태6/11/2024823스크립트: 65. 파이썬 - asgi 버전(2, 3)에 따라 달라지는 uvicorn 호스팅
13641정성태6/11/2024994Linux: 71. Ubuntu 20.04를 22.04로 업데이트
13640정성태6/10/20241014Phone: 21. C# MAUI - Android 환경에서의 파일 다운로드(DownloadManager)
13639정성태6/8/2024946오류 유형: 906. C# MAUI - Android Emulator에서 "Waiting For Debugger"로 무한 대기
13638정성태6/8/20241042오류 유형: 905. C# MAUI - 추가한 layout XML 파일이 Resource.Layout 멤버로 나오지 않는 문제
13637정성태6/6/20241106Phone: 20. C# MAUI - 유튜브 동영상을 MediaElement로 재생하는 방법
13636정성태5/30/20241126닷넷: 2264. C# - 형식 인자로 인터페이스를 갖는 제네릭 타입으로의 형변환파일 다운로드1
13635정성태5/29/2024951Phone: 19. C# MAUI - 안드로이드 "Share" 대상으로 등록하는 방법
13634정성태5/24/20241195Phone: 18. C# MAUI - 안드로이드 플랫폼에서의 Activity 제어
13633정성태5/22/20241138스크립트: 64. 파이썬 - ASGI를 만족하는 최소한의 구현 코드
13632정성태5/20/20241247Phone: 17. C# MAUI - Android 내에 Web 서비스 호스팅
13631정성태5/19/20241281Phone: 16. C# MAUI - /Download 등의 공용 디렉터리에 접근하는 방법
13630정성태5/19/20241842닷넷: 2263. C# - Thread가 Task보다 더 빠르다는 어떤 예제(?)
13629정성태5/18/20241622개발 환경 구성: 710. Android - adb.exe를 이용한 파일 전송
13628정성태5/17/20241620개발 환경 구성: 709. Windows - WHPX(Windows Hypervisor Platform)를 이용한 Android Emulator 가속
13627정성태5/17/20241587오류 유형: 904. 파이썬 - UnicodeEncodeError: 'ascii' codec can't encode character '...' in position ...: ordinal not in range(128)
13626정성태5/15/20241705Phone: 15. C# MAUI - MediaElement Source 경로 지정 방법파일 다운로드1
[1]  2  3  4  5  6  7  8  9  10  11  12  13  14  15  ...