CODEWITHSUNDEEP
pythonpython-3.xpostgresqlpsycopg2netezza
Abhi Garg
Abhi Garg

Reputation: 53

(psycopg2.OperationalError) Invalid - opcode

I am trying to connect to Netezza using SQLalchemy.create_engine(). The reason I want to use SQLAlchmey is because I want to be able to read and write through pandas dataframe.

What works is as follow:

import pandas as pd
import pyodbc
conn = pyodbc.connect('DSN=NZDWW')
df2 = pd.read_sql(Query,conn)

Above code runs fine. But in order to write df dataframe to the Netezza, I need to use the function to_sql(), which needs SQLAlchemy. This is what my code looks like:

from sqlalchemy import create_engine
username = os.getenv('REDSHIFT_USER')
password = os.getenv('REDSHIFT_PASS')
DATABASE = "SHP_TARGET"
HOST = "Netezza1"
PORT = 5480
conn_str = "postgresql://"+username+":"+password+"@"+HOST+':'+str(PORT)+'/'+DATABASE
engine3 = create_engine(conn_str)
df = pd.read_sql(Query, engine3)

When I execute this, I get the following error:

OperationalError: (psycopg2.OperationalError) Invalid - opcode
Invalid - opcodeInvalid packet length (Background on this error at: http://sqlalche.me/e/e3q8)

Any leads will be much appreciated. thanks.

Database: Netezza Python version: 3.6 OS: Windows

Upvotes: 3

Views: 677

Answers (2)

Aniket Kulkarni
Aniket Kulkarni

Reputation: 471

Here's the formal dialect for Netezza has been released.

It can be used as documented here - https://github.com/IBM/nzalchemy#prerequisites

Example

from sqlalchemy import create_engine
from urllib import parse_quote_plus

# assumes NZ_HOST, NZ_USER, NZ_PASSWORD are set
import os

params = parse_quote_plus(f"DRIVER=NetezzaSQL;SERVER={os['NZ_HOST']};"
            f"DATABASE={os['NZ_DATABASE']};USER={os['NZ_USER'};"
            f"PASSWORD={os['NZ_PASSWORD']}")
engine = create_engine(f"netezza+pyodbc:///?odbc_connect={params}", 
            echo=True)

Upvotes: 1

joebeeson
joebeeson

Reputation: 4366

The sqlalchemy dialect for Postges isn't compatible with Netezza.

The error you're receiving is the psycopg2 module, which facilitates the connection, complaining that it can't make sense of what the server is "saying", basically.

There appears to be a dialect for Netezza though. You may want to try that out.

Upvotes: 1

Related Questions