我想使用 Python 连接到在 Docker 容器中运行的 SQL Server 数据库。目前,我面临的问题是
错误:('01000',“[01000] [unixODBC][驱动程序管理器]无法打开 lib 'SQL Server': 找不到文件 (0) (SQLDriverConnect)”)
我已按照文档进行操作,并按照所述进行了所有操作。
我尝试使用以下连接字符串:
connection_string = 'DRIVER={SQL Server};SERVER=localhost;DATABASE=US_NATIONAL_COPY;UID=SA;PWD=<YourStrong!Passw0rd>'
conn = db.connect(connection_string)
这产生了上述结果。
应该注意的是,我的 docker 暴露在端口号 1401 上。
当我输入命令时;curl localhost:1401
,我得到以下结果:Empty reply from server
.
还应该指出的是,我能够执行进入 Docker 并运行它们的 SQL 查询。
我的完整代码是:
import pyodbc as db
import pandas
connection_string = 'DRIVER={SQL Server};SERVER=localhost;DATABASE=US_NATIONAL_COPY;UID=SA;PWD=<YourStrong!Passw0rd>'
conn = db.connect(connection_string)
cursor = conn.cursor()
stateQuery = 'select numeric_id, us_state_terr, abbreviation, is_state from states'
cursor.execute(stateQuery)
stateInfo = cursor.fetchall()
# declare a dictionary
stateIsState = {}
for thisState in stateInfo:
print (thisState[2])
stateIsState[thisState[2]] = thisState[3]
stateIsState[thisState[1]] = thisState[3]
datFrame = pandas.read_sql(stateQuery, conn)
for statename in datFrame.us_state_terr:
print (statename)
def is_it_a_state(stateabbrv):
if stateabbrv in stateIsState:
if stateIsState[stateabbrv] == "State":
return ("yes, " + stateabbrv + " is a state")
else:
return ("no, " + stateabbrv + " is not a state")
else:
return (stateabbrv + " is not in the dictionary")
print (is_it_a_state('NY'))
print (is_it_a_state('MP'))
print (is_it_a_state('QQ'))
错误出在连接线本身。
Update
我尝试了以下方法this https://github.com/lionheart/django-pyodbc/wiki/Mac-setup-to-connect-to-a-MS-SQL-Server文章。有一个转移点:我在我的 Mac 中找不到 unixODBC 配置文件。但有一个目录,/usr/local/Cellar/unixodbc/<version>/
。注意:正如文档所述,必须有一个etc
目录,没有。因此,我创建了一个并在其中创建了两个文件:odbcinst.ini
and odbc.ini
。
前一个是:
[FreeTDS]
Description=FreeTDS Driver for Linux & MSSQL on Win32
Driver=/usr/local/lib/libtdsodbc.so
Setup=/usr/local/lib/libtdsodbc.so
UsageCount=1
后一种情况如下:
[localhost]
Description = Test to SQLServer
Driver = FreeTDS
Trace = Yes
TraceFile = /tmp/sql.log
Database = US_NATIONAL_COPY
Servername = localhost:1401
UserName = SA
Password = <YourStrong!Passw0rd>
Port = 1401
Protocol = 8.0
ReadOnly = No
RowVersioning = No
ShowSystemTables = No
ShowOidColumn = No
FakeOidIndex = No
但接下来,我的命令是:isql localhost SA '<YourStrong!Passw0rd>'
失败并出现错误:[ISQL]ERROR: Could not SQLConnect
我应该怎么做才能以这种方式连接?