爬虫实现股票分析（六） - 写代码的小熊猫

代码稍微优化了一下，能见人了，还是先说下遇到的问题：

1.递归的深度是有限制的 -----开始查询最新价日期的股票时，遇到停盘n久或者退市的，会导致递归层数太多，超过350层左右就会退出

2.头文件互相包含或者包含重复，会导致error C4430: 缺少类型说明符 - 假定为 int 问题，有点很鬼

下面时上代码，做了拆分，将数据库连接，查询，断开等放到一个查询类去了，也是为了后面多线程做准备，下面是代码和效果图：

代码：

#ifndef _THIRD_METHODS_H
#define _THIRD_METHODS_H

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

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

using namespace std;

class quePrice
{
public:
	//与数据库建立连接
	bool ConnectDatabase();
	//释放与数据库的连接
	void FreeConnect();
	//查询历史最高价
	float queryHighestPrice(string strStock);
	//查询最新成交日日期价格，即跳过停盘或者非交易日，离今天最近的一天的成交价
	float newDayPrice(string dayTime,string strStock);
	//查询当天日期
	string queryToday();
	//设置递归深度
	void setDepth(int n );

private:
	//查询昨天的日期
	string queryYesterday(string dateTime);
	//查询某个月的天数
	int queryMonthTotalDays(int year,int month);


private:
	MYSQL mysql;
	char field[32][32]; //存字段名二维数组
	MYSQL_RES *res;     //行的一个查询结果集
	MYSQL_ROW column;   //数据行的列
	char query[150];    //查询语句
	int depth ;			//控制递归查询的深度

};






#endif

#include "thirdMethods.h"

bool quePrice::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 quePrice::FreeConnect()
{
    mysql_free_result(res);
    mysql_close(&mysql);
}


float quePrice::queryHighestPrice(string strStock)
{
    float highestPrice = 0.0;//这边必须赋初值，因为万一有表没有数据会出问题
    string strsql = "select * from stock_" + strStock + " order by  closePrice desc limit 1 ";

    sprintf_s(query, strsql.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 mysql_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 quePrice::newDayPrice(string dayTime,string strStock)
{
    if(--depth  < 0)
    {
        //cout << "can not get " + strStock + " price." << endl;
        return 0;
    }
    float nNewDayPrice = 0.0;
    string tempQuery = "select * from stock_" + strStock + " 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,strStock);
        }
        else
        {
            while (column = mysql_fetch_row(res))
            {
                string todayPrice = column[3];
                nNewDayPrice = atof(todayPrice.c_str());
            }
        }            
    }

    //cout << strStock <<" newDayPrice ok:" << nNewDayPrice << endl;
    return nNewDayPrice;
}

//查询当天日期
string quePrice::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 quePrice::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 quePrice::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;
}

void quePrice::setDepth(int n)
{
    depth = n;
}

#include "thirdMethods.h"

using namespace std;


int main(int argc, char **argv)
{	
	clock_t startTime,endTime,consumeTime;
	startTime = clock();
	
	const int stockFields = 15;
	float biaozhun = 0.33333;
	float highestPrice = 0.0;
	float nNewDayPrice = 0.0;
	float bizhi = 0.0;
	string strTodayDate  = "";

	cout.setf(ios::left,ios::adjustfield);
	ofstream oStockSelect("D:\\data\\python\\test\\select.txt",ios::app); 
	
	quePrice oquePrice;
	
    oquePrice.ConnectDatabase();
	for(long i = 600000;i < 604000;i++)
	{
		oquePrice.setDepth(20);//
		char stock[8];
		sprintf(stock,"%d",i);
		string strStock = stock;
		cout << "query the " <<  strStock << endl;
		highestPrice = oquePrice.queryHighestPrice(strStock);
		strTodayDate = oquePrice.queryToday();
		nNewDayPrice = oquePrice.newDayPrice(strTodayDate,strStock);

		//cout << "highestPrice:" << highestPrice << endl;
		//cout << "strTodayDate:" << strTodayDate << endl;
		//cout << "nNewDayPrice:" << nNewDayPrice << endl;
		bizhi = nNewDayPrice/highestPrice;
		if( bizhi < biaozhun)
		{
			oStockSelect << strStock  << setw(12)  << bizhi   << setw(12) <<  nNewDayPrice << endl;
		}
	}
	oquePrice.FreeConnect();

	endTime = clock();
	consumeTime = endTime - startTime;
	cout << "consume  Time:" << consumeTime << endl;
    system("pause");
    return 0;
}

最终效果图：

加了一个计时，也是为了和多线程的做对比，目前执行时间是：33363ms，效果图

明显是单线程跑出来的

为了可移植性，这次准备试下 windows 安装支持 pthread库，这样就可以再linux也用同一套多线的代码

写代码的小熊猫~开开心心每一天：）

发表于 2019-03-15 22:21 写代码的小熊猫阅读(604) 评论(0) 编辑收藏举报