Connect to SQLite3 server using PyODBC, Python

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 ODBC driver for SQLite 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!

Advertisement

Answer