pymysql: How to format types on query?

Question

I'm trying to insert rows on a MySQL table using pymysql (Python 3), the relevant code is the following.

def saveLogs(DbConnection, tableName, results):
    for row in results:
        formatStrings = ",".join(["?"]*len(row))
        sql = "INSERT INTO %s VALUES (%s);"%(tableName,formatStrings)
        DbConnection.cursor().execute(sql, tuple(row))
    DbConnection.commit()

I'm using "?" for the types, but I get the error not all arguments converted during string formatting. row is a list composed of strings, ints and datetime.datetime. I guess the issue is the "?" but I have checked the PEP 249 and it's still not clear to me how should I do it. Any suggestions?

Answer 1

Use string formatting for the table name only (though make sure you trust the source or have a proper validation in place). For everything else, use query parameters:

def saveLogs(DbConnection, tableName, results):
    cursor = DbConnection.cursor()
    sql = "INSERT INTO {0} VALUES (%s, %s, %s)".format(tableName)
    for row in results:
        cursor.execute(sql, row)
    DbConnection.commit()

There is also that executemany() method:

def saveLogs(DbConnection, tableName, results):
    cursor = DbConnection.cursor()
    cursor.executemany("INSERT INTO {0} VALUES (%s, %s, %s)".format(tableName), results)
    DbConnection.commit()