使用duckdb加载mysql

安装duckdb

https://duckdb.org/docs/installation/index

加载mysql扩展

https://duckdb.org/docs/extensions/mysql.html
离线安装的话，可以等INSTALL mysql;超时后，根据它提示的URL到有网环境下载，再传到服务器挂载Nginx，修改/etc/hosts指向，再重新执行INSTALL。

Demo代码如下：

import duckdb

DB_HOST = "localhost"
DB_USERNAME = "root"
DB_PASS = "123456"
DB_PORT = 3306
DB_NAME = "test_1"

PARAMETERS = f"host={DB_HOST} user={DB_USERNAME} passwd={DB_PASS} port={DB_PORT} database={DB_NAME}"
ATTACH_QUERY = f"ATTACH '{PARAMETERS}' AS user (TYPE mysql);"
USE_QUERY = "USE user;"

with duckdb.connect() as con:
    con.install_extension("mysql")
    con.load_extension("mysql")
    con.sql(ATTACH_QUERY)
    con.sql(USE_QUERY)
    con.sql("SHOW TABLES").show()
    res = con.sql("select * from user;")
    res.show()
    data: list[tuple] = res.fetchall()
    print(f"fetchall: {data!r}")

封装成上下文管理器

from contextlib import contextmanager
from typing import Generator

import asynctor
import duckdb
from duckdb import DuckDBPyConnection


@contextmanager
def duck_client(
    database: str, host="localhost", user="root", passwd="123456", port=3306
) -> Generator[DuckDBPyConnection, None, None]:
    """使用duckdb连接mysql

    Usage::
        >>> with duck_client() as conn:
        ...     conn.sql('show tables').show()
    """

    PARAMETERS = f"host={host} user={user} passwd={passwd} {port=} database={database}"
    ATTACH_QUERY = f"ATTACH '{PARAMETERS}' AS user (TYPE mysql);"
    USE_QUERY = "USE user;"
    with duckdb.connect() as con:
        con.install_extension("mysql")
        con.load_extension("mysql")
        con.sql(ATTACH_QUERY)
        con.sql(USE_QUERY)
        yield con


@asynctor.timeit
def main() -> None:
    with duck_client("test_1") as conn:
        conn.sql("show tables").show()


if __name__ == "__main__":
    main()