p045 Powershell로 Sqlite 다루기

한가지 타입의 데이터라면 CSV로 저장하기도 합니다만 종류가 여러개인 데이터라면 RDBMS에 가까운 형태를 사용하는 것이 좋습니다. 무거운 것은 사용하기 곤란하고, Portable한 file based한 database로 에전에는 MS Access 파일을 사용하던 적도 있지만, 요즘은 Sqlite를 사용하는 것이 일반적입니다. # Apple의 homebrew도 sqlite를 사용하고 있습니다.

이번 포스트에서는 Sqlite파일을 다루는 방법을 알아보고자 합니다.

SQLite Module (비추천)

https://www.powershellgallery.com/packages/SQLite/

결론 부터 이야기 하면, 저는 이쪽 모듈은 사용하고 있지 않습니다. Powershell Gallery에서도 20014년을 마지막으로 더이상 업데이트 되고 있지 않으며, 무엇보다도 Invoke-SqlCmd2의 사용방법과 많이 다릅니다. 아무래도 SQLite라는 이름을 먼저 취득한 덕을 톡톡히 보고 있다고 생각합니다.

제공하는 명령도 하나뿐입니다.

1
2
3
4
5
PS C:\www> gcm -module sqlite

CommandType     Name                                               Version    Source
-----------     ----                                               -------    ------
Function        mount-sqlite                                       2.0        sqlite

현재의 powershell session에서 sqlite 드라이버를 사용할 수 있게 해 주는 정도입니다. 그 때문에 .Net의 클래스를 직접 호출해 사용해야 하지만, 그래도 사용하고 싶다면 사용할 수 있는 예제는 다음과 같습니다.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52

using namespace System.Data.SQLite

$ErrorActionPreference = "STOP"
Import-Module SQLite

$Database = "C:\Names.SQLite"

# Connection 생성
$con = [SQLiteConnection]::new()
$con.ConnectionString = ("Data Source = {0}"-f $Database)
$con.Open()

# 테이블 생성
$cmd = [SQLiteCommand]::new()
$cmd.Connection = $con
$cmd.CommandText = @"
CREATE TABLE IF NOT EXISTS sample (
    id int,
    name text,
    primary key(id)
)
"@
[void]$cmd.ExecuteNonQuery()

# Insert  Record
$cmd.CommandText = "INSERT INTO sample (id, name) values (1,'kim')"
[void]$cmd.ExecuteNonQuery()
$cmd.CommandText = "INSERT INTO sample (id, name) values (2,'lee')"
[void]$cmd.ExecuteNonQuery()

# Update Record
$cmd.CommandText = "UPDATE sample SET name = 'kim' WHERE id = 2"
[void]$cmd.ExecuteNonQuery()

# Delete Record
$cmd.CommandText = "DELETE FROM sample WHERE id = 1"
[void]$cmd.ExecuteNonQuery()

# Select Records
$cmd.CommandText = "SELECT id, name FROM sample"
$rec = $cmd.ExecuteReader()
while ($rec.Read()) {
    Write-Host ("id:{0} name:{1}" -f $rec['id'], $rec['name'])
}
$rec.Close()

# Delete Table
$cmd.CommandText = "DROP TABLE IF EXISTS sample;"
[void]$cmd.ExecuteNonQuery()

$con.Close()

[void]$cmd.ExecuteNonQuery() 와 같이 [void] 를 명시적으로 호출하는 이유는 불필요한 output을 무시하기 위함입니다. 다음과 같이 사용하는 것도 같은 효과를 나타냅니다.

1
$cmd.ExecuteNonQuery() | Out-Null

저는 powershell -c "Uninstall-Module sqlite" 해 두었습니다.

PSSQLite Module (추천)

이쪽이 훨씬 최근에도 유지보수가 되고 있는 모듈이며, 무엇보다도 MSSql을 사용할 때 현재 사용하고 있는 모듈인 Invoke-SqlCmd2과 쓰임새가 비슷합니다. 내용이 훨씬 직관적이며, Select로 취득한 Object를 변환하지 않아도 되므로 사용하기에 훨씬 더 직관적입니다.

https://github.com/RamblingCookieMonster/PSSQLite/tree/master/PSSQLite

프로젝트 코드에서도 알 수 있듯이 직접 x86, x64, core 버전까지 dll을 내장하고 있습니다. 저는 이쪽을 추천합니다.

설치하는 코드는 다음과 같습니다.

``powershell
install-module pssqlite
Import-Module PSSQLite
$Database = “C:\Names.SQLite”


다음과 같은 명령어가 실장되어 있습니다.

```powershell
PS C:\www> gcm -module pssqlite

CommandType     Name                                               Version    Source
-----------     ----                                               -------    ------
Function        Invoke-SQLiteBulkCopy                              1.1.0      PSSQLite
Function        Invoke-SqliteQuery                                 1.1.0      PSSQLite
Function        New-SQLiteConnection                               1.1.0      PSSQLite
Function        Out-DataTable                                      1.1.0      PSSQLite
Function        Update-Sqlite                                      1.1.0      PSSQLite

Create Table (Invoke-SqliteQuery)

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
$Query = "CREATE TABLE NAMES (
    Fullname VARCHAR(20) PRIMARY KEY,
    Surname TEXT,
    Givenname TEXT,
    Birthdate DATETIME)"

#SQLite will create Names.SQLite for us
Invoke-SqliteQuery -Query $Query -DataSource $Database

# We have a database, and a table, let's view the table info
Invoke-SqliteQuery -DataSource $Database -Query "PRAGMA table_info(NAMES)"

$ConnectionString이나, $Conn 등을 사용하지 않고 그냥 -DataSource에 Path를 전달하고 있는데, 사용하기 편리한 디자인입니다. 또 Connection을 직접 사용하는 경우에는 메모리 DB를 사용하는 것도 가능합니다.

Insert And Select (Invoke-SqliteQuery)

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
# Insert some data, use parameters for the fullname and birthdate
$query = "INSERT INTO NAMES (Fullname, Surname, Givenname, Birthdate)
                      VALUES (@full, '김', '동현', @BD)"

Invoke-SqliteQuery -DataSource $Database -Query $query -SqlParameters @{
    full = "김동현"
    BD   = (get-date).addyears(-100)
}

# Check to see if we inserted the data:
Invoke-SqliteQuery -DataSource $Database -Query "SELECT * FROM NAMES"

실행해본 화면은 다음과 같습니다.

Memory Table (New-SQLiteConnection)

다음의 예제는 메모리에 테이블을 만들고 레코드를 저장합니다.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
# Create a SQLite database in memory
# This exists only as long as the connection is open
$C = New-SQLiteConnection -DataSource :MEMORY: 

#Add some tables
Invoke-SqliteQuery -SQLiteConnection $C -Query "
    CREATE TABLE OrdersToNames (OrderID INT PRIMARY KEY, Fullname TEXT);
    CREATE TABLE Names (Fullname TEXT PRIMARY KEY, Birthdate DATETIME);"

#Add some data
Invoke-SqliteQuery -SQLiteConnection $C -SqlParameters @{BD = (Get-Date)} -Query "
    INSERT INTO OrdersToNames (OrderID, fullname) VALUES (1,'Cookie Monster');
    INSERT INTO OrdersToNames (OrderID) VALUES (2);
    INSERT INTO Names (Fullname, Birthdate) VALUES ('Cookie Monster', @BD)"

#Query the data.  Illustrate PSObject vs. Datarow filtering
Invoke-SqliteQuery -SQLiteConnection $C -Query "SELECT * FROM OrdersToNames" |
    Where-Object { $_.Fullname }

Invoke-SqliteQuery -SQLiteConnection $C -Query "SELECT * FROM OrdersToNames" -As DataRow |
    Where-Object { $_.Fullname }

#Joining.  Yeah, a CustomerID would make more sense :)
Invoke-SqliteQuery -SQLiteConnection $C -Query "
    SELECT * FROM Names
        INNER JOIN OrdersToNames
        ON Names.fullname = OrdersToNames.fullname
"

-As DataRow 를 쓰는 경우와 쓰지않고 그냥 PSObject로 받는 것의 차이를 말하자면, DataRow쪽이 더 속도가 더 빠릅니다. 하지만 속도에 문제가 없는 경우에는 PSObject로 사용하는 것이 더 알기쉽고 편합니다.

Bulk Insert (Invoke-SQLiteBulkCopy)

일반적인 경우는 사용하지 않지만 간혹 운용상 사용할 일이 발생하긴 합니다. 또 하나의 컨넥션에서 loop를 돌려서 구현하는 것도 가능합니다만, 가능하다면 하나의 트랜잭션으로 처리하는 것이 더 빠르겠죠. 다음은 그 예제를 보여줍니다.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
#Create a table
Invoke-SqliteQuery -DataSource "C:\Names.SQLite" -Query "CREATE TABLE NAMES (
    fullname VARCHAR(20) PRIMARY KEY,
    surname TEXT,
    givenname TEXT,
    BirthDate DATETIME)"

#Build up some fake data to bulk insert, convert it to a datatable
$DataTable = 1..10000 | %{
    [pscustomobject]@{
        fullname = "Name $_"
        surname = "Name"
        givenname = "$_"
        BirthDate = (Get-Date).Adddays(-$_)
    }
} | Out-DataTable

#Copy the data in within a single transaction (SQLite is faster this way)
Invoke-SQLiteBulkCopy -DataTable $DataTable -DataSource $Database -Table Names -NotifyAfter 1000 -verbose

이상으로 Sqlite의 기본사용법들을 살펴보았습니다.

실전에서는 MSSql과 함께 사용하고 있습니다만, Core버전의 powershell이나, 개인용으로 사용하는 경우에는 대형 RDBMS보다 Sqlite를 사용하는 것도 좋습니다. StoredProcedure나 Function, View등을 사용하려면 MSSql Express를 사용했었는데, 요즘은 로직을 Powershell로 두는 것이(자주사용하는 언어로 로직을 구현하는 것이) 더 유닛테스트와 함께 사용하기 좋고, 알기쉬운 경우가 많아서 이쪽을 더 이용하고 있습니다.