pyodbc to remote sqlite database not working but no errors thrown

Question

I have a Raspberry Pi running OSMC, a media-optimized version of Debian. This Pi holds an SQLITE database that I would like to access using Python from my Windows 7 PC. Both computers are on the same home network.

As recommended by this similar post, I obtained an SQLITE ODBC driver.

I wrote the following code to query the database. The code is not throwing any exceptions, but neither is it returning any data. What I am doing wrong?

import pyodbc

def connectToDB():
    serverIP = '192.168.0.110' #raspberry pi on home network
    databaseName = 'pigarage.db'
    sql = 'DRIVER={SQLite3 ODBC Driver};SERVER=%s;DATABASE=%s;Trusted_connection=yes' % (serverIP, databaseName)
    conn = pyodbc.connect(sql)
    return conn

def runQuery(conn, sql):
    cursor = conn.cursor()
    cursor.execute(sql)
    result = list( cursor.fetchall() ) # list of tuples
    cursor.close()
    return result

if __name__ == '__main__':
    conn = connectToDB()
    print conn
    sql = 'select distinct state from GarageDoorState'
    print runQuery(conn, sql)
    conn.close()
    print 'completed successfully'

OUTPUT:

<pyodbc.Connection object at 0x035434E8>
[]
completed successfully

I noticed the ODBC connection did not require any kind of port (not sure SQLite needs this, as it is not a server DB?), or a user / pwd, etc. I think I am just confused about the process, and that my setup is just wrong. But then I am puzzled why no errors from the code?

Answer 1

An SQLite database is a file, and is accessed as a file.

When you give the database name pigarage.db to the driver, it will open (or create) a file with that name. (Without a directory name, it will use whatever happens to be the current directory.)

To access a database on another machine, you need to use a network file system (see Samba), and ensure that it is correctly configured.