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

Can you post more details about the sqlite server you created? And the existing code you have so far? Also numerous examples of what the strings look like can be found here:code.google.com/p/pyodbc/wiki/GettingStarted — AZhao
– AZhao, Commented Jul 1, 2015 at 14:48

Gord Thompson · Accepted Answer · 2020-12-12 00:53:47Z

20

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!

edited Dec 12, 2020 at 0:53

Gord Thompson

125k38 gold badges251 silver badges458 bronze badges

answered Jul 2, 2015 at 10:12

Tingiskhan

1,3453 gold badges17 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Connect to SQLite3 server using PyODBC, Python

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related