Reported by
Tomas Janco tomas.janco@myinphinity.com
I have found following problem with JDBC driver:
When an updatable result set is created for a table without primary key, any update fails with error: «syntax error at end of input»
The driver generates invalid SQL query to update the table.
JDBC driver version: 42.2.18 (jre8)
Server version: PostgreSQL 9.6.6, compiled by Visual C++ build 1800, 64-bit
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
public class TestPgSql {
/* test table schema:
CREATE TABLE public.sample
(
id integer,
value character varying(255) COLLATE pg_catalog.»default»
)
WITH (
OIDS = FALSE
)
TABLESPACE pg_default;
INSERT INTO public.sample(id, value)
VALUES (1, ‘abcd’);
*/
public static void main(String args[]) throws Exception {
Connection conn = DriverManager.getConnection("jdbc:postgresql://localhost/...?user=....&password=....");
String sql = "SELECT * FROM sample WHERE id = 1;";
PreparedStatement stmt = conn.prepareStatement(sql, ResultSet.CONCUR_UPDATABLE, ResultSet.TYPE_FORWARD_ONLY);
ResultSet rs = stmt.executeQuery();
rs.next();
rs.updateString("value", "something");
rs.updateRow();
rs.close();
}
}
Expected behavior:
The code successfully updates the table OR throws an error explaining primary key is not present in result set and is required for updatable result set.
Actual behavior:
Incorrect SQL command is generated internally: «UPDATE sample SET «value» = $1 WHERE «
The query is missing the WHERE condition expression.
This results in following exception being thrown:
Exception in thread «main» org.postgresql.util.PSQLException: ERROR: syntax error at end of input
Position: 39
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2553)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2285)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:323)
at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:473)
at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:393)
at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:164)
at org.postgresql.jdbc.PgPreparedStatement.executeUpdate(PgPreparedStatement.java:130)
at org.postgresql.jdbc.PgResultSet.updateRow(PgResultSet.java:1445)
at TestPgSql.main(TestPgSql.java:35)
New in version 2.8.
Changed in version 2.8.4: added errors introduced in PostgreSQL 12
Changed in version 2.8.6: added errors introduced in PostgreSQL 13
Changed in version 2.9.2: added errors introduced in PostgreSQL 14
Changed in version 2.9.4: added errors introduced in PostgreSQL 15
This module exposes the classes psycopg raises upon receiving an error from
the database with a SQLSTATE value attached (available in the
pgcode attribute). The content of the module is generated
from the PostgreSQL source code and includes classes for every error defined
by PostgreSQL in versions between 9.1 and 15.
Every class in the module is named after what referred as “condition name” in
the documentation, converted to CamelCase: e.g. the error 22012,
division_by_zero is exposed by this module as the class DivisionByZero.
Every exception class is a subclass of one of the standard DB-API
exception and expose the Error interface.
Each class’ superclass is what used to be raised by psycopg in versions before
the introduction of this module, so everything should be compatible with
previously written code catching one the DB-API class: if your code used to
catch IntegrityError to detect a duplicate entry, it will keep on working
even if a more specialised subclass such as UniqueViolation is raised.
The new classes allow a more idiomatic way to check and process a specific
error among the many the database may return. For instance, in order to check
that a table is locked, the following code could have been used previously:
try: cur.execute("LOCK TABLE mytable IN ACCESS EXCLUSIVE MODE NOWAIT") except psycopg2.OperationalError as e: if e.pgcode == psycopg2.errorcodes.LOCK_NOT_AVAILABLE: locked = True else: raise
While this method is still available, the specialised class allows for a more
idiomatic error handler:
try: cur.execute("LOCK TABLE mytable IN ACCESS EXCLUSIVE MODE NOWAIT") except psycopg2.errors.LockNotAvailable: locked = True
- psycopg2.errors.lookup(code)¶
-
Lookup an error code and return its exception class.
Raise
KeyErrorif the code is not found.try: cur.execute("LOCK TABLE mytable IN ACCESS EXCLUSIVE MODE NOWAIT") except psycopg2.errors.lookup("55P03"): locked = True
SQLSTATE exception classes¶
The following table contains the list of all the SQLSTATE classes exposed by
the module.
Note that, for completeness, the module also exposes all the
DB-API-defined exceptions and a few
psycopg-specific ones exposed by the extensions
module, which are not listed here.
|
SQLSTATE |
Exception |
Base exception |
|---|---|---|
|
Class 02: No Data (this is also a warning class per the SQL standard) |
||
|
|
|
|
|
|
|
|
|
Class 03: SQL Statement Not Yet Complete |
||
|
|
|
|
|
Class 08: Connection Exception |
||
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Class 09: Triggered Action Exception |
||
|
|
|
|
|
Class 0A: Feature Not Supported |
||
|
|
|
|
|
Class 0B: Invalid Transaction Initiation |
||
|
|
|
|
|
Class 0F: Locator Exception |
||
|
|
|
|
|
|
|
|
|
Class 0L: Invalid Grantor |
||
|
|
|
|
|
|
|
|
|
Class 0P: Invalid Role Specification |
||
|
|
|
|
|
Class 0Z: Diagnostics Exception |
||
|
|
|
|
|
|
|
|
|
Class 20: Case Not Found |
||
|
|
|
|
|
Class 21: Cardinality Violation |
||
|
|
|
|
|
Class 22: Data Exception |
||
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Class 23: Integrity Constraint Violation |
||
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Class 24: Invalid Cursor State |
||
|
|
|
|
|
Class 25: Invalid Transaction State |
||
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Class 26: Invalid SQL Statement Name |
||
|
|
|
|
|
Class 27: Triggered Data Change Violation |
||
|
|
|
|
|
Class 28: Invalid Authorization Specification |
||
|
|
|
|
|
|
|
|
|
Class 2B: Dependent Privilege Descriptors Still Exist |
||
|
|
|
|
|
|
|
|
|
Class 2D: Invalid Transaction Termination |
||
|
|
|
|
|
Class 2F: SQL Routine Exception |
||
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Class 34: Invalid Cursor Name |
||
|
|
|
|
|
Class 38: External Routine Exception |
||
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Class 39: External Routine Invocation Exception |
||
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Class 3B: Savepoint Exception |
||
|
|
|
|
|
|
|
|
|
Class 3D: Invalid Catalog Name |
||
|
|
|
|
|
Class 3F: Invalid Schema Name |
||
|
|
|
|
|
Class 40: Transaction Rollback |
||
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Class 42: Syntax Error or Access Rule Violation |
||
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Class 44: WITH CHECK OPTION Violation |
||
|
|
|
|
|
Class 53: Insufficient Resources |
||
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Class 54: Program Limit Exceeded |
||
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Class 55: Object Not In Prerequisite State |
||
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Class 57: Operator Intervention |
||
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Class 58: System Error (errors external to PostgreSQL itself) |
||
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Class 72: Snapshot Failure |
||
|
|
|
|
|
Class F0: Configuration File Error |
||
|
|
|
|
|
|
|
|
|
Class HV: Foreign Data Wrapper Error (SQL/MED) |
||
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Class P0: PL/pgSQL Error |
||
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Class XX: Internal Error |
||
|
|
|
|
|
|
|
|
|
|
|
|
Introduction
This article will provide a brief overview of how you can better handle PostgreSQL Python exceptions while using the psycopg2 adapter in your code. Make sure that the psycopg2 package is installed on your machine using the PIP3 package manager for Python 3 using the following command:
We’ll also be building a function from scratch that prints detailed information about the psycopg2 exceptions by accessing several of its exception library attributes. It should be noted, however, that this is mostly for educational and debugging purposes, and it should be noted that, in the implementation phase your website or application, you may want to handle them less explicitly.
Catching and handling exceptions in Python
A Python script will terminate as soon as an exception or error is raised, but there is a try-except block (that works in a similar fashion to the try {} catch(err) {} code block in PHP or JavaScript) that will allow you to catch the exception, handle it, and then respond to it with more code within the except: part of the indentation block.
The following code allows you to catch all exceptions, as a wildcard, and print them out without having to explicitly mention the exact exception:
|
1 |
try: |
the except Exception as error: bit will allow you to handle any exception, and return the exception information as a TypeError class object using Python’s as keyword.
Exception libraries for the psycopg2 Python adapter
Some of the two most commonly occurring exceptions in the psycopg2 library are the OperationalError and ProgrammingError exception classes.
An OperationalError typically occurs when the parameters passed to the connect() method are incorrect, or if the server runs out of memory, or if a piece of datum cannot be found, etc.
A ProgrammingError happens when there is a syntax error in the SQL statement string passed to the psycopg2 execute() method, or if a SQL statement is executed to delete a non-existent table, or an attempt is made to create a table that already exists, and exceptions of that nature.
Complete list of the psycopg2 exception classes
Here’s the complete list of all of psycopg2 exception classes:
InterfaceError, DatabaseError, DataError, OperationalError, IntegrityError, InternalError, ProgrammingError, and NotSupportedError.
Brief overview of PostgreSQL Error Codes
There is an extensive list of over 200 error codes on the postgresql.org website that describes, in detail, each five-character SQL exception.
In the psycopg2 adapter library you can return the code by accessing the exception’s pgcode attribute. It should be an alpha-numeric string, five characters in length, that corresponds to an exception in the PostgreSQL Error Codes table.
Here’s some example code showing how one can access the attribute for the PostgreSQL error code:
|
1 |
try: |
Import the exception libraries for the psycopg2 Python adapter
You’ll need to import the following libraries at the beginning of your Python script:
|
1 |
# import sys to get more detailed Python exception info # import the connect library for psycopg2 # import the error handling libraries for psycopg2 |
Get the psycopg2 version string
Older versions of the psycopg2 adapter may handle some exceptions differently. Here’s some code that imports the __version__ attribute string for the psycopg2 library and prints it:
|
1 |
# import the psycopg2 library’s __version__ string # print the version string for psycopg2 |
Define a Python function to handle and print psycopg2 SQL exceptions
The code in this section will define a Python function that will take a Python TypeError object class and parse, both the psycopg2 and native Python, exception attributes from it in order to print the details of the exception:
Define the ‘print_psycopg2_exception()’ Python function
Use Python’s def keyword to define a new function and make it accept a TypeError Python object class as its only parameter:
|
1 |
# define a function that handles and parses psycopg2 exceptions |
Use Python’s built-in ‘sys’ library to get more detailed exception information
The next bit of code grabs the traceback information for the exception, including the line number in the code that the error occurred on, by calling the sys library’s exc_info() method:
|
1 |
# get details about the exception # get the line number when exception occured |
Print the details for the psycopg2 exception
Use Python’s print() function to print the details of the psycopg2 exception that was passed to the function call:
|
1 |
# print the connect() error # psycopg2 extensions.Diagnostics object attribute # print the pgcode and pgerror exceptions |
Handle psycopg2 exceptions that occur while connecting to PostgreSQL
Now that the function has been defined it’s time to test it out by running some psycopg2 code. The following Python code attempts to make a connection to PostgreSQL in a try-except indentation block, and, in the case of an exception, passes the TypeError Python object to the print_psycopg2_exception() function defined earlier:
|
1 |
# declare a new PostgreSQL connection object # set the connection to ‘None’ in case of error |
NOTE: The above code will give the connection object a value of None in the case of an exception.
If the username string, passed to the user parameter, doesn’t match any of the users for the PostgreSQL server then the function should print something that closely resembles the following:
|
1 |
psycopg2 ERROR: FATAL: password authentication failed for user «WRONG_USER» extensions.Diagnostics: <psycopg2.extensions.Diagnostics object at 0x7fa3646e1558> |
Handle psycopg2 exceptions that occur while executing SQL statements
If the code to connect to PostgreSQL didn’t have any problems, and no exceptions were raised, then test out the function again. The following code purposely attempts to use a cursor object to execute() a SQL statement with bad syntax:
|
1 |
# if the connection was successful # declare a cursor object from the connection # catch exception for invalid SQL statement # rollback the previous transaction before starting another |
The print_psycopg2_exception() function should print a response that resembles the following:
|
1 |
psycopg2 ERROR: syntax error at or near «INVALID» extensions.Diagnostics: <psycopg2.extensions.Diagnostics object at 0x7f58e0018558> pgcode: 42601 |
The 42601 PostgreSQL code indicates that the exception resulted from a syntax error in the SQL statement:
Catch ‘InFailedSqlTransaction’ psycopg2 exceptions
This last bit of Python code will raise a InFailedSqlTransaction exception if the last PostgreSQL transaction, with the bad SQL statement, wasn’t rolled back using the connection object’s rollback() method:
|
1 |
# returns ‘psycopg2.errors.InFailedSqlTransaction’ if rollback() not called |
Conclusion
The psycopg2 library adapter for PostgreSQL has an extensive list of exception Python classes, and this article only covered a few of them just to give a general idea of how you can handle such exceptions in your own Python script.
Just the Code
|
1 |
# import sys to get more detailed Python exception info # import the connect library for psycopg2 # import the error handling libraries for psycopg2 # import the psycopg2 library’s __version__ string # import sys to get more detailed Python exception info # import the connect library for psycopg2 # import the error handling libraries for psycopg2 # import the psycopg2 library’s __version__ string # print the version string for psycopg2 # define a function that handles and parses psycopg2 exceptions # get the line number when exception occured # print the connect() error # psycopg2 extensions.Diagnostics object attribute # print the pgcode and pgerror exceptions try: # set the connection to ‘None’ in case of error # if the connection was successful # declare a cursor object from the connection # catch exception for invalid SQL statement # rollback the previous transaction before starting another # execute a PostgreSQL command to get all rows in a table # close the cursor object to avoid memory leaks # close the connection object also |
Pilot the ObjectRocket Platform Free!
Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.
Get Started
EDIT: IN THE END, I’VE FOUND A WAY AROUND IT
I don’t know what I’m doing wrong with my syntax.
with engine.connect() as connection:
# sanity check, this line works just fine:
insert = connection.execute("INSERT INTO books (isbn, name, author, year) VALUES ('12345', 'ABCD', 'ABCD', '1234')")
# now with the main file:
with open('books.csv') as file:
reader = csv.reader(file)
next(reader)
for isbn, name, author, year in reader:
connection.execute('''INSERT INTO books (isbn, name, author, year) VALUES (? ? ? ?)''', (isbn, name, author, year))
'''
other kind of query I've tried:
sql_statement = "INSERT INTO books (isbn, name, author, year) VALUES (:isbn, :name, :author, :year)"
values = {'isbn': isbn, 'name': name, 'author': author, 'year': year}
connection.execute(sql_statement, values)
'''
When I try to run I get this error:
2020-04-16 01:40:47,082 INFO sqlalchemy.engine.base.Engine INSERT INTO books (isbn, name, author, year) VALUES (:isbn, :name, :author, :year)
2020-04-16 01:40:47,082 INFO sqlalchemy.engine.base.Engine {'isbn': '0380795272', 'name': 'Krondor: The Betrayal', 'author': 'Raymond E. Feist', 'year': '1998'}
2020-04-16 01:40:47,309 INFO sqlalchemy.engine.base.Engine ROLLBACK
[...]
psycopg2.errors.SyntaxError: syntax error at or near ":"
LINE 1: ...ERT INTO books (isbn, name, author, year) VALUES (:isbn, :na...
^
The above exception was the direct cause of the following exception:
[...]
sqlalchemy.exc.ProgrammingError: (psycopg2.errors.SyntaxError) syntax error at or near ":"
LINE 1: ...ERT INTO books (isbn, name, author, year) VALUES (:isbn, :na...
^
[SQL: INSERT INTO books (isbn, name, author, year) VALUES (:isbn, :name, :author, :year)]
[parameters: {'isbn': '0380795272', 'name': 'Krondor: The Betrayal', 'author': 'Raymond E. Feist', 'year': '1998'}]
(Background on this error at: http://sqlalche.me/e/f405)
[Finished in 2.9s]
I’ve read a lot about PostgreSQL parameters but still can’t figure out what I’m doing wrong.
EDIT: This way it works. I’m not entirely sure about what solved it. Maybe the reader wasn’t passing the dict the way the query was expecting, maybe the «text» attribute for the statement solved the placeholder problem…. I’d still like to know what could be done better. (I forgot to mention, I’m not supposed to use ORM, only raw SQL queries). It is taking forever to send 5k queries because I couldn’t use «commit()», but at least it is working.
import csv
import sqlalchemy
from sqlalchemy import create_engine, text
engine = create_engine('[database_url', echo=True)
with engine.connect() as connection:
with open('books.csv') as file:
reader = csv.reader(file)
next(reader)
data = []
for isbn, name, author, year in reader:
data.append({"isbn": isbn, "name": name, "author": author, "year": year})
statement = text("INSERT INTO books (isbn, name, author, year) VALUES (:isbn, :name, :author, :year)")
for line in data:
connection.execute(statement, **line)