0

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.

zabada
  • 67
  • 1
  • 10

1 Answers1

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)
Ian Wilson
  • 6,223
  • 1
  • 16
  • 24
  • Also I don't think you can reliably get the "next id" this way if there are other writers to the same table unless you use locking. – Ian Wilson Mar 18 '22 at 19:19
  • Thank you! this is helpful. the table was seeded with a record and batch_id of 0. since it's a table that only i write to, max and then max +1 has worked for me. you are correct though, that it may be troublesome with many people writing to it. – zabada Mar 20 '22 at 23:48