pythonsqliteodbcpyodbcpypyodbc

Can't Access SQLite3 Database With pypyodbc


I am switching a python program over from my Windows laptop which had Microsoft SQL Studio 18 on it to a raspberry pi so I can keep it running 24/7. On the laptop, it worked just fine using pyodbc to connect to the local Microsoft SQL Server, but on the raspberry pi, it isn't working. I first had to switch out pyodbc for pypyodbc because there was an issue with the pip installation for pyodbc. Then, I had the database copied over as an .sql file and turned it into a .db file with SQLite3. However, the suggested Driver value for SQLite3 in the pypyodbc.connect() function, Driver=SQLite3 ODBC Driver is throwing the error

('01000', "[01000] [unixODBC][Driver Manager]Can't open lib 'SQLite3 ODBC Driver' : file not found")

I have no idea how to solve this. I tried to download Microsoft SQL Server for Linux Debian 9, but it couldn't find the msodbcsql18 library. I tried to install FreeTDS ODBC, but my terminal doesn't recognize the tsql command, for some reason. I have not been able to locate this mythical "odbc.ini", either. I've been pulling my hair out over what should have been the most trivial step in this transition, and I need help. Anything you can tell me about will be much appreciated.


Solution

  • I'd take a step back and ask if ODBC is the right interface for interacting with Sqlite3 from a raspberry pi. Python should come with a sqlite3 library as part of its standard library, it should be a pretty straightforward replacement.

    So rather than having:

    conn = pypyodbc.connect(<args)
    

    You'll have

    import sqlite3
    
    conn = sqlite3.connect('/path/to/file/db')
    

    That connection object should work mostly the same as the one you got from pypyodbc, assuming that library follow's the DBAPI standard, which most python SQL libraries do.