For loop with many if statements

Question

I am trying to generate SQL statements "on-the-fly".

EDIT: I needed to make the following as well:

THE OUTPUT CAN LOOK LIKE

sql_cols => DESCRIPTION,  FIRSTNAME,  LASTNAME
sql_vals => :1,:2,:3

OR

sql_cols => FIRSTNAME,  LASTNAME
sql_vals => :1,:2

OR ANY OF THE OTHERS BELOW

sql_cols => FIRSTNAME
sql_vals => :1

sql_cols => DESCRIPTION,  LASTNAME
sql_vals => :1,:2

sql_cols => LASTNAME
sql_vals => :1

I pretty much have it together. The only thing is that some code has to be repeated. In particular, the "if" statements"

    if ( row == 'firstname' ) :
        firstname = item['firstname']
        sql_cols  += ' FIRSTNAME, '
        sql_vals  += ':' + str(count) + ',' 
        sql_inputs.append( firstname ) 

    if ( row == 'lastname' ) :
        lastname = item['lastname']
        sql_cols  += ' LASTNAME, '
        sql_vals  += ':' + str(count) + ',' 
        sql_inputs.append(  lastname ) 

    if ( row == 'description' ) :
        description = item['description']
        sql_cols  += ' DESCRIPTION, '
        sql_vals  += ':' + str(count) + ',' 
        sql_inputs.append( description ) 

How can one rewrite this so that so many "if" statements are not being used?

TIA

INPUT

req_data = {
"list":[
    { "description" : "tall", "firstname":"very high", "lastname":"very heavy" },
    { "description" : "short", "firstname":"no high", "lastname":"very light" },
    { "firstname" : "heay" },
    { "lastname" : "light" }
  ]
}

CODING TO CHANGE

def test_create_data():
     try:
        data = []
        for item in req_data['list']:
            sql_cols  = ''
            sql_vals= ''
            sql_inputs = [] 
            sql = 'INSERT INTO newtest '
            count = 1
            for row in item:

                if ( row == 'firstname' ) :
                    firstname = item['firstname']
                    sql_cols  += ' FIRSTNAME, '
                    sql_vals  += ':' + str(count) + ',' 
                    sql_inputs.append( firstname ) 

                if ( row == 'lastname' ) :
                    lastname = item['lastname']
                    sql_cols  += ' LASTNAME, '
                    sql_vals  += ':' + str(count) + ',' 
                    sql_inputs.append(  lastname ) 

                if ( row == 'description' ) :
                    description = item['description']
                    sql_cols  += ' DESCRIPTION, '
                    sql_vals  += ':' + str(count) + ',' 
                    sql_inputs.append( description ) 

                count = count + 1 

            #get rid of the last "," that is on each of the strings
            result = sql_cols.rsplit(',', 1)[0]
            sql_cols = result

            result = sql_vals.rsplit(',', 1)[0]
            sql_vals = result

Answer 1

The only thing that changes based on the value of row is the string appended to sql_cols, and that's just row converted to uppercase. You can use item['description'] directly when appending to sql_inputs.

sql_vals = []
sql_cols = []
sql_inputs = []
for count, row in enumerate(item, start=1):
    if row not in {'firstname', 'lastname', 'description'}:
        continue

    sql_inputs.append(item[row])
    sql_vals.append(count)
    sql_cols.append(row)

sql_cols = " ".join(x.upper() for x in sql_cols)
sql_vals = ",".join(":{}".format(x) for x in sql_vals)

Answer 2

You don't need to check what row name is if you're doing the same thing for each anyways.

for row in item:
    sql_cols  += f' {row.upper()}, '
    sql_vals  += ':' + str(count) + ','
    sql_inputs.append(item[row])