在樹莓派(raspberry pi 4) 上安裝 pymssql 連接MS SQL

裝這pymssql套件,花了點時間爬文,做個記錄

若安裝過程遇到下列訊息

error: command 'arm-linux-gnueabihf-gcc' failed with exit status 1
照著下面步驟安裝就OK了
sudo apt-get install python3-dev freetds-dev -y
sudo apt-get install libffi-dev
sudo apt-get install libzbar-dev
sudo apt-get install clang -y
sudo apt-get install libssl-dev
其他的套件
pip3 install Cython
pip3 install pymssql
最後的執行成功訊息 pi@raspberrypi:~ $ pip3 install pymssql
Looking in indexes: https://pypi.org/simple, https://www.piwheels.org/simple
Collecting pymssql
Using cached https://files.pythonhosted.org/packages/da/bd/ebe3a088c8642e34f2f4ed9e0d58492ddca39e4aa3c57b42bf94d8bb974c/pymssql-2.2.0.tar.gz
Installing build dependencies ... done
Building wheels for collected packages: pymssql
Running setup.py bdist_wheel for pymssql ... done
Stored in directory: /home/pi/.cache/pip/wheels/a9/09/de/27b23b9020f54a039101c9e7e9c65f27c53368bfba56204d40
Successfully built pymssql
Installing collected packages: pymssql
Successfully installed pymssql-2.2.0

以下是官網所提供的範例:
conn = pymssql.connect(server, user, password, "tempdb")
cursor = conn.cursor(as_dict=True)

cursor.execute('SELECT * FROM persons WHERE salesrep=%s', 'John Doe')
for row in cursor:
print("ID=%d, Name=%s" % (row['id'], row['name']))

conn.close()

如果主機不使用預設的PORT,可以透過以下寫法連接:
conn = pymssql.connect(server='192.168.1.100', port=9899, user='username', password='password', database='tempdb')
cursor = conn.cursor(as_dict=True)

SQL查詢 可以也能使用OPENQUERY,例如:
select * from OPENQUERY( linked_server ,'SELECT * FROM dbname.schema.table')