代码之家 › 专栏 › 技术社区 › Reen

使用execute\u批插入时出现Psycopg2类型错误

psycopg2 postgresql python

Reen · 技术社区 · 8 年前

我正在尝试使用psycopg2将批插入postgres数据库。我正在使用%s和元组列表,但它失败了,出现以下错误:

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

这是我的代码:

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)

VAL是一个元组列表,因此它不是 faq 和 various stackoverflow posts.

我希望这是一个问题的类型之一,我插入,但在一个迷失。我比较了psycopg2-python-to-sql datatype conversion chart ,而且似乎应该检查一下。

2 回复 | 直到 8 年前

oshaiken 8 年前

显式地将值的数量传递到查询中。

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)

Paulo Alves 8 年前

我建议您编写以下类型的函数,而不是编写查询(如果有必要,则用于UPSERT):

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)

之后很简单:

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