C++ 连接postgresql数据库，进行数据筛选

/*
 * db.cpp
 *
 *  Created on: Mar 7, 2018
 *      Author: zlf
 */

#include <iostream>
#include <pqxx/pqxx>
#include <time.h>

#include <vector>
#include <string>
#include <string.h>
#include <sstream>
#include <iostream>

using namespace std;
using namespace pqxx;

//=======================================================//
struct DIAGNOSIS {

	string patient_id;
	string visit_number;
	int sex;
	string time_begin;
	string time_end;
	string icd;
	string name;

};
//=======================================================//
void str2int(int &int_temp, const string &string_temp) {
	stringstream stream(string_temp);
	stream >> int_temp;
}

//=======================================================//
int interval_date(string begin, string end) {

	string a1, a2, a3, b1, b2, b3;
	a1 += begin[0];
	a1 += begin[1];
	a1 += begin[2];
	a1 += begin[3];

	a2 += begin[5];
	a2 += begin[6];

	a3 += begin[8];
	a3 += begin[9];

	b1 += end[0];
	b1 += end[1];
	b1 += end[2];
	b1 += end[3];

	b2 += end[5];
	b2 += end[6];

	b3 += end[8];
	b3 += end[9];

	//cout << "begin = " << a1 << " " << a2 << " " << a3 << endl;
	//cout << "end = " << b1 << " " << b2 << " " << b3 << endl;

	int int_a1, int_a2, int_a3, int_b1, int_b2, int_b3;

	str2int(int_a1, a1);
	str2int(int_a2, a2);
	str2int(int_a3, a3);

	str2int(int_b1, b1);
	str2int(int_b2, b2);
	str2int(int_b3, b3);

	int interval_days;
	interval_days = (int_b1 - int_a1) * 365 + (int_b2 - int_a2) * 30
			+ (int_b3 - int_a3);

	//cout << "interval_days = " << interval_days << endl;

	return interval_days;
}

//=======================================================//
int interval_records(DIAGNOSIS begin, DIAGNOSIS end) {
	int interval_days;
	interval_days = interval_date(begin.time_begin, end.time_begin);
	return interval_days;
}

//=======================================================//
bool flag_times_interval(int times, int interval, vector<DIAGNOSIS> vd) {
	DIAGNOSIS origin = vd[0];
	int interval_real;

	int nums = 0;
	for (int p = 1; p < vd.size(); p++) {
		interval_real = interval_records(origin, vd[p]);
		if (interval_real >= interval) {
			nums = nums + 1;
			origin = vd[p];
		}

	}
	if (nums >= times) {
		return true;
	} else {
		return false;
	}

}
//=======================================================//
int select_insert(int times, int interval, vector<DIAGNOSIS> vd) {
	cout << "select_insert() in the records of the same patient:  " << endl;
	connection C(
			"dbname=hello user=postgres password=123 \
	      hostaddr=127.0.0.1 port=5432");

	int num_selected = 0;
	int num_managed = 0;

	int my_flag = 0;
	if (flag_times_interval(times, interval, vd)) {

		for (int p = 1; p < vd.size(); p = p + 1) {
			for (int q = 0; q < p; q = q + 1) {

				if (vd[q].icd == vd[p].icd) { //bu xiang deng
					break;
				} else {
					my_flag = my_flag + 1;
					cout << vd[p].icd << "  " << vd[q].icd << "     "
							<< "p q my_flag: " << p << "   " << q << "    "
							<< my_flag << endl;
				}

			}
			if (my_flag == p) {
				//insert vd[p]
				string sql1 =
						"insert into diagnosis5 (patient_id, visit_number, time_begin, time_end, icd, name) values ('"
								+ vd[p].patient_id + "', '" + vd[p].visit_number
								+ "',    '" + vd[p].time_begin + "', '"
								+ vd[p].time_end + "', '" + vd[p].icd + "', '"
								+ vd[p].name + "');";

				char sql[300];
				strcpy(sql, sql1.c_str());
				/* Create a transactional object. */
				work W(C);

				/* Execute SQL query */
				W.exec(sql);
				W.commit();
				num_selected = num_selected + 1;
			}

			my_flag = 0;
			num_managed = num_managed + 1;
		}

	}
	C.disconnect();
	return num_selected;
}

//=======================================================//
void record2struct(result::const_iterator c, DIAGNOSIS *d) {
	d->patient_id = c[0].as<string>();
	d->visit_number = c[1].as<string>();
	d->sex = c[2].as<int>();
	d->time_begin = c[3].as<string>();
	d->time_end = c[4].as<string>();
	d->icd = c[5].as<string>();
	d->name = c[6].as<string>();
}

//=======================================================//
//=======================================================//
int main(int argc, char* argv[]) {
	int times = 2;
	int interval = 2;
	char * sql0;

	try {
		connection C0(
				"dbname=hello user=postgres password=123 \
      hostaddr=127.0.0.1 port=5432");
		if (C0.is_open()) {
			cout << "Opened database successfully: " << C0.dbname() << endl;
		} else {
			cout << "Can't open database" << endl;
			return 1;
		}
		/* Create SQL statement */
		//sql = "select count(*) as num1 from diagnosis1";
		//sql = "select * into diagnosis2 from diagnosis1 where icd is not null";
		//sql = "select count(*) as num2 from diagnosis2";
		//sql = "select * from diagnosis2 order by VisitNumber asc";
		//sql = "select count(patient_id) ,patient_id from diagnosis2  group by patient_id having count(patient_id)>3";
		//sql = "select count(patient_id), patient_id into diagnosis3 from diagnosis2  group by patient_id having count(patient_id)>3";
		//sql = "select * into diagnosis4 from diagnosis2 where patient_id in (select patient_id from diagnosis3)";
		sql0 = "select * from diagnosisx1 order by patient_id, time_begin asc";

		/* Create a non-transactional object. */
		nontransaction N(C0);

		/* Execute SQL query */
		result R(N.exec(sql0));

		int num = 0;
		/* List down all the records */

		for (result::const_iterator c = R.begin(); c != R.end(); ++c) {

			cout << "PatientID = " << c[0].as<string>() << "  ";
			//cout << "VisitNumber = " << c[1].as<string>() << "  ";
			//cout << "Sex = " << c[2].as<int>() << "  ";
			cout << "time_begin = " << c[3].as<string>() << "  ";
			//cout << "time_end = " << c[4].as<string>() << "  ";
			//cout << "interval_date = " << interval_date(c[3].as<string>(), c[4].as<string>()) << "  ";
			cout << "ICD = " << c[5].as<string>() << "  ";
			cout << "name = " << c[6].as<string>() << "  ";
			num = num + 1;
			cout << "num= " << num << endl;
			if (num > 5000) {
				break;
			}
		}

		result::const_iterator p0 = R.begin();
		DIAGNOSIS d;
		record2struct(p0, &d);
		vector<DIAGNOSIS> vd;
		vd.push_back(d);

		int num_managed = 0;
		int num_selected = 0;

		result::const_iterator p1 = R.begin();
		
		for (int i = 0; i < R.size() - 1; i = i + 1) {			//
			cout << "i:::::" << i << endl;
			p1 = p1 + 1;
			num_managed++;
			cout << "The number of the records of all patients been managed:  "
					<< num_managed << endl;

			if (num_managed > 5000) {
				break;
			}

			p0 = p1 - 1;

			if (p0[0].as<string>() == p1[0].as<string>() && i < R.size() - 2) {
				record2struct(p1, &d);
				vd.push_back(d);
				cout << "The same patient" << endl;
			} else {
				if (i == R.size() - 2)			//p==R.size()-1,last record
						{
					record2struct(p1, &d);
					vd.push_back(d);
					num_selected = select_insert(times, interval, vd);
					cout
							<< "The number of the records of the same patient been selected:  "
							<< num_selected << endl;
					cout
							<< "Now all the records of the same patient have been push_back()"
							<< endl;
					cout
							<< "The last record of the last patient have been push_back()"
							<< endl;

				} else {
					record2struct(p1, &d);
					num_selected = select_insert(times, interval, vd);
					cout
							<< "The number of the records of the same patient been selected:  "
							<< num_selected << endl;
					cout
							<< "Now all the records of the same patient have been push_back()"
							<< endl;

					vd.clear();
					vd.push_back(d);
				}

			}
		}
		

		for (int i = 0; i < vd.size(); i++) {
			cout << "The ICD of the last patient: " << i << "  " << vd[i].icd
					<< endl;
		}

		cout << "Operation done successfully" << endl;
		C0.disconnect();
	} catch (const std::exception &e) {
		cerr << e.what() << std::endl;
		return 1;
	}

	return 0;
}

/*

 g++ $(pkg-config --libs libpqxx) Select.cpp -lpqxx -lpq -o Select

 */