数据库-基于ubuntu系统的postgres数据库的c++接口程序编写完整事例

基于ubuntu系统的postgres数据库的c++接口程序编写完整事例
1. 数据库的创建
创建一个新的数据库，数据库的管理员为：test0，数据库为：test0，密码为：372，地址为：127.0.0.1，端口为：5432。创建命令依次如下：
开启服务器：

mdj@mdj:~$ sudo /etc/init.d/postgresql start

添加新的用户：

mdj@mdj:~$ sudo useradd test0

进入数据库postgres：

mdj@mdj:~$ sudo su -l postgres 
postgres@mdj:~$ psql 

创建数据库的管理者并且设置密码：

postgres=# create user test0 with password '372';

创建属于数据库管理者的数据库（前者为数据库名，后者为用户名）：

postgres=# create database test0 owner test0;

给数据库赋予所有权限（前者为数据库名，后者为用户名）：

postgres=# grant all privileges on database test0 to test0;

postgres=# \q
postgres@mdj:~$ exit 

给新建的数据库设置ip和port并进入（前者为用户名，后者为数据库名）：

mdj@mdj:~$ psql -U test0 -d test0 -h 127.0.0.1 -p 5432
$ psql 

2. 环境配置

sudo apt install libpqxx-dev

3.程序实例
建立连接并创建表的代码：

#include <iostream>
#include <iomanip>
#include <pqxx/pqxx>

using namespace std;
using namespace pqxx;

int main(int argc, char *argv[])
{
	char * sql;
	try{
	    //建立连接
		connection mdj("dbname=test0 user=test0 password=372105 hostaddr=127.0.0.1 port=5432");
		if(mdj.is_open())
		{
			cout<<"opened database successfully: "<<mdj.dbname()<<endl;
		}
		else
		{
			cout << "can't open database"<<"\n";
			return 1;
		}
		//创建表
	    sql = "create table company("
	    "id int primary key not null,"
	    "name text not null,"
	    "age int not null,"
	    "adress char(50),"
	    "salary real);";
	    work w(mdj);
	
	    w.exec(sql);
	    w.commit();

	    cout<<"table created successfully"<<"\n";
		mdj.disconnect();

	}catch(const std::exception &e)
	{
		cerr<<e.what()<<std::endl;
		return 1;
	}
}

新建一个connpgsql.cpp文件，将上述代码复制进去，编译执行：

g++ connpgsql.cpp -lpqxx -lpq

运行执行：

./a.out 

建立连接并向表中插入数据的代码：

#include <iostream>
#include <iomanip>
#include <pqxx/pqxx>

using namespace std;
using namespace pqxx;

int main(int argc, char *argv[])
{
	char * sql;
	try{
	    //建立连接
		connection mdj("dbname=test0 user=test0 password=372105 hostaddr=127.0.0.1 port=5432");
		if(mdj.is_open())
		{
			cout<<"opened database successfully: "<<mdj.dbname()<<endl;
		}
		else
		{
			cout << "can't open database"<<"\n";
			return 1;
		}
		//向表中插入数据
	    sql = "insert into company(id, name, age, adress, salary)"
		"values(1,'ruiy', 23, '朔州', 10000.00);"
		"insert into company (id, name, age, adress, salary)"
		"values(2, 'rui', 21, '合肥', 8000.00);";
	    work w(mdj);
	
	    w.exec(sql);
	    w.commit();

	    cout<<"table created successfully"<<"\n";
		mdj.disconnect();

	}catch(const std::exception &e)
	{
		cerr<<e.what()<<std::endl;
		return 1;
	}
}

新建一个connpgsql.cpp文件，将上述代码复制进去，编译执行：

g++ connpgsql.cpp -lpqxx -lpq

运行执行：

./a.out 

建立连接并向表中查询数据的代码：

#include <iostream>
#include <iomanip>
#include <pqxx/pqxx>

using namespace std;
using namespace pqxx;

int main(int argc, char *argv[])
{
//	输入
	float x = 2.5;
	int a = 7;
	float y = 4.7;
//	计算
	float m = x + a % 3 * (int)(x + y) % 2;
//	输出
	printf("m is d%\n", m);
	char * sql;
	try{
		connection mdj("dbname=test0 user=test0 password=372105 hostaddr=127.0.0.1 port=5432");
		if(mdj.is_open())
		{
			cout<<"opened database successfully: "<<mdj.dbname()<<endl;
		}
		else
		{
			cout << "can't open database"<<"\n";
			return 1;
		}
	       // sql = "create table company("
	      //  "id int primary key not null,"
	      //  "name text not null,"
	      //  "age int not null,"
	      //  "adress char(50),"
	      //  "salary real);";

	       // sql = "insert into company(id, name, age, adress, salary)"
		//"values(1,'ruiy', 23, '朔州', 10000.00);"
		//"insert into company (id, name, age, adress, salary)"
		//"values(2, 'rui', 21, '合肥', 8000.00);";

	        work w(mdj);
	
                result r = w.exec("SELECT * FROM company");
                for (result::const_iterator row = r.begin(); row != r.end(); ++row)
                {
                	for (tuple::const_iterator col = row->begin(); col != row->end();++col)  
                	cout << col->c_str() << '\t';  
            		cout << endl;  
                }
	       // w.exec(sql);
	        w.commit();

	       // cout<<"table created successfully"<<"\n";
               //cout<<"recorded create successfully"<<"\n";


		//mdj.disconnect();

	}catch(const std::exception &e)
	{
		cerr<<e.what()<<std::endl;
		return 1;
	}
}

新建一个connpgsql.cpp文件，将上述代码复制进去，编译执行：

g++ connpgsql.cpp -lpqxx -lpq

运行执行：

./a.out