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.
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.