耶,成功了,可以從 Mac OS X / Linux (ubuntu/centOS) 連到 Microsoft MS SQL server.
使用 Python 查詢 Azure SQL 資料庫
https://docs.microsoft.com/zh-tw/azure/sql-database/sql-database-connect-query-python
範例程式碼:
import pyodbc server = 'your_server.database.windows.net' database = 'your_database' username = 'your_username' password = 'your_password' driver= '{ODBC Driver 13 for SQL Server}' cnxn = pyodbc.connect('DRIVER='+driver+';PORT=1433;SERVER='+server+';PORT=1443;DATABASE='+database+';UID='+username+';PWD='+ password) cursor = cnxn.cursor() cursor.execute("SELECT TOP 20 pc.Name as CategoryName, p.name as ProductName FROM [SalesLT].[ProductCategory] pc JOIN [SalesLT].[Product] p ON pc.productcategoryid = p.productcategoryid") row = cursor.fetchone() while row: print (str(row[0]) + " " + str(row[1])) row = cursor.fetchone()
在命令提示字元中,執行下列命令:
python sqltest.py
請確認前 20 個資料列已傳回
要讓這段 code 能執行,請先參考這篇:
https://www.microsoft.com/en-us/sql-server/developer-get-started/python/mac/
這篇裡的東西太多,我沒有全部服用,只用了一部分。
Install Homebrew.
ruby -e "$(curl -fsSL https://raw.githubusercontent.com/Homebrew/install/master/install)"
Restart the terminal session. 這樣子,才能吃到的 brew 指令和相關的path 設定。
Install Python
brew install python
Install the ODBC Driver and SQL Command Line Utility for SQL Server
SQLCMD for Mac is a command line utility that enables you to connect to SQL Server and run queries.
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 指令沒錯,但不知道怎麼弄,就算了,略過這個功能。
上面設完請接下一篇文章:
Connecting to SQL Server from Python app on CentOS 7
https://stackoverflow.max-everyday.com/2017/08/connecting-to-sql-server-from-python-app-on-centos-7/
pyodbc source code, 相信你會用的到…:
https://github.com/mkleehammer/pyodbc/tree/master/src
pyodbc – Cursor.wiki
https://code.google.com/archive/p/pyodbc/wikis/Cursor.wiki#rowcount