pyscopg2:是否可以在循环中动态添加 %s

2022-01-12 00:00:00 python psycopg2 sql dynamic formatting

问题描述

我正在尝试在 python 中创建一个方法，将记录插入到一​​个表中，传递一个列名列表和一个关联的记录列表.

I am trying to create a method in python insert records into a table passing in a list of column names, and an associated list of records.

我能够将它设置在通过 for 循环动态填充列名的位置，但我无法弄清楚如何对值执行相同的操作，因为 psycopg2.executemany 函数依赖于将 %s 作为占位符.

I was able to set it up where the column names populated dynamically via a for loop, but I can't figure out how to do the same thing with values because the psycopg2.executemany function relies on having %s's as placeholders.

是否可以通过循环动态填充字符串中 %s 的数量?有没有其他方法可以做到这一点?

Is it possible to have the number of %s's in the string populate dynamically via a loop? Is there another way to do this?

def load_table(dbname,table_name,fields,records):
        try:
                #Variable Qty Column Loop
                sql_fields = []
                for i in fields:
                        i = sql.Identifier(i)
                        sql_fields.append(i)

                #Need similar loop to replace %s values
                #Replace (%s,%s,%s) ???
                #.....
                #.....
                sql_values = []
                for i in fields:
                        sql_values.append('%s')

                print(sql_values)
                flist = sql.SQL(',').join(sql_fields)  
                connection, cursor = create_connection(dbname)
                insert_query = sql.SQL('INSERT INTO {table_name} ({fields}) VALUES (%s,%s,%s)').format(
                table_name = sql.Identifier(table_name),
                fields = flist,
                cursor.executemany(insert_query,records)
                print('Records Loaded Successfully')

        except (Exception,psycopg2.Error) as error:
                print("Failed to insert record into table {error}".format(error = error))

        finally:
                # closing database connection.
                if (connection):
                        close_connection(connection,cursor)

解决方案

你可以使用 sql.Placeholder，用你需要的 %s-placeholders 的数量填充 sql 语句:

You can use sql.Placeholder, to populate the sql statement with the amount of %s-placeholders you need:

def load_table(dbname,table_name,fields,records):
        con, cur = create_connection('foo')
        query = sql.SQL("insert into {} ({}) values ({})").format(
            sql.Identifier(table_name),
            sql.SQL(', ').join(map(sql.Identifier, fields)),
            sql.SQL(', ').join(sql.Placeholder() * len(fields)))
        print(query.as_string(con))


if __name__ == '__main__':
    dbname = '...'
    table_name = 'messages'
    fields = ['user_id', 'message_type', 'message_title']
    records = [['12345', 'json', 'my first message'], ]

load_table(dbname,table_name,fields,records)

输出:

insert into "messages" ("user_id", "message_type", "message_title") values (%s, %s, %s)

相关文章