주식 데이터를 SQLite로 저장하는 실시간 수집 시스템

정기적으로 웹에서 실시간 주식 정보를 가져와 로컬에 SQLite 데이터베이스에 기록합니다. 실행 시 자동으로 데이터베이스가 생성되며, 메뉴를 통해 테이블을 생성하고, 10초 간격으로 데이터를 저장할 수 있습니다. 저장된 데이터는 쿼리 기능을 통해 확인 가능합니다. 문자 인코딩은 유니코드보다 ASCII를 사용하여 처리가 간단해졌습니다.

다음은 주식 데이터를 수집하고 저장하는 구현 예제입니다.

// StockDataCollector.cpp: 주식 정보를 데이터베이스에 저장
// sqlite-amalgamation의 sqlite3.c 및 sqlite3.h 파일을 프로젝트에 포함
// 전처리 헤더 사용 제외, 문자 집합은 ASCII로 설정

#include "stdafx.h"
#include "StockDataCollector.h"
#include <windows.h>
#include <wininet.h>
#include <stdio.h>
#include "sqlite3.h"

#pragma comment(lib, "wininet.lib")

#define MAX_BUFFER_SIZE 1024
#define DATABASE_NAME "stock_data.db"

// 전역 변수
HINSTANCE hAppInstance;
TCHAR appTitle[MAX_LOADSTRING];
TCHAR windowClass[MAX_LOADSTRING];

// 함수 선언
ATOM RegisterWindowClass(HINSTANCE);
BOOL InitializeApplication(HINSTANCE, int);
LRESULT CALLBACK WindowProc(HWND, UINT, WPARAM, LPARAM);

// 주식 정보 구조체
struct StockRecord {
    TCHAR symbol[16];        // 종목 코드 (예: sh000001)
    TCHAR timestamp[20];     // 시간 포맷: YYYY-MM-DD HH:MM:SS
    double currentPrice;     // 현재 가격
    double previousClose;    // 전일 종가
    double priceChange;      // 변동률 (%)
};

// GUI 요소
HWND editBox;
TCHAR outputBuffer[MAX_BUFFER_SIZE];
TCHAR statusMessage[256];
StockRecord stockData;

// SQLite 관련
sqlite3* dbHandle;
char* errorMsg;
int result;
char sqlQuery[MAX_BUFFER_SIZE];

// 출력 함수
int PrintLog(TCHAR* format, ...);
static int QueryCallback(void*, int, char**, char**);
bool FetchStockData(StockRecord&);
int OpenDatabase(HWND);
int SaveToDatabase(const StockRecord&);

// 메시지 핸들러
int OnWindowCreate(HWND, UINT, WPARAM, LPARAM);
int OnWindowSize(HWND, UINT, WPARAM, LPARAM);
int OnPaintWindow(HWND, UINT, WPARAM, LPARAM);
int OnTimerEvent(HWND, UINT, WPARAM, LPARAM);
int OnCreateTableMenu(HWND, UINT, WPARAM, LPARAM);
int OnDropTableMenu(HWND, UINT, WPARAM, LPARAM);
int OnTestInsertMenu(HWND, UINT, WPARAM, LPARAM);
int OnQueryDataMenu(HWND, UINT, WPARAM, LPARAM);

// 메인 함수
int APIENTRY wWinMain(HINSTANCE hInstance,
                      HINSTANCE hPrevInstance,
                      LPWSTR lpCmdLine,
                      int nCmdShow)
{
    UNREFERENCED_PARAMETER(hPrevInstance);
    UNREFERENCED_PARAMETER(lpCmdLine);

    MSG message;
    HACCEL acceleratorTable;

    LoadString(hInstance, IDS_APP_TITLE, appTitle, MAX_LOADSTRING);
    LoadString(hInstance, IDC_STOCKCOLLECTOR, windowClass, MAX_LOADSTRING);
    RegisterWindowClass(hInstance);

    if (!InitializeApplication(hInstance, nCmdShow))
        return FALSE;

    acceleratorTable = LoadAccelerators(hInstance, MAKEINTRESOURCE(IDC_STOCKCOLLECTOR));

    while (GetMessage(&message, NULL, 0, 0)) {
        if (!TranslateAccelerator(message.hwnd, acceleratorTable, &message)) {
            TranslateMessage(&message);
            DispatchMessage(&message);
        }
    }

    return (int)message.wParam;
}

// 윈도우 클래스 등록
ATOM RegisterWindowClass(HINSTANCE hInstance) {
    WNDCLASSEX wcex = { sizeof(WNDCLASSEX) };

    wcex.style = CS_HREDRAW | CS_VREDRAW;
    wcex.lpfnWndProc = WindowProc;
    wcex.hInstance = hInstance;
    wcex.hIcon = LoadIcon(hInstance, MAKEINTRESOURCE(IDI_STOCKCOLLECTOR));
    wcex.hCursor = LoadCursor(NULL, IDC_ARROW);
    wcex.hbrBackground = (HBRUSH)(COLOR_WINDOW + 1);
    wcex.lpszMenuName = MAKEINTRESOURCE(IDC_STOCKCOLLECTOR);
    wcex.lpszClassName = windowClass;
    wcex.hIconSm = LoadIcon(wcex.hInstance, MAKEINTRESOURCE(IDI_SMALL));

    return RegisterClassEx(&wcex);
}

// 애플리케이션 초기화
BOOL InitializeApplication(HINSTANCE hInstance, int nCmdShow) {
    HWND hWnd = CreateWindow(windowClass, appTitle,
        WS_OVERLAPPEDWINDOW, CW_USEDEFAULT, 0, CW_USEDEFAULT, 0,
        NULL, NULL, hInstance, NULL);

    if (!hWnd) return FALSE;

    ShowWindow(hWnd, nCmdShow);
    UpdateWindow(hWnd);

    hAppInstance = hInstance;
    return TRUE;
}

// 메시지 루프 처리
LRESULT CALLBACK WindowProc(HWND hWnd, UINT message, WPARAM wParam, LPARAM lParam) {
    int commandId, event;

    switch (message) {
    case WM_CREATE:
        OnWindowCreate(hWnd, message, wParam, lParam);
        break;
    case WM_SIZE:
        OnWindowSize(hWnd, message, wParam, lParam);
        break;
    case WM_PAINT:
        OnPaintWindow(hWnd, message, wParam, lParam);
        break;
    case WM_TIMER:
        OnTimerEvent(hWnd, message, wParam, lParam);
        break;
    case WM_COMMAND:
        commandId = LOWORD(wParam);
        event = HIWORD(wParam);

        switch (commandId) {
        case IDM_CREATETABLE:
            OnCreateTableMenu(hWnd, message, wParam, lParam);
            break;
        case IDM_DELETETABLE:
            OnDropTableMenu(hWnd, message, wParam, lParam);
            break;
        case IDM_INSERTDATA:
            OnTestInsertMenu(hWnd, message, wParam, lParam);
            break;
        case IDM_SQLDATA:
            OnQueryDataMenu(hWnd, message, wParam, lParam);
            break;
        case IDM_ABOUT:
            MessageBox(hWnd, _T("SQLite 기반 주식 데이터 수집기 - 2022"), _T("정보"), MB_OK);
            break;
        case IDM_EXIT:
            DestroyWindow(hWnd);
            break;
        default:
            return DefWindowProc(hWnd, message, wParam, lParam);
        }
        break;

    case WM_DESTROY:
        sqlite3_close(dbHandle);
        PostQuitMessage(0);
        break;

    default:
        return DefWindowProc(hWnd, message, wParam, lParam);
    }

    return 0;
}

// SQL 쿼리 결과 콜백
int QueryCallback(void* notUsed, int argc, char** values, char** columns) {
    for (int i = 0; i < argc; ++i) {
        PrintLog(_T("%s = %s\n"), columns[i], values[i] ? values[i] : _T("NULL"));
    }
    PrintLog(_T("\r\n"));
    return 0;
}

// 데이터베이스 열기
int OpenDatabase(HWND hWnd) {
    result = sqlite3_open(DATABASE_NAME, &dbHandle);
    if (result != SQLITE_OK) {
        PrintLog(_T("<ERR> 데이터베이스 열기 실패: %s\n"), sqlite3_errmsg(dbHandle));
        sqlite3_close(dbHandle);
        return -1;
    }

    wsprintf(statusMessage, _T("데이터베이스 준비 완료: %s"), DATABASE_NAME);
    return 0;
}

// 창 생성 시 초기화
int OnWindowCreate(HWND hWnd, UINT message, WPARAM wParam, LPARAM lParam) {
    editBox = CreateWindow(TEXT("edit"), NULL,
        WS_CHILD | WS_BORDER | WS_VISIBLE | ES_MULTILINE | WS_VSCROLL,
        0, 0, 0, 0, hWnd, (HMENU)IDC_EDIT, hAppInstance, NULL);

    OpenDatabase(hWnd);
    SetTimer(hWnd, 1, 10000, NULL); // 10초 간격

    return 1;
}

// 창 크기 조절 시 컨트롤 위치 조정
int OnWindowSize(HWND hWnd, UINT message, WPARAM wParam, LPARAM lParam) {
    RECT clientRect;
    GetClientRect(hWnd, &clientRect);

    MoveWindow(editBox, 0, 0, clientRect.right, clientRect.bottom - 20, TRUE);
    return DefWindowProc(hWnd, message, wParam, lParam);
}

// 상태 표시줄 업데이트
int OnPaintWindow(HWND hWnd, UINT message, WPARAM wParam, LPARAM lParam) {
    PAINTSTRUCT ps;
    HDC hdc;
    RECT rect;

    hdc = BeginPaint(hWnd, &ps);
    GetClientRect(hWnd, &rect);
    DrawText(hdc, statusMessage, lstrlen(statusMessage), &rect,
             DT_LEFT | DT_BOTTOM | DT_SINGLELINE);
    EndPaint(hWnd, &ps);

    return 1;
}

// 타이머 이벤트: 주식 데이터 수집 및 저장
int OnTimerEvent(HWND hWnd, UINT message, WPARAM wParam, LPARAM lParam) {
    SYSTEMTIME now;
    GetSystemTime(&now);

    PrintLog(_T("\r\n[%04d-%02d-%02d %02d:%02d:%02d] "),
             now.wYear, now.wMonth, now.wDay,
             now.wHour, now.wMinute, now.wSecond);

    wsprintf(stockData.timestamp, _T("%04d-%02d-%02d %02d:%02d:%02d"),
             now.wYear, now.wMonth, now.wDay,
             now.wHour, now.wMinute, now.wSecond);

    const TCHAR* symbols[] = {
        _T("sh000001"), _T("sz399001"),
        _T("sh601398"), _T("sz000063")
    };

    for (int i = 0; i < 4; ++i) {
        wcscpy(stockData.symbol, symbols[i]);
        if (FetchStockData(stockData)) {
            PrintLog(_T(" |%s  %8.2f  %8.4f%% "),
                     stockData.symbol, stockData.currentPrice,
                     stockData.priceChange * 100.0);
            SaveToDatabase(stockData);
        }
    }

    return 1;
}

// 테이블 생성
int OnCreateTableMenu(HWND hWnd, UINT message, WPARAM wParam, LPARAM lParam) {
    strcpy(sqlQuery, "CREATE TABLE IF NOT EXISTS StockRecords (" \
                    "Symbol TEXT, Timestamp TEXT, CurrentPrice REAL);");

    result = sqlite3_exec(dbHandle, sqlQuery, QueryCallback, nullptr, &errorMsg);
    if (result != SQLITE_OK) {
        PrintLog(_T("<ERR> 테이블 생성 실패: %s\n"), errorMsg);
        sqlite3_free(errorMsg);
        return -1;
    }

    PrintLog(_T("테이블 생성 성공"));
    return 1;
}

// 테이블 삭제
int OnDropTableMenu(HWND hWnd, UINT message, WPARAM wParam, LPARAM lParam) {
    strcpy(sqlQuery, "DROP TABLE IF EXISTS StockRecords;");
    result = sqlite3_exec(dbHandle, sqlQuery, QueryCallback, nullptr, &errorMsg);
    if (result != SQLITE_OK) {
        PrintLog(_T("<ERR> 테이블 삭제 실패: %s\n"), errorMsg);
        sqlite3_free(errorMsg);
        return -1;
    }

    PrintLog(_T("테이블 삭제 완료"));
    return 1;
}

// 테스트 데이터 삽입
int OnTestInsertMenu(HWND hWnd, UINT message, WPARAM wParam, LPARAM lParam) {
    strcpy(sqlQuery, "INSERT INTO StockRecords VALUES('sh000001', '2022-01-01 12:10', 3011.23);");
    result = sqlite3_exec(dbHandle, sqlQuery, QueryCallback, nullptr, &errorMsg);
    if (result != SQLITE_OK) {
        PrintLog(_T("<ERR> 삽입 실패: %s\n"), errorMsg);
        sqlite3_free(errorMsg);
    } else {
        PrintLog(_T("테스트 데이터 삽입 완료"));
    }
    return 1;
}

// 전체 데이터 조회
int OnQueryDataMenu(HWND hWnd, UINT message, WPARAM wParam, LPARAM lParam) {
    strcpy(sqlQuery, "SELECT * FROM StockRecords;");
    char** resultData;
    int rowCount = 0, colCount = 0;

    result = sqlite3_get_table(dbHandle, sqlQuery, &resultData, &rowCount, &colCount, &errorMsg);
    if (result != SQLITE_OK) {
        PrintLog(_T("<ERR> 쿼리 실패: %s\n"), errorMsg);
        sqlite3_free(errorMsg);
        return -1;
    }

    PrintLog(_T("행 수: %d, 열 수: %d\n"), rowCount, colCount);

    for (int i = 0; i <= rowCount; ++i) {
        PrintLog(_T("\r\n"));
        for (int j = 0; j < colCount; ++j) {
            PrintLog(_T("\t%s"), resultData[i * colCount + j]);
        }
    }

    sqlite3_free_table(resultData);
    return 1;
}

// 로그 출력
int PrintLog(TCHAR* format, ...) {
    va_list args;
    int length;

    if (!editBox) return 0;

    va_start(args, format);
    length = _vsntprintf(outputBuffer, MAX_BUFFER_SIZE, format, args);
    va_end(args);

    int textLength = GetWindowTextLength(editBox);
    if (textLength + length > 30000) {
        SendMessage(editBox, EM_SETSEL, 0, 10000);
        SendMessage(editBox, WM_CLEAR, 0, 0);
        textLength -= 10000;
    }

    SendMessage(editBox, EM_SETSEL, textLength, textLength);
    SendMessage(editBox, EM_REPLACESEL, 0, (LPARAM)outputBuffer);

    return length;
}

// 실시간 주식 데이터 읽기
bool FetchStockData(StockRecord& record) {
    char buffer[1024] = "";
    char* token;
    char* nextToken = nullptr;
    DWORD bytesRead = 1;

    sprintf(record.symbol, "http://qt.gtimg.cn/q=%s", record.symbol);

    HINTERNET session = InternetOpen(_T("StockFetcher"), INTERNET_OPEN_TYPE_PRECONFIG, nullptr, nullptr, 0);
    if (!session) return false;

    HINTERNET request = InternetOpenUrl(session, record.symbol, nullptr, 0,
                                       INTERNET_FLAG_DONT_CACHE, 0);
    if (!request) {
        InternetCloseHandle(session);
        return false;
    }

    InternetReadFile(request, buffer, 1023, &bytesRead);
    buffer[bytesRead] = '\0';

    token = strtok_s(buffer, "~", &nextToken);
    token = strtok_s(nullptr, "~", &nextToken);
    token = strtok_s(nullptr, "~", &nextToken);
    token = strtok_s(nullptr, "~", &nextToken);
    record.currentPrice = atof(token);
    token = strtok_s(nullptr, "~", &nextToken);
    record.previousClose = atof(token);
    record.priceChange = (record.currentPrice - record.previousClose) / record.previousClose;

    InternetCloseHandle(request);
    InternetCloseHandle(session);

    return true;
}

메뉴 리소스 정의:

#define IDM_CREATETABLE         32773
#define IDM_DELETETABLE         32774
#define IDM_INSERTDATA          32775
#define IDM_SQLDATA             32776

/////////////////////////////////////////////////////////////////////////////
//
// Menu
//

IDC_STOCKCOLLECTOR MENU
BEGIN
    POPUP "&파일"
    BEGIN
        MENUITEM "테이블 생성",     IDM_CREATETABLE
        MENUITEM "테이블 삭제",     IDM_DELETETABLE
        MENUITEM "테스트 삽입",     IDM_INSERTDATA
        MENUITEM "데이터 조회",     IDM_SQLDATA
        MENUITEM "종료",           IDM_EXIT
    END
    POPUP "&도움말"
    BEGIN
        MENUITEM "&정보...",       IDM_ABOUT
    END
END

태그: SQLite C++ Windows API WinInet real-time data

7월 17일 19:17에 게시됨

괴물 클럽

주식 데이터를 SQLite로 저장하는 실시간 수집 시스템

인기 태그