Any way to make this python string statement more pythonic?

Question

I'm generating SQL statements to be used in updating records in an existing database. I'm using pymssql as my db api. Is there any way to make this code more pythonic?

def update_statement(table, keys:list, data:dict):
    """
    This function takes a table name, the keys for the table, and a dictiary for the record to be updated into the table.
    A string representing the SQL statement to be used in the session is returned.
    :param table: The name of the table to be updated.
    :param keys: The primary key/ keys for the table.
    :param data: A dictionary representing the data that is to be updated against.
    :return: The return value is a string representing the SQL statement to be used in the session.
    """
    h = 'UPDATE {} SET'.format(table)
    # Generate SET Clauase
    for key, value in data.items():
        if key in keys:
            continue
        else:
            c = '{} = {},'.format(key, value)
            h = ' '.join([h, c])
    h = h.strip(',')
    h = ' '.join([h, 'WHERE'])
    # Generate WHERE clause
    for key in keys:
        h = ' '.join([h, '{} = {}'.format(key, data[key])])
        h = ''.join([h, ','])
    h = h.strip(',')
    # Add closing semicolon.
    h = ''.join([h, ';'])
    # Return sql statement
    return h

I was wanting to implement the Template class from the string module, but couldn't figure out a way to be able to pass in an iterable number of variables to a template and add a comma to the end of each iteration (except for the last iteration).

If the code is working, and you're worried about how Pythonic it is, I would recommend trying out codereview.stackexchange — AI Generated Response
– AI Generated Response, Commented Jul 24, 2017 at 21:27
@WillemVanOnsem I understand that. That is why I'm asking for advice/ guidance. I would like to know the proper, pythonic way to accomplish what I'm trying to do. I understand that it is not safe. — CaptainDriftwood
– CaptainDriftwood, Commented Jul 24, 2017 at 21:27
@Jasonca1: I would use a tool like SQLAlchemy. It allows quite declarative programming and let you use rows of tables like Python objects. — willeM_ Van Onsem
– willeM_ Van Onsem, Commented Jul 24, 2017 at 21:31

willeM_ Van Onsem · Accepted Answer · 2017-07-24 21:32:54Z

2

First of all it is a bad idea to do this SQL statement generation yourself. You better never pass values yourself to SQL, and you can use tools like SQLAlchemy to build even the statements automatically.

That being said you can do it more pythonic way are the following three statements (that replace the entire function):

h1 = ', '.join('{} = {}'.format(k,v) for k,v in data.items() if k not in keys)
h2 = ', '.join('{} = {}'.format(k,data[k]) for k in keys if k in data)
return 'UPDATE {} SET {} WHERE {}'.format(table,h1,h2)

edited Jul 24, 2017 at 21:32

answered Jul 24, 2017 at 21:30

willeM_ Van Onsem

482k33 gold badges483 silver badges624 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

CaptainDriftwood Over a year ago

Values would be passed via a report generated from another table's records. So no human interaction would ever be made. And for such a small project that I'm wanting to do, I feel like SQLalchemy is a little overkill.

willeM_ Van Onsem Over a year ago

@Jasonca1: this can still result in errors: if you pass a string that contains for instance backquotes, then MySQL will interprete this as escape characters. Making SQL statements yourself can be dangerous, even without a hacker.

willeM_ Van Onsem Over a year ago

@Jasonca1: Furthermore I think most frameworks like SQLAlchemy are more convenient: the processing of converting objects from and to the database is quite transparent. Of course no tool can guarantee to be absolutely safe, but usually popular tools will have been bugfixed more than one a person writes him/herself.

Collectives™ on Stack Overflow

Any way to make this python string statement more pythonic?

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related