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

파이썬 - 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 함수 역할을 하지는 않습니다. 가령 아래와 같은 경우,

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)

만약 with 문에서 close 함수가 호출되었다면 이후의 코드에서 오류가 발생했을 텐데 위의 코드는 아무런 오류도 없이 잘 실행이 됩니다. 물론, 명시적으로 close를 호출하면 오류가 발생합니다.

con = sqlite3.connect('./my_test.db')
con.close()

cursor = con.cursor() # 예외 발생: sqlite3.ProgrammingError: Cannot operate on a closed database.

그러니까, sqlite의 경우 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개의 스레드에서 쿼리 수행이 잘 되는 것을 확인할 수 있습니다.




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

[연관 글]


donaricano-btn



[최초 등록일: ]
[최종 수정일: 10/17/2021]

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

비밀번호

댓글 쓴 사람
 




[1]  2  3  4  5  6  7  8  9  10  11  12  13  14  15  ...
NoWriterDateCnt.TitleFile(s)
12868정성태12/7/202116오류 유형: 770. twine 업로드 시 "HTTPError: 400 Bad Request ..." 오류
12867정성태12/7/202128개발 환경 구성: 612. 파이썬 - PyPI 패키지 만들기 (3) entry_points 옵션
12866정성태12/7/202115오류 유형: 769. "docker build ..." 시 "failed to solve with frontend dockerfile.v0: failed to read dockerfile ..." 오류
12865정성태12/6/202129개발 환경 구성: 611. 파이썬 - PyPI 패키지 만들기 (2) long_description, cmdclass 옵션
12864정성태12/6/202140Linux: 46. WSL 환경에서 find 명령을 사용해 파일을 찾는 방법
12863정성태12/4/202151개발 환경 구성: 610. 파이썬 - PyPI 패키지 만들기
12862정성태12/3/202146오류 유형: 768. Golang - 빌드 시 "cmd/go: unsupported GOOS/GOARCH pair linux /amd64" 오류
12861정성태12/3/202162개발 환경 구성: 609. 파이썬 - "Windows embeddable package"로 개발 환경 구성하는 방법
12860정성태12/1/2021117오류 유형: 767. SQL Server - 127.0.0.1로 접속하는 경우 "Access is denied"가 발생한다면?
12859정성태12/1/2021133개발 환경 구성: 608. Hyper-V 가상 머신에 Console 모드로 로그인하는 방법
12858정성태11/30/2021317개발 환경 구성: 607. 로컬의 USB 장치를 원격 머신에 제공하는 방법 - usbip-win
12857정성태11/24/2021112개발 환경 구성: 606. WSL Ubuntu 20.04에서 파이썬을 위한 uwsgi 설치 방법
12856정성태11/23/2021334.NET Framework: 1121. C# - 동일한 IP:Port로 바인딩 가능한 서버 소켓 [2]
12855정성태11/13/2021111개발 환경 구성: 605. Azure App Service - Kudu SSH 환경에서 FTP를 이용한 파일 전송
12854정성태11/13/2021230개발 환경 구성: 604. Azure - 윈도우 VM에서 FTP 여는 방법
12853정성태11/10/2021137오류 유형: 766. Azure App Service - JBoss 호스팅 생성 시 "This region has quota of 0 PremiumV3 instances for your subscription. Try selecting different region or SKU."
12851정성태11/1/2021236스크립트: 34. 파이썬 - MySQLdb 기본 예제 코드
12850정성태10/27/2021514오류 유형: 765. 우분투에서 pip install mysqlclient 실행 시 "OSError: mysql_config not found" 오류
12849정성태10/17/2021515스크립트: 33. JavaScript와 C#의 시간 변환
12848정성태10/17/2021474스크립트: 32. 파이썬 - sqlite3 기본 예제 코드
12847정성태10/14/2021428스크립트: 31. 파이썬 gunicorn - WORKER TIMEOUT 오류 발생
12846정성태10/7/2021524스크립트: 30. 파이썬 __debug__ 플래그 변수에 따른 코드 실행 제어
12845정성태10/6/2021892.NET Framework: 1120. C# - BufferBlock<T> 사용 예제 [4]파일 다운로드1
12844정성태10/3/2021445오류 유형: 764. MSI 설치 시 "... is accessible and not read-only." 오류 메시지
12843정성태10/3/2021488스크립트: 29. 파이썬 - fork 시 기존 클라이언트 소켓 및 스레드의 동작파일 다운로드1
[1]  2  3  4  5  6  7  8  9  10  11  12  13  14  15  ...