C++连接Postgresql数据库进行数据预筛选（代码教程）

c++连接postgresql进行数据预筛选（代码教程）

/*
 * preselect.cpp
 *
 *  created on: mar 14, 2018
 *      author: zlf
 */

#include 
#include 
 
  
#include 

#include 
#include 
#include 
#include 
#include 

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() << "  ";
				cout << "visitnumber = " << c[1].as() << "  ";
				cout << "sex = " << c[2].as() << "  ";
				cout << "time_begin = " << c[3].as() << "  ";
				cout << "time_end = " << c[4].as() << "  ";
				cout << "icd = " << c[5].as() << "  ";
				cout << "name = " << c[6].as() << "  ";
				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

 */