Psycopg2如何将Python字典以JSON格式插入数据库？

Question

我想将一个Python字典作为JSON插入到我的PostgreSQL数据库中（通过Python和psycopg2）。

我有：

thedictionary = {'price money': '$1', 'name': 'Google', 'color': '', 'imgurl': 'http://www.google.com/images/nav_logo225.png', 'charateristics': 'No Description', 'store': 'google'}

cur.execute("INSERT INTO product(store_id, url, price, charecteristics, color, dimensions) VALUES (%d, %s, %s, %d, %s, %s)", (1,  'http://www.google.com', '$20', thedictionary, 'red', '8.5x11'))

并且它会提示以下错误信息：

cur.execute("INSERT INTO product(store_id, url, price, charecteristics, color, dimensions) VALUES (%d, %s, %s, %d, %s, %s)", (1, 'http://www.google.com', '$20', thedictionary, 'red', '8.5x11')) psycopg2.ProgrammingError: 无法适应类型'dict'

我不确定该如何继续下去。 我在互联网上找不到任何关于如何完成这种精确的事情的信息，而且我对psycopg2非常陌生。

- Rorschach

---

https://dev59.com/gFcO5IYBdhLWcg3w3VAc#45150668 - bibangamba

@bibangamba 这是最近使用PostgreSQL版本的人们应该查看的链接吗？ - Rorschach

我认为没问题。我正在使用Postgres 10，而且没有任何问题。 - bibangamba

@Felipe Augusto 给出了最好的答案，谢谢！ - saza

Answer 1

cur.execute("INSERT INTO product(store_id, url, price, charecteristics, color, dimensions) VALUES (%s, %s, %s, %s, %s, %s)", (1,  'http://www.google.com', '$20', json.dumps(thedictionary), 'red', '8.5x11'))

这将解决您的问题。但是，您确实应该将键和值存储在各自独立的列中。要检索字典，请执行以下操作：

cur.execute('select charecteristics from product where store_id = 1')
dictionary = json.loads(cur.fetchone()[0])

- hd1

---

如果您想避免硬编码所有的%s占位符，可以在获取类似以下内容的列表后使用', '.join(["%s"] * len(columns))：columns = list(dictionary.keys())。 - Benji A.

Answer 2

来自psycopg文档:

注意： 您可以使用register_adapter()将任何Python字典适配为JSON，注册Json或任何子类或工厂创建兼容适配器：

psycopg2.extensions.register_adapter(dict，psycopg2.extras.Json)

此设置是全局的，因此它与类似于register_hstore()注册的其他适配器不兼容。任何JSON支持的其他对象都可以以同样的方式进行注册，但这将覆盖默认的适配规则，因此请注意不要产生意外效果。

所以，在我的情况下我所做的是：

from psycopg2.extras import Json
from psycopg2.extensions import register_adapter

register_adapter(dict, Json)

它运作得非常好。

- Felipe Augusto

---

需要导入 from psycopg2.extras import Json 才能使其正常工作，但这应该是被接受的答案。 - linSESH

Answer 3

您可以使用psycopg2.extras.Json将字典转换为PostgreSQL可接受的JSON格式。

from psycopg2.extras import Json

thedictionary = {'price money': '$1', 
'name': 'Google', 'color': '', 'imgurl': 'http://www.google.com/images/nav_logo225.png', 'charateristics': 'No Description', 'store': 'google'}

item ={
    "store_id":1,
    "url": 'http://www.google.com', 
    "price":'$20', 
    "charecteristics":Json(thedictionary), 
    "color":'red', 
    "dimensions":'8.5x11'
}

def sql_insert(tableName, data_dict):
    '''
    INSERT INTO product (store_id,  url,  price,  charecteristics,  color,  dimensions)
    VALUES (%(store_id)s, %(url)s, %(price)s, %(charecteristics)s, %(color)s, %(dimensions)s );
    '''
    sql = '''
        INSERT INTO %s (%s)
        VALUES (%%(%s)s );
        '''   % (tableName, ',  '.join(data_dict),  ')s, %('.join(data_dict))
    return sql

tableName = 'product'
sql = sql_insert(tableName, item)

cur.execute(sql, item)

更多信息，请查看官方文件。

class psycopg2.extras.Json(adapted, dumps=None)

    An ISQLQuote wrapper to adapt a Python object to json data type.

    Json can be used to wrap any object supported by the provided dumps function. If none is provided, the standard json.dumps() is used (simplejson for Python < 2.6; getquoted() will raise ImportError if the module is not available).

    dumps(obj)
    Serialize obj in JSON format.

    The default is to call json.dumps() or the dumps function provided in the constructor. You can override this method to create a customized JSON wrapper.

- Ferris

---

显然，这不适用于INT数据类型。我尝试了这个，但是出现了错误： InvalidTextRepresentation: invalid input syntax for type integer: "" LINE 3: VALUES ('', 'Verlängerung',...) 有什么想法吗？ - N91

@Nofy 你可以参考我的上一篇帖子。 - Ferris

完成相同的操作，成功插入特征。 现在如何从产品中选择特征并打印所有imgurl。 我的列是文本类型。 当我运行从产品中选择特征时，得到的结果是一个字符串，而不是字典。 如何将其作为字典获取并获取每个对象。 - newuser

如果格式是json样式，您可以使用select charecteristics::json from product。或者在Python中，使用json.loads(json str)或eval(dict str)。 - Ferris

在将字典输入插入到Postgres DB之前，使用Json(thedictionary)进行包装即可解决问题。 - Sumax

最新文档链接（2.9.5）：class psycopg2.extras.Json - Sumax

Answer 4

从psycopg2的2.5版本开始，您可以使用Json适配器。

Psycopg可以将Python对象适应为PostgreSQL json和jsonb类型，并进行相互转换。在PostgreSQL 9.2和更高版本中，适应性开箱即用。

from psycopg2.extras import Json
curs.execute("insert into mytable (jsondata) values (%s)", [ Json({'a': 100}) ] )

更多信息请参阅文档： https://www.psycopg.org/docs/extras.html#json-adaptation

- Serhii

Answer 5

首先，这个错误意味着你试图将一个`dict`值推入到不能接受它的列类型中（例如`TEXT`等）。

接受的解决方案是正确的，将其从JSON/dict转换为字符串以便存储。

但是，有一种列类型可以接受它：JSON。

我建议首先创建一个JSON字段以保留类似于字典的对象，原因如下：

1. 您可以直接将字典推送到数据库中，无需进行`json.dumps`或其他转换（因为记住，在推送时您需要进行`json.dumps`，但在以后在Python中读取时，您需要进行`json.loads`（从字符串转换回字典）。 2. 您可以在实际的JSON列中查询其内容，当它是字符串时无法做到这一点。

因此，在创建列时，我建议将默认值设置为`{}`而不是`NULL`。

参考链接：https://www.postgresqltutorial.com/postgresql-json/

CREATE TABLE my_table (
   ...
   my_json_col JSON default '{}'::JSON
   ...
)

- Ricky Levi

Answer 6

只需将字典类型转换为json_str，使用 json.dumps(adict)。

import pandas as pd
import json
import psycopg2
from sqlalchemy import create_engine
engine_nf = create_engine('postgresql+psycopg2://user:password@192.168.100.120:5432/database')
sql_read = lambda sql: pd.read_sql(sql, engine_nf)
sql_execute = lambda sql: pd.io.sql.execute(sql, engine_nf)

sql = '''
CREATE TABLE if not exists product (
    store_id  int
    , url  text
    , price text
    , charecteristics json
    , color text
    , dimensions text
)
'''
_ = sql_execute(sql)

thedictionary = {'price money': '$1', 'name': 'Google', 
    'color': '', 'imgurl': 'http://www.google.com/images/nav_logo225.png', 
    'charateristics': 'No Description', 
    'store': 'google'}

sql = '''
INSERT INTO product(store_id, url, price, charecteristics, color, dimensions) 
VALUES (%d, '%s', '%s', '%s', '%s', '%s')
''' % (1, 'http://www.google.com', '$20', 
       json.dumps(thedictionary), 'red', '8.5x11')

sql_execute(sql)

sql = '''
select * 
from product
'''
df = sql_read(sql)
df
    #   store_id    url price   charecteristics color   dimensions
    # 0 1   http://www.google.com   $20 {'price money': '$1', 'name': 'Google', 'color...   red 8.5x11

charecteristics = df['charecteristics'].iloc[0]
type(charecteristics)
    # dict

事实上，我喜欢另一种将数据转储到Postgres的方式。

import io
import csv
def df2db(df_a, table_name, engine):
    output = io.StringIO()
    # ignore the index
    df_a.to_csv(output, sep='\t', index = False, header = False, quoting=csv.QUOTE_NONE)
    output.getvalue()
    # jump to start of stream
    output.seek(0)

    #engine ---- from sqlalchemy import create_engine
    connection = engine.raw_connection() 
    cursor = connection.cursor()
    # null value become ''
    cursor.copy_from(output,table_name,null='')
    connection.commit()
    cursor.close()


df = sql_read('select * from product')
type(df.charecteristics.iloc[0])
df.charecteristics = df.charecteristics.map(json.dumps)

# dump pandas DataFrame to postgres
df2db(df, 'product', engine_nf)
df_end = sql_read('select * from product')

- Ferris

Answer 7

你想让每个键都成为自己的列，有特别的原因吗？Postgres允许您在包含有效JSON或JSONB的单个列中执行直接查询操作

这意味着您可以简单地创建一个具有ID（主键）和元数据的2列数据库，然后执行诸如以下查询：

SELECT * FROM users WHERE metadata @> '{"key": "value"}';

这里是一个很好的资源供您参考。

- Aaron Melgar