SQL Server 2022 : Python から利用する2023/12/15 |
Python からの SQL Server 利用例です。
|
|
[1] | Python 3 インストール済みを前提として例示します。 追加で Python DB API for ODBC をインストールしておきます。 |
root@dlp:~# apt -y install python3-pyodbc
|
[2] | テスト用のデータベース接続用ユーザーとデータベースを作成しておきます。 |
root@dlp:~# sqlcmd -S localhost -U SA Password: # ログインユーザー作成 1> create login debian with PASSWORD= N'P@ssw0rd01'; 2> go # [SampleDB3] 作成 1> create database SampleDB3; 2> go 1> use SampleDB3; 2> go Changed database context to 'SampleDB'. # ログインユーザー [debian] と関連付けて DB ユーザー作成 1> create user debian for login debian; 2> go # [debian] に DB オーナーロール付与 1> exec sp_addrolemember 'db_owner', 'debian'; 2> go # テストテーブル作成 1> create table SampleTable ( 2> ID int identity(1,1) not null primary key, First_Name NVARCHAR(50), Last_Name NVARCHAR(50) ); 3> insert into SampleTable ( 4> First_Name, Last_Name) values (N'Ubuntu', N'Linux'), (N'Debian', N'Linux'), (N'RedHat', N'Linux' ); 5> go |
[3] | 基本的な利用例です。データベースや接続ユーザーは上記で作成したものを使用します。 |
# ODBC Driver 確認 debian@dlp:~$ odbcinst -j unixODBC 2.3.11 DRIVERS............: /etc/odbcinst.ini SYSTEM DATA SOURCES: /etc/odbc.ini FILE DATA SOURCES..: /etc/ODBCDataSources USER DATA SOURCES..: /home/debian/.odbc.ini SQLULEN Size.......: 8 SQLLEN Size........: 8 SQLSETPOSIROW Size.: 8debian@dlp:~$ cat /etc/odbcinst.ini [ODBC Driver 17 for SQL Server] Description=Microsoft ODBC Driver 17 for SQL Server Driver=/opt/microsoft/msodbcsql17/lib64/libmsodbcsql-17.10.so.5.1 UsageCount=1
debian@dlp:~$
vi use_mssql.py import pyodbc server = '127.0.0.1' database = 'SampleDB3' username = 'debian' password = 'P@ssw0rd01' cnxn = pyodbc.connect('DRIVER={ODBC Driver 17 for SQL Server}; \ SERVER='+server+'; PORT=1443; DATABASE='+database+'; UID='+username+'; PWD='+ password) cursor = cnxn.cursor() # SampleTable を Select print ('\nReading data from SampleTable') tsql = "select * from SampleTable;" with cursor.execute(tsql): row = cursor.fetchone() while row: print (str(row[0]) + " " + str(row[1]) + " " + str(row[2])) row = cursor.fetchone() # SampleTable に Insert print ('\nInserting a new row into SampleTable') tsql = "insert into SampleTable (First_Name, Last_Name) values (?,?);" with cursor.execute(tsql,'Windows','Microsoft'): print ('- Successfuly Inserted!') # 特定の行を Update print ('\nUpdating Last_Name for Redhat') tsql = "update SampleTable set Last_Name = ? where First_Name = ?" with cursor.execute(tsql,'Plow','Redhat'): print ('- Successfuly Updated!') tsql = "select * from SampleTable;" with cursor.execute(tsql): row = cursor.fetchone() while row: print (str(row[0]) + " " + str(row[1]) + " " + str(row[2])) row = cursor.fetchone() # 特定の行を Delete print ('\nDeleting user Windows') tsql = "delete from SampleTable where First_Name = ?" with cursor.execute(tsql,'Windows'): print ('- Successfuly Deleted!') python3 use_mssql.py Reading data from SampleTable 1 Ubuntu Linux 2 Debian Linux 3 RedHat Linux Inserting a new row into SampleTable - Successfuly Inserted! Updating Last_Name for Redhat - Successfuly Updated! 1 Ubuntu Linux 2 Debian Linux 3 RedHat Plow 4 Windows Microsoft Deleting user Windows - Successfuly Deleted! |
Sponsored Link |
|