Rather than executing a whole query at once, it is possible to set up a cursor that encapsulates the query, and then read the query result a few rows at a time. One reason for doing this is to avoid memory overrun when the result contains a large number of rows. (However, PL/pgSQL users do not normally need to worry about that, since FOR Download source website php. loops automatically use a cursor internally to avoid memory problems.) A more interesting usage is to return a reference to a cursor that a function has created, allowing the caller to read the rows. This provides an efficient way to return large row sets from functions.
41.7.1. 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:
Class cursor ¶ Allows Python code to execute PostgreSQL command in a database session. Cursors are created by the connection.cursor method: they are bound to the connection for the entire lifetime and all the commands are executed in the context of the database session wrapped by the connection. Let us first consider a simple example of printing the table of a particular integer that we pass to our. 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: name NO SCROLL CURSOR (arguments) FOR query. 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.
(FOR can be replaced by IS for Oracle compatibility.) If SCROLL is specified, the cursor will be capable of scrolling backward; if NO SCROLL is specified, backward fetches will be rejected; if neither specification appears, it is query-dependent whether backward fetches will be allowed. arguments, if specified, is a comma-separated list of pairs namedatatype that define names to be replaced by parameter values in the given query. The actual values to substitute for these names will be specified later, when the cursor is opened.
Some examples:
All three of these variables have the data type refcursor, but the first can be used with any query, while the second has a fully specified query already bound to it, and the last has a parameterized query bound to it. (key will be replaced by an integer parameter value when the cursor is opened.) The variable curs1 is said to be unbound since it is not bound to any particular query.
41.7.2. Opening Cursors
Before a cursor can be used to retrieve rows, it must be opened. (This is the equivalent action to the SQL command DECLARE CURSOR.) PL/pgSQL has three forms of the OPEN statement, two of which use unbound cursor variables while the third uses a bound cursor variable.
Note: Bound cursor variables can also be used without explicitly opening the cursor, via the FOR statement described in Section 41.7.4.
41.7.2.1. OPEN FORquery
The cursor variable is opened and given the specified query to execute. The cursor cannot be open already, and it must have been declared as an unbound cursor variable (that is, as a simple refcursor variable). The query must be a SELECT, or something else that returns rows (such as EXPLAIN). The query is treated in the same way as other SQL commands in PL/pgSQL: PL/pgSQL variable names are substituted, and the query plan is cached for possible reuse. When a PL/pgSQL variable is substituted into the cursor query, the value that is substituted is the one it has at the time of the OPEN; subsequent changes to the variable will not affect the cursor's behavior. The SCROLL and NO SCROLL options have the same meanings as for a bound cursor.
An example:
41.7.2.2. OPEN FOR EXECUTE
The cursor variable is opened and given the specified query to execute. The cursor cannot be open already, and it must have been declared as an unbound cursor variable (that is, as a simple refcursor variable). The query is specified as a string expression, in the same way as in the EXECUTE command. As usual, this gives flexibility so the query plan can vary from one run to the next (see Section 41.10.2), and it also means that variable substitution is not done on the command string. As with EXECUTE, parameter values can be inserted into the dynamic command via format() and USING. The SCROLL and NO SCROLL options have the same meanings as for a bound cursor.
An example:
In this example, the table name is inserted into the query via format(). The comparison value for col1 is inserted via a USING parameter, so it needs no quoting.
41.7.2.3. Opening a Bound Cursor
This form of OPEN is used to open a cursor variable whose query was bound to it when it was declared. The cursor cannot be open already. A list of actual argument value expressions must appear if and only if the cursor was declared to take arguments. These values will be substituted in the query.
The query plan for a bound cursor is always considered cacheable; there is no equivalent of EXECUTE in this case. Notice that SCROLL and NO SCROLL cannot be specified in OPEN, as the cursor's scrolling behavior was already determined.
Argument values can be passed using either positional or named notation. In positional notation, all arguments are specified in order. In named notation, each argument's name is specified using := to separate it from the argument expression. Similar to calling functions, described in Section 4.3, it is also allowed to mix positional and named notation.
Examples (these use the cursor declaration examples above):
Because variable substitution is done on a bound cursor's query, there are really two ways to pass values into the cursor: either with an explicit argument to OPEN, or implicitly by referencing a PL/pgSQL variable in the query. However, only variables declared before the bound cursor was declared will be substituted into it. In either case the value to be passed is determined at the time of the OPEN. For example, another way to get the same effect as the curs3 example above is
41.7.3. Using Cursors
Once a cursor has been opened, it can be manipulated with the statements described here.
These manipulations need not occur in the same function that opened the cursor to begin with. You can return a refcursor value out of a function and let the caller operate on the cursor. (Internally, a refcursor value is simply the string name of a so-called portal containing the active query for the cursor. This name can be passed around, assigned to other refcursor variables, and so on, without disturbing the portal.)
All portals are implicitly closed at transaction end. Therefore a refcursor value is usable to reference an open cursor only until the end of the transaction.
41.7.3.1. FETCH
FETCH retrieves the next row from the cursor into a target, which might be a row variable, a record variable, or a comma-separated list of simple variables, just like SELECT INTO. If there is no next row, the target is set to NULL(s). As with SELECT INTO, the special variable FOUND can be checked to see whether a row was obtained or not.
The direction clause can be any of the variants allowed in the SQL FETCH command except the ones that can fetch more than one row; namely, it can be NEXT, PRIOR, FIRST, LAST, ABSOLUTEcount, RELATIVEcount, FORWARD, or BACKWARD. Omitting direction is the same as specifying NEXT. In the forms using a count, the count can be any integer-valued expression (unlike the SQL FETCH command, which only allows an integer constant). direction values that require moving backward are likely to fail unless the cursor was declared or opened with the SCROLL option.
cursor must be the name of a refcursor variable that references an open cursor portal.
Examples:
41.7.3.2. MOVE
MOVE repositions a cursor without retrieving any data. MOVE works exactly like the FETCH command, except it only repositions the cursor and does not return the row moved to. As with SELECT INTO, the special variable FOUND can be checked to see whether there was a next row to move to.
Examples:
41.7.3.3. UPDATE/DELETE WHERE CURRENT OF
When a cursor is positioned on a table row, that row can be updated or deleted using the cursor to identify the row. There are restrictions on what the cursor's query can be (in particular, no grouping) and it's best to use FOR UPDATE in the cursor. For more information see the DECLARE reference page.
An example:
41.7.3.4. CLOSE
CLOSE closes the portal underlying an open cursor. This can be used to release resources earlier than end of transaction, or to free up the cursor variable to be opened again.
An example:
41.7.3.5. Returning Cursors
PL/pgSQL functions can return cursors to the caller. This is useful to return multiple rows or columns, especially with very large result sets. To do this, the function opens the cursor and returns the cursor name to the caller (or simply opens the cursor using a portal name specified by or otherwise known to the caller). The caller can then fetch rows from the cursor. The cursor can be closed by the caller, or it will be closed automatically when the transaction closes.
The portal name used for a cursor can be specified by the programmer or automatically generated. To specify a portal name, simply assign a string to the refcursor variable before opening it. The string value of the refcursor variable will be used by OPEN as the name of the underlying portal. However, if the refcursor variable is null, OPEN automatically generates a name that does not conflict with any existing portal, and assigns it to the refcursor variable.
Note: A bound cursor variable is initialized to the string value representing its name, so that the portal name is the same as the cursor variable name, unless the programmer overrides it by assignment before opening the cursor. But an unbound cursor variable defaults to the null value initially, so it will receive an automatically-generated unique name, unless overridden.
The following example shows one way a cursor name can be supplied by the caller:
The following example uses automatic cursor name generation:
The following example shows one way to return multiple cursors from a single function:
41.7.4. Looping Through a Cursor's Result
There is a variant of the FOR statement that allows iterating through the rows returned by a cursor. The syntax is:
The cursor variable must have been bound to some query when it was declared, and it cannot be open already. The FOR statement automatically opens the cursor, and it closes the cursor again when the loop exits. A list of actual argument value expressions must appear if and only if the cursor was declared to take arguments. These values will be substituted in the query, in just the same way as during an OPEN (see Section 41.7.2.3).
The variable recordvar is automatically defined as type record and exists only inside the loop (any existing definition of the variable name is ignored within the loop). Each row returned by the cursor is successively assigned to this record variable and the loop body is executed.
In this Compose Write Stuff Addon, Lucero Del Alba takes a look at the problem of viewing queries sent to a server, and how to solve this problem by using Psycopg's mogrify. Mayan apocalypse 2020. Do you want to shed light on a favorite feature in your preferred database? Why not write a short 'Addon' for Write Stuff?
When using templates and variables within your script, it's hard to tell what the final query that was sent to the server was, which makes debugging tricky. This is where mogrify can help you.
The Problem
If you have a buggy query, the Python interpreter is going to tell you where the syntax error is, and it will do so by passing you back the DB response. Sometimes, however, it’s not quite a syntax error that's bugging you, but just an empty result set that makes you wonder what went wrong, like:
- did you query the right tables?
- was there some typo on the conditional clauses?
- did you use a lot of substitutions or build the query in such a way that you lost track of it?
Let’s take a step backwards for a moment.
If you use the Python DB API v2 properly you don’t need to worry about input sanitation because the driver does it transparently for you. For example, consider the following query:
If A% and 10 need to be variables, you can do the following:
In that snippet, execute takes a string with the query as a mandatory argument and a tuple as an optional one. The driver will sanitize the tuple’s content for you — in this case broker_name and broker_id — to prevent some SQL injection tricks, and it will treat them as string and integer, accordingly.
The problem is that you just can’t print out cursor.execute() to see what the query was, and even if you turn that line into a print() statement, it’ll most likely not return the actual query unless you’re only dealing with integers:
That is not the query that’s sent to the server — there are no string escaping characters (no apostrophes around A%), let alone any sanitation. If you try to run that query on the DB, you’ll receive an error.
Mogrify It
The PostgreSQL driver for Python, Psycopg, comes with a very handy method for dealing with these situations: mogrify. From the Psycopg documentation, mogrify:
Return[s] a query string after arguments binding. The string returned is exactly the one that would be sent to the database running the execute() method or similar
You can run mogrify() with the same arguments you would use for execute(), and the result will be as expected:
Now this was a simple enough query, but that complexity may escalate very quickly as you start joining tables using Python’s templates, such as {table_name}, to format later with the format() method when building your SQL code, and adding lots of variable substitutions … yes, things will get messy.
Take It Further
In fact, you can implement mogrify() in your workflow and use it as an intermediate step when querying the database, so that you can benefit from easier debugging later:
Notice that instead of doing the substitutions on execute(), you first mogrify the query and use that output for execution. Now you have the SQL template on a SQL constant, and the actual query in a query variable. Should you have a problem later, you just check that variable.
Not a Python DB API Feature … But There’s a Workaround
Unfortunately, mogrify is not a method defined by the Python DB API, but instead an add-on of the Psycopg driver.
If you're using MySQL, you have a workaround to this problem so that you can see the actual query:
However, this is a post hoc analysis (after the fact) will not allow you to see the full query if it provoked an exception in the program (e.g. a syntax error).
In PostgreSQL, you can also do this with the Psycopg driver by replacing _executed with query in the last line, like this:
Conclusions
We've shown ways in Python to see what’s the actual query that’s been sent to the server, and this will save you quality time when debugging. If you want to go further, consider that Psycopg's mogrify for PostgreSQL allows you to cache the actual executed statement so you can reuse it whenever you need it.