问题描述
我正在尝试在 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)