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?

alecxe · Accepted Answer · 2016-04-22 15:32:16Z

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()

edited Apr 22, 2016 at 15:32

answered Apr 22, 2016 at 15:29

alecxe

476k127 gold badges1.1k silver badges1.2k bronze badges

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

2 Comments

lithiium Over a year ago

"%s" is not string? how to handle dates and integers?

alecxe Over a year ago

@lithiium the mysql driver itself should handle the type conversions when you make parameterized queries.

Collectives™ on Stack Overflow

pymysql: How to format types on query?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related