This document describes how to use a SQL ** Server ** database with ** Alibaba Cloud Function Compute ** and how to configure and validate ** Function Compute ** based on fc-docker.
Use Docker to run SQL Server 2017 on your local Mac computer, initialize the table structure, create a test file named index.py, and see if you can access the database.
$ 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 from port 1433 and set the SA account password 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
Confirm that the data has been inserted successfully and exit.
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 fc-docker runs the function inside the container and does not connect to localhost. It then executes the query statement and returns the result.
Create an empty directory and place the index.py file in it. Switch the current path of the command session to the directory where index.py is located. Then execute 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
$ 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
Execute the above command. The installation result will be returned. If you do not need to use an earlier version of pymssql, ignore the following sections.
For versions of pymssql prior to 2.1.3, running the pip install command compiles pymssql and installs it from source code. In this case, install the compile-dependent freetds-dev and the runtime-dependent libsybdb5. Compile dependencies can be installed directly in the system directory, but execution dependencies must be installed in the 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
Start the container in the 1st and 1st columns, stop the container in the 10th column and delete it automatically. Lines 2, 2 and 3 are used to install the runtime-dependent libsybdb5 in a local directory. 3. Link DLL libsybdb.so.5 to the / code / lib directory which is added to LD_LIBRARY_PATH by default. 4. Install freetds-dev and cython in the system directory, and compile and install pymssql. Neither library is needed when running pymssql, so you don't need to install it in your local directory. 5. Install pymssql 2.1.3. Starting with pymssql 2.1.4, the source code is no longer required during 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 exam has passed.
This document describes how to use the SQL Server (https://www.alibabacloud.com/en/product/apsaradb-for-rds-sql-server) database with Alibaba Cloud Function Compute. You no longer need the source code to install the latest version of pymssql. However, the method of installing pymssql from a source code package using pip can be applied to similar scenarios.
This document also describes how to configure and validate Function Compute based on fc-docker. Different files of fcli sbox and fc-docker can be developed as scripts and executed repeatedly, and fc-docker can be executed in the simulated local runtime environment. Both features are very useful in continuous integration (CI) scenarios.
1、https://pymssql.readthedocs.io/en/latest/intro.html#install 2、http://www.freetds.org/ 3、https://pymssql.readthedocs.io/en/latest/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/
Recommended Posts