psycopg2 python 3 example

psycopg2 python 3 example: PostgreSQL Python tutorial with psycopg2 module shows how to program ... In the first code example, we get the version of the PostgreSQL database. Python Example to Connect PostgreSQL Database:-
We connect the database and perform quires as it is the main step.

psycopg2 python 3 example

Import psycopg2
Try:
Connection=psycopg2.connect (user=”sysadmin”, password=”pynative@#29”, host=127.0.0)
Port=”5432”, database=”postgres_db”)
Cursor=connection. cursor ()
Print(connection.get_dsn_parameter (),”\n”)
Cursor. execute (“select version () ;”)
Record=cursor.fetchone ()
Print(“You are connected to-”, record,”\n”)
Except(Exceeption, psycopg2.Error) as error:
Print(“’Error while connecting to PostgreSQL”, error)
Finally:
If (connection):
Cursor.close ()
Connection.close ()
Print(“PostgreSQL connection is closed”)
Output:-
{‘user’:’Postgres’,’dbname’:’pynavtive_DB’,’host’:’127.0.0.1’,’port’:’5432’,’tty’:”,’options’:”,’sslmode’:’prefer’,’sslcompression’:’1’,’krbsrvname’:’pstgres’,’terget_session_attrs’:’any’}
Then we are connected to – (‘PostgreSQL 10.3’)
PostgreSQL connection is closed

Create a Table:-

Import psycopg2
Conn=psycopg2.connect (database=”testdb”, user=”postgres”, password=”pass123”, host=”127.0.0.1”, port=”5432”)
Print “opened database successfully”
Cur=conn.cursor ()
Curr.execute(“create id int primary key not null, table company  name text not null ,age int notnull,address char(50),salary real);”)
Print “Table created successfully”
Conn. commit ()
Conn. close ()
Output:-

Opened database successfully

Table created successfully

Insert Operation:-

Import psycopg2
Conn=psycopg2.connect (databse=”testdb”, user=”postgres”, password=”pass123”, host=”127.0.0.1”, port=”5432”)
Print”opened database successfully”
Cur=conn.cursor ()
Curr.execute (“insert into company (name, id, age, address, salary)\values (’soaf’, 1, 32,’california’, 20000.00)”);

cur.execute (“insert into company (name, id, age, address, salary)\values (’Harry’, 2, 25,’Texas’, 150000.00)”);
cur.execute (“insert into company (name, id, age, address, salary)\values (’sunny’, 3, 23,’Norway’, 20000.00)”);
cur.execute (“insert into company (name, id, age, address, salary)\values (’Neuton’, 4, 25,’Rich-Mond’, 650000.00)”);
                       Conn. Commit ()
Print “Records created successfully”;
Conn. Close ()
Output:-

Opened database successfully

Records created successfully

SELECT Operation:-

Example:-
Import psycopg2
Conn=psycopg2.connect (database=”testdb”, user=”postgres”, password=”pass123”, host=”127.0.0.1”, port=”5432”)
Print “opened database successfully”
Cur=conn.cursor ()
Cur. execute (“select name, id, address, salary from company”)
Rows=cur.fetchall ()
For row in rows:
Print “name=”, row [0]
Print”id=”, row [1]
Print “address=”, row [2]
Print”salary=”, row [3],”\n”;
Print “Operation done successfully”;
Conn. Close ()

Output:-

Opened database successfully

name = Soaf

id= 1

address = California

salary = 20000.0

name = Harry

id= 2

address = Texas

salary = 15000.0

name = Sunny

id = 3

address = Norway

salary = 20000.0

name= Neuton

id= 4

address= Rich-Mond

salary = 65000.0

Operation done successfully

UPDATE Operation:-

Import psycopg2
Conn=psycopg2.connect (database=”testdb”, user=”postgres”, password=”pass123”, host=”127.0.0.1”, port=”5432”)
Print “opened database successfully”
Cur=conn.cursor ()
Cur. execute (“update company set salary=25000.00 where id=1”)
           Conn. commit ()
Print “Total number of rows updated:”, curr.rowcount
Cur. execute (“select name, id, address, salary from company”)
Rows=curr.fetchall ()
For row in rows:
Print “name=”, row [0]
Print “id=”, row [1]
Print “address=”, row [2]
Print “salary=”, row [3],”\n”
Print “operation done successfully”;
Conn. Close ()

Output:-

Opened database successfully
Total number of rows deleted: 1
name=Soaf
id=1
address=California
salary=20000.0
name=Harry
id=2
address=Texas
salary=150000.0
name=Sunny
address=Norway
id=3
salary=20000.0
name=Neuton
id=4
address=Rich-Mond
salary=650000.0
Operation done successfully

DELETE Operation:-

Import psycopg2
Conn=psycopg2.connect (database=”testdb”, user=”postgres”, password=”pass123”, host=”127.0.0.1”, port=”5432”)
Print “opened database successfully”
Cur=conn.cursor ()
Cur. execute (“delete from company where id=2 ;”)
Conn. commit ()
Print “Total number of rows deleted:”, cur.rowcount
Cur. execute (“select name, id, address, salary from company”)
Rows=cur.fetchall ()
For row in rows:
Print “name=”, row [0]
Print”id”=”, row [1]
Print “address=”, row [2]
Print “salary=”, row [3],”\n”
Print “operation done successfully”;
Conn. Close()

Output:-

Opened database successfully
Total number of rows deleted: 1
name=Soaf
id=1
address=California
salary=20000.0
name=Sunny
id=3
address=Norway
salary=20000.0
name=Neuton
Id=4
address=Rich-Mond
salary=650000.0
Operation done successfully