使用PyODBC、Python连接到SQLite3服务器

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?

我正在测试一个类，该类从给定查询的SQL服务器上加载数据。为了做到这一点，我被指示使用sqlite3。现在的问题是，虽然类可以轻松地连接到真正的数据库，但我还是很难连接到我创建的临时sqlite3服务器，因为我不知道连接字符串应该是什么样子。我在类中使用pyodbc连接数据库。有人知道连接字符串应该是什么样子吗?

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

临时sqlite3服务器如下所示

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:

好的，我搜索了一下web，发现一个可能的连接字符串是“DRIVER={SQL Server}; Server =localhost;DATABASE=test.db;Trusted_connection=yes”。但是，连接过一段时间会超时，并返回以下错误消息:

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

维克多

1 个解决方案

#1

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

解决了这个问题!从http://www.ch- werner.de/sqliteodb/下载了一个用于sqlite3的ODCB驱动程序，并定义了如下的连接字符串

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

And it worked, hope this helps people!

它成功了，希望这能帮助人们!

#1