使用 PyODBC、Python 连接到 SQLite3 服务器 [英] Connect to SQLite3 server using PyODBC, Python

问题描述

我正在尝试测试一个类，该类从给定查询的 SQL 服务器加载数据.为此，我被指示使用 sqlite3.现在，问题是虽然该类设法轻松连接到真实数据库，但我正在努力连接我创建的临时 sqlite3 服务器，因为我无法弄清楚连接字符串是什么应该看起来像.我在课程中使用 pyodbc 来连接数据库.那么，有人知道连接字符串应该是什么样子吗?

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?

类如下所示:

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)

而临时的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")

编辑

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

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.

最好，

维克多

推荐答案

解决了问题！从 http://www.ch-werner.de/sqliteodbc/下载了 SQLite 的 ODBC 驱动程序a>，并定义了连接字符串如

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!

这篇关于使用 PyODBC、Python 连接到 SQLite3 服务器的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！