example class for cursor factory

motherduck_client.py

import threading
from duckdb import DuckDBPyConnection

# from duckdb_provider.hooks.duckdb_hook import DuckDBHook
import duckdb

SHARE_URI = "md:_share/s2/7564f992-2f93-4799-bce2-1637445b2881"
DB_PATH = "md:"


class MotherDuckClient:
    def __init__(self, share_uri: str = SHARE_URI):
        self.share_uri = share_uri
        # self._duckdb_con: DuckDBPyConnection = DuckDBHook.get_hook("motherduck").get_conn()
        self._duckdb_con: DuckDBPyConnection = duckdb.connect(DB_PATH)
        self._ensure_share_attached(self._duckdb_con)

    @staticmethod
    def _get_attached_shares(conn: DuckDBPyConnection) -> list[str]:
        res = conn.execute("SHOW ALL DATABASES").fetchall()
        return [
            fully_qualified_name
            for _, is_attached, _type, fully_qualified_name in res
            if is_attached and _type == "motherduck share"
        ]

    def _ensure_share_attached(self, conn: DuckDBPyConnection):
        if self.share_uri not in self._get_attached_shares(conn):
            conn.execute(f"ATTACH '{self.share_uri}'")

    def get_connection(self):
        return self._duckdb_con.cursor()


if __name__ == "__main__":

    lock = threading.Lock()

    def query_from_thread(query: str):
        with lock:
            client = MotherDuckClient()
        conn = client.get_connection()
        result = conn.execute(query).fetchall()
        print(result)
        conn.close()


    queries = ["SELECT * FROM s2.main.t1", "SELECT * FROM s2.main.t1 LIMIT 1"]
    threads = []

    for i, query in enumerate(queries):
        threads.append(
            threading.Thread(
                target=query_from_thread, args=(query,), name="query_" + str(i)
            )
        )
    for thread in threads:
        thread.start()

    for thread in threads:
        thread.join()

Workaround: can query using MD_ALL_DATABASES()