自己投資としてチャレンジしている内容を Blog で公開しています。今回は Microsoft SQL Server 用にサンプルデータの一括生成方法について紹介します。Ubuntu で試した手順を紹介していますが、Windows の SQL Server でも利用できます。(In English: Python – Creating sample data in SQL Server on Ubuntu No.62)
▼1. Microsoft SQL Server とは
Microsoft が提供しているオンプレミス (on-premise) のリレーショナルデータベースです。
クラウドの Azure だと SQL Database とか、Synapse Analytics – SQL pool (旧 SQL Data Warehouse ) や、Azure Virtual Machine (VM) で既に SQL Server がインストールされている VM などがあります。
今までリリースされた SQL Server のバージョンや修正プログラムのバージョンなどは、このサイトが参考になります。Microsoft SQL Server Versions List
試しに使いたい場合は無料版 SQL Server Express edition が Windows や Linux の環境で利用できます。
(Windows OS) Express の今すぐダウンロードをクリックすると SQL2019-SSEI-Expr.exe がダウンロードされ、クリックするとインストールウィザードが進みます。 https://www.microsoft.com/ja-jp/sql-server/sql-server-downloads
(Ubuntu) クイック スタート:Ubuntu に SQL Server をインストールし、データベースを作成する
▼2. 準備
SQL Server on Linux をインストールします。詳細手順は以下を確認ください。今回は Ubuntu の環境で試しました。
Ubuntu:Linux 上に SQL Server をインストールする – SQL Server | Microsoft Learn
▼3. サンプルデータの一括作成
データベースで検証する際、サンプルデータが必要になる事があります。その際以下の手順を参考に、列数を増やしたり、レコード数 (ここでは num の値) を増やし、要望に合ったデータを生成することができます。
3-1. 事前準備
データベースおよびテーブルを生成するクエリcreatedbtable.sql を作成します。
// createdbtable.sql の中身
IF NOT EXISTS (SELECT * FROM sys.databases WHERE name = 'sampleDB')
Begin
Create database sampleDB
End;
go
use sampleDB;
go
IF EXISTS (SELECT * FROM sys.tables WHERE name = 'sampletable')
Begin
drop table sampletable
END;
go
Create table sampletable(
id int identity(1,1) not null,
num int,
cdatetime datetime,
note nvarchar(max)
)
goこの sql 文ではデータベース sampleDB を作成し、その DB の中で sampletable を作成しています。sampletable のスキマは以下のようになっています。
sampletable の Schema Column_name Type ----------- -------- id int num int cdatetime datetime note nvarchar
3-2. サンプルデータを作成するために、python で insert クエリ “insertmultipledata.sql” を作成
(参考) Python – Visual Studio Code の利用 No.34 – 2021/07
id の列には自動生成した番号が入り。num は指定した値が入り、cdatetime は現在の時刻が入り、note はランダムに生成した文字列が入ります。以下の python コードを実行すると insertmultipledata.sql が生成されます。
# create random string
import random, string,datetime
# create variable dtnow get date time
dtnow = datetime.datetime.now()
# create function random_key for random string
def randomword(length):
letters = string.ascii_lowercase
return ''.join(random.choice(letters) for i in range(length))
# put insert query to the file insertmultipledata.sql
with open("insertmultipledata.sql", "w") as f:
f.write("INSERT INTO sampletable(num,cdatetime,note) VALUES (0,GETDATE(),'init')")
for num in range(1, 10): # 10 rows
f.write(",(%d,CAST('%s' as DATETIME2),'%s')" % (num,dtnow,randomword(num)))
f.write(";")python コードで生成された insertmultipledata.sql の中身
INSERT INTO sampletable(num,cdatetime,note) VALUES (0,GETDATE(),'init'),(1,CAST('2022-10-20 14:00:30.053558' as DATETIME2),'k'),(2,CAST('2022-10-20 14:00:30.053558' as DATETIME2),'ef'),(3,CAST('2022-10-20 14:00:30.053558' as DATETIME2),'rqo'),(4,CAST('2022-10-20 14:00:30.053558' as DATETIME2),'gryz'),(5,CAST('2022-10-20 14:00:30.053558' as DATETIME2),'sgvzk'),(6,CAST('2022-10-20 14:00:30.053558' as DATETIME2),'tpngss'),(7,CAST('2022-10-20 14:00:30.053558' as DATETIME2),'ylrhfmt'),(8,CAST('2022-10-20 14:00:30.053558' as DATETIME2),'dkxoghpt'),(9,CAST('2022-10-20 14:00:30.053558' as DATETIME2),'whevhstvg');3-3. sqlcmd で 3-1, 3-2 で作成した sql 文 を実行
(参考) sqlcmd ユーティリティ
// -P の値は sa のパスワードを記載します。
> sqlcmd -S localhost -U sa -P yourpassowrd -i ./createdbtable.sql
(出力)
Changed database context to 'sampleDB'.
> sqlcmd -S localhost -U sa -P yourpassword -d sampleDB -i ./insertmultipledata.sql
(出力)
(10 rows affected)3-4. 生成したデータを確認
sqlcmd -S localhost -U sa -P yourpassword -d sampleDB -Q"select * from sampletable;"
（出力）
id num cdatetime note
----------- ----------- ----------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 0 2022-10-20 16:13:13.477 init
2 1 2022-10-20 16:12:11.777 a
3 2 2022-10-20 16:12:11.777 ah
4 3 2022-10-20 16:12:11.777 lam
5 4 2022-10-20 16:12:11.777 nddv
6 5 2022-10-20 16:12:11.777 vdhpw
7 6 2022-10-20 16:12:11.777 fdfszy
8 7 2022-10-20 16:12:11.777 rgofcgt
9 8 2022-10-20 16:12:11.777 hezwdqae
10 9 2022-10-20 16:12:11.777 bywqcunva
(10 rows affected)▼4. 参考情報
- Microsoft SQL Server Versions List
- クイック スタート:Ubuntu に SQL Server をインストールし、データベースを作成する
- Python – Visual Studio Code の利用 No.34
- sqlcmd ユーティリティ
以上です。参考になりましたら幸いです。