使用Psycopg2插入Python字典 [英] Insert Python Dictionary using Psycopg2

问题描述

在不枚举所有键的情况下将具有很多键的python字典插入Postgres数据库的最佳方法是什么？

What is the best way to insert a python dictionary with with many keys into a Postgres database without having to enumerate all keys?

我想做类似的事情...

I would like to do something like...

song = dict()
song['title'] = 'song 1'
song['artist'] = 'artist 1'
...

cursor.execute('INSERT INTO song_table (song.keys()) VALUES (song)')

推荐答案

from psycopg2.extensions import AsIs

song = {
    'title': 'song 1',
    'artist': 'artist 1'
}

columns = song.keys()
values = [song[column] for column in columns]

insert_statement = 'insert into song_table (%s) values %s'

    # cursor.execute(insert_statement, (AsIs(','.join(columns)), tuple(values)))
print cursor.mogrify(insert_statement, (AsIs(','.join(columns)), tuple(values)))

打印：

insert into song_table (artist,title) values ('artist 1', 'song 1')

Psycopg修改了元组到记录和 AsIs 可以通过Python的字符串替换来完成。

Psycopg adapts a tuple to a record and AsIs does what would be done by Python's string substitution.

这篇关于使用Psycopg2插入Python字典的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！