Python - PostgreSQL DB connection

Hello friends , in this blog post we will learn how to connect python with PostgreSQL.PostgreSQL is a powerful, open source object-relational database system that uses and extends the SQL language combined with many features that safely store and scale the most complicated data workloads.PostgreSQL has earned a strong reputation for its proven architecture, reliability, data integrity, robust feature set, extensibility, and the dedication of the open source community behind the software to consistently deliver performant and innovative solutions. PostgreSQL runs on all major operating systems, has been ACID-compliant since 2001.

Step 1 : ) Create a table in PostgreSQL , here we have created a table called test.

Step 2 :) import module psycopg2 module from python repository

Type ‘pip install psycopg2’ from command line terminal

Step 3:) The required code to connect python with PostgreSQL.

import psycopg2

def connect():

conn_string = "host='localhost' dbname='postgres' user='postgres' password='postgres'"

print ("Print the connection details \n ->%s" % (conn_string))

conn = psycopg2.connect(conn_string)

cursor = conn.cursor()

cursor.execute("SELECT * FROM test")

display_data=cursor.fetchall()

print(display_data)

cursor.close()

if __name__== '__main__':

connect()

Step 4:) We can check the output on the console.

Hive - Complex Data Type (Array)

The complex data types contains :

Array

Map

Structs

Arrays :- The array contains elements of same data type and can be accessed using the index. For example :- if we have an array called animal , it can contain animal like [‘dog’,’cat’,’lion’].These animals can be accessed via index like animal[1] =cat

Let us understand this in four simple steps :-

Step 1.) We will create a sample file called ‘array_exm.txt’

We are having three fields namely id, name and goals .Goals is an array datatype.

Step 2.) we will create the table .

Create table player(id int,name varchar(20),goals array<int>) row format delimited fields terminated by ‘\t’ collection items terminated by ‘,’;

Step 3.) We will load the data into the table.

Load data local inpath ‘pathname’ into table player;

Step 4.) Select the data from the table .

We can also select the specified array position value data from the table .