1. Postgresql Create Table Examples
2. How To Create Cursor In Postgresql Example Using

last modified July 6, 2020

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. 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. 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. Important Note: The cursor remains open until the end of transaction, and since PostgreSQL works in auto-commit mode by default, the cursor is closed immediately after the procedure call, so it is not available to the caller.To work with cursors the caller have to start a transaction.

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.

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

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.

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.

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.

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

The second example uses parameterized statements withPython extended format.

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

The named placeholders start with a colon character.

This is the output.

Python psycopg2 mogrify

The mogrify is a psycopg2 extension to the Python DB API thatreturns a query string after arguments binding. The returned string is exactlythe one that would be sent to the database running the execute() method or similar.

The program shows a SELECT query string after binding the arguments with mogrify().

This is the output.

Python psycopg2 insert image

In this section we are going to insert an image to thePostgreSQL database.

For this example, we create a new table called images. For theimages, we use the BYTEA data type. It allows to store binary strings.

In the program, we read an image from the current working directoryand write it into the images table of the PostgreSQLtestdb database.

We read binary data from the filesystem. We have a JPEG imagecalled sid.jpg.

The data is encoded using the psycopg2Binary object.

This SQL statement is used to insert the image into the database.

Python psycopg2 read image

In this section, we are going to perform the reverse operation.We read an image from the database table.

We read image data from the images table and write itto another file, which we call sid2.jpg.

We open a binary file in a writing mode. The datafrom the database is written to the file.

These two lines select and fetch data from the imagestable. We obtain the binary data from the first row.

Python PostgreSQL metadata

Metadata is information about the data in the database.Metadata in a PostgreSQL database contains information about the tablesand columns, in which we store data. Number of rows affectedby an SQL statement is a metadata. Number of rows and columns returnedin a result set belong to metadata as well.

Metadata in PostgreSQL can be obtained using from the descriptionproperty of the cursor object or from the information_schema table.

Next we print all rows from the cars table with theircolumn names.

We print the contents of the cars table to the console.Now, we include the names of the columns too.

We get the column names from the description propertyof the cursor object.

This line prints three column names of the cars table.

We print the rows using the for loop. The datais aligned with the column names.

This is the output.

In the following example we list all tables in thetestdb database.

The code example prints all available tables in the current databaseto the terminal.

The table names are stored inside the system information_schema table.

These were the tables on our system.

Python psycopg2 export and import of data

We can export and import data using copy_to()and copy_from().

The code example copies data from the cars table intothe cars.csv file.

We open a file where we write the data from the carstable.

The copy_to method copies data from the cars tableto the opened file. The columns are separated with the |character.

These are the contents of the cars file.

Now we are going to perform a reverse operation. We importthe dumped table back into the database table.

We delete the data from the cars table.

In the program we read the contents of the cars fileand copy it back to the cars table.

We open the cars.csv file for reading and copy the contents to thecars table. The changes are committed.

The output shows that we have successfullyrecreated the saved cars table.

Python psycopg2 transactions

A transaction is an atomic unit of database operations againstthe data in one or more databases. The effects of all the SQLstatements in a transaction can be either all committedto the database or all rolled back.

In psycopg2 module transactions are handled by the connection class.The first command of a connection cursor starts a transaction. (We do not need to encloseour SQL commands by BEGIN and END statements tocreate a transaction. This is handled automatically by psycopg2.) Thefollowing commands are executed in the context of this newtransaction. In case of an error, the transaction is aborted andno further commands are executed until the rollback() method.

The documentation to the psycopg2 module says that the connection isresponsible to terminate its transaction, calling either thecommit() or rollback() method. The committed changes areimmediately made persistent into the database. Closing the connection using theclose() method or destroying the connection object (using del orletting it fall out of scope) will result in an implicit rollback() call.

The psycopg2 module also supports an autocommit mode,where all changes to the tables are immediately effective.To run in autocommit mode, we set the autocommitproperty of the connection object to True.

We create the friends table and try to fill it with data. However,as we will see, the data will be not committed.

The commit() method is commented. If we uncomment theline, the data will be written to the table.

The finally block is always executed. If we have not committed thechanges and no error occurs (which would roll back the changes)the transaction is still opened. The connection is closed with theclose() method and the transaction isterminated with an implicit call to the rollback() method.

Only after we have uncommented the line, the friends tableis created.

Python psycopg2 autocommit

In the autocommit mode, an SQL statement is executed immediately.

In this example, we connect to the database in the autocommit mode.We do not call neither commit() nor rollback()methods.

We set the connection to the autocommit mode.

The data was successfully committed to the friends table.

Visit Python tutorial or list all Python tutorials.

Introduction

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:

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:

Refer to the following screenshot:

The result should resemble the following:

psycopg2 ‘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:

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:

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:

psycopg2 ‘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:

Finally, check the results using the following SELECT statement:

Postgresql Create Table Examples

Conclusion

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.

Just the Code

How To Create Cursor In Postgresql Example Using