Connecting to Database
import psycopg2
from psycopg2 import Error
try:
# Connect to an existing database
connection = psycopg2.connect(user=”gpadmin”,
password=”pgadmin”,
host=”127.0.0.1″,
port=”5432″,
database=”tutorial”)
# Create a cursor to perform database operations
cursor = connection.cursor()
# Print PostgreSQL details
print(“PostgreSQL server information”)
print(connection.get_dsn_parameters(), “\n”)
# Executing a SQL query
cursor.execute(“SELECT version();”)
# Fetch result
record = cursor.fetchone()
print(“You are connected to – “, record, “\n”)
except (Exception, Error) as error:
print(“Error while connecting to PostgreSQL”, error)
finally:
if (connection):
cursor.close()
connection.close()
print(“PostgreSQL connection is closed”)
Creating a Table
import psycopg2
from psycopg2 import Error
try:
connection = psycopg2.connect(user=”gpadmin”,
password=”pivotal”,
host=”127.0.0.1″,
port=”5432″,
database=”tutorial”)
cursor = connection.cursor()
# SQL query to create a new table
create_table_query = ”’CREATE TABLE mobile
(ID INT PRIMARY KEY NOT NULL,
MODEL TEXT NOT NULL,
PRICE REAL); ”’
# Execute a command: this creates a new table
cursor.execute(create_table_query)
connection.commit()
print(“Table created successfully in PostgreSQL “)
except (Exception, Error) as error:
print(“Error while connecting to PostgreSQL”, error)
finally:
if (connection):
cursor.close()
connection.close()
print(“PostgreSQL connection is closed”)
Executing a SQL Query to Insert Data into the Table
# Executing a SQL query to insert data into table
insert_query = “”” INSERT INTO mobile (ID, MODEL, PRICE) VALUES (1, ‘Iphone12’, 1100)”””
cursor.execute(insert_query)
connection.commit()
print(“1 Record inserted successfully”)
# Fetch result
cursor.execute(“SELECT * from mobile”)
record = cursor.fetchall()
print(“Result “, record)
Executing a SQL Query to Update Data into the Table
# Executing a SQL query to update table
update_query = “””Update mobile set price = 1500 where id = 1″””
cursor.execute(update_query)
connection.commit()
count = cursor.rowcount
print(count, “Record updated successfully “)
# Fetch result
cursor.execute(“SELECT * from mobile”)
print(“Result “, cursor.fetchall())