Autoit sqlite

autoit sqlite 예제

#include <Array.au3>
#include <SQLite.au3>

_SQLite_Startup() ; Load the DLL
If @error Then Exit MsgBox(0, "Error", "Unable to start SQLite, Please verify your DLL")

Local $sDatabase = @ScriptDir & '\SQLiteTestDatabase.db'
Local $hDatabase = _SQLite_Open($sDatabase) ; Create the database file and get the handle for the database

_SQLite_Exec($hDatabase, 'CREATE TABLE People (first_name, last_name);') ; CREATE a TABLE with the name "People"
_SQLite_Exec($hDatabase, 'INSERT INTO People VALUES ("Timothy", "Lee");') ; INSERT "Timothy Lee" into the "People" TABLE
_SQLite_Exec($hDatabase, 'INSERT INTO People VALUES ("John", "Doe");') ; INSERT "John Doe" into the "People" TABLE

Local $aResult, $iRows, $iColumns ; $iRows and $iColuums are useless but they cannot be omitted from the function call so we declare them

_SQLite_GetTable2d($hDatabase, 'SELECT * FROM People;', $aResult, $iRows, $iColumns) ; SELECT everything FROM "People" TABLE and get the $aResult
_ArrayDisplay($aResult, "Results from the query")

_SQLite_Close($hDatabase)
_SQLite_Shutdown()

sqlite이용 하여 그룹별로 저장하고 그룹별 탭을 클릭시 해당룹의 내용을 보여주는 예제

#include <GUIConstantsEx.au3>
#include <WindowsConstants.au3>
#include <SQLite.au3> ; SQLite UDF 포함 (미리 설치 필요)
#include <Array.au3>

; 데이터베이스 초기화
_SQLite_Startup()
Local $db = _SQLite_Open(":memory:") ; 메모리 기반 데이터베이스 사용

; 데이터베이스 테이블 생성
_SQLite_Exec($db, "CREATE TABLE data (key TEXT, value TEXT, group_name TEXT);")

; 예제 데이터 삽입
_SQLite_Exec($db, "INSERT INTO data VALUES ('Key1', 'Value1', 'Group1');")
_SQLite_Exec($db, "INSERT INTO data VALUES ('Key2', 'Value2', 'Group1');")
_SQLite_Exec($db, "INSERT INTO data VALUES ('Key3', 'Value3', 'Group2');")
_SQLite_Exec($db, "INSERT INTO data VALUES ('Key4', 'Value4', 'Group2');")
_SQLite_Exec($db, "INSERT INTO data VALUES ('Key5', 'Value5', 'Group3');")

; GUI 생성
Local $hGUI = GUICreate("Tab View with SQLite", 500, 400)

; Tab 컨트롤 생성
Local $hTab = GUICtrlCreateTab(10, 10, 480, 350)

; 그룹별 탭 생성
Local $aGroups
_SQLite_GetTable2d($db, "SELECT DISTINCT group_name FROM data;", $aGroups)

; 각 그룹 탭에 데이터를 표시
For $i = 1 To UBound($aGroups) - 1
    Local $sGroupName = $aGroups[$i][0]
    GUICtrlCreateTabItem($sGroupName)

    ; 그룹 내의 키-값 데이터 가져오기
    Local $aData
    _SQLite_GetTable2d($db, "SELECT key, value FROM data WHERE group_name = '" & $sGroupName & "';", $aData)

    ; 각 그룹의 데이터를 Label로 추가
    Local $nY = 20
    For $j = 1 To UBound($aData) - 1
        Local $sText = $aData[$j][0] & ": " & $aData[$j][1]
        GUICtrlCreateLabel($sText, 20, $nY, 440, 20)
        $nY += 25
    Next
Next

; 첫 번째 탭을 기본 탭으로 설정
GUICtrlCreateTabItem("")

; GUI 표시
GUISetState(@SW_SHOW)

; 메시지 루프
While 1
    If GUIGetMsg() = $GUI_EVENT_CLOSE Then ExitLoop
WEnd

; 종료 처리
GUIDelete()
_SQLite_Close($db)
_SQLite_Shutdown()
Exit

sqlite 입력 수정 삭제 예제

#include <GUIConstantsEx.au3>
#include <WindowsConstants.au3>
#include <GuiListView.au3>
#include <SQLite.au3>
#include <Array.au3>

; SQLite 초기화
_SQLite_Startup()
If @error Then
    MsgBox(16, "Error", "SQLite.dll을 로드할 수 없습니다.")
    Exit
EndIf

; GUI 생성
Global $hGUI = GUICreate("SQLite ListView Demo", 600, 400)
Global $hListView = _GUICtrlListView_Create($hGUI, "ID|Name|Age|City", 10, 10, 580, 300)
Global $btnInsert = GUICtrlCreateButton("Insert", 10, 320, 80, 30)
Global $btnUpdate = GUICtrlCreateButton("Update", 100, 320, 80, 30)
Global $btnDelete = GUICtrlCreateButton("Delete", 190, 320, 80, 30)
Global $inputName = GUICtrlCreateInput("", 10, 360, 120, 20)
Global $inputAge = GUICtrlCreateInput("", 140, 360, 50, 20)
Global $inputCity = GUICtrlCreateInput("", 200, 360, 120, 20)

; ListView 설정
_GUICtrlListView_SetColumnWidth($hListView, 0, 50)
_GUICtrlListView_SetColumnWidth($hListView, 1, 200)
_GUICtrlListView_SetColumnWidth($hListView, 2, 50)
_GUICtrlListView_SetColumnWidth($hListView, 3, 200)

; 데이터베이스 생성/연결
Global $hDB = _SQLite_Open("test.db")
If @error Then
    MsgBox(16, "Error", "데이터베이스를 열 수 없습니다.")
    Exit
EndIf

; 테이블 생성
_SQLite_Exec($hDB, "CREATE TABLE IF NOT EXISTS people (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, age INTEGER, city TEXT);")

; 초기 데이터 로드
LoadData()

GUISetState(@SW_SHOW)

; 메인 루프
While 1
    Switch GUIGetMsg()
        Case $GUI_EVENT_CLOSE
            ExitLoop
        Case $btnInsert
            InsertData()
        Case $btnUpdate
            UpdateData()
        Case $btnDelete
            DeleteData()
    EndSwitch
WEnd

; 정리
_SQLite_Close($hDB)
_SQLite_Shutdown()
GUIDelete($hGUI)

; 함수들
Func LoadData()
    _GUICtrlListView_DeleteAllItems($hListView)
    Local $aResult, $iRows, $iCols
    _SQLite_GetTable2d($hDB, "SELECT * FROM people;", $aResult, $iRows, $iCols)
    If $iRows > 0 Then
        For $i = 1 To $iRows
            _GUICtrlListView_AddItem($hListView, $aResult[$i][0])
            _GUICtrlListView_AddSubItem($hListView, $i-1, $aResult[$i][1], 1)
            _GUICtrlListView_AddSubItem($hListView, $i-1, $aResult[$i][2], 2)
            _GUICtrlListView_AddSubItem($hListView, $i-1, $aResult[$i][3], 3)
        Next
    EndIf
EndFunc

Func InsertData()
    Local $sName = GUICtrlRead($inputName)
    Local $iAge = GUICtrlRead($inputAge)
    Local $sCity = GUICtrlRead($inputCity)
    
    If $sName <> "" And $iAge <> "" And $sCity <> "" Then
        _SQLite_Exec($hDB, "INSERT INTO people (name, age, city) VALUES ('" & $sName & "', " & $iAge & ", '" & $sCity & "');")
        GUICtrlSetData($inputName, "")
        GUICtrlSetData($inputAge, "")
        GUICtrlSetData($inputCity, "")
        LoadData()
    Else
        MsgBox(48, "Warning", "모든 필드를 입력해주세요.")
    EndIf
EndFunc

Func UpdateData()
    Local $iSelected = _GUICtrlListView_GetSelectedIndices($hListView)
    If $iSelected <> "" Then
        Local $iID = _GUICtrlListView_GetItemText($hListView, $iSelected, 0)
        Local $sName = GUICtrlRead($inputName)
        Local $iAge = GUICtrlRead($inputAge)
        Local $sCity = GUICtrlRead($inputCity)
        
        If $sName <> "" And $iAge <> "" And $sCity <> "" Then
            _SQLite_Exec($hDB, "UPDATE people SET name = '" & $sName & "', age = " & $iAge & ", city = '" & $sCity & "' WHERE id = " & $iID & ";")
            GUICtrlSetData($inputName, "")
            GUICtrlSetData($inputAge, "")
            GUICtrlSetData($inputCity, "")
            LoadData()
        Else
            MsgBox(48, "Warning", "모든 필드를 입력해주세요.")
        EndIf
    Else
        MsgBox(48, "Warning", "수정할 항목을 선택해주세요.")
    EndIf
EndFunc

Func DeleteData()
    Local $iSelected = _GUICtrlListView_GetSelectedIndices($hListView)
    If $iSelected <> "" Then
        Local $iID = _GUICtrlListView_GetItemText($hListView, $iSelected, 0)
        _SQLite_Exec($hDB, "DELETE FROM people WHERE id = " & $iID & ";")
        LoadData()
    Else
        MsgBox(48, "Warning", "삭제할 항목을 선택해주세요.")
    EndIf
EndFunc