Add variable columns to INSERT statement

I’m learning SQL using python library pymysql.

I wanted to make an INSERT statement that will update 3, 5 or 10 columns using the same code by just passing the Database name, the Columns name and the Values.

Thus I made this:

def insert_statement(db,cols,values):
    separator = ","
    separator.join(cols)
    statement = "INSERT INTO " + db + " (" + separator.join(cols) + ") VALUES ("
    aux = []
    for i in range(0,len(values)):
        aux.append("%s")

    statement = statement + separator.join(aux) + ")"
    print (statement)
    return statement

Passing the values, the function produces:

>>>INSERT INTO Publicaciones (item_id,title,price) VALUES (%s,%s,%s)

Which works but, is it there a more pythonic way?

You’re not using values except for its length. But this should be the same length than cols, so use that instead.

You also don’t need a for-loop to build a list with the same element N times, list multiplication can handle that just fine.

Lastly, I would use f-strings or at least str.format instead of string concatenation, it is prettyier.

def insert_statement(db, columns):
    column_names = ', '.join(columns)
    placeholders = ', '.join(['%s'] * len(columns))
    return f'INSERT INTO {db} ({column_names}) VALUES ({placeholders})'

Usage:

>>> insert_statement('Publicationes', ['item_id', 'title', 'price'])
'INSERT INTO Publicationes (item_id,title,price) VALUES (%s,%s,%s)'

Depending on your calling site, you can also make columns a variable length argument, it may be easier to use:

def insert_statement(db, *columns):
    column_names = ', '.join(columns)
    placeholders = ', '.join(['%s'] * len(columns))
    return f'INSERT INTO {db} ({column_names}) VALUES ({placeholders})'

Usage:

>>> insert_statement('Publicationes', 'item_id', 'title', 'price')
'INSERT INTO Publicationes (item_id,title,price) VALUES (%s,%s,%s)'

But you should limit yourself to only use this function using trusted input. If anything comming from a user enters here, this is a vulnerability waiting to happen:

>>> user_input = "price) VALUES (42, 'foobar', 0.00); DROP TABLE Publicationes; -- Now this a vulnerability in disguise :"
>>> insert_statement('Publicationes', 'item_id', 'title', user_input)
"INSERT INTO Publicationes (item_id, title, price) VALUES (42, 'foobar', 0.00); DROP TABLE Publicationes; -- Now this a vulnerability in disguise :) VALUES (%s, %s, %s)"

SQL Injections

Even though you are having these proper placeholders for values, your code is still vulnerable to SQL Injection attacks as table and column names are not properly sanitized.

As table and column names cannot be parameterized the usual way, you could validate them separately. For instance, you could check that the table and column names are simply valid MySQL identifiers:

• Python MySQL parameter queries for dynamic table names

Object Relational Mappers

As I understand the purpose of the task is to learn how to interact with the MySQL database via Python MySQL database driver, but, in general, this kind of problems are already solved by different abstraction layer libraries or commonly called ORMs (Object Relational Mappers) like SQLAlchemy, Peewee or PonyORM) which provide an extra Python layer around Python-to-database communication allowing you to basically write SQL queries in Python.