Connecting to PostgreSQL Server via Programming Languages

In order to store or access the data inside a PostgreSQL database, you first need to connect to the PostgreSQL database server. We will show you the sample codes to connect your PostgreSQL via JAVA, Python, and PHP.

Connecting via JAVA

Before you start, you need to add PostgreSQL JDBC driver to your java project based on your java version. The download link for the PostgreSQL JDBC driver is https://jdbc.postgresql.org/download.html.

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class PostgresqlConnection {

    public static void main(String[] args) {
        // create three connections to three different databases on localhost
        Connection conn = null;

        try {
            String dbServer = "postgresql-xxxxx-0.cloudclusters.net"; // change it to your database server name
            int dbPort = 15253; // change it to your database server port
            String dbName = "your database name";
            String userName = "your database user name";
            String password = "your database password";
            String url = String.format("jdbc:postgresql://%s:%d/%s?user=%s&password=%s", 
                                        dbServer, dbPort, dbName, userName, password);
            conn = DriverManager.getConnection(url);
            Statement stmt = conn.createStatement();
            String sql1 = "drop table if exists teacher";
            stmt.executeUpdate(sql1);
            // create table
            String sql = "create table teacher(NO varchar(20), name varchar(20),primary key(NO))";
            int result = stmt.executeUpdate(sql);

            // insert data
            if (result != -1) {
                sql = "insert into teacher(NO, name) values('202001','ben')";
                result = stmt.executeUpdate(sql);
                sql = "insert into teacher(NO, name) values('202002','ethan')";
                result = stmt.executeUpdate(sql);
            }

            // query data
            sql = "select * from teacher";
            ResultSet rs = stmt.executeQuery(sql);
            System.out.println("No.\tName");
            while (rs.next()) {
                System.out.println(rs.getString(1) + "\t" + rs.getString(2));
            }
        } catch (SQLException e) {
            System.out.println("PostgreSQL connection had an exception");
            e.printStackTrace();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

    }

}

Connecting via Python

Install psycopg2

pip install psycopg2

Connect to your database

import psycopg2


class PostgresqlConnection(object):
    def __init__(self):
        self.host = 'your host'
        self.port = 'your port'
        self.user = 'your user name'
        self.passwd = 'your user password'
        self.db = 'your connect database name'

    def connect_postgresql(self):
        conn = psycopg2.connect(database=self.db, user=self.user, password=self.passwd, host=self.host, port=self.port)
        conn.set_isolation_level(0)
        return conn

    def operate_database(self):
        # examole select all database name
        connect = self.connect_postgresql()
        cursor = connect.cursor()
        sql = "SELECT datname FROM pg_database;"
        cursor.execute(sql)
        database_list = [i[0] for i in cursor.fetchall()]
        print(database_list)


if _name_ == '__main__':
    PostgresqlConnection().operate_database()

Connecting via PHP

Install PostgreSQL extension

The PostgreSQL extension is enabled by default in the latest releases of PHP 5.3.x.

If you are using a different version of php, you can use the command below to install PostgreSQL extension.

In CentOS：

yum install php-pgsql

In Ubuntu: To install the latest version of the PostgreSQL extension.

sudo apt update
sudo apt install php-pgsql

To install a specific version of the PostgreSQL extension. Replace the {php version} with the version you'd like to install.

sudo apt update
sudo apt install php{php version}-pgsql

Connect to PostgreSQL Database

<?php
   $host        = "host = postgresql-xxxx-0.tripanels.com";
   $port        = "port = 40xx";
   $dbname      = "dbname = testdatabase";
   $credentials = "user = testuser password=Testuser123go";

   $db = pg_connect( "$host $port $dbname $credentials"  );
   if(!$db) {
      echo "Error : Unable to open database\n";
   } else {
      echo "Opened database successfully\n";
   }
?>