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

Dynamic Queries and Querydsl JPA support in Spring

How You Can Efficiently Keep Your Organization’s Cloud Cost Under Control

What is Cloud storage and how does it work?

What is Scratch Programming?

Explain about PEGA Testing?

[CSS Series] Hovering Effects of Letters

Is Warren Buffett Secretly Buying Bitcoin?

Worst abuse of the C preprocessor (IOCCC winner, 1986)

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

Enable LDAP Authentication in Airflow

Apache Airflow(四) start_date & schedule_interval

Using Python’s DataComPy to Compare PostgreSQL Data

Integrate Databricks to SQL with MSAL using secret and certificates)