如何在Python中使用SQL指令操作CSV文件？求适配库及示例

Absolutely! You can absolutely run SQL queries against your CSV files (A.csv, B.csv, C.csv, D.csv) in Python—no need to pre-convert them to .db files or struggle with tools that lack clear Python integration. Here are two of the most reliable, easy-to-use approaches with step-by-step examples:

1. Pandas + SQLite (In-Memory Database) #

This is perfect if you already know SQLite syntax. We’ll load your CSVs into a temporary in-memory SQLite database, then query it just like a regular database.

First, make sure you have pandas installed (sqlite3 comes with Python’s standard library):

pip install pandas

Example Code #

import pandas as pd
import sqlite3

# Connect to an in-memory SQLite database (no physical .db file created)
db_conn = sqlite3.connect(':memory:')

# Load each CSV into a named table in the database
# Replace the file paths with your actual CSV locations
pd.read_csv('A.csv').to_sql('table_a', db_conn, index=False)
pd.read_csv('B.csv').to_sql('table_b', db_conn, index=False)
pd.read_csv('C.csv').to_sql('table_c', db_conn, index=False)
pd.read_csv('D.csv').to_sql('table_d', db_conn, index=False)

# Run your SQL queries
# Example 1: Fetch a sample from table_a
sample_query = "SELECT * FROM table_a LIMIT 10;"
sample_result = pd.read_sql_query(sample_query, db_conn)
print("Sample data from A.csv:\n", sample_result)

# Example 2: Join two tables (adjust columns to match your data)
join_query = """
SELECT a.id, a.name, b.sales_amount
FROM table_a a
INNER JOIN table_b b ON a.id = b.customer_id
WHERE b.sales_amount > 500;
"""
join_result = pd.read_sql_query(join_query, db_conn)
print("\nJoined sales data:\n", join_result)

# Clean up: close the connection
db_conn.close()

2. DuckDB (Lightweight Analytical Database) #

DuckDB is a fantastic tool for this use case—it lets you query CSV files directly with SQL, no need to load them into a database first. It’s fast, simple, and designed for data analysis tasks.

Install DuckDB first:

pip install duckdb

Example Code #

import duckdb

# Connect to an in-memory DuckDB instance
db_conn = duckdb.connect()

# Query a single CSV directly
single_csv_query = "SELECT * FROM 'A.csv' LIMIT 10;"
single_result = db_conn.execute(single_csv_query).fetchdf()
print("Sample data from A.csv:\n", single_result)

# Join multiple CSVs directly (no pre-loading needed!)
join_query = """
SELECT c.*, d.status
FROM 'C.csv' c
LEFT JOIN 'D.csv' d ON c.order_id = d.order_id
WHERE c.order_date >= '2024-01-01';
"""
join_result = db_conn.execute(join_query).fetchdf()
print("\nJoined order data:\n", join_result)

# Create a temporary table if you want to reuse a CSV's data
db_conn.execute("CREATE TEMP TABLE orders AS SELECT * FROM 'C.csv';")
count_query = "SELECT COUNT(*) FROM orders WHERE total > 1000;"
order_count = db_conn.execute(count_query).fetchone()[0]
print(f"\nNumber of high-value orders: {order_count}")

# Close the connection
db_conn.close()

Bonus: csvkit (Command-Line + Python API) #

If you prefer a command-line tool but want to integrate it into Python, you can use csvkit’s csvsql command via subprocess. For example:

import subprocess

# Run a SQL query on CSVs and save results to a new CSV
subprocess.run([
    "csvsql",
    "--query", "SELECT * FROM A.csv WHERE value > 100",
    "A.csv",
    "--output", "filtered_A.csv"
])

But for most Python scripting use cases, Pandas+SQLite or DuckDB will be more flexible and easier to integrate into your code.

内容的提问来源于stack exchange，提问作者Michael