使用Python的DataFrame和duckdb查询PG数据库

直接使用以下脚本：

import psycopg2
import duckdb
import pandas as pd

# Connect to PostgreSQL
conn_postgres = psycopg2.connect(
    dbname="pg_test_db",
    user="your_username",  # Replace with your username
    password="your_password",  # Replace with your password
    host="localhost",  # Or your PostgreSQL server's address
    port="5432"  # Default PostgreSQL port
)

# Fetch data from PostgreSQL into a pandas DataFrame
query = "SELECT * FROM table_test"
df = pd.read_sql_query(query, conn_postgres)

# Close the PostgreSQL connection
conn_postgres.close()

# Initialize DuckDB in-memory database
duckdb_conn = duckdb.connect(database=':memory:')

# Load the DataFrame into DuckDB
duckdb_conn.register('table_test_duck', df)

# Now you can perform queries using DuckDB on the registered table
result_df = duckdb_conn.execute("SELECT * FROM table_test_duck WHERE some_column = 'some_value'").fetchdf()

# Display the result
print(result_df)

# Don't forget to close the DuckDB connection when done
duckdb_conn.close()