Using Python to Connect Function Compute to SQL Server

Alibaba Cloud
5 min readJan 29, 2019

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.

Preparing a Test Environment

Use Docker to run SQL Server 2017 on a local Mac computer, initialize the table structure, create a test file named, and check whether the database is accessible.

$ docker pull$ docker run -e 'ACCEPT_EULA=Y' -e '' \
-p 1433:1433 --name sql1 \

Start SQL Server through port 1433 and set the password of the SA account to

$ brew tap microsoft/mssql-release
$ brew update
$ ACCEPT_EULA=y brew install --no-sandbox msodbcsql mssql-tools

Use Homebrew to install the MSSQL client SQLCMD.

$ sqlcmd -S localhost -U SA -P ''
2>SELECT Name from sys.Databases
(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)

Check that the data has been successfully inserted and exit.

Preparing a Test Function

import pymssqldef handler(event, context):
conn = pymssql.connect(

cursor = conn.cursor()
cursor.execute('SELECT * FROM inventory WHERE quantity > 152')

result = ''
for row in cursor:
result += 'row = %r\n' % (row,)
return result

Develop a test function named This function connects to SQL Server on the Mac host but not to the localhost because fc-docker runs the function within the container. Then, run a query statement to return the result.

Completing the Installationv — Latest Version of pymssql

Create an empty directory and place the file into it. Switch the current path of the command session to the directory where 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
  1. In this example, the simulated Python 3.6 runtime environment (aliyunfc/runtime-python3.6) provided by fc-docker is used.
  2. The first row is used to start a Docker container that never exits, while docker exec in the second row is used to install dependencies in the container. The last row is used to exit the container. Given that the local path $(pwd) is mounted to the /code directory of the container, after you exit the container, the content in the /code directory is retained on the current local path.
  3. pip installs the Wheel package to the /code directory with the -t parameter.
$ 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: 19

Run the preceding command. The installation result is returned. If you do not need to use earlier versions of pymssql, ignore the following sections.

Completing the Installation — Earlier Versions of pymssql

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.6docker 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/ ."
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
  1. The first row is used to start a container while the tenth row is used to stop and automatically delete the container.
  2. The second and third rows are used to install the runtime dependency libsybdb5 to a local directory.
  3. Link the DLL to the /code/lib directory that is already added to the environment variable path LD_LIBRARY_PATH by default.
  4. Install freetds-dev and cython to the system directory for pymssql compilation and installation. Both libraries do not have to be installed to a local directory because they are not required during pymssql runtime.
  5. Install pymssql 2.1.3. From pymssql 2.1.4 and later, the source code is no longer required for installation.
$ 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: 19

The 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.






Alibaba Cloud

Follow me to keep abreast with the latest technology news, industry insights, and developer trends. Alibaba Cloud website: