With PostgreSQL from 9.0, you can simply drop into executing plpgsql using a 'DO' block. For prior versions, you need to create a function and select it. If you're looking for the PostgreSQL equivalent of, for example, iterating through a result with a cursor on SQL Server, that's what it is.
- Postgresql Cursor Loop
- Postgresql Cursor With Hold
- Postgresql Cursor Insert Example
- Postgresql Loop Through Rows
- Python PostgreSQL Statement Problem psycopg2 cursor.execute(Table Union) 160. Psycopg2: insert multiple rows with one query. How do I get a list of column names from a psycopg2 cursor? Django.db connection return different values from psycopg2.
- To access to a cursor, you need to declare a cursor variable in the declaration section of a block. PostgreSQL provides you with a special type called REFCURSOR to declare a cursor variable.
- Python PostgreSQL Tutorial
- Python PostgreSQL Useful Resources
- Selected Reading
The Cursor class of the psycopg library provide methods to execute the PostgreSQL commands in the database using python code.
Using the methods of it you can execute SQL statements, fetch data from the result sets, call procedures.
You can create Cursor object using the cursor() method of the Connection object/class.
Example
Methods
Following are the various methods provided by the Cursor class/object.
| Sr.No. | Methods & Description |
|---|---|
| 1 | callproc() This method is used to call existing procedures PostgreSQL database. |
| 2 | close() This method is used to close the current cursor object. |
| 3 | executemany() This method accepts a list series of parameters list. Prepares an MySQL query and executes it with all the parameters. |
| 4 | execute() This method accepts a MySQL query as a parameter and executes the given query. |
| 5 | fetchall() This method retrieves all the rows in the result set of a query and returns them as list of tuples. (If we execute this after retrieving few rows it returns the remaining ones) |
| 6 | fetchone() This method fetches the next row in the result of a query and returns it as a tuple. |
| 7 | fetchmany() This method is similar to the fetchone() but, it retrieves the next set of rows in the result set of a query, instead of a single row. |
Properties
Following are the properties of the Cursor class −
| Sr.No. | Property & Description |
|---|---|
| 1 | description This is a read only property which returns the list containing the description of columns in a result-set. |
| 2 | lastrowid This is a read only property, if there are any auto-incremented columns in the table, this returns the value generated for that column in the last INSERT or, UPDATE operation. |
| 3 | rowcount This returns the number of rows returned/updated in case of SELECT and UPDATE operations. |
| 4 | closed This property specifies whether a cursor is closed or not, if so it returns true, else false. |
| 5 | connection This returns a reference to the connection object using which this cursor was created. |
| 6 | name This property returns the name of the cursor. |
| 7 | scrollable This property specifies whether a particular cursor is scrollable. |
The psycopg database adapter is used to connect with PostgreSQL database server through python.
Installing psycopg:
First, use the following command line from the terminal:
If you have downloaded the source package into your computer, you can use the setup.py as follows:
Create a new database
First, log in to the PostgreSQL database server using any client tool such as pgAdmin or psql.
Second, use the following statement to create a new database named suppliers in the PostgreSQL database server.
Connect to the PostgreSQL database using the psycopg2
To connect to the suppliers database, you use the connect() function of the psycopg2 module.
The connect() function creates a new database session and returns a new instance of the connection class. By using the connection object, you can create a new cursor to execute any SQL statements.
To call the connect() function, you specify the PostgreSQL database parameters as a connection string and pass it to the function like this:
Or you can use a list of keyword arguments:
The following is the list of the connection parameters:
database: the name of the database that you want to connect.
user: the username used to authenticate.
password: password used to authenticate.
host: database server address e.g., localhost or an IP address.
port: the port number that defaults to 5432 if it is not provided.
To make it more convenient, you can use a configuration file to store all connection parameters.
The following shows the contents of the database.ini file:
By using the database.ini, you can change the PostgreSQL connection parameters when you move the code to the production environment without modifying the code.
Notice that if you git, you need to add the database.ini to the .gitignore file to not committing the sensitive information to the public repo like github. The .gitignore file will be like this:
The following config() function read the database.ini file and returns connection parameters. The config() function is placed in the config.py file:
The following connect() function connects to the suppliers database and prints out the PostgreSQL database version.
How it works.
First, read database connection parameters from the database.ini file.
Next, create a new database connection by calling the connect() function.
Then, create a new cursor and execute an SQL statement to get the PostgreSQL database version.
After that, read the result set by calling the fetchone() method of the cursor object.
Finally, close the communication with the database server by calling the close() method of the cursor and connection objects.
Execute the connect.py file
Postgresql Cursor Loop
To execute the connect.py file, you use the following command:
You will see the following output:
Troubleshooting
The connect() function raises the DatabaseError exception if an error occurred. To see how it works, you can change the connection parameters in the database.ini file.
For example, if you change the host to localhosts, the program will output the following message:
The following displays error message when you change the database to a database that does not exist e.g., supplier:
If you change the user to postgress, it will not be authenticated successfully as follows:
Attention geek! Strengthen your foundations with the Python Programming Foundation Course and learn the basics.
To begin with, your interview preparations Enhance your Data Structures concepts with the Python DS Course.
Recommended Posts:
Postgresql Cursor With Hold
If you like GeeksforGeeks and would like to contribute, you can also write an article using contribute.geeksforgeeks.org or mail your article to contribute@geeksforgeeks.org. See your article appearing on the GeeksforGeeks main page and help other Geeks.
Postgresql Cursor Insert Example
Please Improve this article if you find anything incorrect by clicking on the 'Improve Article' button below.
Postgresql Loop Through Rows