last modified July 6, 2020

In a Python Program, using the Psycopg2, a developer can create a database connection, a database cursor and call the necessary execute and fetch methods to perform any of the required SQL operations on a PostgreSQL server. The CREATE TABLE statement is as well, executed through a database cursor object obtained using the Psycopg2 module. The curfilms is a cursor that encapsulates all rows in the film table. The curfilms2 is a cursor that encapsulates film with a particular release year in the film table. Opening cursors. Cursors must be opened before they can be used to query rows. PostgreSQL provides the syntax for opening an unbound and bound cursor. Opening unbound cursors. Example: Returning a REF CURSOR from a procedure (PL/SQL) This example demonstrates how to define and open a REF CURSOR variable, and then pass it as a procedure parameter. The cursor variable is specified as an IN OUT parameter so that the result set is made available to the caller of the procedure.

Python PostgreSQL tutorial with psycopg2 module showshow to program PostgreSQL databases in Python with psycopg2 module.

PostgreSQL

PostgreSQL is a powerful, open source object-relational database system. It is amulti-user database management system. It runs on multiple platforms including Linux,FreeBSD, Solaris, Microsoft Windows and Mac OS X. PostgreSQL is developed by thePostgreSQL Global Development Group.

The psycopg2 module

There are several Python libraries for PostgreSQL. language. In this tutorial weuse the psycopg2 module. It is a PostgreSQL database adapter forthe Python programming language. It is mostly implemented in C as alibpq wrapper.

We install the psycopg2 module.

Python psycopg2 version example

In the first code example, we get the version of the PostgreSQL database.

version.py

In the program we connect to the previously createdtestdb database. We execute an SQL statement which returns theversion of the PostgreSQL database.

The psycopg2 is a Python module which is used to workwith the PostgreSQL database.

We initialize the con variable to None. In case we could not create a connectionto the database (for example the disk is full), we would not have a connectionvariable defined. This would lead to an error in the finally clause.

The connect() method creates a new database session andreturns a connection object. The user was created without a password.On localhost, we can omit the password option. Otherwise, it mustbe specified.

From the connection, we get the cursor object. The cursor is used to traversethe records from the result set. We call the execute() method ofthe cursor and execute the SQL statement.

We fetch the data. Since we retrieve only one record, we call thefetchone() method.

We print the data that we have retrieved to the console.

In case of an exception, we print an error message andexit the program with an error code 1.

In the final step, we release the resources.

This is the output.

In the second example, we again get the version of the PostgreSQL database. Thistime we use the with keyword.

The program returns the current version of the PostgreSQL database. With the useof the with keyword. The code is more compact.

With the with keyword, Python automaticallyreleases the resources. It also provides error handling.

Python psycopg2 execute

We create the cars table and insert several rows to it.The execute() executes a database operation (query or command).

create_table.py

The program creates the cars table and inserts eight rows into thetable.

This SQL statement creates a new cars table. The table hasthree columns.

These two lines insert two cars into the table.

We verify the written data with the psql tool.

Python psycopg2 executemany

The executemany() method is a convenience method for launching a database operation (query or command) against all parameter tuples or mappings found in the provided sequence. The function is mostly useful for commands that update the database: any result set returned by the query is discarded.

This example drops the cars table if it exists and (re)creates it.

The first SQL statement drops the cars table if it exists.The second SQL statement creates the cars table.

This is the query that we use.

We insert eight rows into the table using the convenience executemany()method. The first parameter of this method is a parameterized SQL statement.The second parameter is the data, in the form of a tuple of tuples.

Python psycopg2 last inserted row id

The psycopg2 does not support the lastrowid attribute.To return the id of the last inserted row, we have to use PostgreSQL's RETURNING id clause.

lastrowid.py

The program creates a new words table and prints the Id of the last inserted row.

This is the output.

Python psycopg2 fetchall

The fetchall() fetches all the (remaining) rows of a query result,returning them as a list of tuples. An empty list is returned if there is nomore record to fetch.

In this example, we retrieve all data from the cars table.

This SQL statement selects all data from the cars table.

The fetchall() method gets all records. It returnsa result set. Technically, it is a tuple of tuples. Each of theinner tuples represents a row in the table.

We print the data to the console, row by row.

This is the output of the example.

Python psycopg2 fetchone

The fetchone() returns the next row of a query result set,returning a single tuple, or None when no more data is available.

fetchone.py

In this example we connect to the database and fetch the rowsof the cars table one by one.

We access the data from the while loop. When we read the last row,the loop is terminated.

The fetchone() method returns the next row fromthe table. If there is no more data left, it returns None.In this case we break the loop.

The data is returned in the form of a tuple. Here we selectrecords from the tuple. The first is the Id, the secondis the car name and the third is the price of the car.

Python psycopg2 dictionary cursor

The default cursor retrieves the data in a tuple of tuples. Witha dictionary cursor, the data is sent in a form of Python dictionaries.We can then refer to the data by their column names.

In this example, we print the contents of the cars tableusing the dictionary cursor.

The dictionary cursor is located in the extras module.

We create a DictCursor.

The data is accessed by the column names. The column names arefolded to lowercase in PostgreSQL (unless quoted) and are case sensitive.Therefore, we have to provide the column names in lowercase.

Python psycopg2 parameterized queries

When we use parameterized queries, we use placeholders instead of directlywriting the values into the statements. Parameterized queries increasesecurity and performance.

The Python psycopg2 module supports two types of placeholders:ANSI C printf format and the Python extended format.

We update a price of one car. In this code example, we use the questionmark placeholders.

The characters (%s) are placeholders for values. The values areadded to the placeholders.

The rowcount property returns the number of updatedrows. In our case one row was updated.

How to create cursor in postgresql example java

The price of the car was updated. We check the change with thepsql tool.

The second example uses parameterized statements withPython extended format.

parameterized_query2.py

We select a name and a price of a car using pyformat parameterizedstatement.

Postgresql Create View

The named placeholders start with a colon character.

This is the output.

How To Create Cursor In Postgresql Example