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 packagesimport psycopg2# forming connectionconn = 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 packagesimport psycopg2# forming connectionconn = psycopg2.connect( database="Classroom", user='postgres', password='pass', host='127.0.0.1', port='5432')conn.autocommit = True# creating a cursorcursor = conn.cursor()# list of rows to be insertedsql = ''' create table Student_Details(student_id int, student_name varchar(30), cgpa decimal)'''# executing sql statementcursor.execute(sql)print('Table successfully created')# list of rows to be insertedvalues = [(12891, 'rachel', 9.5), (12892, 'ross', 8.93), (12893, 'nick', 9.2)]# executing the sql statementcursor.executemany("INSERT INTO Student_Details1 VALUES(%s,%s,%s)", values)# select statement to display outputsql1 = '''select * from Student_Details;'''# executing sql statementcursor.execute(sql1)# fetching rowsfor i in cursor.fetchall(): print(i)# committing changesconn.commit()# closing connectionconn.close() |
Output:
Table successfully created
(12891, 'rachel', Decimal('9.5'))
(12892, 'ross', Decimal('8.93'))
(12893, 'nick', Decimal('9.2'))
