sqlite 正在尝试从JSON文件插入到数据库

cxfofazt 于 2022-11-14 发布在 SQLite

关注(0)|答案(2)|浏览(237)

我必须做一个Django网络应用程序的Yelp API。我创建了db()来将数据输入到数据库中，但是如何解决这个错误？我试着在没有Pandas的情况下做到这一点：

Message=string indices must be integers
Source=C:\Users\diggt\OneDrive\College\Rowan\Fall22\10430_computing_and_informatics_capstone\yelp_VSCode\yelp.py
StackTrace:
File "C:\Users\diggt\OneDrive\College\Rowan\Fall22\10430_computing_and_informatics_capstone\yelp_VSCode\yelp.py", line 104, in <genexpr>
  keys = (entry[c] for c in columns)
File "C:\Users\diggt\OneDrive\College\Rowan\Fall22\10430_computing_and_informatics_capstone\yelp_VSCode\yelp.py", line 115, in db
  cur.executemany(sql, keys)
File "C:\Users\diggt\OneDrive\College\Rowan\Fall22\10430_computing_and_informatics_capstone\yelp_VSCode\yelp.py", line 153, in main
  db()
File "C:\Users\diggt\OneDrive\College\Rowan\Fall22\10430_computing_and_informatics_capstone\yelp_VSCode\yelp.py", line 157, in <module> (Current frame)
  main()

# -*- coding: utf-8 -*-

from __future__ import print_function

import argparse
import json
import csv
import pprint
import requests
import sys
import sqlite3
#import pandas as pd

from urllib.error import HTTPError
from urllib.parse import quote

API_KEY = 'secret'

# API constants, you shouldn't have to change these.
API_HOST = 'https://api.yelp.com'
SEARCH_PATH = '/v3/businesses/search'
BUSINESS_PATH = '/v3/businesses/'  # Business ID will come after slash.

# Defaults
DEFAULT_TERM = 'dinner'
DEFAULT_LOCATION = 'Glassboro, NJ'
SEARCH_LIMIT = 3
OFFSET = 0

def request(host, path, api_key, url_params=None):
    url_params = url_params or {}
    url = '{0}{1}'.format(host, quote(path.encode('utf8')))
    headers = {
        'Authorization': 'Bearer %s' % api_key,
    }

    print(u'Querying {0} ...'.format(url))

    response = requests.request('GET', url, headers=headers, params=url_params)

    return response.json()

def search(api_key, term, location):
    url_params = {
        'term': term.replace(' ', '+'),
        'location': location.replace(' ', '+'),
        'limit': SEARCH_LIMIT,
        'offset': OFFSET
    }
    return request(API_HOST, SEARCH_PATH, api_key, url_params=url_params)

def get_business(api_key, business_id):
    business_path = BUSINESS_PATH + business_id

    return request(API_HOST, business_path, api_key)

def query_api(term, location):
    response = search(API_KEY, term, location)

    businesses = response.get('businesses')

    if not businesses:
        print(u'No businesses for {0} in {1} found.'.format(term, location))
        return

    business_id = businesses[0]['id']

    print(u'{0} businesses found, querying business info ' \
          'for the top result "{1}" ...'.format(
        len(businesses), business_id))
    response = get_business(API_KEY, business_id)

    print(u'Result for business "{0}" found:'.format(business_id))
    pprint.pprint(response, indent=2)

    str_to_write_to_file = json.dumps(response, skipkeys=True, allow_nan=True, indent=4)

    with open('yelp.json', 'w') as f:
        f.write(str_to_write_to_file)

def db():
    
    with open('yelp.json', 'r') as f:
        data = f.readlines()

    conn = sqlite3.connect('yelp.db')
    cur = conn.cursor()
        # Create the table if it doesn't exist.
    cur.execute(
        """CREATE TABLE IF NOT EXISTS yelp(
                id INTEGER PRIMARY KEY,
                alias varchar(100),
                location varchar(100),
                display_phone varchar(15)
            );"""
    )
    
    for entry in data:
        columns = ["id" "alias", "location", "display_phone"]
        keys = (entry[c] for c in columns)
        
        # Execute the command and replace '?' with the each value
        # in 'values'. DO NOT build a string and replace manually.
        # the sqlite3 library will handle non safe strings by doing this.
        sql = """INSERT INTO yelp (id, alias, location, display_phone) VALUES(
                    ?,
                    ?,
                    ?,
                    ?
                );"""
        cur.executemany(sql, keys)
        print(f'{entry["alias"]} data inserted Succefully')

    conn.commit()
    conn.close()

    with sqlite3.connect("yelp.db") as conn:
        cmd = """SELECT * FROM yelp;"""
        cur = conn.execute(cmd)
        res = cur.fetchall()
        for r in res:
            print(r)

def main():
    parser = argparse.ArgumentParser()

    parser.add_argument('-q', '--term', dest='term', default=DEFAULT_TERM,
                        type=str, help='Search term (default: %(default)s)')
    parser.add_argument('-l', '--location', dest='location',
                        default=DEFAULT_LOCATION, type=str,
                        help='Search location (default: %(default)s)')

    input_values = parser.parse_args()

    try:
        query_api(input_values.term, input_values.location)

    except HTTPError as error:
        sys.exit(
            'Encountered HTTP error {0} on {1}:\n {2}\nAbort program.'.format(
                error.code,
                error.url,
                error.read(),
            )
        )
    
    db()

if __name__ == '__main__':
    main()

JSON文件：

{
    "id": "umC69pkiPyk3qY7IB49ZYw",
    "alias": "bosphorus-mediterranean-cuisine-glassboro",
    "name": "Bosphorus Mediterranean Cuisine",
    "image_url": "https://s3-media4.fl.yelpcdn.com/bphoto/G7VCO3tvx8NGPz5g0fSpMw/o.jpg",
    "is_claimed": true,
    "is_closed": false,
    "url": "https://www.yelp.com/biz/bosphorus-mediterranean-cuisine-glassboro?adjust_creative=9aYQmmK21ApZ7TfokeTk1A&utm_campaign=yelp_api_v3&utm_medium=api_v3_business_lookup&utm_source=9aYQmmK21ApZ7TfokeTk1A",
    "phone": "+18562432015",
    "display_phone": "(856) 243-2015",
    "review_count": 14,
    "categories": [
        {
            "alias": "turkish",
            "title": "Turkish"
        },
        {
            "alias": "halal",
            "title": "Halal"
        },
        {
            "alias": "kebab",
            "title": "Kebab"
        }
    ],
    "rating": 5.0,
    "location": {
        "address1": "524 Delsea Drive N",
        "address2": null,
        "address3": null,
        "city": "Glassboro",
        "zip_code": "08028",
        "country": "US",
        "state": "NJ",
        "display_address": [
            "524 Delsea Drive N",
            "Glassboro, NJ 08028"
        ],
        "cross_streets": ""
    },
    "coordinates": {
        "latitude": 39.7150351328115,
        "longitude": -75.1118882
    },
    "photos": [
        "https://s3-media4.fl.yelpcdn.com/bphoto/G7VCO3tvx8NGPz5g0fSpMw/o.jpg",
        "https://s3-media2.fl.yelpcdn.com/bphoto/HvhYRZO2rOYUBX0DagVE3w/o.jpg",
        "https://s3-media2.fl.yelpcdn.com/bphoto/PQHr3upfVULUjwz1M-ILcw/o.jpg"
    ],
    "hours": [
        {
            "open": [
                {
                    "is_overnight": false,
                    "start": "1100",
                    "end": "2200",
                    "day": 0
                },
                {
                    "is_overnight": false,
                    "start": "1100",
                    "end": "2200",
                    "day": 1
                },
                {
                    "is_overnight": false,
                    "start": "1100",
                    "end": "2200",
                    "day": 2
                },
                {
                    "is_overnight": false,
                    "start": "1100",
                    "end": "2200",
                    "day": 3
                },
                {
                    "is_overnight": false,
                    "start": "1100",
                    "end": "2200",
                    "day": 4
                },
                {
                    "is_overnight": false,
                    "start": "1100",
                    "end": "2200",
                    "day": 5
                },
                {
                    "is_overnight": false,
                    "start": "1100",
                    "end": "2200",
                    "day": 6
                }
            ],
            "hours_type": "REGULAR",
            "is_open_now": true
        }
    ],
    "transactions": [
        "pickup",
        "delivery"
    ],
    "messaging": {
        "url": "https://www.yelp.com/raq/umC69pkiPyk3qY7IB49ZYw?adjust_creative=9aYQmmK21ApZ7TfokeTk1A&utm_campaign=yelp_api_v3&utm_medium=api_v3_business_lookup&utm_source=9aYQmmK21ApZ7TfokeTk1A#popup%3Araq",
        "use_case_text": "Message the Business"
    }
}

sqlite

来源：https://stackoverflow.com/questions/74021027/trying-to-insert-from-json-file-to-database

2条答案

按热度按时间

dldeef671#

您不应该使用f.readlines()来读取JSON文件，而应该使用json.load(f)。
JSON中只有一组值，因此不需要循环或executemany()。

def db():
    
    with open('yelp.json', 'r') as f:
        data = json.load(f)

    conn = sqlite3.connect('yelp.db')
    cur = conn.cursor()
        # Create the table if it doesn't exist.
    cur.execute(
        """CREATE TABLE IF NOT EXISTS yelp(
                id INTEGER PRIMARY KEY,
                alias varchar(100),
                location varchar(100),
                display_phone varchar(15)
            );"""
    )
    
    columns = ["id" "alias", "location", "display_phone"]
    keys = [entry[c] for c in columns]
        
    # Execute the command and replace '?' with the each value
    # in 'values'. DO NOT build a string and replace manually.
    # the sqlite3 library will handle non safe strings by doing this.
    sql = """INSERT INTO yelp (id, alias, location, display_phone) VALUES(
                ?,
                ?,
                ?,
                ?
            );"""
    cur.execute(sql, keys)
    print(f'{entry["alias"]} data inserted Succefully')

    conn.commit()
    conn.close()

    with sqlite3.connect("yelp.db") as conn:
        cmd = """SELECT * FROM yelp;"""
        cur = conn.execute(cmd)
        res = cur.fetchall()
        for r in res:
            print(r)

赞(0）回复(0）举报 2022-11-14

jaql4c8m2#

所以最终我想通了.差不多吧。我使用了@Bramar所说的，但解决方案是将json文件变成一个数组，然后我开始收到这个错误sqlite3.ProgrammingError: Incorrect number of bindings supplied. The current statement uses 4, and there are 1 supplied.，它原来是我作为字典存储在json中的条目之一，所以我临时删除了它，看看我是否可以使它工作，它工作了，这是代码-

print(u'Result for business "{0}" found:'.format(business_id))

    str_to_write_to_file = json.dumps([response], indent=4)

    with open('yelp.json', 'w') as f:
        f.write(str_to_write_to_file)

def db():
    
    with open('yelp.json', 'r') as f:
        data = json.load(f)

    conn = sqlite3.connect('data/yelp.db')
    cur = conn.cursor()
        # Create the table if it doesn't exist.
    cur.execute(
        """CREATE TABLE IF NOT EXISTS yelp(
                id INTEGER PRIMARY KEY,
                alias varchar(100),
                display_phone varchar(15),
                location dictionary
            );"""
    )
    columns = ["alias", "display_phone"]
    keys = [data[0][c] for c in columns]
        
    # Execute the command and replace '?' with the each value
    # in 'values'. DO NOT build a string and replace manually.
    # the sqlite3 library will handle non safe strings by doing this.
    sql = '''INSERT INTO yelp (alias, display_phone) VALUES(
                ?,
                ?
            );'''
    cur.execute(sql, keys)

    conn.commit()
    conn.close()

希望这能帮助一些人，这可能会非常令人困惑。

赞(0）回复(0）举报 2022-11-14

我来回答

sqlite 正在尝试从JSON文件插入到数据库

2条答案

相关问题

热门标签

最新问答