如何使用Python3连接到在Mac上的docker中运行的SQL Server? [英] How do I connect to SQL Server running inside docker on mac using Python3?

问题描述

我想使用Python连接到在Docker容器中运行的SQL Server数据库.目前，我面临着

I want to connect to my SQL Server database running in a Docker container using Python. Currently, I am facing issues with

错误:("01000"，"[01000] [unixODBC] [驱动程序管理器]无法打开lib'SQL Server':找不到文件(0)(SQLDriverConnect))

Error: ('01000', "[01000] [unixODBC][Driver Manager]Can't open lib 'SQL Server' : file not found (0) (SQLDriverConnect)")

我已经按照文档进行了操作，如上所述.

and I have followed the documentation, did everything as mentioned.

我尝试使用以下连接字符串:

I tried using the following connection string:

connection_string = 'DRIVER={SQL Server};SERVER=localhost;DATABASE=US_NATIONAL_COPY;UID=SA;PWD=<YourStrong!Passw0rd>'
conn = db.connect(connection_string)

产生了上述结果.

应该注意，我的泊坞窗暴露在端口号1401上.

It should be noted that my docker exposes on a port number 1401.

当我键入命令时； curl localhost:1401 ，我得到以下结果:服务器的空回复.

When I type the command; curl localhost:1401, I get the following result: Empty reply from server.

还应注意，我能够执行进入Docker并运行它们的SQL查询.

It should also be noted that I am able to execute SQL queries entering the Docker and running them.

我的完整代码是:

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'))

错误在于连接线本身.

我尝试了以下

I tried the following this article. There was a point of diversion: I could not find the unixODBC config files in my Mac. But there was a directory, /usr/local/Cellar/unixodbc/<version>/. Note: as the documentation states that there must be an etc directory, there wasn't. So, I created one and created two files inside it: odbcinst.ini and odbc.ini. The former one is:

[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:无法使用SQLConnect 我应该怎么做才能以这种方式建立联系?

But then, my command: isql localhost SA '<YourStrong!Passw0rd>' failed with the error: [ISQL]ERROR: Could not SQLConnect What should I do to get connected in this manner?

推荐答案

Microsoft为SQL Server提供了与unixODBC兼容的驱动程序，可从 Homebrew获得..完整参考是

Microsoft provides a unixODBC compatible driver for SQL Server, available from Homebrew. The full reference is here.

如果您使用的是自制软件，则可以:

If you're using Homebrew, you can:

1. 安装unixodbc

brew install unixodbc

1. 进入Microsoft存储库

brew tap microsoft/mssql-release https://github.com/Microsoft/homebrew-mssql-release

1. 更新并安装

brew update
brew install msodbcsql17 mssql-tools

这可能会在您的/usr/local/etc/odbcinst.ini 文件中正确安装unixodbc驱动程序(应该，但是对我来说是50/50).如果没有，则可以将驱动程序配置从msodbcsql17目录(/usr/local/Cellar/msodbcsql17/17.4.1.1/odbcinst.ini )复制到 odbcinst.ini

That might properly install the unixodbc driver in your /usr/local/etc/odbcinst.ini file (it should, but it's been 50/50 for me). If it didn't, you can copy the driver config from the msodbcsql17 dir (/usr/local/Cellar/msodbcsql17/17.4.1.1/odbcinst.ini) into your odbcinst.ini

最终，您需要在/usr/local/etc/odbcinst.ini 文件中添加一个节，如下所示:

Ultimately, you need a stanza in your /usr/local/etc/odbcinst.ini file that looks like:

[ODBC Driver 17 for SQL Server]
Description=Microsoft ODBC Driver 17 for SQL Server
Driver=/usr/local/lib/libmsodbcsql.17.dylib

您的python连接字符串看起来像

And your python connection string will look like

connection_string = 'DRIVER={ODBC Driver 17 for SQL Server};SERVER=localhost;DATABASE=US_NATIONAL_COPY;UID=SA;PWD=<YourStrong!Passw0rd>'

这篇关于如何使用Python3连接到在Mac上的docker中运行的SQL Server?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！