Using Python to Connect Function Compute to SQL Server

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 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-latest

Start 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-tools

Use 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> QUIT

Check that the data has been successfully inserted and exit.

Preparing a Test Function

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 result

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

Completing the Installationv - Latest Version of pymssql

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

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

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 libsybdb.so.5 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.

Conclusion

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.

References

1. http://www.pymssql.org/en/latest/intro.html#install
2. http://www.freetds.org/
3. http://www.pymssql.org/en/stable/pymssql_examples.html
4. https://docs.microsoft.com/en-us/sql/linux/quickstart-install-connect-docker?view=sql-server-2017
5. https://cloudblogs.microsoft.com/sqlserver/2017/05/16/sql-server-command-line-tools-for-macos-released/