Introduction
*Cursors In Postgresql Tutorial
*Postgresql How To Create Database
*Postgresql Cursor For Loop
*Postgresql Cursor With Hold
*Postgresql Cursor ExampleCursors In Postgresql Tutorial
To create a Postgres table in Python, we use the CREATE TABLE SQL statement. This query should be executed after establishing a connection to the database. We also create a cursor object by calling the cursor method that belongs to the connection object. Declaring Cursor Variables. All access to cursors in PL/pgSQL goes through cursor variables, which are always of the special data type refcursor.One way to create a cursor variable is just to declare it as a variable of type refcursor.Another way is to use the cursor declaration syntax, which in general is.
This is part two of a tutorial series supplying a PostgreSQL crud example in Python with the Psycopg2 adapter. Part one of this series explained how to create a test database for Psycopg2, install psycopg2, connect to PostgreSQL using psycopg2, provided a Postgres ‘CREATE TABLE’ example and explained how to use psycopg2 to insert PostgreSQL record data. This part two of this series will explain the table data select, update and delete functions using psycopg2.Prerequisites
*The Psycopg2 or the python driver for PostgreSQL database cluster must be properly installed and working to execute a PostgreSQL crud example in python. Refer to part one of this tutorial series for the installation instructions.psycopg2 select example
With data inserted into the table book, done in part one of this series, the data can now be recalled and displayed using psycopg2. Create a new file in Python and enter the following codes:# Import the python driver for PostgreSQLimport psycopg2# Use the same connection credentials of the PostgreSQL databasetry: connection = psycopg2.connect(user='postgres', password ='1234', host ='localhost', port ='5432', database ='demo')
Now use the connection object instance created from the psycopg2.connect() method to create a new cursor object. As shown below, use this cursor object to execute the SQL statements to select table rows from the PostgreSQL table:# Create a connection instance for the PostgreSQL and fetch data from the table cursor = connection.cursor() pg_select ='SELECT * FROM book' cursor.execute(pg_select)# Execute and print the outputprint('Selected rows from book table') book_records = cursor.fetchall()print('Records of books in the table')for row in book_records:print('nid = ', row[0])print('author = ', row[1])print('isbn = ', row[2])print('title = ', row[3])print('date_published = ', row[4],'n')except(Exception, psycopg2.Error)as error:print('Error selecting data from table book', error)# Close the connection to the PostgreSQL databasefinally:if(connection): cursor.close() connection.close()print('PostgreSQL connection is now closed')
Refer to the following screenshot:
The result should resemble the following:Selected rows from book tableRecords of books in the tableid = 1author = Layla Nowiztkiisbn = 789-1-46-268414-1title = How to become a professional programmerdate_published = 2011-01-25id = 2author = Miya Grandisbn = 123-7-58-364785-1title = Coding for Beginnersdate_published = 2009-02-23id = 3author = Grange Tollisbn = 143-4-15-135147-7title = Tutorials in Web Development for Beginnersdate_published = 2007-03-12PostgreSQL connection is now closedpsycopg2 ‘UPDATE’ example
Now modify the existing data in the table by using psycopg2 by creating a Python file. Alternatively, the following code can be placed into a new Python script on the same machine or server:123456789101112131415161718192021222324252627282930313233343536373839404142434445464748# Import the python driver for PostgreSQLimport psycopg2# Create a function header to update the tabledef update_table(bookid, title):try: connection = psycopg2.connect(user='postgres', password ='1234', host ='localhost', port ='5432', database ='demo')# Create a cursor connection object to a PostgreSQL instance and print the connection properties. cursor = connection.cursor()print('Book table before updating') pg_select =''SELECT * FROM book WHERE id = %s'' cursor.execute(pg_select,(bookid,)) book_record = cursor.fetchone()print(book_record)# Update the row from book pg_update =''Update book set title = %s where id = %s'' cursor.execute(pg_update,(title, bookid)) connection.commit() count = cursor.rowcountprint(count,'Successfully Updated!')print('Book table after updating') pg_select =''SELECT * FROM book where id = %s''# Execute the above SQL string cursor.execute(pg_select,(bookid,)) book_record = cursor.fetchone()print(book_record)except(Exception, psycopg2.Error)as error:print('Error in updating the data:', error) connection =None# Close the connection to the PostgreSQL databasefinally:if connection !=None: cursor.close() connection.close()print('PostgreSQL connection is now closed')
In the last part of the script, outside of the function indentation, new values must be specified for the record being updated. Now pass them to the update_table() function call, as shown in the following example:_id =3title ='Tutorials in FrontEnd Developers'# call the psycopg2 function to execute SQLupdate_table(_id, title)
NOTE: In Python, id() is a built-in function that gives an object’s identity. To avoid confusion, or affecting its namespace, the above example uses _id with an underscore in the variable declaration instead. This is shown in the following screenshot:
When the above script is executed using python3, the results should resemble the following:Book table before updating(3, 'Grange Toll', '143-4-15-135147-7', 'Tutorials in Web Developers for Beginners', datetime.date(2007, 3, 12))1 Successfully Updated!Book table after updating(3, 'Grange Toll', '143-4-15-135147-7', 'Tutorials in FrontEnd Developers', datetime.date(2007, 3, 12))PostgreSQL connection is now closedPostgresql How To Create Databasepsycopg2 ‘DELETE’ example
Lastly, the table data will now be delete using psycopg2. This is performed in the same manner as the DELETE SQL statement in the psql command line interface is used to delete rows of Postgres data. Execute the following code:12345678910111213141516171819202122232425262728293031323334353637# Import the python driver for PostgreSQLimport psycopg2# Create a delete functiondef delete_data(bookid):try: connection = psycopg2.connect(user='postgres', password ='1234', host ='localhost', port ='5432', database ='demo') cursor = connection.cursor()# Delete the data from table book pg_delete ='DELETE FROM book where id = ${}'.format(bookid) cursor.execute( pg_delete ) connection.commit() count = cursor.rowcountprint('Successfully deleted', count,'rows.')except(Exception, psycopg2.Error)as error:print('Error in Deleting the data:', error) connection =None# Close the connection to the PostgreSQL databasefinally:if connection !=None: cursor.close() connection.close()print('PostgreSQL connection is now closed')_id =2delete_data(_id)1 Successfully deletedPostgreSQL connection is now closedPostgresql Cursor For Loop
Finally, check the results using the following SELECT statement:Selected rows from book tableRecords of books in the tableid = 1author = Layla Nowiztkiisbn = 789-1-46-268414-1title = How to become a professional programmerdate_published = 2011-01-25id = 3author = Grange Tollisbn = 143-4-15-135147-7title = Tutorials in FrontEnd Developersdate_published = 2007-03-12PostgreSQL connection is now closedConclusion
This was part two in a tutorial series supplying a PostgreSQL crud example in python with the Psycopg2 adapter. Part two built on the functions explained in part one of this series and specifically covered a psycopg2 ‘SLECT’ example, a psycopg2 ‘UPDATE’ example and psycopg2 ‘DELETE’ example. Part two also covered how to check the result of these functions using the Slect statement. Remember that either Psycopg2 or the Python driver for the PostgreSQL database must be properly installed to execute a PostgreSQL crud example in Python. Installation instructions for the Python driver for the PostgreSQL database can be found in part one of this tutorial series.Postgresql Cursor With HoldJust the CodePostgresql Cursor Example1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950515253545556575859606162636465666768697071727374757677787980818283848586878889909192#!/usr/bin/python3# Import the python driver for PostgreSQLimport psycopg2# Create a function header to update the tabledef update_table(bookid, title):try: connection = psycopg2.connect(user='objectrocket', password ='1234', host ='localhost', port ='5432', database ='demo')# Create a cursor connection object to a PostgreSQL instance and print the connection properties. cursor = connection.cursor()print('Book table before updating') pg_select =''SELECT * FROM book WHERE id = %s'' cursor.execute(pg_select,(bookid,)) book_record = cursor.fetchone()print(book_record)# Update the row from book pg_update =''Update book set title = %s where id = %s'' cursor.execute(pg_update,(title, bookid)) connection.commit() count = cursor.rowcountprint(count,'Successfully Updated!')print('Book table after updating') pg_select =''SELECT * FROM book where id = %s''# Execute the above SQL string cursor.execute(pg_select,(bookid,)) book_record = cursor.fetchone()print(book_record)except(Exception, psycopg2.Error)as error:print('Error in updating the data:', error) connection =None# Close the connection to the PostgreSQL databasefinally:if connection !=None: cursor.close() connection.close()print('PostgreSQL connection is now closed')_id =3title ='Tutorials in FrontEnd Developers'# call the psycopg2 function to execute SQLupdate_table(_id, title)# Create a delete functiondef delete_data(bookid):try: connection = psycopg2.connect(user='postgres', password ='1234', host ='localhost', port ='5432', database ='demo') cursor = connection.cursor()# Delete the data from table book pg_delete ='DELETE FROM book where id = ${}'.format(bookid) cursor.execute( pg_delete ) connection.commit() count = cursor.rowcountprint('Successfully deleted', count,'rows.')except(Exception, psycopg2.Error)as error:print('Error in Deleting the data:', error) connection =None# Close the connection to the PostgreSQL databasefinally:if connection !=None: cursor.close() connection.close()print('PostgreSQL connection is now closed')_id =2delete_data(_id)