时间戳

首页 > 解决方案 > Python 到 Netezza JDBC 连接在 Mac 上给出 SQL .execute 错误

python - Python 到 Netezza JDBC 连接在 Mac 上给出 SQL .execute 错误

问题描述

所以我正在编写一个 Python 脚本来检索 Netezza 表 DB 的计数。但是，我收到以下错误。请让我知道如何解决此问题。

  File "/Users/skulk00/PycharmProjects/NZ_RS_Validator/table_details_thread_pool.py", line 175, in <module>
    result = result_netezza_cursor.execute('SELECT COUNT(1) FROM STORE')
AttributeError: 'Connection' object has no attribute 'execute'

这是我的连接代码：

NETEZZA_JAR_LOC = os.path.join('/Users/skulk00/Connection Softwares/nzjdbc.jar')
NETEZZA_PP = ('username', 'password', 'xx.6.8.xx', 'dbname', 'netezza', 'org.netezza.Driver', '5480')

class Connector:
    """Create Database connection."""

    def __init__(self, db_name):
        self.db_name = db_name
        self.all_db = {
            'netezza_pp': NETEZZA_PP,
            'netezza_prod': NETEZZA_PROD,
        }
        if self.db_name not in self.all_db:
            raise ValueError(f'Available database choices are {tuple(self.all_db.keys())}')

    def _get_cxn_string(self, user, password, host, dbname, dialect, driver, port):
        if 'netezza' in self.db_name:
            db_string = f'jdbc:{dialect}://{host}:{port}/{dbname}'
        return db_string

    def cursor(self):
        db_name = self.db_name
        db_creds = self.all_db[db_name]
        db_string = self._get_cxn_string(*db_creds)

        print(db_name)
        print(db_creds)
        print(db_string)

        if 'netezza' in db_name:
            cxn = jaydebeapi.connect('org.netezza.Driver', db_string, {'user': db_creds[0], 'password': db_creds[1]}, jars=NETEZZA_JAR_LOC)
        return cxn

这是我的主要功能代码：

result_netezza_connector = Connector('netezza_pp')
result_netezza_cursor = result_netezza_connector.cursor()
result = result_netezza_cursor.execute('SELECT COUNT(1) FROM STORE')
print(result)

标签： pythondatabasemacosjdbcnetezza

解决方案

我建议使用Python 原生 nzpy驱动程序来实现 Python 脚本与 Netezza 的交互

import nzpy

conn = nzpy.connect(user="...", password="...", host='...', 
                port=5480, database="...")

with conn.cursor() as cursor:
    cursor.execute(...)

有关更详细的示例，您可以参考这些示例

回到 JDBC 问题 - 问题是您返回的是jayedebeapi.ConnectionfromConnector.cursor()方法，而不是游标。

result_netezza_connector = Connector('netezza_pp')
with result_netezza_connector.cursor().cursor() as result_netezza_cursor:
    result_netezza_cursor.execute('SELECT COUNT(1) FROM STORE')
    print(result_netezza_cursor.fetchall())

# a better option to display is using pandas
import pandas
df = pandas.read_sql('SELECT COUNT(1) from STORE',
      result_netezza_connect)
df 
# will print tabular and you can use the dataframe for further
# processing

这.cursor().cursor()令人困惑，我建议将Connection.cursor()方法重命名为Connection.connection(). 这将使它更具可读性-

with result_netezza_connector.connection().cursor() as ...

这是python笔记本中相同的最小示例

这是python笔记本中相同的最小示例

推荐阅读