C/C++连接PostgreSQL数据库
目录
C/C++连接PostgreSQL数据库
本教程将使用libpqxx库,它是PostgreSQL的官方C++客户端API。 libpqxx的源代码可以在BSD许可证下使用,因此您可以免费下载它,将其传递给其他人,更改它,销售,将其包含在您自己的代码中,并与任何人分享您的更改/修改。
安装
最新版本的libpqxx可从链接下载:Libpqxx下载。 所以下载最新版本,并按照以下步骤:
wget http://pqxx.org/download/software/libpqxx/libpqxx-4.0.tar.gz
tar xvfz libpqxx-4.0.tar.gz
cd libpqxx-4.0
./configure
make
make install
在开始使用C/C++的PostgreSQL接口之前,请在PostgreSQL安装目录中找到pg_hba.conf
文件,并添加以下行:
# IPv4 local connections:
host all all 127.0.0.1/32 md5
可以启动/重新启动postgres服务器,使用以下命令运行:
[root@host]# service postgresql restart
Stopping postgresql service: [ OK ]
Starting postgresql service: [ OK ]
C/C++连接到PostgreSQL数据库
以下C
代码段显示如何连接到端口5432
上本地机器上运行的现有数据库。在这里,我使用反斜杠\
行继续。
#include <iostream>
#include <pqxx/pqxx>
using namespace std;
using namespace pqxx;
int main(int argc, char* argv[])
{
try{
connection C("dbname=testdb user=postgres password=cohondob \
hostaddr=127.0.0.1 port=5432");
if (C.is_open()) {
cout << "Opened database successfully: " << C.dbname() << endl;
} else {
cout << "Can't open database" << endl;
return 1;
}
C.disconnect ();
}catch (const std::exception &e){
cerr << e.what() << std::endl;
return 1;
}
}
现在,我们编译并运行上面的程序来连接到数据库testdb
,它已经在你的架构中可用,可以使用用户postgres
和密码为:pass123
进行访问。 您可以根据数据库设置使用用户名和密码。记住保持-lpqxx
和-lpq
在给定的顺序! 否则,链接器将抱怨关于缺少以“PQ
”开头的名称的函数。
$g++ test.cpp -lpqxx -lpq
$./a.out
Opened database successfully: testdb
创建表
以下C
代码段将用于在之前创建的数据库(testdb
)中创建一个表:
#include <iostream>
#include <pqxx/pqxx>
using namespace std;
using namespace pqxx;
int main(int argc, char* argv[])
{
char * sql;
try{
connection C("dbname=testdb user=postgres password=cohondob \
hostaddr=127.0.0.1 port=5432");
if (C.is_open()) {
cout << "Opened database successfully: " << C.dbname() << endl;
} else {
cout << "Can't open database" << endl;
return 1;
}
/* Create SQL statement */
sql = "CREATE TABLE COMPANY(" \
"ID INT PRIMARY KEY NOT NULL," \
"NAME TEXT NOT NULL," \
"AGE INT NOT NULL," \
"ADDRESS CHAR(50)," \
"SALARY REAL );";
/* Create a transactional object. */
work W(C);
/* Execute SQL query */
W.exec( sql );
W.commit();
cout << "Table created successfully" << endl;
C.disconnect ();
}catch (const std::exception &e){
cerr << e.what() << std::endl;
return 1;
}
return 0;
}
当编译和执行上述程序时,它将在testdb
数据库中创建一张COMPANY
表,并显示以下语句:
Opened database successfully: testdb
Table created successfully
插入操作
以下C
代码段显示了如何在上述示例中创建的COMPANY表中创建记录:
#include <iostream>
#include <pqxx/pqxx>
using namespace std;
using namespace pqxx;
int main(int argc, char* argv[])
{
char * sql;
try{
connection C("dbname=testdb user=postgres password=cohondob \
hostaddr=127.0.0.1 port=5432");
if (C.is_open()) {
cout << "Opened database successfully: " << C.dbname() << endl;
} else {
cout << "Can't open database" << endl;
return 1;
}
/* Create SQL statement */
sql = "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) " \
"VALUES (1, 'Paul', 32, 'California', 20000.00 ); " \
"INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) " \
"VALUES (2, 'Allen', 25, 'Texas', 15000.00 ); " \
"INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)" \
"VALUES (3, 'Teddy', 23, 'Norway', 20000.00 );" \
"INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)" \
"VALUES (4, 'Mark', 25, 'Rich-Mond ', 65000.00 );";
/* Create a transactional object. */
work W(C);
/* Execute SQL query */
W.exec( sql );
W.commit();
cout << "Records created successfully" << endl;
C.disconnect ();
}catch (const std::exception &e){
cerr << e.what() << std::endl;
return 1;
}
return 0;
}
当上述程序被编译和执行时,它将在COMPANY
表中创建给定的记录,并显示以下两行:
Opened database successfully: testdb
Records created successfully
SELECT操作
以下C
代码段显示了如何从上述示例中创建的COMPANY
表中获取和显示记录:
#include <iostream>
#include <pqxx/pqxx>
using namespace std;
using namespace pqxx;
int main(int argc, char* argv[])
{
char * sql;
try{
connection C("dbname=testdb user=postgres password=cohondob \
hostaddr=127.0.0.1 port=5432");
if (C.is_open()) {
cout << "Opened database successfully: " << C.dbname() << endl;
} else {
cout << "Can't open database" << endl;
return 1;
}
/* Create SQL statement */
sql = "SELECT * from COMPANY";
/* Create a non-transactional object. */
nontransaction N(C);
/* Execute SQL query */
result R( N.exec( sql ));
/* List down all the records */
for (result::const_iterator c = R.begin(); c != R.end(); ++c) {
cout << "ID = " << c[0].as<int>() << endl;
cout << "Name = " << c[1].as<string>() << endl;
cout << "Age = " << c[2].as<int>() << endl;
cout << "Address = " << c[3].as<string>() << endl;
cout << "Salary = " << c[4].as<float>() << endl;
}
cout << "Operation done successfully" << endl;
C.disconnect ();
}catch (const std::exception &e){
cerr << e.what() << std::endl;
return 1;
}
return 0;
}
当上述程序被编译和执行时,将产生以下结果:
Opened database successfully: testdb
ID = 1
Name = Paul
Age = 32
Address = California
Salary = 20000
ID = 2
Name = Allen
Age = 25
Address = Texas
Salary = 15000
ID = 3
Name = Teddy
Age = 23
Address = Norway
Salary = 20000
ID = 4
Name = Mark
Age = 25
Address = Rich-Mond
Salary = 65000
Operation done successfully
更新操作
以下C
代码段显示了如何使用UPDATE
语句来更新指定记录,然后从COMPANY
表中获取并显示更新的记录:
#include <iostream>
#include <pqxx/pqxx>
using namespace std;
using namespace pqxx;
int main(int argc, char* argv[])
{
char * sql;
try{
connection C("dbname=testdb user=postgres password=cohondob \
hostaddr=127.0.0.1 port=5432");
if (C.is_open()) {
cout << "Opened database successfully: " << C.dbname() << endl;
} else {
cout << "Can't open database" << endl;
return 1;
}
/* Create a transactional object. */
work W(C);
/* Create SQL UPDATE statement */
sql = "UPDATE COMPANY set SALARY = 25000.00 where ID=1";
/* Execute SQL query */
W.exec( sql );
W.commit();
cout << "Records updated successfully" << endl;
/* Create SQL SELECT statement */
sql = "SELECT * from COMPANY";
/* Create a non-transactional object. */
nontransaction N(C);
/* Execute SQL query */
result R( N.exec( sql ));
/* List down all the records */
for (result::const_iterator c = R.begin(); c != R.end(); ++c) {
cout << "ID = " << c[0].as<int>() << endl;
cout << "Name = " << c[1].as<string>() << endl;
cout << "Age = " << c[2].as<int>() << endl;
cout << "Address = " << c[3].as<string>() << endl;
cout << "Salary = " << c[4].as<float>() << endl;
}
cout << "Operation done successfully" << endl;
C.disconnect ();
}catch (const std::exception &e){
cerr << e.what() << std::endl;
return 1;
}
return 0;
}
当上述程序被编译和执行时,将产生以下结果:
Opened database successfully: testdb
Records updated successfully
ID = 2
Name = Allen
Age = 25
Address = Texas
Salary = 15000
ID = 3
Name = Teddy
Age = 23
Address = Norway
Salary = 20000
ID = 4
Name = Mark
Age = 25
Address = Rich-Mond
Salary = 65000
ID = 1
Name = Paul
Age = 32
Address = California
Salary = 25000
Operation done successfully
删除操作
以下C
代码段显示了如何使用DELETE
语句删除指定记录,然后再COMPANY
表中获取并显示剩余的记录:
#include <iostream>
#include <pqxx/pqxx>
using namespace std;
using namespace pqxx;
int main(int argc, char* argv[])
{
char * sql;
try{
connection C("dbname=testdb user=postgres password=cohondob \
hostaddr=127.0.0.1 port=5432");
if (C.is_open()) {
cout << "Opened database successfully: " << C.dbname() << endl;
} else {
cout << "Can't open database" << endl;
return 1;
}
/* Create a transactional object. */
work W(C);
/* Create SQL DELETE statement */
sql = "DELETE from COMPANY where ID = 2";
/* Execute SQL query */
W.exec( sql );
W.commit();
cout << "Records deleted successfully" << endl;
/* Create SQL SELECT statement */
sql = "SELECT * from COMPANY";
/* Create a non-transactional object. */
nontransaction N(C);
/* Execute SQL query */
result R( N.exec( sql ));
/* List down all the records */
for (result::const_iterator c = R.begin(); c != R.end(); ++c) {
cout << "ID = " << c[0].as<int>() << endl;
cout << "Name = " << c[1].as<string>() << endl;
cout << "Age = " << c[2].as<int>() << endl;
cout << "Address = " << c[3].as<string>() << endl;
cout << "Salary = " << c[4].as<float>() << endl;
}
cout << "Operation done successfully" << endl;
C.disconnect ();
}catch (const std::exception &e){
cerr << e.what() << std::endl;
return 1;
}
return 0;
}
当上述程序被编译和执行时,将产生以下结果:
Opened database successfully: testdb
Records deleted successfully
ID = 3
Name = Teddy
Age = 23
Address = Norway
Salary = 20000
ID = 4
Name = Mark
Age = 25
Address = Rich-Mond
Salary = 65000
ID = 1
Name = Paul
Age = 32
Address = California
Salary = 25000
Operation done successfully
移动端:请扫描本页面底部(右侧)二维码并关注微信公众号,回复:”教程” 选择相关教程阅读