Могу ли я работать как с локальными, так и с ODBC-связанными таблицами в базе данных Access из Python?

Как pypyodbc может подключиться к связанным таблицам в .база данных accdb? Возможно ли это вообще, или это ограничение pyodbc?

Мне нужно получить данные от MS Acess .база данных accdb в Python. Это работает отлично, и я могу использовать pypyodbc для доступа к таблицам и запросам, определенным внутри .база данных accdb. Однако база данных также содержит таблицы, связанные с внешним SQL-сервером. При обращении к таким связанным таблицам pypyodbc жалуется, что не может подключиться к SQL сервер.

test.accdb содержит две таблицы: Test (локальная таблица) и cidb_ain (связанная SQL-таблица)

Следующий код Python 3-это моя попытка получить доступ к данным: 

import pypyodbc as pyodbc

cnxn = pyodbc.connect(driver='Microsoft Access Driver (*.mdb, *.accdb)',
                      dbq='test.accdb',
                      readonly=True)

cursor = cnxn.cursor()

# access to the local table works
for row in cursor.execute("select * from Test"):
    print(row)

print('----')

# access to the linked table fails
for row in cursor.execute("select * from cidb_ain"):
    print(row)

Вывод: 

(1, 'eins', 1)
(2, 'zwei', 2)
(3, 'drei', 3)
----
Traceback (most recent call last):
  File "test_02_accdb.py", line 14, in <module>
    for row in cursor.execute("select * from cidb_ain"):
  File "C:softwareinstalledminiconda3libsite-packagespypyodbc.py", line 1605, in execute
    self.execdirect(query_string)
  File "C:softwareinstalledminiconda3libsite-packagespypyodbc.py", line 1631, in execdirect
    check_success(self, ret)
  File "C:softwareinstalledminiconda3libsite-packagespypyodbc.py", line 986, in check_success
    ctrl_err(SQL_HANDLE_STMT, ODBC_obj.stmt_h, ret, ODBC_obj.ansi)
  File "C:softwareinstalledminiconda3libsite-packagespypyodbc.py", line 964, in ctrl_err
    raise Error(state,err_text)
pypyodbc.Error: ('HY000', "[HY000] [Microsoft][ODBC-Treiber für Microsoft Access] ODBC-Verbindung zu 'SQL Server Native Client 11.0SQLHOST' fehlgeschlagen.")

Сообщение об ошибке примерно переводится как " соединение ODBC с 'SQL Server Native Client 11.0 SQLHOST' не удалось".

Я не могу получить доступ к SQL-серверу через .база данных accdb с pypyodbc, но запрос таблицы cidb_ain из MS Access возможен. Более того, я могу подключение к SQL-серверу напрямую: 

cnxn = pyodbc.connect(driver='SQL Server Native Client 11.0',
                      server='SQLHOST',
                      trusted_connection='yes',
                      database='stuffdb')

Учитывая, что (1) MS Access (и Matlab тоже) может использовать информацию, содержащуюся в .accdb файл для запроса связанных таблиц, и (2) SQL Server доступен, я предполагаю, что проблема связана с pypyodbc. (То, как имя драйвера и имя хоста искажаются в 'SQL Server Native Client 11.0SQLHOST' в сообщении об ошибке, тоже кажется несколько подозрительным.)

У меня нет предыдущего опыта работы с доступом, поэтому, пожалуйста, наберитесь терпения и дайте мне знать, если я упустил важное информация, которая казалась мне ненужной...
2 2
sql-server ms-access odbc

2 ответа:

Обновление:

Оказывается, что решение этой задачи так же просто, как установка pyodbc.pooling = False перед установлением соединения с базой данных Access: 

import pyodbc
# ... also works with `import pypyodbc as pyodbc`, too
pyodbc.pooling = False  # this prevents the error
cnxn = pyodbc.connect(r"DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};DBQ= ... ")

(предыдущий ответ)

похоже, что ни pypyodbc, ни pyodbc не могут прочитать связанную таблицу SQL Server из базы данных Access. Однако, System.Data.Odbc в .NET это можно сделать, так чтоIronPython тоже может.

Для проверки я создал таблицу с именем [Foods] в SQL Сервер 

id  guestId  food
--  -------  ----
 1        1  pie
 2        2  soup

Я создал связанную таблицу ODBC с именем [dbo_Foods] в Access, которая указывала на эту таблицу на SQL Server.

Я также создал локальную таблицу доступа с именем [Guests]...

id  firstName
--  ---------
 1  Gord
 2  Jenn

... и сохраненный запрос доступа с именем [qryGuestPreferences]...

SELECT Guests.firstName, dbo_Foods.food
FROM Guests INNER JOIN dbo_Foods ON Guests.id = dbo_Foods.guestId;

Запуск следующего скрипта в IronPython ...

import clr
import System
clr.AddReference("System.Data")
from System.Data.Odbc import OdbcConnection, OdbcCommand

connectString = (
    r"DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};"
    r"DBQ=C:\Users\Public\Database1.accdb;"
)
conn = OdbcConnection(connectString)
conn.Open()

query = """\
SELECT firstName, food 
FROM qryGuestPreferences
"""
cmd = OdbcCommand(query, conn)
rdr = cmd.ExecuteReader()
while rdr.Read():
    print("{0} likes {1}.".format(rdr["firstName"], rdr["food"]))
conn.Close()

... возвращает 

Gord likes pie.
Jenn likes soup.
1

Во-первых, MS Access-это уникальный тип приложения для баз данных, который несколько отличается от других RDMS (например, SQLite, MySQL, PostgreSQL, Oracle, DB2) , поскольку он поставляется с бэкэндом по умолчанию Jet / ACE SQL реляционный движок (который, кстати, является не компонентом с ограниченным доступом, а общей технологией Microsoft) и интерфейсный графический интерфейс и генератор отчетов. По сути, доступ - это набор объектов.

Связанные таблицы являются в некотором роде особенностью переднего плана MS Access используется для замены базы данных Jet/ACE по умолчанию (т. е. локальных таблиц) на другую серверную базу данных, специально для вас SQL Server. Кроме того, связанные таблицы сами являются соединениями ODBC/OLEDB! Вы должны были использовать DSN, драйвер или провайдер, чтобы даже установить и создать связанные таблицы в файле MS Access.

Следовательно, любой внешний клиент, здесь являющийся вашим скриптом Python, который подключается к базе данных MS Access [driver='Microsoft Access Driver (*.mdb, *.accdb)], фактически подключается к база данных backend Jet/ACE. Клиент / скрипт никогда не взаимодействует с объектами frontend. В вашей ошибке Python считывает соединение ODBC связанной таблицы, и поскольку драйвер/поставщик SQL Server [SQL Server Native Client 11.0SQLHOST] никогда не вызывается в сценарии, сценарий завершается ошибкой.

В целом, чтобы разрешить вашу ситуацию, вы должны подключить Python непосредственно к базе данных SQL Server (и не использовать MS Access в качестве носителя) для подключения к любым локальным таблицам там, здесь cidb_ain. Просто используйте строку подключения доступа связанная таблица: 

#(USING DSN)
db = pypyodbc.connect('DSN=dsn name;')

cur = db.cursor()
cur.execute("SELECT * FROM dbo.cidb_ain")

for row in cur.fetchall()
  print(row)

cur.close()
db.close()


# (USING DRIVER)
constr = 'Trusted_Connection=yes;DRIVER={SQL Server};SERVER=servername;' \
         'DATABASE=database name;UID=username;PWD=password'
db = pypyodbc.connect(constr)

cur = db.cursor()
cur.execute("SELECT * FROM dbo.cidb_ain")

for row in cur.fetchall()
  print(row)

cur.close()
db.close()
1