Psycopg2 errors syntaxerror syntax error at or near where

Python Error Handling with the Psycopg2 PostgreSQL Adapter 645 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 […]

Содержание

  1. Python Error Handling with the Psycopg2 PostgreSQL Adapter 645
  2. Introduction
  3. Catching and handling exceptions in Python
  4. Exception libraries for the psycopg2 Python adapter
  5. Complete list of the psycopg2 exception classes
  6. Brief overview of PostgreSQL Error Codes
  7. Import the exception libraries for the psycopg2 Python adapter
  8. Get the psycopg2 version string
  9. Define a Python function to handle and print psycopg2 SQL exceptions
  10. Define the ‘print_psycopg2_exception()’ Python function
  11. Use Python’s built-in ‘sys’ library to get more detailed exception information
  12. Print the details for the psycopg2 exception
  13. Handle psycopg2 exceptions that occur while connecting to PostgreSQL
  14. Handle psycopg2 exceptions that occur while executing SQL statements
  15. Catch ‘InFailedSqlTransaction’ psycopg2 exceptions
  16. Conclusion
  17. Just the Code
  18. Pilot the ObjectRocket Platform Free!

Python Error Handling with the Psycopg2 PostgreSQL Adapter 645

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:

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:

Import the exception libraries for the psycopg2 Python adapter

You’ll need to import the following libraries at the beginning of your Python script:

# import sys to get more detailed Python exception info
import sys

# import the connect library for psycopg2
from psycopg2 import connect

# import the error handling libraries for psycopg2
from psycopg2 import OperationalError , errorcodes , errors

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:

# import the psycopg2 library’s __version__ string
from psycopg2 import __version__ as psycopg2_version

# print the version string for psycopg2
print ( «psycopg2 version:» , psycopg2_version , » n » )

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:

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:

# get details about the exception
err_type , err_obj , traceback = sys . exc_info ( )

# get the line number when exception occured
line_num = traceback . tb_lineno

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:

# print the connect() error
print ( » n psycopg2 ERROR:» , err , «on line number:» , line_num )
print ( «psycopg2 traceback:» , traceback , «— type:» , err_type )

# psycopg2 extensions.Diagnostics object attribute
print ( » n extensions.Diagnostics:» , err. diag )

# print the pgcode and pgerror exceptions
print ( «pgerror:» , err. pgerror )
print ( «pgcode:» , err. pgcode , » n » )

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:

# declare a new PostgreSQL connection object
try :
conn = connect (
dbname = «python_test» ,
user = «WRONG_USER» ,
host = «localhost» ,
password = «mypass»
)
except OperationalError as err:
# pass exception to function
print_psycopg2_exception ( err )

# set the connection to ‘None’ in case of error
conn = None

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:

psycopg2 ERROR: FATAL: password authentication failed for user «WRONG_USER»
FATAL: password authentication failed for user «WRONG_USER»
on line number: 43
psycopg2 traceback: — type:

pgerror: None
pgcode: None

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:

# if the connection was successful
if conn != None :

# declare a cursor object from the connection
cursor = conn. cursor ( )
print ( «cursor object:» , cursor , » n » )

# catch exception for invalid SQL statement
try :
cursor. execute ( «INVALID SQL STATEMENT» )
except Exception as err:
# pass exception to function
print_psycopg2_exception ( err )

# rollback the previous transaction before starting another
conn. rollback ( )

The print_psycopg2_exception() function should print a response that resembles the following:

psycopg2 ERROR: syntax error at or near «INVALID»
LINE 1: INVALID SQL STATEMENT
^
on line number: 90
psycopg2 traceback: — type:

pgerror: ERROR: syntax error at or near «INVALID»
LINE 1: INVALID SQL STATEMENT
^

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:

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

# import sys to get more detailed Python exception info
import sys

# import the connect library for psycopg2
from psycopg2 import connect

# import the error handling libraries for psycopg2
from psycopg2 import OperationalError , errorcodes , errors

# import the psycopg2 library’s __version__ string
from psycopg2 import __version__ as psycopg2_version
«`python
#!/usr/bin/python3
# -*- coding: utf-8 -*-

# import sys to get more detailed Python exception info
import sys

# import the connect library for psycopg2
from psycopg2 import connectDoes NOT need TextBroker to re-write

# import the error handling libraries for psycopg2
from psycopg2 import OperationalError , errorcodes , errors

# import the psycopg2 library’s __version__ string
from psycopg2 import __version__ as psycopg2_version

# print the version string for psycopg2
print ( «psycopg2 version:» , psycopg2_version , » n » )

# define a function that handles and parses psycopg2 exceptions
def print_psycopg2_exception ( err ) :
# get details about the exception
err_type , err_obj , traceback = sys . exc_info ( )

# get the line number when exception occured
line_num = traceback . tb_lineno

# print the connect() error
print ( » n psycopg2 ERROR:» , err , «on line number:» , line_num )
print ( «psycopg2 traceback:» , traceback , «— type:» , err_type )

# psycopg2 extensions.Diagnostics object attribute
print ( » n extensions.Diagnostics:» , err. diag )

# print the pgcode and pgerror exceptions
print ( «pgerror:» , err. pgerror )
print ( «pgcode:» , err. pgcode , » n » )

try :
conn = connect (
dbname = «python_test» ,
user = «objectrocket» ,
host = «localhost» ,
password = «mypass»
)
except OperationalError as err:
# pass exception to function
print_psycopg2_exception ( err )

# set the connection to ‘None’ in case of error
conn = None

# if the connection was successful
if conn != None :

# declare a cursor object from the connection
cursor = conn. cursor ( )
print ( «cursor object:» , cursor , » n » )

# catch exception for invalid SQL statement
try :
cursor. execute ( «INVALID SQL STATEMENT» )
except Exception as err:
# pass exception to function
print_psycopg2_exception ( err )

# rollback the previous transaction before starting another
conn. rollback ( )

# execute a PostgreSQL command to get all rows in a table
# returns ‘psycopg2.errors.InFailedSqlTransaction’ if rollback() not called
try :
cursor. execute ( «SELECT * FROM some_table;» )
except errors. InFailedSqlTranroughsaction as err:
# pass exception to function
print_psycopg2_exception ( err )

# close the cursor object to avoid memory leaks
cursor. close ( )

# close the connection object also
conn. close ( )

Pilot the ObjectRocket Platform Free!

Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.

Источник

Источник

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
2
3
4
5
6

try:
    a = 1234
    print (a + » hello world»)
except Exception as error:
    print («Oops! An exception has occured:», error)
    print («Exception TYPE:», type(error))

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.

Screenshot of Python IDLE returning exception information in a try-except indentation block

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
2
3
4
5

try:
        cursor.execute(«INVALID SQL STATEMENT»)
    except Exception as err:
        print («Oops! An exception has occured:», error)
        print («Exception TYPE:», type(error))

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
2
3
4
5
6
7
8

# import sys to get more detailed Python exception info
import sys

# import the connect library for psycopg2
from psycopg2 import connect

# import the error handling libraries for psycopg2
from psycopg2 import OperationalError, errorcodes, errors

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
2
3
4
5

# import the psycopg2 library’s __version__ string
from psycopg2 import __version__ as psycopg2_version

# print the version string for psycopg2
print («psycopg2 version:», psycopg2_version, «n«)

Screenshot of Python's IDLE getting 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
2

# define a function that handles and parses psycopg2 exceptions
def print_psycopg2_exception(err):

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
2
3
4
5
   

# get details about the exception
    err_type, err_obj, traceback = sys.exc_info()

    # get the line number when exception occured
    line_num = traceback.tb_lineno

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
2
3
4
5
6
7
8
9
10
   

# print the connect() error
    print («npsycopg2 ERROR:», err, «on line number:», line_num)
    print («psycopg2 traceback:», traceback, «— type:», err_type)

    # psycopg2 extensions.Diagnostics object attribute
    print («nextensions.Diagnostics:», err.diag)

    # print the pgcode and pgerror exceptions
    print («pgerror:», err.pgerror)
    print («pgcode:», err.pgcode, «n«)

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
2
3
4
5
6
7
8
9
10
11
12
13
14

# declare a new PostgreSQL connection object
try:
    conn = connect(
        dbname = «python_test»,
        user = «WRONG_USER»,
        host = «localhost»,
        password = «mypass»
    )
except OperationalError as err:
    # pass exception to function
    print_psycopg2_exception(err)

    # set the connection to ‘None’ in case of error
    conn = None

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
2
3
4
5
6
7
8

psycopg2 ERROR: FATAL:  password authentication failed for user «WRONG_USER»
FATAL:  password authentication failed for user «WRONG_USER»
 on line number: 43
psycopg2 traceback: <traceback object at 0x7fa361660a88> — type: <class ‘psycopg2.OperationalError’>

extensions.Diagnostics: <psycopg2.extensions.Diagnostics object at 0x7fa3646e1558>
pgerror: None
pgcode: None

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
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

# if the connection was successful
if conn != None:

    # declare a cursor object from the connection
    cursor = conn.cursor()
    print («cursor object:», cursor, «n«)

    # catch exception for invalid SQL statement
    try:
        cursor.execute(«INVALID SQL STATEMENT»)
    except Exception as err:
        # pass exception to function
        print_psycopg2_exception(err)

        # rollback the previous transaction before starting another
        conn.rollback()

The print_psycopg2_exception() function should print a response that resembles the following:

1
2
3
4
5
6
7
8
9
10
11
12

psycopg2 ERROR: syntax error at or near «INVALID»
LINE 1: INVALID SQL STATEMENT
        ^
 on line number: 90
psycopg2 traceback: <traceback object at 0x7f58dd244188> — type: <class ‘psycopg2.errors.SyntaxError’>

extensions.Diagnostics: <psycopg2.extensions.Diagnostics object at 0x7f58e0018558>
pgerror: ERROR:  syntax error at or near «INVALID»
LINE 1: INVALID SQL STATEMENT
        ^

pgcode: 42601

The 42601 PostgreSQL code indicates that the exception resulted from a syntax error in the SQL statement:

Screenshot of the PostgreSQL Error Codes web page in a browser tab

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
2
3
4
5
6

# returns ‘psycopg2.errors.InFailedSqlTransaction’ if rollback() not called
    try:
        cursor.execute(«SELECT * FROM some_table;»)
    except errors.InFailedSqlTransaction as err:
        # pass exception to function
        print_psycopg2_exception(err)

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
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93

# import sys to get more detailed Python exception info
import sys

# import the connect library for psycopg2
from psycopg2 import connect

# import the error handling libraries for psycopg2
from psycopg2 import OperationalError, errorcodes, errors

# import the psycopg2 library’s __version__ string
from psycopg2 import __version__ as psycopg2_version
«`python
#!/usr/bin/python3
# -*- coding: utf-8 -*-

# import sys to get more detailed Python exception info
import sys

# import the connect library for psycopg2
from psycopg2 import connectDoes NOT need TextBroker to re-write

# import the error handling libraries for psycopg2
from psycopg2 import OperationalError, errorcodes, errors

# import the psycopg2 library’s __version__ string
from psycopg2 import __version__ as psycopg2_version

# print the version string for psycopg2
print («psycopg2 version:», psycopg2_version, «n«)

# define a function that handles and parses psycopg2 exceptions
def print_psycopg2_exception(err):
    # get details about the exception
    err_type, err_obj, traceback = sys.exc_info()

    # get the line number when exception occured
    line_num = traceback.tb_lineno

    # print the connect() error
    print («npsycopg2 ERROR:», err, «on line number:», line_num)
    print («psycopg2 traceback:», traceback, «— type:», err_type)

    # psycopg2 extensions.Diagnostics object attribute
    print («nextensions.Diagnostics:», err.diag)

    # print the pgcode and pgerror exceptions
    print («pgerror:», err.pgerror)
    print («pgcode:», err.pgcode, «n«)

try:
    conn = connect(
        dbname = «python_test»,
        user = «objectrocket»,
        host = «localhost»,
        password = «mypass»
    )
except OperationalError as err:
    # pass exception to function
    print_psycopg2_exception(err)

    # set the connection to ‘None’ in case of error
    conn = None

# if the connection was successful
if conn != None:

    # declare a cursor object from the connection
    cursor = conn.cursor()
    print («cursor object:», cursor, «n«)

    # catch exception for invalid SQL statement
    try:
        cursor.execute(«INVALID SQL STATEMENT»)
    except Exception as err:
        # pass exception to function
        print_psycopg2_exception(err)

        # rollback the previous transaction before starting another
        conn.rollback()

    # execute a PostgreSQL command to get all rows in a table
    # returns ‘psycopg2.errors.InFailedSqlTransaction’ if rollback() not called
    try:
        cursor.execute(«SELECT * FROM some_table;»)
    except errors.InFailedSqlTranroughsaction as err:
        # pass exception to function
        print_psycopg2_exception(err)

    # close the cursor object to avoid memory leaks
    cursor.close()

    # close the connection object also
    conn.close()

Pilot the ObjectRocket Platform Free!

Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.

Get Started

Источник
statement = """
INSERT INTO images
(id, width, height, order, imageable_type, imageable_id, created_at, updated_at, file_file_name, file_content_type, file_file_size, file_updated_at)
VALUES
(%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s), (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s), (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s), (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s), (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s), (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s), (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s), (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s), (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s), (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s), (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s), (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s), (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s), (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s), (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s), (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s), (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s), (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s), (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s), (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s), (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s), (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s), (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s), (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s), (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s), (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s), (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s), (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s), (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s), (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s), (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s), (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s), (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s), (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s), (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s), (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s), (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s), (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s), (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
"""

Yields me:

SyntaxError: syntax error at or near "order"
LINE 3:         (id, width, height, order, imageable_type, imageable...
Источник

Psycopg 3 uses the common DBAPI structure of many other database adapters and
tries to behave as close as possible to psycopg2. There are however a few
differences to be aware of.

Tip

Most of the times, the workarounds suggested here will work with both
Psycopg 2 and 3, which could be useful if you are porting a program or
writing a program that should work with both Psycopg 2 and 3.

Server-side binding#

Psycopg 3 sends the query and the parameters to the server separately, instead
of merging them on the client side. Server-side binding works for normal
SELECT and data manipulation statements (INSERT, UPDATE,
DELETE), but it doesn’t work with many other statements. For instance,
it doesn’t work with SET or with NOTIFY:

>>> conn.execute("SET TimeZone TO %s", ["UTC"])
Traceback (most recent call last):
...
psycopg.errors.SyntaxError: syntax error at or near "$1"
LINE 1: SET TimeZone TO $1
                        ^

>>> conn.execute("NOTIFY %s, %s", ["chan", 42])
Traceback (most recent call last):
...
psycopg.errors.SyntaxError: syntax error at or near "$1"
LINE 1: NOTIFY $1, $2
               ^

and with any data definition statement:

>>> conn.execute("CREATE TABLE foo (id int DEFAULT %s)", [42])
Traceback (most recent call last):
...
psycopg.errors.UndefinedParameter: there is no parameter $1
LINE 1: CREATE TABLE foo (id int DEFAULT $1)
                                         ^

Sometimes, PostgreSQL offers an alternative: for instance the set_config()
function can be used instead of the SET statement, the pg_notify()
function can be used instead of NOTIFY:

>>> conn.execute("SELECT set_config('TimeZone', %s, false)", ["UTC"])

>>> conn.execute("SELECT pg_notify(%s, %s)", ["chan", "42"])

If this is not possible, you must merge the query and the parameter on the
client side. You can do so using the psycopg.sql objects:

>>> from psycopg import sql

>>> cur.execute(sql.SQL("CREATE TABLE foo (id int DEFAULT {})").format(42))

or creating a client-side binding cursor
such as ClientCursor:

>>> cur = ClientCursor(conn)
>>> cur.execute("CREATE TABLE foo (id int DEFAULT %s)", [42])

If you need ClientCursor often, you can set the Connection.cursor_factory
to have them created by default by Connection.cursor(). This way, Psycopg 3
will behave largely the same way of Psycopg 2.

Note that, both server-side and client-side, you can only specify values
as parameters (i.e. the strings that go in single quotes). If you need to
parametrize different parts of a statement (such as a table name), you must
use the psycopg.sql module:

>>> from psycopg import sql

# This will quote the user and the password using the right quotes
# e.g.: ALTER USER "foo" SET PASSWORD 'bar'
>>> conn.execute(
...     sql.SQL("ALTER USER {} SET PASSWORD {}")
...     .format(sql.Identifier(username), password))

Multiple statements in the same query#

As a consequence of using server-side bindings,
when parameters are used, it is not possible to execute several statements in
the same execute() call, separating them by semicolon:

>>> conn.execute(
...     "INSERT INTO foo VALUES (%s); INSERT INTO foo VALUES (%s)",
...     (10, 20))
Traceback (most recent call last):
...
psycopg.errors.SyntaxError: cannot insert multiple commands into a prepared statement

One obvious way to work around the problem is to use several execute()
calls.

There is no such limitation if no parameters are used. As a consequence, you
can compose a multiple query on the client side and run them all in the same
execute() call, using the psycopg.sql objects:

>>> from psycopg import sql
>>> conn.execute(
...     sql.SQL("INSERT INTO foo VALUES ({}); INSERT INTO foo values ({})"
...     .format(10, 20))

or a client-side binding cursor:

>>> cur = psycopg.ClientCursor(conn)
>>> cur.execute(
...     "INSERT INTO foo VALUES (%s); INSERT INTO foo VALUES (%s)",
...     (10, 20))

Warning

If a statements must be executed outside a transaction (such as
CREATE DATABASE), it cannot be executed in batch with other
statements, even if the connection is in autocommit mode:

>>> conn.autocommit = True
>>> conn.execute("CREATE DATABASE foo; SELECT 1")
Traceback (most recent call last):
...
psycopg.errors.ActiveSqlTransaction: CREATE DATABASE cannot run inside a transaction block

This happens because PostgreSQL itself will wrap multiple statements in a
transaction. Note that your will experience a different behaviour in
psql (psql will split the queries on semicolons and
send them to the server separately).

This is not new in Psycopg 3: the same limitation is present in
psycopg2 too.

Multiple results returned from multiple statements#

If more than one statement returning results is executed in psycopg2, only the
result of the last statement is returned:

>>> cur_pg2.execute("SELECT 1; SELECT 2")
>>> cur_pg2.fetchone()
(2,)

In Psycopg 3 instead, all the results are available. After running the query,
the first result will be readily available in the cursor and can be consumed
using the usual fetch*() methods. In order to access the following
results, you can use the Cursor.nextset() method:

>>> cur_pg3.execute("SELECT 1; SELECT 2")
>>> cur_pg3.fetchone()
(1,)

>>> cur_pg3.nextset()
True
>>> cur_pg3.fetchone()
(2,)

>>> cur_pg3.nextset()
None  # no more results

Remember though that you cannot use server-side bindings to execute more
than one statement in the same query.

Different cast rules#

In rare cases, especially around variadic functions, PostgreSQL might fail to
find a function candidate for the given data types:

>>> conn.execute("SELECT json_build_array(%s, %s)", ["foo", "bar"])
Traceback (most recent call last):
...
psycopg.errors.IndeterminateDatatype: could not determine data type of parameter $1

This can be worked around specifying the argument types explicitly via a cast:

>>> conn.execute("SELECT json_build_array(%s::text, %s::text)", ["foo", "bar"])

You cannot use IN %s with a tuple#

IN cannot be used with a tuple as single parameter, as was possible with
psycopg2:

>>> conn.execute("SELECT * FROM foo WHERE id IN %s", [(10,20,30)])
Traceback (most recent call last):
...
psycopg.errors.SyntaxError: syntax error at or near "$1"
LINE 1: SELECT * FROM foo WHERE id IN $1
                                      ^

What you can do is to use the = ANY() construct and pass the candidate
values as a list instead of a tuple, which will be adapted to a PostgreSQL
array:

>>> conn.execute("SELECT * FROM foo WHERE id = ANY(%s)", [[10,20,30]])

Note that ANY() can be used with psycopg2 too, and has the advantage of
accepting an empty list of values too as argument, which is not supported by
the IN operator instead.

Different adaptation system#

The adaptation system has been completely rewritten, in order to address
server-side parameters adaptation, but also to consider performance,
flexibility, ease of customization.

The default behaviour with builtin data should be what you would expect. If you have customised the way to adapt data, or if you
are managing your own extension types, you should look at the new
adaptation system.

See also

  • Adapting basic Python types for the basic behaviour.

  • Data adaptation configuration for more advanced use.

Copy is no longer file-based#

psycopg2 exposes a few copy methods to interact with
PostgreSQL COPY. Their file-based interface doesn’t make it easy to load
dynamically-generated data into a database.

There is now a single copy() method, which is similar to
psycopg2 copy_expert() in accepting a free-form COPY command and
returns an object to read/write data, block-wise or record-wise. The different
usage pattern also enables COPY to be used in async interactions.

with connection#

In psycopg2, using the syntax with connection,
only the transaction is closed, not the connection. This behaviour is
surprising for people used to several other Python classes wrapping resources,
such as files.

In Psycopg 3, using with connection will close the
connection at the end of the with block, making handling the connection
resources more familiar.

In order to manage transactions as blocks you can use the
Connection.transaction() method, which allows for finer control, for
instance to use nested transactions.

callproc() is gone#

cursor.callproc() is not implemented. The method has a simplistic semantic
which doesn’t account for PostgreSQL positional parameters, procedures,
set-returning functions… Use a normal execute() with SELECT
function_name(...) or CALL procedure_name(...) instead.

client_encoding is gone#

Psycopg automatically uses the database client encoding to decode data to
Unicode strings. Use ConnectionInfo.encoding if you need to read the
encoding. You can select an encoding at connection time using the
client_encoding connection parameter and you can change the encoding of a
connection by running a SET client_encoding statement… But why would
you?

No default infinity dates handling#

PostgreSQL can represent a much wider range of dates and timestamps than
Python. While Python dates are limited to the years between 1 and 9999
(represented by constants such as datetime.date.min and
max), PostgreSQL dates extend to BC dates and past the year
10K. Furthermore PostgreSQL can also represent symbolic dates “infinity”, in
both directions.

In psycopg2, by default, infinity dates and timestamps map to ‘date.max’
and similar constants. This has the problem of creating a non-bijective
mapping (two Postgres dates, infinity and 9999-12-31, both map to the same
Python date). There is also the perversity that valid Postgres dates, greater
than Python date.max but arguably lesser than infinity, will still
overflow.

In Psycopg 3, every date greater than year 9999 will overflow, including
infinity. If you would like to customize this mapping (for instance flattening
every date past Y10K on date.max) you can subclass and adapt the
appropriate loaders: take a look at this example to see how.

What’s new in Psycopg 3#

  • Asynchronous support

  • Server-side parameters binding

  • Prepared statements

  • Binary communication

  • Python-based COPY support

  • Support for static typing

  • A redesigned connection pool

  • Direct access to the libpq functionalities

Источник

Jul-18-2017, 02:54 AM
(This post was last modified: Jul-18-2017, 02:55 AM by olgethorpe.)

Hi there all

I am using the psycopg2 module for postgresql and I keep receiving an error in my SQL statement saying there is a syntax error at or near «INSERT INTO. I’ve stared at it forever and I just can’t seem to find it, maybe someone can help me? Thanks!

The connection is working fine between the database, and dates is a list of dates also autocommit is set to True

def addToDatabase(self, dates):
.
.
.
   sql = """INSERT INTO "%s" ("Date") VALUES(%s);"""

       for date in range(0, len(dates)):
           data = (ticker, dates[date])
           cursor.execute(sql, data)
.
.
.

Posts: 11,566

Threads: 446

Joined: Sep 2016

Reputation:
444

Please:

  • Show the code in context
  • Show actual (verbatim) error traceback

Thank you

Posts: 2

Threads: 1

Joined: Jul 2017

Reputation:
0

The error traceback is as follows:
Traceback (most recent call last):
  File «stockMain.py», line 21, in <module>
    main()
  File «stockMain.py», line 8, in main
    databases.addToDatabases(databases.dataFeed.genDates[‘daily’])
  File «/home/dondusko/Documents/AtomProjects/StockProject/stockDatabase.py», line 110, in addToDatabases
    cursor.execute(sql, data)
psycopg2.ProgrammingError: syntax error at or near «INSERT INTO»
LINE 1: INSERT INTO «‘AAPL'» («Date») VALUES(‘2008-01-01’);

This is the function in question, not exactly sure what you mean code in context so this might be helpful?:

def addToDatabases(self, dates):
       ticker = self.getTickerName()

       for databaseName in range(0, len(self.databaseNames)):
           tempConnection = psycopg2.connect(dbname="'"+self.databaseNames[databaseName]+"'", user=self.databaseCredentials['username'], host=self.databaseCredentials['host'], password=self.databaseCredentials['password'])
           cursor = tempConnection.cursor()
           tempConnection.autocommit = True

           sql = """CREATE TABLE IF NOT EXISTS "%s" (""" + self.getTableSQL() + ");"
           data = (ticker, )
           cursor.execute(sql, data)

           sql = """INSERT INTO "%s" ("Date") VALUES(%s);"""

           for date in range(0, len(dates)):
               data = (ticker, dates[date])
               cursor.execute(sql, data)

           cursor.close()
           tempConnection.close()

Posts: 11,566

Threads: 446

Joined: Sep 2016

Reputation:
444

add a print statement after line 9

print(sql: {}'.format(sql))

Posts: 3,458

Threads: 101

Joined: Sep 2016

Reputation:
143

Jul-21-2017, 07:39 PM
(This post was last modified: Jul-21-2017, 07:40 PM by nilamo.)

(Jul-18-2017, 03:58 AM)olgethorpe Wrote: INSERT INTO «‘AAPL'»

I don’t know postgres, but for most databases, it’s an sql error to quote a table name.  If you can’t just use the table name (ie: insert into AAPL), you could use brackets (ie: insert into [AAPL]), or sometimes backticks.

Источник

380

May 26, 2017, at 9:13 PM

I get a syntax error at or near the WHERE clause in the last query below.

The query immediately before it with only one parameter works fine, so I am guessing the error has to do with the fact that I am trying to pass two parameters.

Thanks in advance.

cur.execute('''SELECT street_name, id FROM tablename 
            WHERE (((suburb = '') IS NOT FALSE) 
            AND ((street_name2 = '') IS NOT FALSE));''')
datesfrompdf = cur.fetchall()
for rowdate, rowid in datesfrompdf:
    cur.execute("DELETE FROM tablename WHERE id = %s;", (rowid + 1,)  #this works fine
    cur.execute('''INSERT INTO tablename (got_date)
                VALUES (%s) WHERE ((suburb IS NOT NULL)     #syntax error here
                AND (street_name2 IS NOT NULL)
                AND (id > %s));''', (rowdate, rowid))

Running psql 9.3.14, python 2.7

Answer 1

You cannot put a WHERE clause into an INSERT statement — insert inserts new rows. You are probably looking for the UPDATE statement.

I can only guess, but you might want something like this:

UPDATE tablename 
    SET got_date = (%s)
    WHERE ((suburb IS NOT NULL)
        AND (street_name2 IS NOT NULL)
        AND (id > %s));

  • 05:30

    Trying to take the file extension out of my URL

  • 04:00

    display list that in each row 1 li

  • 00:00

    Read audio channel data from video file nodejs

  • 10:30

    session not saved after running on the browser

  • 9:10

    Best way to trigger worker_thread OOM exception in Node.js

  • 07:40

    Firebase Cloud Functions: PubSub, «res.on is not a function»

  • 04:00

    TypeError: Cannot read properties of undefined (reading ‘createMessageComponentCollector’)

  • 9:20

    AWS XRAY on Fargate service

  • 7:40

    How to resolve getting Error 429 Imgur Api

Rent Charter Buses Company

Источник

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)
Источник

Понравилась статья? Поделить с друзьями:
  • Psycopg2 errors syntaxerror syntax error at end of input
  • Psycopg install error
  • Psychonauts has encountered an error crash 0xc0000005
  • Pstream connect error or disconnect reset before headers reset reason connection failure
  • Pstocapt3 write error