Python Psycopg – Connection class

The connection to a PostgreSQL database instance is managed by the connection class. It’s more like a container for a database session. The function connect() is used to create connections to the database. The connect() function starts a new database session and returns a connection class instance. We can construct a new cursor to perform any SQL statements by putting the connection object to use.

Syntax:

psycopg2.connect(database=”dbname”, user=’postgres’, password=passwords, host=local_host, port= port_number)

parameters:

• dbname =the database name
• user =user name used to authenticate (widely used= postgres)
• password= password
• host =database host address
• port =connection port number (defaults = 5432 )

Example:

We import the psycopg package. conn is the connection variable that contains the connection for the “Classroom database”, where the user is ‘Postgres’, the password is ‘pass’, the local host is ‘127.0.0.1’ and the port number is ‘5432’.

Python3

# importing packages
import psycopg2

# forming connection
conn = psycopg2.connect(
    database="Classroom",
    user='postgres',
    password='pass',
    host='127.0.0.1',
    port='5432'
)

Methods in Connection Class

• cursor(): The connection is what creates cursors. They are permanently connected to the connection, and all instructions are run in the context of the database session are covered by the connection.

Syntax:

cursor(name=None, cursor_factory=None, scrollable=None, withhold=False)

Parameters:

• name: by default “none”, if the name is given a server-side cursor will be returned, if none is given a regular client-side server will be returned.
• cursor_factory: to create non-standard cursors
• scrollable: default None
• withhold: default False

• commit(): commit() is a method for committing data. This method commits the current transaction by sending a “COMMIT” statement to the Postgresql server. Because Python does not auto-commit by default, calling this method after each transaction that alters data for tables is necessary.

Syntax:

connection.commit()

• rollback(): It helps us return to the beginning of any pending transaction. Closing a connection without first committing or saving the modifications will result in an implicit rollback.

Syntax:

connection.rollback()

• close(): This method is used to close the connection to the database.

Syntax:

connection.close()

Example:

In the below example,  The create table command is executed and values are inserted into the table using the insert SQL command. The changes are saved using the commit() method. Finally, the connection to the database is closed using the close() method.

Python3

# importing packages
import psycopg2

# forming connection
conn = psycopg2.connect(
    database="Classroom",
  user='postgres',
  password='pass',
    host='127.0.0.1', port='5432'
)

conn.autocommit = True

# creating a cursor
cursor = conn.cursor()

# list of rows to be inserted
sql = ''' create table Student_Details(student_id int, student_name varchar(30),
        cgpa decimal)'''

# executing sql statement
cursor.execute(sql)
print('Table successfully created')

# list of rows to be inserted
values = [(12891, 'rachel', 9.5), (12892, 'ross', 8.93),
          (12893, 'nick', 9.2)]

# executing the sql statement
cursor.executemany("INSERT INTO Student_Details1 VALUES(%s,%s,%s)", values)

# select statement to display output
sql1 = '''select * from Student_Details;'''

# executing sql statement
cursor.execute(sql1)

# fetching rows
for i in cursor.fetchall():
    print(i)

# committing changes
conn.commit()

# closing connection
conn.close()

Output:

Table successfully created
(12891, 'rachel', Decimal('9.5'))
(12892, 'ross', Decimal('8.93'))
(12893, 'nick', Decimal('9.2'))