使用 Python 查詢 MS SQL 資料庫

耶,成功了,可以從 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

 

發佈留言

發佈留言必須填寫的電子郵件地址不會公開。 必填欄位標示為 *