python - 使用 sqlite 的 fetchone() 读取循环中的第一行

Question

我正在使用循环来获取 fetchone() 的内容，每次代码读取 fetchone() 时，光标都会移动到下一行。我明白我需要小心。我正在使用此处找到的示例 https://dev.mysql.com/doc/connector-python/en/connector-python-api-mysqlcursor-fetchone.html当我有多于一行时，这是有效的，但是当只有一行时，我会收到错误

    for k, v in cur.fetchone().items():
AttributeError: 'NoneType' object has no attribute 'items'

两个相关函数

def dict_factory(cursor, row):
    d = {}
    for idx, col in enumerate(cursor.description):
        d[col[0]] = row[idx]
    return d

def open_sql(sql_folder, sql_name, sql_table):
    # databases are located at /work/jmjohnso1/db_project  
    path_name = os.path.join(sql_folder,sql_name).strip()
    con = lite.connect(path_name)
    con.row_factory = dict_factory
    cur = con.cursor()
    cur.execute('SELECT * FROM ' + sql_table) 

    dict_contents = defaultdict(list) 

    for row in cur:
        for k, v in cur.fetchone().items():
            dict_contents[k].append(v)

    con.close()
    print(dict_contents)
    return dict_contents

如果您想要完整的代码:

# python3.5
# pymongo version 3.2.2
# MongoDB shell version: 3.0.11

import os
import pymongo
from pymongo import MongoClient
import sqlite3 as lite
import pyewf
import hashlib
from itertools import chain
from collections import defaultdict
import pprint

def list_sql_db(folder):
    # need a list for multiprocessing so I made a file. 
    file_name = os.path.join(folder, 'sql_db')
    if not os.path.isfile(file_name):
        with open (file_name, 'w') as line:
            for (dirpath, dirs, files) in os.walk(folder):
                for name in files:
                    line.write(name + '\n')
    return file_name    

def dict_factory(cursor, row):
    d = {}
    for idx, col in enumerate(cursor.description):
        d[col[0]] = row[idx]
    return d

def open_sql(sql_folder, sql_name, sql_table):
    # databases are located at /work/jmjohnso1/db_project  
    path_name = os.path.join(sql_folder,sql_name).strip()
    con = lite.connect(path_name)
    con.row_factory = dict_factory
    cur = con.cursor()
    cur.execute('SELECT * FROM ' + sql_table) 

    dict_contents = defaultdict(list) 

    for row in cur:
        for k, v in cur.fetchone().items():
            dict_contents[k].append(v)

    con.close()
    print(dict_contents)
    return dict_contents

def insert_tsk_mongo(sql_folder, sql_name, sql_table):
    client = MongoClient() # connect to mongodb
    db = client.nus # make or use a db called nus
    contents = open_sql(sql_folder, sql_name, sql_table)
    collection = sql_name.strip().replace('-','_') # because mongo will write but not read a collection with -

    # document_id = db[collection].insert({ # sql_name is the hard drive name 
        # sql_table:
            # contents           
    # })

###############################################################################

sql_folder = '/work/jmjohnso1/db_project'    
# sql_tables = ['tsk_fs_info', 'tsk_image_info',
              # 'tsk_db_info ', 'tsk_image_names',
              # 'tsk_file_layout', 'tsk_objects',
              # 'tsk_files', 'tsk_vs_info', 'tsk_vs_parts']

sql_tables = ['tsk_fs_info']              

sql_folder_name = list_sql_db(sql_folder)

with open (sql_folder_name, 'r') as read: 
    sql_names = read.readlines()

for sql_name in sql_names:
    for sql_table in sql_tables:
        insert_tsk_mongo(sql_folder, sql_name, sql_table)
    break    

Answer 1

您将提取行两次，一次使用 for row in cur: 循环，然后在循环中使用 cur.fetchone() 。在最后一行，您已用完要提取的行，因此返回 None。

当您已经在光标上循环时，不需要使用cur.fetchone()。只需删除 cur.fetchone() 调用并使用您已通过 for 获取的行即可:

for row in cur:
    for k, v in row.items():
        dict_contents[k].append(v)