使用pyodbc连接SQLite3并启用fast_executemany优化写入速度时的问题求助

Let's break down how to resolve your connection errors and get fast bulk writes working with your .sosat SQLite database file.

First: Consider Using SQLAlchemy's Native SQLite Engine (Recommended) #

You don't actually need pyodbc to get fast writes with SQLite in SQLAlchemy. The native sqlite3 integration is more straightforward and has built-in optimizations for bulk inserts. Here's how to use it:

Example: Fast Pandas Write #

import pandas as pd
from sqlalchemy import create_engine

# Connect directly to your .sosat file (treat it like a regular .db)
engine = create_engine('sqlite:///C:\\Users\\Documents\\PythonScripts\\FLR.sosat')

# Use method='multi' and chunksize to speed up pandas writes
df.to_sql(
    'your_table_name',
    engine,
    if_exists='append',
    method='multi',  # Executes multiple rows in a single INSERT
    chunksize=1000   # Adjust based on your data size
)

Example: Manual Bulk Insert with Cursor #

If you need more control over the cursor:

with engine.connect() as conn:
    # Get the native sqlite3 cursor
    cursor = conn.connection.cursor()
    
    # Bulk insert using executemany
    cursor.executemany(
        "INSERT INTO your_table (col1, col2) VALUES (?, ?)",
        your_data_list  # List of tuples with row data
    )
    conn.commit()

If You Must Use PyODBC for fast_executemany #

If you specifically need to leverage pyodbc's fast_executemany feature, here's how to fix your connection issues:

Step 1: Fix Driver Name & Connection String #

The "Data source name not found" error happens because either:

• Your Python version (32/64-bit) doesn't match the installed SQLite ODBC driver version
• You're using the wrong driver name in your connection string

For the driver you downloaded, use either:

• {SQLite3 ODBC Driver} (for the SQLite3-specific driver)
• {SQLite ODBC Driver} (for the generic SQLite driver)

Step 2: Correct SQLAlchemy Connection #

SQLAlchemy's default RFC1738 URL format doesn't play nicely with SQLite + pyodbc. Instead, use one of these two approaches:

Approach A: Pass a Pre-Made PyODBC Connection to SQLAlchemy

import pyodbc
from sqlalchemy import create_engine
from sqlalchemy.pool import StaticPool

# Build the pyodbc connection string correctly
conn_str = (
    "DRIVER={SQLite3 ODBC Driver};"
    "DATABASE=C:\\Users\\Documents\\PythonScripts\\FLR.sosat;"
)

# Create the pyodbc connection
pyodbc_conn = pyodbc.connect(conn_str)

# Initialize SQLAlchemy engine with the existing connection
engine = create_engine(
    "pyodbc:///",
    creator=lambda: pyodbc_conn,
    poolclass=StaticPool  # Critical for SQLite: avoids multiple connection conflicts
)

# Now enable fast_executemany and run your bulk insert
with engine.connect() as conn:
    cursor = conn.connection.cursor()
    cursor.fast_executemany = True  # Enable the fast bulk write feature
    
    cursor.executemany(
        "INSERT INTO your_table (col1, col2) VALUES (?, ?)",
        your_data_list
    )
    conn.commit()

Approach B: Use URL-Encoded ODBC Connection String

If you prefer using a SQLAlchemy URL directly, you need to URL-encode the ODBC parameters:

from sqlalchemy import create_engine

# URL-encoded connection string (encode special chars like =, {, }, \)
engine_url = (
    "pyodbc:///?odbc_connect="
    "DRIVER%3D%7BSQLite3+ODBC+Driver%7D%3B"
    "DATABASE%3DC%3A%5CUsers%5CDocuments%5CPythonScripts%5CFLR.sosat"
)

engine = create_engine(engine_url)

# Same fast_executemany setup as above
with engine.connect() as conn:
    cursor = conn.connection.cursor()
    cursor.fast_executemany = True
    cursor.executemany("INSERT INTO your_table (col1, col2) VALUES (?, ?)", your_data_list)
    conn.commit()

Bonus: Additional SQLite Write Speed Tips #

To squeeze even more speed out of your bulk writes:

• Disable auto-commit: Manually commit after bulk inserts instead of per-row.
• Turn off indexes temporarily: Drop indexes before writing, then rebuild them afterward.
• Enable WAL mode: Run PRAGMA journal_mode = WAL; on your connection to enable write-ahead logging, which improves concurrent write performance.

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