Python DB API

Python DB API

표준 DuckDB Python API는 PEP 249가 설명하는 DB-API 2.0 명세를 따르는 SQL 인터페이스를 제공해요. SQLite Python API와 비슷하죠.

출처: 문서

본문

커넥션 (Connection)

모듈을 사용하려면 먼저 데이터베이스에 대한 커넥션을 나타내는 DuckDBPyConnection 객체를 만들어야 해요. 이는 [duckdb.connect]({% link docs/current/clients/python/reference/index.md %}#duckdb.connect) 메서드로 해요.

config 키워드 인자는 DuckDB가 이해하는 [설정]({% link docs/current/configuration/overview.md %}#configuration-reference)을 참조하는 키→값 쌍을 담은 dict를 제공하는 데 쓸 수 있어요.

인메모리 커넥션

특별한 값 :memory:를 사용하면 인메모리 데이터베이스를 만들 수 있어요. 참고로 인메모리 데이터베이스는 디스크에 아무것도 저장되지 않으니(즉 Python 프로세스를 종료하면 모든 데이터가 사라져요) 이 점을 기억하세요.

이름 붙은 인메모리 커넥션

특별한 값 :memory:는 이름을 뒤에 붙일 수도 있어요. 예: :memory:conn3. 이름이 제공되면 이후의 duckdb.connect 호출은 같은 데이터베이스에 대한 새 커넥션을 만들어 카탈로그(뷰, 테이블, 매크로 등)를 공유해요.

: 뒤 이름 없이 :memory:를 사용하면 항상 새롭고 별도의 데이터베이스 인스턴스를 만들어요.

기본 커넥션

기본적으로 duckdb 모듈 안에 사는 (이름 없는) 인메모리 데이터베이스를 만들어요. DuckDBPyConnection의 모든 메서드는 duckdb 모듈에서도 사용할 수 있는데, 이 메서드들이 사용하는 것이 바로 이 커넥션이에요.

특별한 값 :default:로 이 기본 커넥션을 얻을 수 있어요.

파일 기반 커넥션

database가 파일 경로면 영구 데이터베이스에 대한 커넥션이 설정돼요. 파일이 없으면 파일이 만들어져요(파일 확장자는 무관하며 .db, .duckdb, 또는 아무거나 가능해요).

read_only 커넥션

읽기 전용 모드로 연결하려면 read_only 플래그를 True로 설정하면 돼요. 파일이 없으면 읽기 전용으로 연결할 때 파일이 만들어지지 않아요. 여러 Python 프로세스가 같은 데이터베이스 파일에 동시에 접근하려면 읽기 전용 모드가 필요해요.

import duckdb

duckdb.execute("CREATE TABLE tbl AS SELECT 42 a")
con = duckdb.connect(":default:")
con.sql("SELECT * FROM tbl")
# or
duckdb.default_connection().sql("SELECT * FROM tbl")

┌───────┐
│   a   │
│ int32 │
├───────┤
│    42 │
└───────┘

import duckdb

# to start an in-memory database
con = duckdb.connect(database = ":memory:")
# to use a database file (not shared between processes)
con = duckdb.connect(database = "my-db.duckdb", read_only = False)
# to use a database file (shared between processes)
con = duckdb.connect(database = "my-db.duckdb", read_only = True)
# to explicitly get the default connection
con = duckdb.connect(database = ":default:")

기존 커넥션의 또 다른 핸들은 cursor() 메서드로 얻을 수 있어요. 이 메서드는 새 커넥션을 여는 게 아니라 같은 커넥션에 대한 또 다른 핸들을 만든다는 점을 주의하세요. 따라서 한 커넥션에서 만든 커서들은 동시에 쿼리를 실행할 수 없어요. 단일 커넥션은 스레드 안전하지만 각 쿼리 동안 잠겨서 데이터베이스 접근을 사실상 직렬화해요. 자세한 내용은 [About cursor()]({% link docs/current/clients/python/overview.md %}#about-cursor)를 참고하세요.

커넥션은 스코프를 벗어날 때 암묵적으로 닫히거나 close()로 명시적으로 닫혀요. 데이터베이스 인스턴스에 대한 마지막 커넥션이 닫히면 데이터베이스 인스턴스도 닫혀요.

쿼리

SQL 쿼리는 커넥션의 execute() 메서드로 DuckDB에 보낼 수 있어요. 쿼리가 실행되면 커넥션의 fetchone과 fetchall 메서드로 결과를 가져올 수 있어요. fetchall은 모든 결과를 가져오고 트랜잭션을 완료해요. fetchone은 더 이상 결과가 없을 때까지 호출할 때마다 결과의 한 행을 가져와요. 트랜잭션은 fetchone이 호출되고 남은 결과가 없을 때(반환 값이 None)에만 닫혀요. 예를 들어 단일 행만 반환하는 쿼리의 경우 fetchone을 한 번 호출해 결과를 가져오고 두 번째 호출로 트랜잭션을 닫아야 해요. 몇 가지 간단한 예시를 볼게요.

# create a table
con.execute("CREATE TABLE items (item VARCHAR, value DECIMAL(10, 2), count INTEGER)")
# insert two items into the table
con.execute("INSERT INTO items VALUES ('jeans', 20.0, 1), ('hammer', 42.2, 2)")

# retrieve the items again
con.execute("SELECT * FROM items")
print(con.fetchall())
# [('jeans', Decimal('20.00'), 1), ('hammer', Decimal('42.20'), 2)]

# retrieve the items one at a time
con.execute("SELECT * FROM items")
print(con.fetchone())
# ('jeans', Decimal('20.00'), 1)
print(con.fetchone())
# ('hammer', Decimal('42.20'), 2)
print(con.fetchone()) # This closes the transaction. Any subsequent calls to .fetchone will return None
# None

커넥션 객체의 description 속성은 명세에 따라 열 이름을 담아요.

준비된 문 (Prepared Statements)

DuckDB는 API에서 execute와 executemany 메서드로 [준비된 문]({% link docs/current/sql/query_syntax/prepared_statements.md %})도 지원해요. 값은 ? 또는 $1(달러 기호와 숫자) 플레이스홀더를 포함하는 쿼리 뒤에 추가 파라미터로 전달될 수 있어요. ? 표기를 쓰면 Python 파라미터에 전달된 순서와 같은 순서로 값이 추가돼요. $ 표기를 쓰면 Python 파라미터 안에서 발견된 값의 번호와 인덱스에 기반해 SQL 문 안에서 값을 재사용할 수 있어요. 값은 [변환 규칙]({% link docs/current/clients/python/conversion.md %}#object-conversion-python-object-to-duckdb)에 따라 변환돼요.

몇 가지 예시를 볼게요. 먼저 [준비된 문]({% link docs/current/sql/query_syntax/prepared_statements.md %})으로 행을 삽입해요.

con.execute("INSERT INTO items VALUES (?, ?, ?)", ["laptop", 2000, 1])

둘째, [준비된 문]({% link docs/current/sql/query_syntax/prepared_statements.md %})으로 여러 행을 삽입해요.

con.executemany("INSERT INTO items VALUES (?, ?, ?)", [["chainsaw", 500, 10], ["iphone", 300, 2]] )

[준비된 문]({% link docs/current/sql/query_syntax/prepared_statements.md %})으로 데이터베이스를 쿼리해요.

con.execute("SELECT item FROM items WHERE value > ?", [400])
print(con.fetchall())

[('laptop',), ('chainsaw',)]

[준비된 문]({% link docs/current/sql/query_syntax/prepared_statements.md %})에 $ 표기와 재사용 값으로 쿼리하기:

con.execute("SELECT $1, $1, $2", ["duck", "goose"])
print(con.fetchall())

[('duck', 'duck', 'goose')]

Warning executemany를 대량의 데이터를 DuckDB에 삽입하는 데 사용하지 마세요. 더 나은 옵션은 [data ingestion page]({% link docs/current/clients/python/data_ingestion.md %})를 참고하세요.

이름 있는 파라미터 (Named Parameters)

표준 이름 없는 파라미터($1, $2 등) 외에도 $my_parameter 같은 이름 있는 파라미터를 제공할 수 있어요. 이름 있는 파라미터를 사용할 때는 parameters 인자에 str에서 값으로의 딕셔너리 매핑을 제공해야 해요. 사용 예시는 다음과 같아요.

import duckdb

res = duckdb.execute("""
    SELECT
        $my_param,
        $other_param,
        $also_param
    """,
    {
        "my_param": 5,
        "other_param": "DuckDB",
        "also_param": [42]
    }
).fetchall()
print(res)

[(5, 'DuckDB', [42])]

더 알아보기 (Learn more)