C++连接Postgresql数据库进行数据预筛选（1）

/*
 * PreSelect.cpp
 *
 *  Created on: Mar 14, 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;


//=======================================================//
//=======================================================//
int main(int argc, char* argv[]) {

	char * sql_delete0;
	char * sql_delete1;
	char * sql_delete2;

	char * sql0;
	char * sql1;
	char * sql2;
	char * sql3;
	char * sql4;
	char * sql5;

	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_delete0 = "drop table if exists diagnosis2";
		sql_delete1 = "drop table if exists diagnosis3";
		sql_delete2 = "drop table if exists diagnosis4";

		sql0 = "select * into diagnosis2 from diagnosis1 where icd is not null";

		sql1 =
				"select count(patient_id), patient_id into diagnosis3 from diagnosis2  group by patient_id having count(patient_id)>3";

		sql2 =
				"select * into diagnosis4 from diagnosis2 where patient_id in (select patient_id from diagnosis3) order by patient_id, time_begin asc";

		sql4 = "create table if not exists diagnosis5 ("
				"patient_id character varying,"
				"visit_number character varying,"
				"sex integer,"
				"time_begin timestamp without time zone,"
				"time_end timestamp without time zone,"
				"icd character varying,"
				"name character varying );";

		sql5 = "delete from diagnosis5";

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

		/* Execute SQL query */
		result R0(N.exec(sql_delete0));
		result R1(N.exec(sql_delete1));
		result R2(N.exec(sql_delete2));
		result R3(N.exec(sql0));
		result R4(N.exec(sql1));
		result R5(N.exec(sql2));
		result R6(N.exec(sql4));
		result R7(N.exec(sql5));


		/* List down all the records */
		int num = 0;
		bool show = 0;
		if (show == 1) {

			for (result::const_iterator c = R5.begin(); c != R5.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 << "ICD = " << c[5].as<string>() << "  ";
				cout << "name = " << c[6].as<string>() << "  ";
				num = num + 1;
				cout << "num= " << num << endl;
				if (num > 5000) {
					break;
				}
			}

		}

		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) PreSelect.cpp -lpqxx -lpq -o PreSelect

 */