The cursor class Enables Python scripts to use a database session to run PostgreSQL commands. The connection class is what creates cursors.
cursor() method: They are permanently connected to the connection, and all instructions are run in the context of the database session covered by the connection. Cursors generated from the same connection aren’t separated, which means that any alterations made to the database by one cursor are incontinently visible to the others. Cursors made from separate connections can be isolated or not, depending on the insulation position of the connections.
Cursors are not thread-safe, a multithreaded application can construct multiple cursors from a single connection, and each cursor should be used by a single thread.
Create a simple cursor:
in the below code we form a connection to the “Hospital_database” and a cursor is created using connection.cursor() method.
Python3
# importing packages import psycopg2 # forming the connection conn = psycopg2.connect( database="Hospital_database", user='postgres', password='pass', host='127.0.0.1', port='5432') # Setting auto commit to True conn.autocommit = True # Creating a cursor object using the # cursor() method cursor = conn.cursor() |
Methods in Cursor Class
execute() method:
Prepare a database operation and run it (query or command). Parameters can be provided in the form of a series or a mapping, and they’ll be tied to variables in the operation. Positional (% s) or named (% (name)s) placeholders are used to specify variables.
None is returned by the method.
Syntax: execute(operation[, parameters])
Example:
Python3
sql = '''SELECT * FROM employee;''' # executing the sql command cursor.execute(sql) |
executemany() method:
Build a database action (query or command) and run it against all of the parameter tuples or mappings in a sequence of parameters. The function is especially useful for database update instructions because it discards any result set produced by the query.
Syntax executemany(operation, sequence_of_parameters)
Example:
Python3
# executing the sql statement cursor.executemany("INSERT INTO classroom VALUES(%s,%s,%s)", values) |
fetchall() method:
All (remaining) rows of a query result are fetched and returned as a list of tuples. If there are no more records to fetch, an empty list is returned.
Syntax: cursor.fetchall()
Example:
Python3
sql = '''SELECT * FROM employee;''' # executing the sql command cursor.execute(sql) # fetching all the rows results = cursor.fetchall() print(results) |
Output:
fetchone() method:
Returns a single tuple if the next row of a query result set is available, or None if no further data is available.
Syntax: cursor.fetchone()
Example:
Python3
sql = '''SELECT * FROM employee;''' # executing the sql command cursor.execute(sql) # fetching one row result = cursor.fetchone() print(result) |
Output:
fetchmany() method:
Returns a list of tuples after fetching the next set of rows from a query result. if there are no more rows available, a blank list is returned.
The argument specifies the number of rows to fetch each call. The cursor’s array size specifies the number of rows to be fetched if it is not specified. The procedure should attempt to retrieve as many rows as the size parameter specifies.
Syntax: cursor. fetchmany([size=cursor.arraysize])
Example:
The below example is to fetch the first two rows.
Python3
sql = '''SELECT * FROM employee;''' # executing the sql command cursor.execute(sql) # fetching first two rows result = cursor.fetchmany(2) print(result) |
Output:
callproc() method:
Use the name of a stored database procedure to invoke it. Each argument that the procedure expects must have its own entry in the parameter sequence. The call returns a changed duplicate of the input sequence as the result. The input parameters are left alone, while the output parameters maybe get replaced with new values.
Syntax: curor.callproc(procname[, parameters])
mogrify() method:
After the arguments have been bound, a query string is returned. The string returned is the same as what was sent to the database if you used the execute() method or anything similar.
Syntax: cursor.mogrify(operation[, parameters])
Example:
Python3
# cursor.mogrify() to insert multiple values args = ','.join(cursor.mogrify("(%s,%s,%s)", i).decode('utf-8') for i in values) |
close() method:
used to close the cursor. From this point forth, the cursor will be inoperable; if any operation is performed with the cursor, an InterfaceError will be raised.
Syntax: curor.close()
Let’s see the below example to see the complete working of the cursor object.
A connection is established to the “Employee_db” database. a cursor is created using the conn.cursor() method, select SQL statement is executed by the execute() method and all the rows of the Table are fetched using the fetchall() method.
Python3
# importing packages import psycopg2 # establishing connection conn = psycopg2.connect( database="Employee_db", user='postgres', password='root', host='localhost', port='5432') # setting autocommit to True conn.autocommit = True # creating a cursor cursor = conn.cursor() sql = '''SELECT * FROM employee;''' # executing the sql command cursor.execute(sql) # fetching all the rows results = cursor.fetchall() print(results) # committing changes conn.commit() # closing connection conn.close() |
Output:
[(1216755, ‘raj’, ‘data analyst’, 1000000, 2, ‘1216755raj’), (1216756, ‘sarah’, ‘App developer’, 60000, 3, ‘1216756sarah’), (1216757, ‘rishi’, ‘web developer’, 60000, 1, ‘1216757rishi’), (1216758, ‘radha’, ‘project analyst’, 70000, 4, ‘1216758radha’), (1216759, ‘gowtam’, ‘ml engineer’, 90000, 5, ‘1216759gowtam’), (1216754, ‘rahul’, ‘web developer’, 70000, 5, ‘1216754rahul’), (191351, ‘divit’, ‘100000.0’, None, None, ‘191351divit’), (191352, ‘rhea’, ‘70000.0’, None, None, ‘191352rhea’)]
