Psycopg2 type error when inserting with execute_batch

Question

I am trying to do a batch insert using psycopg2 into a postgres database. I am using %s and a list of tuples, but it's failing with the following error:

File ".../python3.6/site-packages/psycopg2/extras.py", line 1183, in execute_batch
    sqls = [cur.mogrify(sql, args) for args in page]
  File ".../python3.6/site-packages/psycopg2/extras.py", line 1183, in <listcomp>
    sqls = [cur.mogrify(sql, args) for args in page]
TypeError: not all arguments converted during string formatting

Here's my code:

import psycopg2
import psycopg2.extras
    conn = psycopg2.connect(
        database='mydb',
        user='name',
        password='pass')
    cur = conn.cursor()
    query = "INSERT INTO my_table (tweet_id, user_id, time, text, 
        reply_to_user_id, reply_to_tweet_id, reply_to_handle, is_retweet, 
        is_quote, quote_usr_id, quote_usr_handle, quote_id, quote_text, 
        retweet_usr_id, retweet_usr_handle, retweet_id, longitude, latitude, 
        location, time_zone) VALUES (%s);"
    #vals are values to insert, a list of tuples
    vals = [(123, 123, datetime.datetime(2017, 1, 18, 17, 12, 33), 
        "'Some Text'", None, None, None, None, None, None, None, None, 
        None, 1234, "'username'", 1234, None, None, "'Somewhere'", 
        "'Pacific Time (US & Canada)'"), 
        (321, 321, datetime.datetime(2017, 1, 18, 15, 43, 19), 
        "'More text'", 321, 321, "'person'", None, None, None, None, None,  
        None, None, None, None, None,None, "'faraway'", 
        "'Pacific Time (US & Canada)'")]
    psycopg2.extras.execute_batch(cur,query,vals)

I have also tried replacing the Nones with "NULL" strings to no avail.

vals is a list of tuples, so it isn't the common problem from the faq and various stackoverflow posts.

I'm hoping it's an issue with one of the types that I'm inserting, but am at a loss for which one. I've compared against the psycopg2 python to sql datatype conversion chart, and it seems like it should check out.

Answer 1

Explicitly pass number of values into your query.

import psycopg2
import psycopg2.extras
conn = psycopg2.connect(
    database='mydb',
    user='name',
    password='pass')
    cur = conn.cursor()
query = "INSERT INTO my_table (tweet_id, user_id, time, text, 
    reply_to_user_id, reply_to_tweet_id, reply_to_handle, is_retweet, 
    is_quote, quote_usr_id, quote_usr_handle, quote_id, quote_text, 
    retweet_usr_id, retweet_usr_handle, retweet_id, longitude, latitude, 
    location, time_zone) VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s);"
#vals are values to insert, a list of tuples
vals = [(123, 123, datetime.datetime(2017, 1, 18, 17, 12, 33), 
    "'Some Text'", None, None, None, None, None, None, None, None, 
    None, 1234, "'username'", 1234, None, None, "'Somewhere'", 
    "'Pacific Time (US & Canada)'"), 
    (321, 321, datetime.datetime(2017, 1, 18, 15, 43, 19), 
    "'More text'", 321, 321, "'person'", None, None, None, None, None,  
    None, None, None, None, None,None, "'faraway'", 
    "'Pacific Time (US & Canada)'")]
psycopg2.extras.execute_batch(cur,query,vals)

Answer 2

Instead of writing queries (for UPSERT if it's necesseray), I advise you to write a function of type:

def execute_upsert(cur, table, champs, rows, champs_unique=None):
    """
        Fonction qui renvoie la requete pour execute_batch(cur, requete, list_rows) de psycopg2
        :param cur: cursor psycopg2
        :param table: Table postgresql, pour l'insertion
        :param champs: List ou Tuple des Champs de la table, pour les insertions
        :param rows: Tableau (list, tuple, generator) des lignes à insérer
        :param champs_unique: Si Upsert, on donne un tuple des champs d'unicité
    """

    def get_sql(t, col, c_u):
        """
            Fonction qui renvoie la requete pour execute_batch(cur, requete, list_rows) de psycopg2
            :param t: Table postgresql, pour l'insertion
            :param col: List ou Tuple des Champs de la table, pour les insertions
            :param c_u: Si Upsert, on donne un tuple des champs d'unicité
            :return: Retourne la Requête
        """
        sql_insert = f'INSERT INTO "{t}" ('
        champs_insertion = []

        for champ in col:
            sql_insert += f'"{champ}", '
            test_champ = "%s"
            champs_insertion.append(test_champ)

        if len(champs_insertion) > 1:
            c = str(tuple(champs_insertion)).replace("'", "")
            sql_insert = sql_insert[:-2] + f') VALUES {c} '
        else:
            c = "(" + str(champs_insertion[0]).replace("'", "") + ")"
            sql_insert = sql_insert[:-2] + f') VALUES {c} '

        if c_u is None:
            sql_insert += 'ON CONFLICT DO NOTHING;'

        else:
            if champs_unique is not None:
                chu = "("
                for value in champs_unique:
                    chu += f'"{value}", '
                chu = f'{chu[:-2]})'
                sql_insert += f' ON CONFLICT {chu} DO UPDATE SET '
                for champ in champs:
                    if champ not in champs_unique:
                        sql_insert += f'"{str(champ)}" = excluded."{str(champ)}", '

                sql_insert = sql_insert[:-2] + ';'

            else:
                sql_insert += ';'

        return sql_insert

    execute_batch(cur, get_sql(table, champs, champs_unique), rows)

after is very simple :

execute_upsert(
    cur=cur,
    table=table_projets_colonnesprojet,
    champs=champs_table,
    rows=list_rows,
    champs_unique=("code_comptable",)
)