Normally, a third-party module is required for establishing Python-based database connections. To connect to Microsoft SQL Server, pymssql is required. FreeTDS is required for pymssql versions earlier than 2.1.3 because pymssql depends on FreeTDS. Earlier versions of pymssql only support the Wheel packaging mode for Windows. To install pymssql on other operating systems such as Linux, you must first install the freetds-dev package to provide the required header file for compiling pymssql from source code.
When Alibaba Cloud Function Compute is running, its runtime directories are read-only. In cases where apt-get and pip are required for dependency installation, you must install dependencies to the code but not the system directory. For more information, see the Installing a Dependency Library for Function Compute. Earlier versions of pymssql must be compiled before installation. Therefore, this method is more complicated than the binary package installation method in which pymssql is installed to a local directory.
A simulated Linux environment is required to install Function Compute dependencies. Previously, fcli shell sbox was recommended to install the dependencies in a Docker container, which resembles the actual production environment. Some dependencies can only run on certain systems. For example, Dynamic Link Libraries (DDLs) installed on Mac systems are unavailable on Linux. pymssql is also the case. This document explains how to use fc-docker for installation and local testing.
In the following example, the Function Compute runtime environment is Python 3.6 and the test is also applicable to Python 2.7.
Use Docker to run SQL Server 2017 on a local Mac computer, initialize the table structure, create a test file named index.py, and check whether the database is accessible.
$ docker pull mcr.microsoft.com/mssql/server:2017-latest
$ docker run -e 'ACCEPT_EULA=Y' -e 'SA_PASSWORD=Codelife.me' \
-p 1433:1433 --name sql1 \
-d mcr.microsoft.com/mssql/server:2017-latestStart SQL Server through port 1433 and set the password of the SA account to Codelife.me.
$ brew tap microsoft/mssql-release https://github.com/Microsoft/homebrew-mssql-release
$ brew update
$ ACCEPT_EULA=y brew install --no-sandbox msodbcsql mssql-toolsUse Homebrew to install the MSSQL client SQLCMD.
$ sqlcmd -S localhost -U SA -P 'Codelife.me'
1>CREATE DATABASE TestDB
2>SELECT Name from sys.Databases
3>GO
Name
-----------------------------------------------
master
tempdb
model
msdb
TestDB
(5 rows affected)Create a test database named TestDB.
1> USE TestDB
2> CREATE TABLE Inventory (id INT, name NVARCHAR(50), quantity INT)
3> INSERT INTO Inventory VALUES (1, 'banana', 150); INSERT INTO Inventory VALUES (2, 'orange', 154);
4> GO
Changed database context to 'TestDB'.
(1 rows affected)
(1 rows affected)Create a table named Inventory and insert a row of test data.
1> SELECT * FROM Inventory WHERE quantity > 152;
2> GO
id name quantity
----------- -------------------------------------------------- -----------
2 orange 154
(1 rows affected)
1> QUITCheck that the data has been successfully inserted and exit.
import pymssql
def handler(event, context):
conn = pymssql.connect(
host=r'docker.for.mac.host.internal',
user=r'SA',
password=r'Codelife.me',
database='TestDB'
)
cursor = conn.cursor()
cursor.execute('SELECT * FROM inventory WHERE quantity > 152')
result = ''
for row in cursor:
result += 'row = %r\n' % (row,)
conn.close()
return resultDevelop a test function named index.py. This function connects to SQL Server on the Mac host docker.for.mac.host.internal but not to the localhost because fc-docker runs the function within the container. Then, run a query statement to return the result.
Create an empty directory and place the index.py file into it. Switch the current path of the command session to the directory where index.py is located. Then, run the following command:
$ docker run --rm --name mssql-builder -t -d -v $(pwd):/code --entrypoint /bin/sh aliyunfc/runtime-python3.6
$ docker exec -t mssql-builder pip install -t /code pymssql
$ docker stop mssql-builder$ docker run --rm -v $(pwd):/code aliyunfc/runtime-python3.6 --handler index.handler
row = (2, 'orange', 154)
RequestId: d66496e9-4056-492b-98d9-5bf51e448174 Billed Duration: 144 ms Memory Size: 19Run the preceding command. The installation result is returned. If you do not need to use earlier versions of pymssql, ignore the following sections.
For pymssql versions earlier than 2.1.3, running the pip install command compiles pymssql and installs it from the source code. In this case, install the compilation dependency freetds-dev and the runtime dependency libsybdb5. The compilation dependency can be directly installed to the system directory while the running dependency must be installed to a local directory.
docker run --rm --name mssql-builder -t -d -v $(pwd):/code --entrypoint /bin/sh aliyunfc/runtime-python3.6
docker exec -t mssql-builder apt-get install -y -d -o=dir::cache=/code libsybdb5
docker exec -t mssql-builder bash -c 'for f in $(ls /code/archives/*.deb); do dpkg -x $f $(pwd) ; done;'
docker exec -t mssql-builder bash -c "rm -rf /code/archives/; mkdir /code/lib;cd /code/lib; ln -sf ../usr/lib/x86_64-linux-gnu/libsybdb.so.5 ."
docker exec -t mssql-builder apt-get install -y freetds-dev
docker exec -t mssql-builder pip install cython
docker exec -t mssql-builder pip install -t /code pymssql==2.1.3
docker stop mssql-builder$ docker run --rm -v $(pwd):/code aliyunfc/runtime-python3.6 --handler index.handler
row = (2, 'orange', 154)
RequestId: d66496e9-4056-492b-98d9-5bf51e448174 Billed Duration: 144 ms Memory Size: 19The test is passed.
This document explains how to use SQL Server databases with Alibaba Cloud Function Compute. The source code is no longer required for installing the latest version of pymssql. However, the method of using pip to install pymssql from the source code package is applicable to similar scenarios.
This document also shows how to configure and verify Function Compute based on fc-docker. Different fcli sbox and fc-docker files can be developed as scripts for repeated execution and fc-docker can be executed in the simulated local runtime environment. Both features are very useful in continuous integration (CI) scenarios.
Alex - September 18, 2018
Alibaba Clouder - April 25, 2021
Alex - September 18, 2018
Alibaba Cloud MaxCompute - April 26, 2020
GXIC - June 11, 2019
Sabith - August 8, 2018
ApsaraDB RDS for SQL Server
An on-demand database hosting service for SQL Server with automated monitoring, backup and disaster recovery capabilities
Learn More
Serverless Workflow
Visualization, O&M-free orchestration, and Coordination of Stateful Application Scenarios
Learn More
Serverless Application Engine
Serverless Application Engine (SAE) is the world's first application-oriented serverless PaaS, providing a cost-effective and highly efficient one-stop application hosting solution.
Learn More
ACK One
Provides a control plane to allow users to manage Kubernetes clusters that run based on different infrastructure resources
Learn MoreMore Posts by vangie
