爬虫实现股票分析（五）

这边实际上有点偏了，是c++实现具体的数据分析，但是标题连贯性就不改了~

 

注意点：

1.c++的 string字符串，c风格字符串，char *等连接方式

2.sprintf函数使用（记得补齐日期前面的0）

3.mysql_store_result函数，只要sql语句执行成功即返回结果集不为空，需要mysql_affected_rows判断是否有有效函数

 

补充源码：

#include <iostream>
#include <string>
#include <Windows.h>
#include <WinSock.h>  
#include <mysql.h> 
#include <time.h>

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


using namespace std;

const int stockFields = 15;

MYSQL mysql;
char field[32][32]; //存字段名二维数组
MYSQL_RES *res;     //行的一个查询结果集
MYSQL_ROW column;   //数据行的列
char query[150];    //查询语句

//函数声明
bool ConnectDatabase();
void FreeConnect();

//查询历史最高价
float queryHighestPrice();
//查询最新成交日日期价格，即跳过停盘或者非交易日，离今天最近的一天的成交价
float newDayPrice(string dayTime);
//查询当天日期
string queryToday();
//查询某个月的天数
int queryMonthTotalDays(int year,int month);
//查询昨天的日期
string queryYesterday(string dateTime);


int main(int argc, char **argv)
{
    float highestPrice = 0.0;
    float nNewDayPrice = 0.0;
    string strTodayDate  = "";
    ConnectDatabase();
    //QueryDatabase();
    
    highestPrice = queryHighestPrice();
    strTodayDate = queryToday();
    nNewDayPrice = newDayPrice(strTodayDate);

    FreeConnect();

    cout << "highestPrice:" << highestPrice << endl;
    cout << "strTodayDate:" << strTodayDate << endl;
    cout << "nNewDayPrice:" << nNewDayPrice << endl;
    system("pause");
    return 0;
}

//连接数据库
bool ConnectDatabase(){
    //Gets or initializes a MYSQL structure
    mysql_init(&mysql); 

    // Connects to a MySQL server
    const char host[] = "localhost";
    const char user[] = "root";
    const char passwd[] = "Test_123";
    const char db[] = "stockDataBase";
    unsigned int port = 3306;
    const char *unix_socket = NULL;
    unsigned long client_flag = 0;

    if (mysql_real_connect(&mysql, host, user, passwd, db, port, unix_socket, client_flag))
    {
        printf("The connection was successful.\n");
        return true;
    }
    else
    {
        printf("Error connecting to database:%s\n", mysql_error(&mysql));
        return false;
    }
}

//释放资源
void FreeConnect()
{
    mysql_free_result(res);
    mysql_close(&mysql);
}


//查询历史最高价
float queryHighestPrice()
{
    float highestPrice;
    sprintf_s(query, "select * from stock_600000 order by  closePrice desc limit 1 ");
    mysql_query(&mysql, "set names gbk");
    if (mysql_query(&mysql, query))
    {
        printf("Query failed (%s)\n", mysql_error(&mysql));
        return false;
    }
    else{
        printf("query success\n");
    }
    res = mysql_store_result(&mysql);
    if (!res){
        printf("Couldn't get result from %s\n", mysql_error(&mysql));
        return false;
    }
    while (column = mysql_fetch_row(res)){
        string tempHighPri = column[3];
        highestPrice = atof(tempHighPri.c_str());
    }

    return highestPrice;
}




//查询最新成交日日期价格
float newDayPrice(string dayTime)
{
    
    float nNewDayPrice = 0.0;
    string tempQuery = "select * from stock_600000 where dateTime = '"  + dayTime + "'" ;

    sprintf_s(query, tempQuery.c_str());
    mysql_query(&mysql, "set names gbk");
    if (mysql_query(&mysql, query))
    {
        printf("Query failed (%s)\n", mysql_error(&mysql));
        return false;
    }
    else
    {
        printf("query success\n");
    }
    res = mysql_store_result(&mysql);
    if (!res) //这边理论上需要优化，万一优质股票一直没有价格，就陷入死循环了
    {
        cout << "can not get result" << endl;        
    }
    else
    {
        if(0 == mysql_affected_rows(&mysql))
        {
            cout << "can not get " + dayTime + " price." << endl;  
            string tempDay =  queryYesterday(dayTime);
            nNewDayPrice = newDayPrice(tempDay);
        }
        else
        {
            while (column = mysql_fetch_row(res))
            {
                string todayPrice = column[3];
                nNewDayPrice = atof(todayPrice.c_str());
            }
        }            
    }    
    return nNewDayPrice;
}

//查询当天日期
string queryToday()
{
    //query today date strToday
    time_t nowtime;
    tm  * pt;
    char strTime[20];

    time(&nowtime);
    pt = localtime(&nowtime);
    strftime(strTime,20,"%Y-%m-%d",pt);
    string strToday = strTime;
    return strToday;
}



//查询某个月的天数
int queryMonthTotalDays(int year,int month)
{
    if(2 == month && 0 == year%4)
    {
        return 29;
    }

    switch(month)
    {
    case 1:
    case 3:
    case 5:
    case 7:
    case 8:
    case 10:
    case 12:
        return 31;
    case 2:
        return 28;
    case 4:
    case 6:
    case 9:
    case 11:
        return 30;
    default:
        return 0;
    }
}


//查询昨天的日期
string queryYesterday(string dateTime)
{
    string strYear = dateTime.substr(0,4);
    string strMonth = dateTime.substr(5,2);
    string strDay = dateTime.substr(8,2);

    int nYear = atoi(strYear.c_str());
    int nMonth = atoi(strMonth.c_str());
    int nDay = atoi(strDay.c_str());

    int tempDay = 0;
    int tempMonth = nMonth;
    int tempYear = nYear;

    if(nDay > 1)
    {
        tempDay =  nDay - 1;
    }
    else
    {
        if(nMonth > 1)
        {
            tempMonth = tempMonth -1;
            tempDay = queryMonthTotalDays(tempYear,tempMonth);
        }
        else
        {
            tempYear = tempYear -1;
            tempMonth = 12;
            tempDay = 31;
        }
    }

    string lianjiefu = "-";

    //string tempDateTime = tempYear + lianjiefu + tempMonth + lianjiefu + tempDay; //c++不能像java直接+数字和字符串
    char str1[10];
    sprintf(str1,"%04d",tempYear);
    char str2[10];
    sprintf(str2,"%02d",tempMonth);
    char str3[10];
    sprintf(str3,"%02d",tempDay);

    string tempDateTime = str1 + lianjiefu + str2 + lianjiefu + str3; //error C2110: “+”: 不能添加两个指针，即 str1 + "-" + str2 也不行

    return tempDateTime;
}

 

缺点：

1.命名规范

2.魔鬼数字

3.安全函数使用

4.功能目前只实现了 查询出某只股票的最高价，以及离今天最近的一天的成交日的价格，大概执行效果

 

 

下一步是实现：能查询所有 上海股票的最近价格与历史最高价比，并筛选出比值少于1/3的

生命在于运动，知识在于积累

写代码的小熊猫~       ：）