Using Python to Connect Function Compute to SQL Server-阿里云开发者社区

开发者社区> 倚贤> 正文

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.
+关注继续查看

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/

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
使用NAT网关轻松为单台云服务器设置多个公网IP
在应用中,有时会遇到用户询问如何使单台云服务器具备多个公网IP的问题。 具体如何操作呢,有了NAT网关这个也不是难题。
22561 0
阿里云服务器ECS远程登录用户名密码查询方法
阿里云服务器ECS远程连接登录输入用户名和密码,阿里云没有默认密码,如果购买时没设置需要先重置实例密码,Windows用户名是administrator,Linux账号是root,阿小云来详细说下阿里云服务器远程登录连接用户名和密码查询方法
2207 0
阿里云服务器端口号设置
阿里云服务器初级使用者可能面临的问题之一. 使用tomcat或者其他服务器软件设置端口号后,比如 一些不是默认的, mysql的 3306, mssql的1433,有时候打不开网页, 原因是没有在ecs安全组去设置这个端口号. 解决: 点击ecs下网络和安全下的安全组 在弹出的安全组中,如果没有就新建安全组,然后点击配置规则 最后如上图点击添加...或快速创建.   have fun!  将编程看作是一门艺术,而不单单是个技术。
3965 0
使用OpenApi弹性释放和设置云服务器ECS释放
云服务器ECS的一个重要特性就是按需创建资源。您可以在业务高峰期按需弹性的自定义规则进行资源创建,在完成业务计算的时候释放资源。本篇将提供几个Tips帮助您更加容易和自动化的完成云服务器的释放和弹性设置。
7612 0
windows server 2008阿里云ECS服务器安全设置
最近我们Sinesafe安全公司在为客户使用阿里云ecs服务器做安全的过程中,发现服务器基础安全性都没有做。为了为站长们提供更加有效的安全基础解决方案,我们Sinesafe将对阿里云服务器win2008 系统进行基础安全部署实战过程! 比较重要的几部分 1.
4989 0
阿里云ECS云服务器初始化设置教程方法
阿里云ECS云服务器初始化是指将云服务器系统恢复到最初状态的过程,阿里云的服务器初始化是通过更换系统盘来实现的,是免费的,阿里云百科网分享服务器初始化教程: 服务器初始化教程方法 本文的服务器初始化是指将ECS云服务器系统恢复到最初状态,服务器中的数据也会被清空,所以初始化之前一定要先备份好。
10744 0
腾讯云服务器 设置ngxin + fastdfs +tomcat 开机自启动
在tomcat中新建一个可以启动的 .sh 脚本文件 /usr/local/tomcat7/bin/ export JAVA_HOME=/usr/local/java/jdk7 export PATH=$JAVA_HOME/bin/:$PATH export CLASSPATH=.
2027 0
阿里云服务器如何登录?阿里云服务器的三种登录方法
购买阿里云ECS云服务器后如何登录?场景不同,云吞铺子总结大概有三种登录方式: 登录到ECS云服务器控制台 在ECS云服务器控制台用户可以更改密码、更换系统盘、创建快照、配置安全组等操作如何登录ECS云服务器控制台? 1、先登录到阿里云ECS服务器控制台 2、点击顶部的“控制台” 3、通过左侧栏,切换到“云服务器ECS”即可,如下图所示 通过ECS控制台的远程连接来登录到云服务器 阿里云ECS云服务器自带远程连接功能,使用该功能可以登录到云服务器,简单且方便,如下图:点击“远程连接”,第一次连接会自动生成6位数字密码,输入密码即可登录到云服务器上。
16296 0
+关注
倚贤
全栈工程师,从事了 12 年以 Java 语⾔为主的软件开发工作,热衷于整合框架与开发工具,关注 交互设计,喜欢写技术博客(http://codelife.me/),Linux拥趸,问题终结者。近期开始学习和关注 Elixir 函数语言,合作翻译了《Elixir 程序设计》。
35
文章
48
问答
来源圈子
更多
专注Serverless、微服务、函数计算等
+ 订阅
文章排行榜
最热
最新
相关电子书
更多
文娱运维技术
立即下载
《SaaS模式云原生数据仓库应用场景实践》
立即下载
《看见新力量:二》电子书
立即下载