时间戳

首页 > 解决方案 > 如何连接到本地运行的 MSSQL？

python - 如何连接到本地运行的 MSSQL？

问题描述

我不断得到

sqlalchemy.exc.OperationalError: (pyodbc.OperationalError) ('HYT00', '[HYT00] [Microsoft][ODBC Driver 17 for SQL Server]Login timeout expired (0) (SQLDriverConnect)')
(Background on this error at: http://sqlalche.me/e/13/e3q8)

当我尝试将 SQLAlchemy 连接到本地运行的 MSSQL 时。我究竟做错了什么？

我只想将此设置用于测试目的。

我做了什么

Dockerfile：

FROM mcr.microsoft.com/mssql/server:2019-latest
USER root
RUN apt-get update

# pyenv
RUN apt-get -y install gcc git curl make build-essential libssl-dev zlib1g-dev libbz2-dev libreadline-dev libsqlite3-dev wget curl llvm libncurses5-dev xz-utils tk-dev libxml2-dev libxmlsec1-dev libffi-dev liblzma-dev
ENV HOME "/home"
ENV PYENV_ROOT="/opt/pyenv"
ENV PATH="/opt/pyenv/shims:/opt/pyenv/bin:$PATH"
RUN git clone https://github.com/pyenv/pyenv.git $PYENV_ROOT
RUN pyenv install 3.7.8
RUN pyenv global 3.7.8

# MSSQL
ENV ACCEPT_EULA "Y"
ENV SA_PASSWORD "Devel0per"
ENV SQLALCHEMY_DATABASE_URI "mssql+pyodbc://sa:Devel0per@localhost:5432/main?driver=ODBC+Driver+17+for+SQL+Server"
RUN apt-get -y install g++ unixodbc-dev sudo

我跑：

$ sudo docker build . -t test
$ sudo docker run -it --entrypoint "/bin/bash" test

# Start the MSSQL server
$ setsid /opt/mssql/bin/sqlservr

# Check if its running and create a schema
$ /opt/mssql-tools/bin/sqlcmd -U sa -P Devel0per
1> CREATE DATABASE main;
2> GO
2020-09-25 05:54:06.71 spid51      [5]. Feature Status: PVS: 0. CTR: 0. ConcurrentPFSUpdate: 1.
2020-09-25 05:54:06.72 spid51      Starting up database 'main'.
2020-09-25 05:54:06.82 spid51      Parallel redo is started for database 'main' with worker pool size [4].
2020-09-25 05:54:06.84 spid51      Parallel redo is shutdown for database 'main' with worker pool size [4].

1> USE master;
2> GO
Changed database context to 'master'.

# I'm super uncertain if that actually worked
1> CREATE SCHEMA local;
2> GO
1> exit

1> USE master
2> GO
Changed database context to 'master'.
1> xp_readerrorlog 0, 1, N'Server is listening on'
2> GO
2020-09-25 06:17:54.23 spid58      Attempting to load library 'xpstar.dll' into memory. This is an informational message only. No user action is required.
2020-09-25 06:17:54.29 spid58      Using 'xpstar.dll' version '2019.150.4063' to execute extended stored procedure 'xp_readerrorlog'. This is an informational message only; no user action is required.
LogDate                 ProcessInfo  Text
2020-09-25 05:51:18.290 spid25s      Server is listening on [ 'any' <ipv6> 1433].
2020-09-25 05:51:18.300 spid25s      Server is listening on [ 'any' <ipv4> 1433].
2020-09-25 05:51:18.310 Server       Server is listening on [ ::1 <ipv6> 1434].
2020-09-25 05:51:18.310 Server       Server is listening on [ 127.0.0.1 <ipv4> 1434].
2020-09-25 05:51:18.330 spid25s      Server is listening on [ ::1 <ipv6> 1431].
2020-09-25 05:51:18.340 spid25s      Server is listening on [ 127.0.0.1 <ipv4> 1431].
(6 rows affected)

$ pip install sqlalchemy pyodbc
$ python
>>> import sqlalchemy as sa
>>> engine = sa.create_engine("mssql+pyodbc://sa:Devel0per@localhost:5432/main?driver=ODBC+Driver+17+for+SQL+Server")
>>> conn = engine.connect()
[...]
sqlalchemy.exc.OperationalError: (pyodbc.OperationalError) ('HYT00', '[HYT00] [Microsoft][ODBC Driver 17 for SQL Server]Login timeout expired (0) (SQLDriverConnect)')
(Background on this error at: http://sqlalche.me/e/13/e3q8)

>>> engine = sa.create_engine("mssql+pyodbc://sa:Devel0per@localhost:1434/main?driver=ODBC+Driver+17+for+SQL+Server")
>>> conn = engine.connect()
[...]
sqlalchemy.exc.OperationalError: (pyodbc.OperationalError) ('HYT00', '[HYT00] [Microsoft][ODBC Driver 17 for SQL Server]Login timeout expired (0) (SQLDriverConnect)')
(Background on this error at: http://sqlalche.me/e/13/e3q8)

$ tail /var/opt/mssql/log/errorlog
2020-09-25 05:51:19.14 spid30s     Service Broker manager has started.
2020-09-25 05:51:19.15 spid9s      Database 'msdb' running the upgrade step from version 902 to version 903.
2020-09-25 05:51:19.17 spid9s      Database 'msdb' running the upgrade step from version 903 to version 904.
2020-09-25 05:51:19.30 spid9s      Recovery is complete. This is an informational message only. No user action is required.
2020-09-25 05:51:19.31 spid26s     The default language (LCID 0) has been set for engine and full-text services.
2020-09-25 05:51:20.18 spid26s     The tempdb database has 8 data file(s).
2020-09-25 05:54:06.71 spid51      [5]. Feature Status: PVS: 0. CTR: 0. ConcurrentPFSUpdate: 1.
2020-09-25 05:54:06.72 spid51      Starting up database 'main'.
2020-09-25 05:54:06.82 spid51      Parallel redo is started for database 'main' with worker pool size [4].
2020-09-25 05:54:06.84 spid51      Parallel redo is shutdown for database 'main' with worker pool size [4].

标签： pythonsql-serverdockersqlalchemy

解决方案

显然我只是使用了错误的端口：

import sqlalchemy as sa

driver = "ODBC+Driver+17+for+SQL+Server"
s = f"mssql+pyodbc://sa:Devel0per@localhost:1433/master?driver={driver}"
engine = sa.create_engine(s)
conn = engine.connect()

推荐阅读