Skip to content

Commit 005073d

Browse files
jackwotherspoonjackwotherspoon
authored
chore: update SQL Server samples to use pytds (GoogleCloudPlatform#8344)
1 parent a014358 commit 005073d

3 files changed

Lines changed: 12 additions & 37 deletions

File tree

‎cloud-sql/sql-server/sqlalchemy/Dockerfile‎

Lines changed: 0 additions & 17 deletions
Original file line numberDiff line numberDiff line change
@@ -17,16 +17,6 @@
1717
FROM python:3.10-buster
1818

1919
RUN apt-get update
20-
RUN apt install unixodbc-dev -y
21-
22-
# Add SQL Server ODBC Driver 17 for Ubuntu 18.04
23-
RUN curl https://packages.microsoft.com/keys/microsoft.asc | apt-key add -
24-
RUN curl https://packages.microsoft.com/config/ubuntu/18.04/prod.list > /etc/apt/sources.list.d/mssql-release.list
25-
RUN apt-get update
26-
RUN ACCEPT_EULA=Y apt-get install -y --allow-unauthenticated msodbcsql17
27-
RUN ACCEPT_EULA=Y apt-get install -y --allow-unauthenticated mssql-tools
28-
RUN echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bash_profile
29-
RUN echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bashrc
3020

3121
# Copy application dependency manifests to the container image.
3222
# Copying this separately prevents re-running pip install on every code change.
@@ -45,13 +35,6 @@ COPY . ./
4535
# Copy any certificates if present.
4636
COPY ./certs /app/certs
4737

48-
# Use server certificate for encrypted connection.
49-
# Conditionally copy the server-ca.pem file to the container image if the file is present.
50-
COPY Dockerfile ./certs/server-ca.pem* /usr/local/share/ca-certificates/
51-
# Rename the copied server-ca.pem file if it exists.
52-
RUN mv /usr/local/share/ca-certificates/server-ca.pem /usr/local/share/ca-certificates/server-ca.crt; exit 0
53-
RUN update-ca-certificates
54-
5538
# Run the web service on container startup. Here we use the gunicorn
5639
# webserver, with one worker process and 8 threads.
5740
# For environments with multiple CPU cores, increase the number of workers

‎cloud-sql/sql-server/sqlalchemy/connect_tcp.py‎

Lines changed: 12 additions & 19 deletions
Original file line numberDiff line numberDiff line change
@@ -34,41 +34,34 @@ def connect_tcp_socket() -> sqlalchemy.engine.base.Engine:
3434
db_name = os.environ["DB_NAME"] # e.g. 'my-database'
3535
db_port = os.environ["DB_PORT"] # e.g. 1433
3636

37-
# [END cloud_sql_sqlserver_sqlalchemy_sslcerts]
38-
driver_name = "mssql+pytds"
39-
query = {
40-
"driver": "ODBC Driver 17 for SQL Server"
41-
}
4237
# [END cloud_sql_sqlserver_sqlalchemy_connect_tcp]
43-
# [START cloud_sql_sqlserver_sqlalchemy_sslcerts]
38+
# [START_EXCLUDE]
39+
connect_args = {}
40+
# [END_EXCLUDE]
4441
# For deployments that connect directly to a Cloud SQL instance without
4542
# using the Cloud SQL Proxy, configuring SSL certificates will ensure the
4643
# connection is encrypted.
4744
if os.environ.get("DB_ROOT_CERT"): # e.g. '/path/to/my/server-ca.pem'
48-
driver_name = "mssql+pyodbc"
49-
query = {
50-
"driver": "ODBC Driver 17 for SQL Server",
51-
"Encrypt": "yes",
52-
"Trusted_Connection": "no"
53-
}
54-
if os.environ.get("CLOUD_SQL_AUTH_PROXY_IP_ADDRESS_TYPE") == "PRIVATE":
55-
driver_name = "mssql+pyodbc"
56-
query = {
57-
"driver": "ODBC Driver 17 for SQL Server"
45+
connect_args = {
46+
"cafile" : os.environ["DB_ROOT_CERT"],
47+
"validate_host": False,
5848
}
49+
5950
# [START cloud_sql_sqlserver_sqlalchemy_connect_tcp]
6051
pool = sqlalchemy.create_engine(
6152
# Equivalent URL:
62-
# <driver_name>://<db_user>:<db_pass>@<db_host>:<db_port>/<db_name>?driver=ODBC+Driver+17+for+SQL+Server
53+
# mssql+pytds://<db_user>:<db_pass>@<db_host>:<db_port>/<db_name>
6354
sqlalchemy.engine.url.URL.create(
64-
drivername=driver_name,
55+
drivername="mssql+pytds",
6556
username=db_user,
6657
password=db_pass,
6758
database=db_name,
6859
host=db_host,
6960
port=db_port,
70-
query=query,
7161
),
62+
# [END cloud_sql_sqlserver_sqlalchemy_connect_tcp]
63+
connect_args=connect_args,
64+
# [START cloud_sql_sqlserver_sqlalchemy_connect_tcp]
7265
# [START_EXCLUDE]
7366
# [START cloud_sql_sqlserver_sqlalchemy_limit]
7467
# Pool size is the maximum number of permanent connections to keep.

‎cloud-sql/sql-server/sqlalchemy/requirements.txt‎

Lines changed: 0 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1,7 +1,6 @@
11
Flask==2.1.0
22
gunicorn==20.1.0
33
python-tds==1.11.0
4-
pyodbc==4.0.34
54
pyopenssl==22.0.0
65
SQLAlchemy==1.4.38
76
cloud-sql-python-connector==0.6.1

0 commit comments

Comments
 (0)