sqlite3.InterfaceError报错：dict_keys无法存入SQL表Opened_ports列

The error you're hitting happens because SQLite can't natively handle Python's dict_keys view object—it expects a serializable data type like a string, integer, or blob. Your attempt to use .list() was on the right track, but you got the syntax wrong: dict_keys doesn't have a .list() method; instead, you need to pass it to the built-in list() function to convert it to a regular list.

Here are two solid solutions to store your open ports properly:

Solution 1: Store as a Comma-Separated String (Simple for Basic Use) #

Convert the dict_keys to a list of strings, then join them into a single comma-separated string that fits perfectly in your VARCHAR column:

import sqlite3
import pandas as pd
from sqlalchemy import create_engine

def db(*args):
    with sqlite3.connect('Test.db') as db:
        cursor = db.cursor()
        ns, ip_addr, ports = args
        # Convert dict_keys to a comma-separated string
        open_ports = ','.join(map(str, list(ns[ip_addr]['tcp'].keys())))
        cursor.execute(''' CREATE TABLE IF NOT EXISTS Scaninfo( scanID INTEGER PRIMARY KEY, ip_address VARCHAR(40) NOT NULL, scanned_ports VARCHAR(100) NOT NULL, Opened_ports VARCHAR(100), Hostname VARCHAR(100) NOT NULL, ipaddress_state VARCHAR(100) NOT NULL); ''')
        cursor.execute("insert into Scaninfo (ip_address, scanned_ports, Opened_ports ,Hostname, ipaddress_state) values (?, ?, ?, ?, ?)", 
                      (ip_addr, ports, open_ports, ns[ip_addr].hostname(), ns[ip_addr].state() ))
        db.commit()

def Report_csv():
    db_name = 'Test.db'
    engine = create_engine('sqlite:///' + db_name)
    df = pd.read_sql_table('Scaninfo', engine)
    df.to_csv('test.csv')

Report_csv()

Breakdown: #

• list(ns[ip_addr]['tcp'].keys()): Converts the dict_keys view to a regular list of integers (your port numbers)
• map(str, ...): Turns each integer port into a string so we can join them
• ','.join(...): Combines all port strings into one like "53,80,111,443" which SQLite can store as a VARCHAR

Solution 2: Store as JSON (Better for Structured Data) #

If you want to preserve the list structure (so you can easily convert it back to a Python list later), store it as a JSON string. This requires importing the json module:

import sqlite3
import pandas as pd
from sqlalchemy import create_engine
import json

def db(*args):
    with sqlite3.connect('Test.db') as db:
        cursor = db.cursor()
        ns, ip_addr, ports = args
        # Convert dict_keys to a JSON string
        open_ports_json = json.dumps(list(ns[ip_addr]['tcp'].keys()))
        cursor.execute(''' CREATE TABLE IF NOT EXISTS Scaninfo( scanID INTEGER PRIMARY KEY, ip_address VARCHAR(40) NOT NULL, scanned_ports VARCHAR(100) NOT NULL, Opened_ports VARCHAR(100), Hostname VARCHAR(100) NOT NULL, ipaddress_state VARCHAR(100) NOT NULL); ''')
        cursor.execute("insert into Scaninfo (ip_address, scanned_ports, Opened_ports ,Hostname, ipaddress_state) values (?, ?, ?, ?, ?)", 
                      (ip_addr, ports, open_ports_json, ns[ip_addr].hostname(), ns[ip_addr].state() ))
        db.commit()

def Report_csv():
    db_name = 'Test.db'
    engine = create_engine('sqlite:///' + db_name)
    df = pd.read_sql_table('Scaninfo', engine)
    # Optional: Convert the JSON string back to a list when reading
    df['Opened_ports'] = df['Opened_ports'].apply(json.loads)
    df.to_csv('test.csv')

Report_csv()

Breakdown: #

• json.dumps(list(...)): Converts the list of ports into a JSON string like "[53,80,111,443]"
• When reading, json.loads() converts the string back to a Python list, making it easy to work with programmatically

Either solution will fix your InterfaceError—pick the one that fits how you plan to use the data later!

内容的提问来源于stack exchange，提问作者Haseeb Javed