sqlalchemy连接字符串在非服务器机器上不起作用

发布于 2025-01-26 07:26:55 字数 1094 浏览 1 评论 0原文

我正在使用在前提服务器上运行的MSSQL数据库。要从Python访问此数据库，请使用SQLalchemy（请参见下面的代码）。当我直接从服务器调用下面的代码时，它运行良好，但是如果我将代码移至本地计算机，则会引发错误。

该服务器可从我的本地计算机触及。（如果我调用ping {host}我会得到有效的响应，并且与dbeaver连接到同一数据库）。我的第一个怀疑是，某些库的不同版本是我更新 pandas ， pyodbc 和 sqlalchemy 的原因均已。

据我了解错误消息，这表明连接字符串是错误的（如果在服务器上工作的几乎不可能？我无法弄清楚这是什么。我想念什么？有人知道问题是什么吗？

ERRORMESSAGE（对不起，最后一部分是德语，但基本上翻译为“找不到数据库元素，并且没有定义默认驱动程序”）：

InterfaceError: (pyodbc.InterfaceError) ('IM002', '[IM002] [Microsoft][ODBC Driver Manager] Der Datenquellenname wurde nicht gefunden, und es wurde kein Standardtreiber angegeben (0) (SQLDriverConnect)')
(Background on this error at: https://sqlalche.me/e/14/rvf5)

请注意，我替换了所有用于此问题的变量。

import pandas as pd
import sqlalchemy
query = "SELECT TOP(10)* From {table} "
engine = sqlalchemy.create_engine(f'mssql+pyodbc://{USER}:{PASSWORD}@{HOSTNAME}:1433/{DATABASENAME}?driver=SQL+Server+Native+Client+11.0', echo=True)
with engine.connect() as con:
    SQl_result_df = pd.read_sql(query, con, coerce_float=False)

原文

I am working with an MSSQL database which runs on an On premise server. To access this database from python I use SQLalchemy (see code below). When I call the code below directly from the server it works great, but if I move the code to my local machine it throws an error.

The server is reachable from my local machine. (If I call ping {Host} I get a valid response and also I am connected to the same database with DBeaver).
As my first suspect was that different versions of some libraries are the reason I updated pandas, pyodbc and sqlalchemy all to the newest version on both machines.

As I understand the error message, it suggests that the connection string is wrong (which can hardly be if it works on the server?) My second suspect is that I need to install some thing on my local machine for the driver to work, but I cannot quite figure out what this would be.
What am I missing? Does anyone know what the problem is?

ErrorMessage (Sorry that the last part is in German but it basically translates to "datasourcename could not be found and no default driver was defined"):

InterfaceError: (pyodbc.InterfaceError) ('IM002', '[IM002] [Microsoft][ODBC Driver Manager] Der Datenquellenname wurde nicht gefunden, und es wurde kein Standardtreiber angegeben (0) (SQLDriverConnect)')
(Background on this error at: https://sqlalche.me/e/14/rvf5)

Note that I have replaced all variables which are written in {} for this question.

import pandas as pd
import sqlalchemy
query = "SELECT TOP(10)* From {table} "
engine = sqlalchemy.create_engine(f'mssql+pyodbc://{USER}:{PASSWORD}@{HOSTNAME}:1433/{DATABASENAME}?driver=SQL+Server+Native+Client+11.0', echo=True)
with engine.connect() as con:
    SQl_result_df = pd.read_sql(query, con, coerce_float=False)

分享到QQ

分享到微博