logo标签列表

将JSON插入Postgres 9.5

pythonsqlpostgresql
3

在 Python 3.6 中，我有一个名为 'data2' 的变量，其中包含一个看起来像这样的 JSON：

{
    'id': 4573457, 'account_id': 456, 'address': '15 Millers Rd, WA', 
    'category_id': 4565, 'description': None, 'is_anonymous': False, 
    'iso_created_at': '2017-11-21T14:08:54+11:00', 
    'location': {
        'latitude': -56.564848493, 'longitude': 345.5948493}, 
    'report_state_id': 45655, 'report_state_name': 'ALL PICKED', 
    'title': 'South', 'user_id': 44555, 'user_short_name': 'Todd G.', 
    'users_alerted_count': 0, 'users_opened_count': 6, 'shape_id': 56
}

我想编写一个插入语句，将这些数据插入到我已经在Postgres 9.5中创建的表中。该表有3列 - channel、report_id和report_data。
我想将JSON中的'account_id'插入到channel列中，将'id'插入到report_id列中，并将JSON中的其余内容全部插入到report_data列中。
请问如何实现？
3个回答
4

以下是在Postgres中提取JSON值的基本方法：

  data2->'account_id' AS channel

因此，您的执行SQL应该类似于这样：
cursor.execute("
    INSERT INTO MyTable (channel, report_id, report_data)
    SELECT 
      src.MyJSON->'account_id',
      src.MyJSON->'id',
      src.MyJSON
    FROM (
      SELECT %s AS MyJSON
    ) src
  ",
  (data2,)
)

如果您想在将其余JSON插入report_data字段之前删除account_id/id键，则可以创建第二个“data2”变量（即带有已删除键的“data2_final”），并将其作为参数传递给SQL。请告诉我它对您有何作用。
CREATE TABLE Mytable (
  channel INTEGER, 
  report_id INTEGER, 
  report_data JSONB
);

cursor.execute("
    INSERT INTO MyTable (channel, report_id, report_data)
    SELECT 
      CAST(src.MyJSON->>'account_id' AS INTEGER),
      CAST(src.MyJSON->>'id' AS INTEGER),
      src.MyJSON
    FROM (
      SELECT CAST(%s AS JSONB) AS MyJSON
    ) src
  ",
  (data2,)
)

http://www.sqlfiddle.com/#!17/fb3af/1

我更新了提取代码，返回JSON值作为文本，然后将它们强制转换为整数。

更新的更新：我按照您的代码格式进行了格式化，并在下面注明了我所做的更改：

def calldb( db, sql_cmd): 
    try: 
        cur = db.cursor() 
        cur.execute(sql_cmd, (data2,)) 
        return 
    except Exception as e: 
        print ('Error ', e ) 
        raise 

sql_cmd=" INSERT INTO MyTable (channel, report_id, report_data) SELECT CAST(src.MyJSON->>'account_id' AS INTEGER), CAST(src.MyJSON->>'id' AS INTEGER), src.MyJSON FROM ( SELECT CAST(%s AS JSONB) AS MyJSON ) src" 
calldb(conn, sql_cmd) 
conn.commit()

修改:

  • 删除了sql_cmd开头和结尾的额外双引号
  • 在查询中"src"后面添加了一个双引号
  • 将 (data2,) 元组移动到了 cur.execute() 调用中

execute() 函数的工作方式是，你将要执行的 SQL 字符串（即 sql_cmd）作为第一个参数传递给它。字符串中的 %s 表示占位符，用于放置参数化的值。作为第二个参数，你需要传递一个包含参数值的数组/元组（即 (data2,)）。

祝好运 :)

更新内容
这里是可用的代码（稍微修改了你提供的版本）:

import psycopg2
import json

def calldb(db, sql_cmd, sql_params): 
    try: 
        cur = db.cursor()
        cur.execute(sql_cmd, sql_params)
        return
    except Exception as e: 
        print ('Error ', e ) 
        raise 

params = {
  "host":"DB_HOSTNAME",
  "database":"DB_NAME",
  "user":"USERNAME",
  "password":"PASSWORD"
}

conn = psycopg2.connect(**params)

# Prepare SQL
sql_cmd = "INSERT INTO MyTable (channel, report_id, report_data) SELECT CAST(src.MyJSON->>'account_id' AS INTEGER), CAST(src.MyJSON->>'id' AS INTEGER), src.MyJSON FROM ( SELECT CAST(%s AS JSONB) AS MyJSON ) src" 

# Convert dictionary to native JSON data type
data2 = {"id": 4573457, "account_id": 456, "address": "15 Millers Rd, WA"}
data2_json = json.dumps(data2)

sql_params = (data2_json,)

# Execute SQL
calldb(conn, sql_cmd, sql_params) 
conn.commit()

变更

  • 添加了sql_params变量到calldb()函数，以传递sql参数
  • 添加了连接参数行以连接到数据库（不确定你在你的代码中是如何处理的）
  • 将data2字典数据类型转换为JSON数据类型（这与之前出现的“无法调整”字典错误相关）
  • 建议：在完成后，应该关闭DB游标和连接，不确定您是否这样做

您可以根据需要进行清理和修改。试试并让我知道。

感谢您的回复。我已经尝试了上面的SQL语句，但是出现了错误： - Paul
感谢您的回复。我尝试了上面的SQL，但是出现了错误：Traceback（最近的调用最先）： File“smartoysters_06122017_V1.py”，第163行，在<module>中 calldb（conn，sql_cmd） File“smartoysters_06122017_V1.py”，第27行，在calldb中 cur.execute（sql_cmd） psycopg2.ProgrammingError：在“'，（％s，））”附近未终止引用字符串 LINE 6：“）' - Paul
def calldb(db, sql_cmd): try: cur = db.cursor() cur.execute(sql_cmd) returnexcept Exception as e: print('错误', e) raisesql_cmd = """INSERT INTO MyTable (channel, report_id, report_data) SELECT CAST(src.MyJSON->>'account_id' AS INTEGER), CAST(src.MyJSON->>'id' AS INTEGER), src.MyJSON FROM ( SELECT CAST(%s AS JSONB) AS MyJSON ) src , (data2,) )"""calldb(conn, sql_cmd)conn.commit() - Paul
看起来可以工作了！谢谢，但是我该如何在Postgres中查看表以检查它是否已被填充？ - Paul
你好，请问我该如何在Postgres 9.5中向JSON数组中插入数据？ - Paul
显示剩余15条评论
1
上面的答案有些复杂。
从psycopg2 官方文档中可以看到，有一个叫做psycopg2.extras.Json的类类型，它是一个ISQLQuote包装器，用于将Python对象适配为json数据类型。
import psycopg2
import logging
from psycopg2.extras import Json

conn = psycopg2.connect(dbname="  ", 
                        user="  ",
                        password="  ", 
                        host="127.0.0.1", 
                        port="5432")
data2 = {
    'id': 4573457, 'account_id': 456, 'address': '15 Millers Rd, WA', 
    'category_id': 4565, 'description': None, 'is_anonymous': False, 
    'iso_created_at': '2017-11-21T14:08:54+11:00', 
    'location': {
        'latitude': -56.564848493, 'longitude': 345.5948493}, 
    'report_state_id': 45655, 'report_state_name': 'ALL PICKED', 
    'title': 'South', 'user_id': 44555, 'user_short_name': 'Todd G.', 
    'users_alerted_count': 0, 'users_opened_count': 6, 'shape_id': 56
}

item = {
    'channel': data2['account_id'],
    'report_id': data2['id'],
    'report_data': Json(dict([(k, v) for k, v in data2.items() if k not in ['account_id', 'id']]))
}



def sql_insert(tableName, data_dict):
    '''
        INSERT INTO onetable (channel,  report_id,  report_data)
        VALUES (%(channel)s, %(report_id)s, %(report_data)s );
    '''
    sql = '''
        INSERT INTO %s (%s)
        VALUES (%%(%s)s );
        '''   % (tableName, ',  '.join(data_dict),  ')s, %('.join(data_dict))
    return sql

tableName = 'onetable'
sql = sql_insert(tableName, item)
try:
    with conn.cursor() as cur:
        cur.execute(sql, item)
    conn.commit()
except Exception as e:
    logging.debug(e)
    conn.rollback()
finally:
    conn.close()
0
网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接