How to connect and run select query for a table in SQL Server on Ubuntu using Microsoft ODBC Driver for SQL Server by Python are shown in this blog.
Contents
▼1. Connecting to SQL Server on Ubuntu
In the past, how to connect and run queries using Java are shown in this blog as below.
(As of 2022/12)
- Java – Update Statistics in SQL Server on Ubuntu No.91
- Java – Creating sample data for SQL Server on Ubuntu No.89
- Java – Generating the sample data on SQL Server No.87
- Java – Save the results of select query on SQL Server into Azure Blob Storage No.75
- Java – SQL Server Select 実行結果をテキストに保存 No.70
- Java – JDBC Driver for SQL Server on Ubuntuの自動リトライ接続 No53
- Java – Azure SQL DB への ActiveDirectoryPassword を使った接続 No.10
- Java – SQL Server on Linux トランザクションでクエリを制御する方法 No9
- Java – SQL Server on Linux で select を実行する方法 On Ubuntu No.8
- Java – Connection String of Microsoft JDBC Driver for SQL Server No.7
- Java – SQL Server on Linux に接続時の「エラーハンドリング」 &「リトライ」方法 No.6
- Java – Connect to SQL Server on Ubuntu No.5
▼2. Connecting to SQL Server and Select using Python
2-1. Installing SQL Server on Linux
SQL Server 2022 is GA (General Avaiable) on Dec 2022.Quickstart: Install SQL Server and create a database on Ubuntu
2-2. Installing Microsoft ODBC Driver for SQL Server
pip install pyodbc- Step 1: Configure development environment for pyodbc Python development
- Install the Microsoft ODBC driver for SQL Server (Linux)
2-3. Creating a python code to connect to SQL Server on Linux and run Select
Creating runquery.py as below. the following parameters need to be changed according to your environment.
- server
- database
- username
- password
// runquery.py
import pyodbc
server = 'localhost'
database = 'testdb'
username = 'sa'
password = 'yourpassword'
driver= '{ODBC Driver 17 for SQL Server}'
try:
cnxn = pyodbc.connect('DRIVER='+driver+';SERVER='+server+';PORT=1433;DATABASE='+database+';UID='+username+';PWD='+ password)
cursor = cnxn.cursor()
cursor.execute("SELECT * FROM testdb.dbo.sampletable")
row = cursor.fetchone()
while row:
print (str(row[0]) + " " + str(row[1]))
row = cursor.fetchone()
except pyodbc.Error as pye:
print(pye)2-4. Results of this python code when it work fine.
A part of the result is below.
0 0 10 1 20 2 xxx xxx 80 8 90 9 100 0
2-5. Results of this python code when it fails to work
2-5-1. Error 18456 happens when the password of sa is incorrect.
('28000', "[28000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Login failed for user 'sa'. (18456) (SQLDriverConnect)")
2-5-2. Error 4060 happens when database name “testdb2” doesn’t exist.
('42000', '[42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Cannot open database "testdb2" requested by the login. The login failed. (4060) (SQLDriverConnect)')
2-5-3. Error 208 happens when the table “testdb.dbo.sampletable1” doesn’t exist.
('42S02', "[42S02] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Invalid object name 'testdb.dbo.sampletable1'. (208) (SQLExecDirectW)")
▼3. Reference
- Quickstart: Install SQL Server and create a database on Ubuntu
- pyodbc https://pypi.org/project/pyodbc/
- https://learn.microsoft.com/ja-jp/sql/connect/python/pyodbc/step-3-proof-of-concept-connecting-to-sql-using-pyodbc?view=sql-server-ver15#connect
That’s all. Have a nice day ahead !!!