Using Python to Connect Function Compute to SQL Server

本文涉及的产品
函数计算FC,每月15万CU 3个月
Serverless 应用引擎免费试用套餐包,4320000 CU,有效期3个月
简介: 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/
相关实践学习
使用SQL语句管理索引
本次实验主要介绍如何在RDS-SQLServer数据库中,使用SQL语句管理索引。
SQL Server on Linux入门教程
SQL Server数据库一直只提供Windows下的版本。2016年微软宣布推出可运行在Linux系统下的SQL Server数据库,该版本目前还是早期预览版本。本课程主要介绍SQLServer On Linux的基本知识。 相关的阿里云产品:云数据库RDS SQL Server版 RDS SQL Server不仅拥有高可用架构和任意时间点的数据恢复功能,强力支撑各种企业应用,同时也包含了微软的License费用,减少额外支出。 了解产品详情: https://www.aliyun.com/product/rds/sqlserver
目录
相关文章
|
2月前
|
SQL 自然语言处理 数据库
【Azure Developer】分享两段Python代码处理表格(CSV格式)数据 : 根据每列的内容生成SQL语句
本文介绍了使用Python Pandas处理数据收集任务中格式不统一的问题。针对两种情况:服务名对应多人拥有状态(1/0表示),以及服务名与人名重复列的情况,分别采用双层for循环和字典数据结构实现数据转换,最终生成Name对应的Services列表(逗号分隔)。此方法高效解决大量数据的人工处理难题,减少错误并提升效率。文中附带代码示例及执行结果截图,便于理解和实践。
|
1月前
|
Python
[oeasy]python086方法_method_函数_function_区别
本文详细解析了Python中方法(method)与函数(function)的区别。通过回顾列表操作如`append`,以及随机模块的使用,介绍了方法作为类的成员需要通过实例调用的特点。对比内建函数如`print`和`input`,它们无需对象即可直接调用。总结指出方法需基于对象调用且包含`self`参数,而函数独立存在无需`self`。最后提供了学习资源链接,方便进一步探索。
65 17
|
1月前
|
人工智能 Python
[oeasy]python083_类_对象_成员方法_method_函数_function_isinstance
本文介绍了Python中类、对象、成员方法及函数的概念。通过超市商品分类的例子,形象地解释了“类型”的概念,如整型(int)和字符串(str)是两种不同的数据类型。整型对象支持数字求和,字符串对象支持拼接。使用`isinstance`函数可以判断对象是否属于特定类型,例如判断变量是否为整型。此外,还探讨了面向对象编程(OOP)与面向过程编程的区别,并简要介绍了`type`和`help`函数的用法。最后总结指出,不同类型的对象有不同的运算和方法,如字符串有`find`和`index`方法,而整型没有。更多内容可参考文末提供的蓝桥、GitHub和Gitee链接。
59 11
|
2月前
|
SQL Oracle 关系型数据库
【YashanDB知识库】共享利用Python脚本解决Oracle的SQL脚本@@用法
【YashanDB知识库】共享利用Python脚本解决Oracle的SQL脚本@@用法
|
2月前
|
SQL Oracle 关系型数据库
【YashanDB知识库】共享利用Python脚本解决Oracle的SQL脚本@@用法
本文来自YashanDB官网,介绍如何处理Oracle客户端sql*plus中使用@@调用同级目录SQL脚本的场景。崖山数据库23.2.x.100已支持@@用法,但旧版本可通过Python脚本批量重写SQL文件,将@@替换为绝对路径。文章通过Oracle示例展示了具体用法,并提供Python脚本实现自动化处理,最后调整批处理脚本以适配YashanDB运行环境。
|
5月前
|
SQL 存储 数据挖掘
使用Python和PDFPlumber进行简历筛选:以SQL技能为例
本文介绍了一种使用Python和`pdfplumber`库自动筛选简历的方法,特别是针对包含“SQL”技能的简历。通过环境准备、代码解析等步骤,实现从指定文件夹中筛选出含有“SQL”关键词的简历,并将其移动到新的文件夹中,提高招聘效率。
135 8
使用Python和PDFPlumber进行简历筛选:以SQL技能为例
|
7月前
|
Linux Python
【Azure Function】Python Function部署到Azure后报错No module named '_cffi_backend'
ERROR: Error: No module named '_cffi_backend', Cannot find module. Please check the requirements.txt file for the missing module.
159 2
|
6月前
|
中间件 Docker Python
【Azure Function】FTP上传了Python Function文件后,无法在门户页面加载函数的问题
通过FTP上传Python Function至Azure云后,出现函数列表无法加载的问题。经排查,发现是由于`requirements.txt`中的依赖包未被正确安装。解决方法为:在本地安装依赖包到`.python_packages/lib/site-packages`目录,再将该目录内容上传至云上的`wwwroot`目录,并重启应用。最终成功加载函数列表。
|
7月前
|
Java C++ Python
Python Function详解!
本文详细介绍了Python函数的概念及其重要性。函数是一组执行特定任务的代码,通过`def`关键字定义,能显著提升代码的可读性和重用性。Python函数分为内置函数和用户自定义函数两大类,支持多种参数类型,包括默认参数、关键字参数、位置参数及可变长度参数。文章通过多个实例展示了如何定义和调用函数,解释了匿名函数、递归函数以及文档字符串的使用方法。掌握Python函数有助于更好地组织和优化代码结构。
151 4
|
6月前
|
SQL 数据挖掘 Python
数据分析编程:SQL,Python or SPL?
数据分析编程用什么,SQL、python or SPL?话不多说,直接上代码,对比明显,明眼人一看就明了:本案例涵盖五个数据分析任务:1) 计算用户会话次数;2) 球员连续得分分析;3) 连续三天活跃用户数统计;4) 新用户次日留存率计算;5) 股价涨跌幅分析。每个任务基于相应数据表进行处理和计算。