Connect to SQLite3 server using PyODBC, Python
Question: I'm trying to test a class that loads data from an SQL server given a query.
To do this, I was instructed to use `sqlite3`. Now, the problem is that while
the class manages to connect to the real database with ease, I'm struggling to
connect with the temporary `sqlite3` server that I create, as I cannot figure
out what the connection string should look like. I'm using `pyodbc` in the
class to connect with databases. So, has anyone got an idea on what the
connection string should look like?

The class looks as follows:

    
    
    import petl as etl
    import pyodbc
    class Loader:
      """
      This is a class from which one can load data from an SQL server.
      """
    
      def __init__(self, connection_string):
          """
          This is the initialization file, and it requires the connection_string.
    
          :param connection_string:
          :type connection_string: str
          :return:
          """
    
          self.connection = pyodbc.connect(connection_string)
    
      def loadFromSQL(self, query):
          """
          This function loads the data according to the query passed in query.
    
          :param query:
          :type query: str
          """
    
          self.originalTableETL = etl.fromdb(self.connection, query)
    
          self.originalTablePD = etl.todataframe(self.originalTableETL)
    

And the temporary `sqlite3` server is as follows

    
    
    import sqlite3 as lite
    con = lite.connect('test.db')
    with con:
      cur = con.cursor()
      cur.execute("DROP TABLE IF EXISTS test_table")
      cur.execute("CREATE TABLE test_table(col1 TEXT, col2 TEXT)")
      cur.execute("INSERT INTO test_table VALUES('Hello', 'world!')")
    

So, what I wish to input is something like

    
    
    tester = Loader('connection_string_goes_here')
    tester.loadFromSQL("SELECT * FROM test_table")
    

**EDIT**

Okay, I've scoured the web a bit and found that a possible connection string
is `"DRIVER={SQL
Server};SERVER=localhost;DATABASE=test.db;Trusted_connection=yes"`. However,
the connection times out after a while and returns the following error
message:

    
    
    pyodbc.Error: ('08001', '[08001] [Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not exist or access denied. (17) (SQLDriverConnect)')
    

Which I found strange as it's local and as I haven't specified any password.
I've also tried specifying the exact path name to no avail.

Best,

Victor


Answer: Solved the problem! Downloaded an ODCB driver for `sqlite3` from
<http://www.ch-werner.de/sqliteodbc/>, and defined the connection string such
as

    
    
    "DRIVER={SQLite3 ODBC Driver};SERVER=localhost;DATABASE=test.db;Trusted_connection=yes"
    

And it worked, hope this helps people!

