欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

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

程序员文章站 2022-06-30 08:39:59
c++连接postgresql进行数据预筛选(代码教程) /* * preselect.cpp * * created on: mar 14, 2018 * auth...

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

 */