使用Python定位JSON API中的特定值并将其插入Postgresql [英] Targeting specific values from JSON API and inserting into Postgresql, using Python

问题描述

现在，我可以连接到url api和我的数据库了.我正在尝试使用psycopg2将URL中的数据插入到postgresql数据库中.我不完全了解如何执行此操作，而这就是我想出的全部方法.

Right now i am able to connect to the url api and my database. I am trying to insert data from the url to the postgresql database using psycopg2. I dont fully understand how to do this, and this is all i could come up with to do this.

import urllib3
import json
import certifi
import psycopg2
from psycopg2.extras import Json


http = urllib3.PoolManager(
    cert_reqs='CERT_REQUIRED',
    ca_certs=certifi.where())
url = '<API-URL>'
headers = urllib3.util.make_headers(basic_auth='<user>:<passowrd>')
r = http.request('GET', url, headers=headers)
data = json.loads(r.data.decode('utf-8'))


def insert_into_table(data):

    for item in data['issues']:
        item['id'] = Json(item['id'])

    with psycopg2.connect(database='test3', user='<username>', password='<password>', host='localhost') as conn:
        with conn.cursor() as cursor:
            query = """
                INSERT into
                     Countries
                    (revenue)
                VALUES
                    (%(id)s);
            """
            cursor.executemany(query, data)

        conn.commit()


insert_into_table(data)

所以这段代码在cursor.executemany(query, data)

所以我知道json.loads带回来一个类型对象，而json.dumps带回来了一个类型字符串.我不确定应该使用哪一个.而且我知道我完全不知道如何将im定位到'id'值，并将其插入查询中.

So i know that json.loads brings back a type object and that json.dumps brings a type string . I wasn't sure which one i should be using. and i know i am completely missing something on how im targeting the 'id' value, and inserting it into the query.

关于API的一些知识，它非常大且复杂，最终我将不得不倒下许多树来获取某些值，这是我从中提取的示例.

Also a little about the API, it is very large and complex and eventually i'll have to go down multiple trees to grab certain values, here is an example of what i'm pulling from.

我正在尝试在问题"下获取"id"，而不是问题类型"下

I am trying to grab "id" under "issues" and not "issue type"

{
  "expand": "<>",
  "startAt": 0,
  "maxResults": 50,
  "total": 13372,
  "issues": [
    {
      "expand": "<>",
      "id": "41508",
      "self": "<>",
      "key": "<>",
      "fields": {
        "issuetype": {
          "self": "<>",
          "id": "1",
          "description": "<>",
          "iconUrl": "<>",
          "name": "<>",
          "subtask": <>,
          "avatarId": <>
        },

推荐答案

首先，将ids提取到元组列表中:

First, extract ids into a list of tuples:

ids = list((item['id'],) for item in data['issues'])
# example ids: [('41508',), ('41509',)]

接下来使用函数 extras.execute_values():

from psycopg2 import extras

query = """
    INSERT into Countries (revenue)
    VALUES %s;
"""
extras.execute_values(cursor, query, ids)

为什么我遇到类型错误?

Why I was getting type errors?

函数 executemany(查询，vars_list)应该是一个序列，而data是一个对象，不能通过整数索引访问元素.

The second argument of the function executemany(query, vars_list) should be a sequence while data is an object which elements cannot be accessed by integer indexes.

为什么使用execute_values()代替executemany()?

由于性能，第一个函数执行带有多个参数的单个查询，而第二个函数执行与参数一样多的查询.

Because of performance, the first function executes a single query with multiple arguments, while the second one executes as many queries as arguments.

请注意，默认情况下，execute_values()的第三个参数是一个元组列表，因此我们以这种方式提取了ids.

Note, that by default the third argument of execute_values() is a list of tuples, so we extracted ids just in this way.

如果必须将值插入多个列中，则列表中的每个元组都应包含单个插入行的所有值，例如:

If you have to insert values into more than one column, each tuple in the list should contain all the values for a single inserted row, example:

values = list((item['id'], item['key']) for item in data['issues'])

query = """
    INSERT into Countries (id, revenue)
    VALUES %s;
"""
extras.execute_values(cur, query, values)

这篇关于使用Python定位JSON API中的特定值并将其插入Postgresql的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！