What Is A PostgreSQL Cursor?
Introduction to Cursors in PostgreSQL
If you’re writing code that interacts with PostgreSQL using Python or PHP, you’ll probably want to use cursors in your scripts. A PostgreSQL database cursor is a read-only pointer that allows a program, regardless of the language used, to access the result set of a query. This conserves the free memory of the server or machine running the SQL commands when a result set contains a large number of rows. Using cursors to iterate over rows of data also offers more control over the SQL records returned and makes the whole process more efficient. In this article, we’ll take a closer look at cursors and show you how to use a PostgreSQL cursor in Python, PHP and psql.
Cursors in Python using the PostgreSQL psycopg2 adapter
In Python, you can use the psycopg2 adapter for PostgreSQL to create cursors. A cursor can be created by calling the connection object’s cursor() method. Let’s look at an example where we instantiate a cursor and get its attributes:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | # import the connect library from psycopg2 from psycopg2 import connect # declare a new psycopg2 object for connecting to PostgreSQL conn = connect( dbname = "python_test", user = "objectrocket", host = "localhost", password = "mypass" ) # return a cursor object from the connection cursor = conn.cursor() # print the cursor object's attributes print (dir(cursor)) |
NOTE: If you’re following along with this code example on your own machine, don’t forget to change the host parameters for the connect() method to match your PostgreSQL database’s settings.
When you pass the cursor instance to Python’s dir() function, it will return the following list of attributes:
1 | ['__class__', '__delattr__', '__dir__', '__doc__', '__enter__', '__eq__', '__exit__', '__format__', '__ge__', '__getattribute__', '__gt__', '__hash__', '__init__', '__init_subclass__', '__iter__', '__le__', '__lt__', '__ne__', '__new__', '__next__', '__reduce__', '__reduce_ex__', '__repr__', '__setattr__', '__sizeof__', '__str__', '__subclasshook__', 'arraysize', 'binary_types', 'callproc', 'cast', 'close', 'closed', 'connection', 'copy_expert', 'copy_from', 'copy_to', 'description', 'execute', 'executemany', 'fetchall', 'fetchmany', 'fetchone', 'itersize', 'lastrowid', 'mogrify', 'name', 'nextset', 'pgresult_ptr', 'query', 'row_factory', 'rowcount', 'rownumber', 'scroll', 'scrollable', 'setinputsizes', 'setoutputsize', 'statusmessage', 'string_types', 'typecaster', 'tzinfo_factory', 'withhold'] |
You can get the cursor object’s type by passing it to Python’s type() function; it should return the following:
1 | psycopg2.extensions.cursor |
Execute a SQL query by calling the psycopg2 cursor object’s execute() method
The following block of Python code uses the cursor object’s execute() method to execute SQL commands to a PostgreSQL database:
1 2 3 4 5 | # execute a PostgreSQL command to get all rows in a table cursor.execute("SELECT * FROM some_table;") # call the cursor object's fetchall() method print (cursor.fetchall()) |
The code shown above uses the cursor object’s fetchall() method to return all of the table’s records in the form of a Python list.
Instantiate Cursors in PHP using the PDO() method
Now that we’ve looked at a Python example, let’s check out some PHP code. Here’s how to create a cursor pointer in PHP using the PDO (PHP Data Objects) interface to connect to PostgreSQL:
1 2 3 4 | <?php // declare a cursor object for the PostgreSQL database $cursor = "DECLARE some_cursor CURSOR FOR SELECT * FROM some_table"; |
Create a new PDO connection to PostgreSQL in PHP
The following PHP code passes the cursor object to the print_r() method to print the SQL commands. After that, it creates a new connection to PostgreSQL using the PDO() method:
1 2 3 4 5 6 7 8 9 10 11 | // use PHP's print_r() function to print cursor object echo " print_r() for cursor: "; print_r($cursor); echo " "; // connect to PostgreSQL using the PDO() method $conn = new PDO("pgsql:host=localhost dbname=python_test", "objectrocket", "mypass"); |
Use the PHP prepare() method to avoid SQL injection attacks
When you’re writing PHP code that interacts with a database, it’s important to add safeguards against SQL injection attacks. In the following block of code, we call the connection object’s beginTransaction() method to disable auto-commit. This ensures that changes to a table or database will not be in effect until the PDO commit() method is called:
1 2 3 4 5 | // disable auto-commit for changes until calling PDO commit() $conn->beginTransaction(); // prepare for execution and prevent SQL injection attacks $pdoStatement = $conn->prepare($cursor); |
NOTE: The connection’s prepare() method, which returns a statement object, is used to help prevent SQL injection attacks.
The following code prints some class attributes for the PDO statement object:
1 2 3 4 5 6 | // echo the PDO statement object's class echo " get_class for PDO statement obj: ". get_class($pdoStatement); // disable auto-commit for changes until calling PDO commit() $conn->beginTransaction(); |
Prepare an inner statement for the SQL query and execute
In the next code snippet, we prepare a SQL query and execute the PDO statement. This enables the inner statement to prepare a response from the cursor:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | // prepare for execution and prevent SQL injection attacks $pdoStatement = $conn->prepare($cursor); // echo the PDO statement object's class echo " get_class for PDO statement obj: ". get_class($pdoStatement); // execute the PDO statement $pdoStatement->execute(); echo " print_r(pdoStatement): "; print_r($pdoStatement); echo " "; |
Execute the PDO inner statement and fetch records from a PostgreSQL table
Next, let’s FETCH all of the records from the cursor object by passing a SQL statement to the connection object’s prepare() method. This will return a PDO inner statement object:
1 2 3 4 5 6 7 8 9 10 | // pass a SQL query to the PDO object's prepare() method $innerStatement = $conn->prepare("FETCH ALL FROM some_cursor"); // execute the SQL statement $innerStatement->execute(); echo " "; // print the fetch() response for the inner statement print_r ($innerStatement->fetch(PDO::FETCH_ASSOC)); |
The code shown above executes the inner statement and prints the response. To test out this example on your own machine, simply save the PHP code in a script with the .php file extension, making sure the file is in your web root directory. Then, navigate to the file in a browser tab.
Create a CURSOR inside of the ‘psql’ interface for PostgreSQL
It’s also possible to create and use a PostgreSQL cursor in the psql interface. You can enter into the psql command-line interface for PostgreSQL by typing your username, domain host (-h), and PostgreSQL database (-d):
1 | psql objectrocket -h 127.0.0.1 -d python_test |
Once you’re connected, you can use the DECLARE keyword to create a new cursor for a table in the database:
1 | DECLARE some_cursor CURSOR FOR SELECT * FROM some_table; |
DECLARE CURSOR can only be used in transaction blocks
If you don’t use the keyword WITH HOLD after declaring your cursor, you’ll get an error saying: DECLARE CURSOR can only be used in transaction blocks. This is because a cursor no longer exists after a transaction is called. It will be freed from the system’s memory unless you explicitly tell it not to.
To avoid this issue, simply use the WITH HOLD keywords before the SQL command:
1 | DECLARE some_cursor CURSOR WITH HOLD FOR SELECT * FROM some_table;DECLARE CURSOR |
After you execute this statement, you’ll get a response from PostgreSQL saying:
1 | DECLARE CURSOR |
Conclusion
When you’re writing code that connects to PostgreSQL and executes queries, you want your interaction with the database to be as efficient as possible. Using cursors in your code can help you achieve this, allowing you to process queries with large result sets without maxing out your server’s free memory. In this article, we showed you how to create and use a PostgreSQL cursor in PHP, Python and psql. With all of these examples to guide you, you’ll be able to implement cursors in any of your database applications.
Pilot the ObjectRocket Platform Free!
Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.
Get Started