sqlalchemy engine.result.RowProxy to int in function with snowflake

Question

I have searched the few questions with answers on SO with no luck.

like this one, and this one, or this one, and this one

I have a basic function that needs to read a log table, pull the max batch_id, and then create an object that is batch_id +1 and stored as a number/int.

it neither creates the objects, nor can I turn the sql results into anything readable other than a RowProxy or NoneType.

def sf_get_batchid():
engine = create_engine(
'snowflake://{user}:{password}@{account}/{database}/{schema}?warehouse=wh'.format(
    database='dev_db',
    schema='a_schema',
    user='user',
    password='password',
    account='account'
    )
)
connection = engine.connect()
print('Connected to DB: dev_db')
batchid_sql = "SELECT MAX(batch_id) FROM DEV_DB.A_SCHEMA.SN_DISTRIBUTION_LOG;" 
try:
    # execute sql
    result = connection.execute(batchid_sql)
    batch_id = result.fetchall()
    # return int(batch_id[0][0]) + 1
    new_batch_id = batch_id[0] +1
except Exception as e:
    print('Error: {}'.format(str(e)))
    sys.exit(1)

ultimately I want to just run this function with no args, and get two usable items

batch_id = the current max number
new_batch_id = that new number +1

thank you for any help.

Answer 1

Try using text() and scalar(). Also I'm not sure what MAX returns when the table is empty. You might have to check for None or use coalesce.

from sqlalchemy.sql import text

batchid_sql = text("SELECT COALESCE(MAX(batch_id), 0) FROM DEV_DB.A_SCHEMA.SN_DISTRIBUTION_LOG")
try:
    # execute sql
    result = connection.execute(batchid_sql)
    batch_id = result.scalar()
    # return int(batch_id) + 1
    new_batch_id = batch_id + 1
except Exception as e:
    print('Error: {}'.format(str(e)))
    sys.exit(1)