CODEWITHSUNDEEP
pythonhana
fiberhead
fiberhead

Reputation: 19

PyHDB gets "Could not find table/view" from SAP HANA Express

I have the following Python Code [pyhdb] to connect to SAP HANA Express: Is there an error in my code? or has it something to do with the SYSTEM user?

Error Message is:

Could not find table/view TABLE in schema APP: line 1 col 19 (at pos 18)

import os
import random
import platform
from constant import *
import pyhdb

def is_rpi():
    return 'arm' in platform.uname()[4]

if is_rpi():
    import Adafruit_DHT

def read_dht():
    if is_rpi():
    sensor = Adafruit_DHT.DHT22
    humidity, temperature = Adafruit_DHT.read_retry(sensor, DHT_PIN)

    if humidity is not None and temperature is not None:
        print('Temp={0:0.1f}*C  Humidity={1:0.1f}%'.format(temperature, humidity))
        return int(humidity), int(temperature)
    else:
        return None, None
else:
    return random.randint(20, 30), random.randint(40, 70)

if __name__ == '__main__':

connection = pyhdb.connect(host=SAP_HOST, port=39015, user=SAP_USER, password=SAP_PWD)
cursor = connection.cursor()

temp, humi = read_dht()
query = "INSERT INTO \"{}\".\"{}\" VALUES(\'{}\', {}, {}, \'{}\')".format(
    SAP_SCHEMA, SAP_TABLE, DEVICE_ID, temp, humi, ROOM_NAME)
print("Executing query: "), query
cursor.execute(query)

print("New Row count: "), cursor.rowcount
connection.close()

And here is the constant code:

DHT_PIN = 4
DEVICE_ID = '0ada9de4-bc4f-4e53-990a-cbcfccaed4c4'
ROOM_NAME = 'room 101
SAP_HOST = 'hxehost'
SAP_USER = 'SYSTEM'
SAP_PWD = 'XXXXXXXXXXXX'
SAP_SCHEMA = 'APP'
SAP_TABLE = 'TABLE'

Upvotes: 0

Views: 1804

Answers (2)

fiberhead
fiberhead

Reputation: 19

The user SYSTEM had not enough privilege to insert into table. Solved Thanks to everyone.

Upvotes: 1

import_this
import_this

Reputation: 27

The error message

Could not find table/view TABLE in schema APP

points to the fact that the table does not exist. In order to check whether the table is known to the system you could, e.g., also run the SQL statement

SELECT * FROM TABLES WHERE SCHEMA_NAME='APP' AND TABLE_NAME='TABLE';

which would lead to an empty result set for a non-existing table.

In case of an authorization problem you could rather expect an error like

insufficient privilege: Not authorized

Regarding the question about checking the authorization, you might want to take a look into the system views EFFECTIVE_PRIVILEGES, EFFECTIVE_ROLES resp. GRANTED_PRIVILEGES and GRANTED_ROLES (refer to the SAP HANA Security Guide). Generally, a privilege can be granted either by a user or a role. Roles can contain other roles, which might make finding the authorization a bit more complex.

However, in your specific case, you could probably try the rather simple SQL query:

SELECT * FROM "PUBLIC"."EFFECTIVE_PRIVILEGES" 
WHERE USER_NAME='SYSTEM' AND SCHEMA_NAME='APP' AND PRIVILEGE='INSERT';

(Depending on your scenario, you might also want to check for the UPDATE privilege.)

Please allow me to add the remark that your INSERT statement from the example probably needs to be explicitly committed to be effective, as by default the connection sets autocommit=False, if I remember correctly.

Upvotes: 2

Related Questions