使用psycopg2sql模块。

create table books (id serial, bookcode integer, bookname text);

import psycopg2
from psycopg2 import sql

data = [{'bookcode': 1, 'bookname': 'test'}, {'bookcode': 2}]

con = psycopg2.connect("dbname=test host=localhost  user=postgres")
cur = con.cursor()

for d in data:
    col_names = list(d.keys())
    print(col_names)
    insert_qry = sql.SQL("insert into books ({})  values({})").format(sql.SQL(",").join(map(sql.Identifier, col_names)), 
                         sql.SQL(",").join(map(sql.Placeholder, col_names)))
    cur.execute(insert_qry, d)
con.commit()

select * from books;
 id | bookcode | bookname 
----+----------+----------
  1 |        1 | test
  2 |        2 | NULL

这当然假设列可以接受NULL值。如果不是这种情况，那么您需要为缺少的字段创建一些适当的值。

用一个模式合并字典（所有列都包含None的字典）：

#...

data = [
  {'name': 'Bob', 'age': 32},
  {'name': 'Sara', 'city': 'Dallas'},
  {'name': 'John', 'age': 45, 'city': 'Atlanta'}
]

# pattern - all columns with None as value
nulls = {'name': None, 'age': None, "city": None}

# get the columns from the pattern
columns = nulls.keys()

# write the SQL query to insert the records
query = """INSERT INTO schema.table
            ({}) VALUES %s
            ON CONFLICT (name) DO NOTHING""".format(
        ",".join(columns)
)

# extract the values from each dictionary into as list of lists
values = [[value for value in (nulls | line).values()] for line in data]

# execute the SQL query with the associated values
execute_values(cur, query, values)

假设你知道完整的列列表（如果你正在填写一个表，你应该知道），你可以转换你的数据来填写缺失的值：

data = [
  {'name': 'Bob', 'age': 32},
  {'name': 'Sara', 'city': 'Dallas'},
  {'name': 'John', 'age': 45, 'city': 'Atlanta'}
]

import psycopg2
from psycopg2.extras import execute_values

# connect to the database
conn = psycopg2.connect(
    host="localhost",
    database="db_name",
    user="psql_user",
    password="psql_password",
)
conn.autocommit = True
cur = conn.cursor()

columns = ['name','age','city','state','zip']
empty = {k:None for k in columns}

# get the columns from first dictionary

# write the SQL query to insert the records
query = """INSERT INTO schema.table 
            ({}) VALUES ({})
            ON CONFLICT (name) DO NOTHING""".format(
        ",".join(columns),
        ','.join('%' for _ in columns)
)

# extract the values from each dictionary into as list of lists

values = []
for row in data:
    x = empty.copy()
    x.update( row )
    values.append( x )
print(query)
print(values)

# execute the SQL query with the associated values
#execute_values(cur, query, values)