fix: use pytds driver in SQL Server sample (GoogleCloudPlatform#5292)

shubha-rajan · web-flow · commit efc51a4f0f46 · 2021-02-26T14:38:38.000-05:00
* fix: use pytds driver in SQL Server sample

* fix tests

* add license header to test file

* add pyopenssl to requirements.txt

* remove query string
diff --git a/cloud-sql/sql-server/sqlalchemy/Dockerfile b/cloud-sql/sql-server/sqlalchemy/Dockerfile
@@ -16,11 +16,6 @@
 # https://hub.docker.com/_/python
 FROM python:3.9-buster
 
-#Download the desired package(s) for Microsoft ODBC 17 Driver
-RUN (curl https://packages.microsoft.com/keys/microsoft.asc | apt-key add -) && \
-        curl https://packages.microsoft.com/config/debian/10/prod.list > /etc/apt/sources.list.d/mssql-release.list && \
-        apt-get update && ACCEPT_EULA=Y apt-get install -y msodbcsql17  unixodbc-dev
-
 # Copy application dependency manifests to the container image.
 # Copying this separately prevents re-running pip install on every code change.
 COPY requirements.txt ./
diff --git a/cloud-sql/sql-server/sqlalchemy/README.md b/cloud-sql/sql-server/sqlalchemy/README.md
@@ -25,7 +25,7 @@
 
     3.  Click **CREATE**.
 
-1. Install the version of [Microsoft ODBC 17 Driver for SQL Server](https://docs.microsoft.com/en-us/sql/connect/odbc/download-odbc-driver-for-sql-server?view=sql-server-ver15_) for your operating system.
+
 
 1. Create a service account with the 'Cloud SQL Client' permissions by following these 
 [instructions](https://cloud.google.com/sql/docs/postgres/connect-external-app#4_if_required_by_your_authentication_method_create_a_service_account).
diff --git a/cloud-sql/sql-server/sqlalchemy/connection_test.py b/cloud-sql/sql-server/sqlalchemy/connection_test.py
@@ -1,9 +1,22 @@
+# Copyright 2020 Google LLC
+#
+# Licensed under the Apache License, Version 2.0 (the "License");
+# you may not use this file except in compliance with the License.
+# You may obtain a copy of the License at
+#
+#     http://www.apache.org/licenses/LICENSE-2.0
+#
+# Unless required by applicable law or agreed to in writing, software
+# distributed under the License is distributed on an "AS IS" BASIS,
+# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+# See the License for the specific language governing permissions and
+# limitations under the License.
 from contextlib import contextmanager
 import logging
 import os
 from typing import Dict
 
-import pyodbc
+import pytds
 import pytest
 
 import main
@@ -45,7 +58,7 @@ def tcp_db_connection():
 def _common_setup():
     try:
         pool = main.init_connection_engine()
-    except pyodbc.OperationalError as e:
+    except pytds.OperationalError as e:
         logger.warning(
             'Could not connect to the production database. '
             'If running tests locally, is the cloud_sql_proxy currently running?'
diff --git a/cloud-sql/sql-server/sqlalchemy/main.py b/cloud-sql/sql-server/sqlalchemy/main.py
@@ -92,15 +92,14 @@ def init_tcp_connection_engine(db_config):
     # managing a pool of connections to your database
     pool = sqlalchemy.create_engine(
         # Equivalent URL:
-        # mssql+pyodbc://<db_user>:<db_pass>@/<host>:<port>/<db_name>?driver=ODBC+Driver+17+for+SQL+Server
+        # mssql+pytds://<db_user>:<db_pass>@/<host>:<port>/<db_name>?driver=ODBC+Driver+17+for+SQL+Server
         sqlalchemy.engine.url.URL(
-            "mssql+pyodbc",
+            "mssql+pytds",
             username=db_user,
             password=db_pass,
             database=db_name,
             host=db_hostname,
             port=db_port,
-            query={"driver": "ODBC Driver 17 for SQL Server"},
         ),
         **db_config
     )
diff --git a/cloud-sql/sql-server/sqlalchemy/requirements.txt b/cloud-sql/sql-server/sqlalchemy/requirements.txt
@@ -1,4 +1,7 @@
 Flask==1.1.2
-SQLAlchemy==1.3.23
-pyodbc==4.0.30
+SQLAlchemy==1.3.22
+python-tds==1.10.0
+sqlalchemy-pytds==0.3.1
+pyopenssl==20.0.1
+
 
