时间戳

首页 > 解决方案 > “SQL语句中没有使用所有参数”——如何用python在mysql中插入API json数据？

python - “SQL语句中没有使用所有参数”——如何用python在mysql中插入API json数据？

问题描述

我需要将 Yandex API 获得的 JSON Metrics 插入到已经创建的 mysql 表中。代码、json 和错误文本如下。据我了解，错误不在于正确的数据格式-需要将json中的数据分解为表格，并且指标应分为2列

我用 API 得到的是 json：

{
"data": [
    {
        "dimensions": [
            {
                "name": "2019-02-03"
            }
        ],
        "metrics": [
            100.0,
            1000.0
        ]
    },
    {
        "dimensions": [
            {
                "name": "2019-02-01"
            }
        ],
        "metrics": [
            200.0,
            2000.0
        ]
    },
    {
        "dimensions": [
            {
                "name": "2019-02-02"
            }
        ],
        "metrics": [
            300.0,
            3000.0
        ]
    }
],
"data_lag": 148,
"max": [
    300.0,
    3000.0
],
"min": [
    100.0,
    1000.0
],
"query": {
    "adfox_event_id": "0",
    "attribution": "Last",
    "auto_group_size": "1",
    "currency": "RUB",
    "date1": "2019-02-01",
    "date2": "2019-02-03",
    "dimensions": [
        "ym:s:date"
    ],
    "filters": "ym:s:lastsignUTMSource=='yandex_market'",
    "group": "Week",
    "ids": [
        COUNTER_ID
    ],
    "limit": 100,
    "metrics": [
        "ym:s:ecommercePurchases",
        "ym:s:ecommerceRevenue"
    ],
    "offline_window": "21",
    "offset": 1,
    "quantile": "50",
    "sort": [
        "-ym:s:ecommercePurchases"
    ]
},
"sample_share": 1.0,
"sample_size": 619636,
"sample_space": 619636,
"sampled": false,
"total_rows": 3,
"total_rows_rounded": false,
"totals": [
    600.0,
    6000.0
]
}

代码：

import requests
import json
import mysql.connector

headers = {'Authorization': 'OAuth TOKEN'}
ids = {
    'Count_1': COUNTER_ID,
}

body = {
    'metrics': 'ym:s:ecommercePurchases,ym:s:ecommerceRevenue',
    'dimensions': 'ym:s:date',
    'date1': '2019-02-01',
    'date2': '2019-02-03',
    'filters': "ym:s:lastsignUTMSource=='yandex_market'",
    'ids': COUNTER_ID,
    'accuracy': 'full',
}

while True:
  try:
    req = requests.get('https://api-metrika.yandex.ru/stat/v1/data', params=body, headers=headers)
    req.encoding = 'utf-8'  # UTF-8
    #succes\error messages
    #...
    elif req.status_code == 200: #succes message
            print("Report succes") 
            parsed = json.loads(req.text) 
            print (json.dumps(parsed, indent=4, sort_keys=True)) #json with hierarchy
            break
    #...

con = mysql.connector.connect(
  host="host_IP",
  user="USER",
  passwd="PWD",
  database="DB_NAME"
)

mycursor = con.cursor()
sql = "INSERT INTO API_METRIKA(Date, Purchases, Revenue) VALUES (%s, %s, %s)" 

mycursor.executemany(sql, parsed) #inserting api-data into mysql table
print('Вставлено строк:', mycursor.rowcount) #how many rows were inserted

con.commit()  
con.close()

完整的错误文本：

ProgrammingError Traceback (last recent call last) in () 115 print (parsed) 116 --> 117 mycursor.executemany(sql, parsed) 118 119 print('Вставлено строк:', mycursor.rowcount)

1 帧 /usr/local/lib/python3.6/dist-packages/mysql/connector/cursor.py in _batch_insert(self, operation, seq_params) 595 if psub.remaining != 0: 596 raise errors.ProgrammingError(-- > 597 “SQL 语句中并未使用所有参数”) 598 #for p in self._process_params(params): 599 # tmp = tmp.replace(b'%s',p,1)

ProgrammingError: 并非所有参数都在 SQL 语句中使用

标签： pythonmysqljson

解决方案

要从 json 提要中传递特定参数，您需要从树结构中提取特定值。目前，您将整个json 传递给executemany()调用。另外，考虑.json()响应对象的方法，避免需要json.loads()甚至导入json库：

parsed = req.json()
...

从您发布的结果中，所需的维度和指标显示在数据列表级别：

for v in parsed["data"]:
   print(v)

# {'metrics': [100.0, 1000.0], 'dimensions': [{'name': '2019-02-03'}]}
# {'metrics': [200.0, 2000.0], 'dimensions': [{'name': '2019-02-01'}]}
# {'metrics': [300.0, 3000.0], 'dimensions': [{'name': '2019-02-02'}]}

因此，您可以通过列表推导创建一个vals列表以传递给executemany：

sql = "INSERT INTO API_METRIKA (`Date`, `Purchases`, `Revenue`) VALUES (%s, %s, %s)" 

# LIST OF TUPLES
vals = [(v['dimensions'][0]['name'],
         v['metrics'][0],
         v['metrics'][1]) for v in parsed["data"]]

# inserting api-data into mysql table
mycursor.executemany(sql, vals)

推荐阅读