我的代码没有运行,我正在尝试从json文件中检索数据,我不知道我的SQLITE3查询有什么问题

8xiog9wr  于 2022-11-15  发布在  SQLite
关注(0)|答案(1)|浏览(150)

所有这些数据都已获取,并且打印正常,但无法反映在SQLBrowser的数据库中。当我打印变量的值时,它完全正确,我在代码的末尾使用了conn.commit。但是在它生成的SQLite文件中没有获得一个值[图像显示了它在SQLFILE中的外观][1]

import ssl
import sqlite3
import json

ctx = ssl.create_default_context()
ctx.check_hostname= False
ctx.verify_mode = ssl.CERT_NONE

conn = sqlite3.connect('covid.sqlite')
cur = conn.cursor()

cur.executescript('''
DROP TABLE IF EXISTS Covid;
CREATE TABLE Covid (start_date TEXT, end_date TEXT, state TEXT, sex TEXT, age_group TEXT, covid_19_deaths INTEGER, total_deaths INTEGER, pneumonia_deaths INTEGER, influenza_deaths INTEGER, pneumonia_influenza_or_covid INTEGER)''')
conn.commit
hand = open('Covid.json').read()
data = json.loads(hand)

for item in data:
        start_date = item['start_date']
        end_date = item['end_date']
        state = item['state']
        sex = item['sex']
        age_group = item['age_group']
        covid_19_deaths = item['covid_19_deaths']
        total_deaths = item['total_deaths']
        pneumonia_deaths = item['pneumonia_deaths']
        influenza_deaths = item['influenza_deaths']
        pneumonia_influenza_or_covid = item['pneumonia_influenza_or_covid']
        
        cur.execute('''INSERT INTO Covid (start_date)
                VALUES ( ? )''', (start_date,) )
        cur.execute('''INSERT  INTO Covid (end_date)
                VALUES ( ? )''', ( end_date,) )
        cur.execute('''INSERT INTO Covid (state)
                VALUES ( ? )''', ( state,) )
        cur.execute('''INSERT INTO Covid (sex)
                VALUES ( ? )''', ( sex,) )
        cur.execute('''INSERT INTO Covid (age_group)
                VALUES ( ? )''', ( age_group,) )
        cur.execute('''INSERT INTO Covid (covid_19_deaths)
                VALUES ( ? )''', ( covid_19_deaths,) )
        cur.execute('''INSERT INTO Covid (total_deaths)
                VALUES ( ? )''', ( total_deaths,) )
        cur.execute('''INSERT INTO Covid (pneumonia_deaths)
                VALUES ( ? )''', ( pneumonia_deaths,) )
        cur.execute('''INSERT INTO Covid (influenza_deaths)
                VALUES ( ? )''', ( influenza_deaths,) )
        cur.execute('''INSERT INTO Covid (pneumonia_influenza_or_covid)
                VALUES ( ? )''', ( pneumonia_influenza_or_covid,) )
        cur.execute('''INSERT INTO Covid
                (start_date,end_date,state,sex,age_group,covid_19_deaths,total_deaths,pneumonia_deaths,influenza_deaths,pneumonia_influenza_or_covid ) VALUES ( ?, ?, ? ,?, ?, ?, ?, ?, ?, ?)''',
        (start_date,end_date,state,sex,age_group,covid_19_deaths,total_deaths,pneumonia_deaths,influenza_deaths,pneumonia_influenza_or_covid ))
        conn.commit```

  [1]: https://i.stack.imgur.com/RAdy1.png

I cannot find the mistakes i'm making in the code.
ercv8c1e

ercv8c1e1#

代码不完整(没有创建/初始化数据,并且cur.execute语句似乎缩进不正确)。
你也没有解释你观察到的确切问题是什么。
然而,您的代码中有一个明显的问题:您在data上迭代

for item in data:

但是,不是使用item,而是继续使用data[0]

start_date = data[0]['start_date']

所以您不是要插入data中的所有内容,而是要多次插入第一行len(data)
您也从来不会调用commit,所以它不会任何事情:

conn.commit

PS:我重新格式化了帖子,所以代码块是正确的,你需要开始和结束的“三个反引号”在他们自己的行上。

for foo in bar:
...

```for foo in bar:
    ...```

这就是您编写并呈现为破碎片段的内容。

相关问题