Python Psycopg - Cursor class

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.

# 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:

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:

# 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:

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:

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.

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:

# 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.

# 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')]