用SQLite 记录采集到的股票数据

定时从网上读取实时股票数据，并用SQLite数据库记录到本地，

数据库在运行时会自动创建，先用菜单创建数据表，每十秒钟记录一次数据，可以用菜单中的查询查看记录的数据。

程序编译时用ASC字符集，unicode 在接口要转换太繁琐了。

效果如图：

 

 代码如下：

// XSQLite.cpp: 采集股票数据放进数据库中
// sqlite-amalgamation 下的sqlite3.c，sqlite3.h，加入工程。sqlite3.c，不使用预编译头文件
// 开始用unicode字符集，太繁琐了，还是改回ASC比较方便
//XGZ 2022-4-27 SZ

#include "stdafx.h"
#include "XSQLite.h"
#include "stdio.h"
#include "sqlite3.h"

#include <WinInet.h>
#pragma comment(lib, "Wininet.lib")

#define MAX_LOADSTRING 100
// Global Variables:
HINSTANCE hInst;                                // current instance
TCHAR szTitle[MAX_LOADSTRING];                    // The title bar text
TCHAR szWindowClass[MAX_LOADSTRING];            // the main window class name

// Forward declarations of functions included in this code module:
ATOM                MyRegisterClass(HINSTANCE hInstance);
BOOL                InitInstance(HINSTANCE, int);
LRESULT CALLBACK    WndProc(HWND, UINT, WPARAM, LPARAM);

#define IDC_EDIT               1000 
struct XSTOCK
{
    TCHAR url[256];
    TCHAR   time[20];
    TCHAR   code[10];
    double    RealValue;
    double    LastValue;
    double    change;
};

HWND m_hWndEdit;
TCHAR buffer[1024];
TCHAR wStr[1024];
TCHAR StatusText[256];
XSTOCK stXStock;

sqlite3 *db;  
char *szErrMsg;  
int rc;  
char sql[1024];

int PRINT(TCHAR *fmt, ...);
static int sqlcallback(void *NotUsed, int argc, char **argv, char **azColName);    
BOOL ReadStock(XSTOCK &stXStock);
int Open(HWND hWnd);
int InsertStockData(XSTOCK &stXStock);

int OnCreate(HWND, UINT, WPARAM, LPARAM);
int OnSize(HWND, UINT, WPARAM, LPARAM);
int OnPaint(HWND, UINT, WPARAM, LPARAM);
int OnTimer(HWND, UINT, WPARAM, LPARAM);
int OnCreateTable(HWND, UINT, WPARAM, LPARAM);
int OnDeleteTable(HWND, UINT, WPARAM, LPARAM);
int OnInsertData(HWND, UINT, WPARAM, LPARAM);
int OnSQLData(HWND, UINT, WPARAM, LPARAM);

int APIENTRY _tWinMain(HINSTANCE hInstance,
                     HINSTANCE hPrevInstance,
                     LPTSTR    lpCmdLine,
                     int       nCmdShow)
{
    UNREFERENCED_PARAMETER(hPrevInstance);
    UNREFERENCED_PARAMETER(lpCmdLine);

     // TODO: Place code here.
    MSG msg;
    HACCEL hAccelTable;

    // Initialize global strings
    LoadString(hInstance, IDS_APP_TITLE, szTitle, MAX_LOADSTRING);
    LoadString(hInstance, IDC_XSQLITE, szWindowClass, MAX_LOADSTRING);
    MyRegisterClass(hInstance);

    // Perform application initialization:
    if (!InitInstance (hInstance, nCmdShow))
    {
        return FALSE;
    }

    hAccelTable = LoadAccelerators(hInstance, MAKEINTRESOURCE(IDC_XSQLITE));

    // Main message loop:
    while (GetMessage(&msg, NULL, 0, 0))
    {
        if (!TranslateAccelerator(msg.hwnd, hAccelTable, &msg))
        {
            TranslateMessage(&msg);
            DispatchMessage(&msg);
        }
    }

    return (int) msg.wParam;
}

ATOM MyRegisterClass(HINSTANCE hInstance)
{
    WNDCLASSEX wcex;

    wcex.cbSize = sizeof(WNDCLASSEX);

    wcex.style            = CS_HREDRAW | CS_VREDRAW;
    wcex.lpfnWndProc    = WndProc;
    wcex.cbClsExtra        = 0;
    wcex.cbWndExtra        = 0;
    wcex.hInstance        = hInstance;
    wcex.hIcon            = LoadIcon(hInstance, MAKEINTRESOURCE(IDI_XSQLITE));
    wcex.hCursor        = LoadCursor(NULL, IDC_ARROW);
    wcex.hbrBackground    = (HBRUSH)(COLOR_WINDOW+1);
    wcex.lpszMenuName    = MAKEINTRESOURCE(IDC_XSQLITE);
    wcex.lpszClassName    = szWindowClass;
    wcex.hIconSm        = LoadIcon(wcex.hInstance, MAKEINTRESOURCE(IDI_SMALL));

    return RegisterClassEx(&wcex);
}

BOOL InitInstance(HINSTANCE hInstance, int nCmdShow)
{
   HWND hWnd;

   hInst = hInstance; // Store instance handle in our global variable

   hWnd = CreateWindow(szWindowClass, szTitle, WS_OVERLAPPEDWINDOW,
      CW_USEDEFAULT, 0, CW_USEDEFAULT, 0, NULL, NULL, hInstance, NULL);

   if (!hWnd)
   {
      return FALSE;
   }

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

   return TRUE;
}

LRESULT CALLBACK WndProc(HWND hWnd, UINT message, WPARAM wParam, LPARAM lParam)
{
    int wmId, wmEvent;

    switch (message)
    {
    case WM_CREATE:
        OnCreate(hWnd, message, wParam, lParam);
        break;
    case WM_SIZE:
        OnSize(hWnd, message, wParam, lParam);
        break;
    case WM_PAINT:
        OnPaint(hWnd, message, wParam, lParam);
        break;
    case WM_TIMER:
        OnTimer(hWnd, message, wParam, lParam);
        break;
    case WM_COMMAND:
        wmId    = LOWORD(wParam);
        wmEvent = HIWORD(wParam);
        // Parse the menu selections:
        switch (wmId)
        {
        case IDM_CREATETABLE:
            OnCreateTable(hWnd, message, wParam, lParam);
            break;
        case IDM_DELETETABLE:
            OnDeleteTable(hWnd, message, wParam, lParam);
            break;
        case IDM_INSERTDATA:
            OnInsertData(hWnd, message, wParam, lParam);
            break;
        case IDM_SQLDATA:
            OnSQLData(hWnd, message, wParam, lParam);
            break;
        case IDM_ABOUT:
            MessageBox(hWnd, _T("SQLite Test by XGZ 2022-4-27 SZ"),_T("MSG"),MB_OK);
            break;
        case IDM_EXIT:
            DestroyWindow(hWnd);
            break;
        default:
            return DefWindowProc(hWnd, message, wParam, lParam);
        }
        break;

    case WM_DESTROY:
        sqlite3_close(db);
        PostQuitMessage(0);
        break;
    default:
        return DefWindowProc(hWnd, message, wParam, lParam);
    }
    return 0;
}

//sql执行回调函数
int sqlcallback(void *NotUsed, int argc, char **argv, char **azColName)
{  
    int i;  
    for(i=0; i<argc; i++){  
        PRINT(_T("%s = %s\n"), azColName[i], argv[i]?argv[i]:"NULL");  
    }  
    PRINT(_T("\r\n"));  
    return 0;  
}  
//打开数据库
int Open(HWND hWnd)
{
    rc=sqlite3_open("test.db", &db);  /* open database */  
    if(rc)
    {  
        PRINT(_T("\r\n<ERR>Can't open database: %s\n"), sqlite3_errmsg(db));
        sqlite3_close(db);  
        return -1; 
    }  
    wsprintf(StatusText, _T("  test.db open"));
        
    return 0;
}

int OnCreate(HWND hWnd, UINT message, WPARAM wParam, LPARAM lParam)
{
    m_hWndEdit = CreateWindow(TEXT("edit"),NULL,
        WS_CHILD|WS_BORDER|WS_VISIBLE|ES_MULTILINE|WS_VSCROLL,
        0,0,0,0,
        hWnd, (HMENU) IDC_EDIT, hInst, NULL);
    Open(hWnd);
    
    SetTimer(hWnd,1,10000,NULL);   //10s
    return 1;
}

//留一个状态条的位置
int OnSize(HWND hWnd, UINT message, WPARAM wParam, LPARAM lParam)
{
    int cxClient,cyClient;

    cxClient = LOWORD (lParam);
    cyClient = HIWORD (lParam);

    MoveWindow(m_hWndEdit, 0, 0, cxClient, cyClient-20, TRUE);
    return DefWindowProc(hWnd, message, wParam, lParam);

}

//窗口底部显示状态
int OnPaint(HWND hWnd, UINT message, WPARAM wParam, LPARAM lParam)
{
    PAINTSTRUCT ps;
    HDC hdc;
    RECT rt;

    hdc = BeginPaint(hWnd, &ps);
    GetClientRect(hWnd, &rt);
    DrawText(hdc, StatusText, strlen(StatusText), &rt, DT_LEFT|DT_BOTTOM|DT_SINGLELINE);
    EndPaint(hWnd, &ps);

    return 1;
}

//定时读取股票数据，显示并记录
int OnTimer(HWND hWnd, UINT message, WPARAM wParam, LPARAM lParam)
{
    SYSTEMTIME time;
    GetSystemTime(&time);
    PRINT(_T("\r\n Time: %04d-%02d-%02d %02d:%02d:%02d "),
        time.wYear, time.wMonth,time.wDay, time.wHour,time.wMinute,time.wSecond);

    sprintf(stXStock.time,  _T("%04d-%02d-%02d %02d:%02d:%02d "),
        time.wYear, time.wMonth,time.wDay, time.wHour,time.wMinute,time.wSecond);

    strcpy(stXStock.code,  _T("sh000001")) ;
    if(ReadStock( stXStock))
    {
        PRINT( _T(" |%s  %8.2f  %8.4f%% "), stXStock.code, stXStock.RealValue,  stXStock.change *100.0 );
        InsertStockData(stXStock);
    }

    strcpy(stXStock.code,  _T("sz399001")) ;
    if(ReadStock( stXStock))
    {
        PRINT( _T(" |%s  %8.2f  %8.4f%% "), stXStock.code, stXStock.RealValue,  stXStock.change *100.0 );
        InsertStockData(stXStock);
    }
    
    strcpy(stXStock.code,  _T("sh601398")) ;
    if(ReadStock( stXStock))
    {
        PRINT( _T(" |%s  %8.2f  %8.4f%% "), stXStock.code, stXStock.RealValue,  stXStock.change *100.0 );
        InsertStockData(stXStock);
    }
    
    strcpy(stXStock.code,  _T("sz000063")) ;
    if(ReadStock( stXStock))
    {
        PRINT( _T(" |%s  %8.2f  %8.4f%% "), stXStock.code, stXStock.RealValue,  stXStock.change *100.0 );
        InsertStockData(stXStock);
    }
    
    return 1;
}

//创建表
int OnCreateTable(HWND hWnd, UINT message, WPARAM wParam, LPARAM lParam)
{
    strcpy(sql,"create table  TStock(StockCode text, Time text, RealValue real );");

    rc=sqlite3_exec(db, sql, sqlcallback,0,&szErrMsg);  /* execute SQL statement */  
    if(rc!=SQLITE_OK){  
        PRINT(_T("\r\n<ERR>SQL error: %s"),szErrMsg);  
        sqlite3_free(szErrMsg);  
        return -1;
    } 
    
    PRINT(_T("\r\n CreateTable OK"));
    
    return 1;
}

//删掉表
int OnDeleteTable(HWND hWnd, UINT message, WPARAM wParam, LPARAM lParam)
{

    strcpy(sql,"DROP TABLE TStock;");

    rc=sqlite3_exec(db, sql, sqlcallback,0,&szErrMsg);  /* execute SQL statement */  
    if(rc!=SQLITE_OK)
    { 
        PRINT(_T("\r\n<ERR>SQL error: %s"),szErrMsg);  
        sqlite3_free(szErrMsg);  
        return -1;
    } 
    PRINT(_T("\r\n DeleteTable OK"));
    return 1;
}

//测试用数据插入
int OnInsertData(HWND hWnd, UINT message, WPARAM wParam, LPARAM lParam)
{
    strcpy(sql,"insert into TStock values('sh000001', '2022-1-1 12:10',  3011.23);");
    rc=sqlite3_exec(db, sql, sqlcallback,0,&szErrMsg);  /* execute SQL statement */  
    if(rc!=SQLITE_OK){  
        if(NULL != szErrMsg)
        {
            PRINT(_T("\r\n<ERR>SQL error: %s"),szErrMsg);  
            
            sqlite3_free(szErrMsg);    
        }
        PRINT(_T("\r\n<OK>Insert success!"));  
    } 

    return 1;
}

//数据库插入
int InsertStockData(XSTOCK &stXStock)
{
    sprintf(sql,"insert into TStock values('%s', '%s',  %8.2f);", stXStock.code, stXStock.time, stXStock.RealValue);
    rc=sqlite3_exec(db, sql, sqlcallback,0,&szErrMsg);  /* execute SQL statement */  
    if(rc!=SQLITE_OK)
    {  
        if(NULL != szErrMsg)
        {
            PRINT(_T("\r\n<ERR>SQL error: %s"),szErrMsg);  
            sqlite3_free(szErrMsg);    
        }
        PRINT(_T("\r\n<OK>Insert success!"));  
    } 

    return 1;
}

//查询记录的数据
int OnSQLData(HWND hWnd, UINT message, WPARAM wParam, LPARAM lParam)
{
    int i,j;

    //strcpy(sql,"select * from sqlite_master"); 
    strcpy(sql,"select * from TStock"); 
    
    char **sresult;
    int nrow = 0, ncolum = 0;

    rc=sqlite3_get_table(db, sql, &sresult, &nrow, &ncolum,&szErrMsg);  /* execute SQL statement */  
    if(rc!=SQLITE_OK)
    {  
        if(NULL != szErrMsg)
        {
            PRINT(_T("\r\n<ERR>SQL error: %s\n"),szErrMsg);  
            sqlite3_free(szErrMsg);  
        }
        PRINT(_T("\r\n<OK>select success!"));  
    } 

    PRINT(_T("\r\n row:%d, colum:%d"), nrow, ncolum);

    for(i=0;i<nrow+1; i++)
    {
        PRINT(_T("\r\n"));
        for(j = 0; j< ncolum;j++)
        {
            PRINT(_T("\t%s"), sresult[i* ncolum+j]);
        }
    }
    sqlite3_free_table(sresult);

    return 1;
}

//打印输出
int PRINT(TCHAR *fmt, ...)
{
    va_list argptr;
    int cnt;

    int iEditTextLength;
    HWND hWnd;

    if(NULL == m_hWndEdit) return 0; 

    va_start(argptr, fmt);
    cnt = vsprintf(buffer, fmt, argptr);
    va_end(argptr);

    iEditTextLength = GetWindowTextLength(m_hWndEdit);
    if(iEditTextLength + cnt > 30000)       // edit text max length is 30000
    {
        SendMessage(m_hWndEdit, EM_SETSEL, 0, 10000);
        SendMessage(m_hWndEdit, WM_CLEAR, 0, 0);
        iEditTextLength = iEditTextLength - 10000;
    }
    SendMessage(m_hWndEdit, EM_SETSEL, iEditTextLength, iEditTextLength);
    SendMessage(m_hWndEdit, EM_REPLACESEL,0, (LPARAM) buffer);

    return(cnt);        
}

//读取股票实时数据
BOOL ReadStock(XSTOCK &stXStock)
{
    char strRead[1024]="";
    char *token;
    char *next_token = NULL;
    DWORD strLength = 1;

    sprintf(stXStock.url,  _T("http://qt.gtimg.cn/q=%s"), stXStock.code );

    HINTERNET hSession = InternetOpen(_T("UrlTest"), INTERNET_OPEN_TYPE_PRECONFIG, NULL, NULL, 0);
    if(hSession != NULL)
    {
        HINTERNET hHttp = InternetOpenUrl(hSession, stXStock.url, NULL, 0, INTERNET_FLAG_DONT_CACHE, 0);
        if (hHttp != NULL)
        {
            InternetReadFile(hHttp, strRead, 1023, &strLength);  
            strRead[strLength] = '\0';

            token = strtok_s(strRead, "~", &next_token);  //交易所
            token = strtok_s(NULL, "~", &next_token); //股票名字
            token = strtok_s(NULL, "~", &next_token); //股票代码
            token = strtok_s(NULL, "~", &next_token); //当前价格
            stXStock.RealValue = atof(token);
            token = strtok_s(NULL, "~", &next_token); //昨收
            stXStock.LastValue = atof(token);
            stXStock.change = (stXStock.RealValue - stXStock.LastValue)/stXStock.LastValue;

            return TRUE;
        }
    }

    return FALSE;
};

涉及到的资源，就一个菜单。

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

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

IDC_XSQLITE MENU
BEGIN
    POPUP "&File"
    BEGIN
        MENUITEM "CreateTable",                 IDM_CREATETABLE
        MENUITEM "DeleteTable",                 IDM_DELETETABLE
        MENUITEM "InsertData",                  IDM_INSERTDATA
        MENUITEM "SQLData",                     IDM_SQLDATA
        MENUITEM "E&xit",                       IDM_EXIT
    END
    POPUP "&Help"
    BEGIN
        MENUITEM "&About ...",                  IDM_ABOUT
    END
END