Connect to SQLite3 with pyodbc for fast

We currently use a program that creates and writes large datasets to databases, but the process can take a long time. We are trying to incorporate cursor.fast_executemany = True from sqlalchemy to improve the write times to these databases. My code errors out when I try to create an engine using SQLite3 and pyodbc here:

import pandas as pd
from sqlite3 import connect
import pyodbc
from sqlalchemy import create_engine

engine = create_engine('SQLite3 ODBC Driver pyodbc:///C:\\Users\\Documents\\PythonScripts\\FLR.sosat')

conn = engine.connect()
c = conn.cursor()

We have tried numerous ways where we specify the driver and server and things like that like the following:

# conn = pyodbc.connect('DRIVER={SQL Server};'
#                       'SERVER=localhost;'
#                       'DATABASE=C:\\Users\\Documents\\PythonScripts\\FLR.sosat')

The single engine line seems to be the closest to working due to us receiving driver and server errors from the commented out code above. We have downloaded the ODBC driver from http://www.ch-werner.de/sqliteodbc/

We receive the ArgumentError: Could not parse rfc1738 URL from string.

We would appreciate any help or ideas on how to get the SQLite3 database to pyodbc and how to improve the write speed. Thanks!

Note the .sosat file is a database file that uses sqlite3, it should work like any .db file

We tried the fix from here: Connect to SQLite3 server using PyODBC, Python and that did not work for us, we received the driver error: InterfaceError: ('IM002', '[IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified (0) (SQLDriverConnect)')

CodePudding user response：

What you are trying to accomplish will not work for two reasons:

Reason 1:

SQLAlchemy does not support pyodbc as a DBAPI layer for SQLite.

Reason 2:

Even if SQLAlchemy did support sqlite pyodbc:// the SQLite ODBC Driver would have to support "parameter arrays", an optional ODBC feature that fast_executemany = True uses to do its magic. Not all ODBC drivers support fast_executemany = True as shown here. A quick test with vanilla pyodbc shows that "SQLite3 ODBC Driver" doesn't support it, in fact it crashes the Python interpreter:

crsr.fast_executemany = True
crsr.executemany(
    f"INSERT INTO {table_name} (txt) VALUES (?)", [("foo",), ("bar",)]
)
# Process finished with exit code -1073741819 (0xC0000005)

(Error 0xC0000005 is "Access Violation".)

CodePudding user response：

Have you tried

import sqlite3
db = r'C:\Users\Documents\PythonScripts\FLR.sosat'
conn = sqlite3.connect(db)
print('connection established')