Using Python to Connect Function Compute to SQL Server

Preparing a Test Environment

$ 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
$ brew tap microsoft/mssql-release https://github.com/Microsoft/homebrew-mssql-release
$ brew update
$ ACCEPT_EULA=y brew install --no-sandbox msodbcsql mssql-tools
$ 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)
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)
1> SELECT * FROM Inventory WHERE quantity > 152;
2> GO
id name quantity
----------- -------------------------------------------------- -----------
2 orange 154
(1 rows affected)
1> QUIT

Preparing a Test Function

import pymssqldef 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

Completing the Installationv — Latest Version of pymssql

$ 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

Completing the Installation — Earlier Versions of pymssql

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

Conclusion

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/

--

--

--

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

Love podcasts or audiobooks? Learn on the go with our new app.

Recommended from Medium

Docker 101

What to consider when building NFT Marketplace?

NFT Development

Java 8 | For & If Practice

Setting Up CI/CD Pipeline in Robotframework and Slack Integration with Teamcity

🎃 An engineering spooky story 🎃

7 Women Developers want to give you their best Tips.

NFT New Collections Bot for Discord and Telegram

A Simple Continuous Deployment of a React Native App to Google Play

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Alibaba Cloud

Alibaba Cloud

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

More from Medium

Cloud Building a Python Poetry-based Cloud Function with Python Private GCP Artifact Repo

How to deploy micro-services on kbernetes, hands on tutorial !

Installing Hadoop on Ubuntu 20.04

Load CSV Data Into Elasticsearch Using Python