跳过CSV中的行并插入Sqlite

knsnq2tg 于 2023-03-23 发布在 SQLite

关注(0)|答案(1)|浏览(127)

我有一个CSV文件，格式如下：

第一行是空的，然后访问点数据来自另一个空行来自客户端数据的来源。我试图将此数据插入到sqlite db的两个表中，但没有成功。

import sqlite3
import csv

con = sqlite3.connect("access_wifi.db")
cur = con.cursor()

cur.execute("""CREATE TABLE IF NOT EXISTS access_points (BSSID TEXT, First_seen TEXT, Last_seen TEXT, Channel INT, 
Speed INT, Privacy TEXT, Cipher TEXT, Authentication TEXT, Power INT, beacons INT, IV INT, LAN_IP TEXT, ID_length INT, 
ESSID TEXT)""")

cur.execute("""CREATE TABLE IF NOT EXISTS clients (Station TEXT, First_seen TEXT, Last_seen TEXT, Power INT, 
Packets INT, BSSID TEXT, Probed_ESSID TEXT)""")

with open('dump-01.csv') as csv_file:
    reader = csv.reader(csv_file, delimiter=',')

    for row in reader:
        if not row:
            continue

        if row[0] == 'BSSID':
            current_table = 'access_points'

        elif row[0] == 'Station MAC':
            current_table = 'clients'
            # cur.execute(f'INSERT INTO {current_table} VALUES (?, ?, ?, ?, ?, ?, ?)', row)

        else:
            cur.execute(f'INSERT INTO {current_table} VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)', row[:-1])

con.commit()
con.close()

sqlite

来源：https://stackoverflow.com/questions/75811158/skipping-row-in-csv-and-inserting-in-sqlite

1条答案

按热度按时间

vwhgwdsa1#

我想问题出在Inserts上，因为当我运行修改后的代码时：

with open("input.csv", newline="") as f:
    reader = csv.reader(f)

    current_table = ""
    for row in reader:
        if not row:
            continue
        elif row[0] == "BSSID":
            current_table = "access_points"
        elif row[0] == "Station MAC":
            current_table = "clients"
        else:
            print(f"{current_table}: {row[:-1]}")

在这个精简的CSV上：

BSSID,FOO
64:6D:6C...,foo
AC:37:28...,foo
C4:27:28...,foo
B8:C1:A2...,foo

Station MAC,BAR
BC:2F:3D...,bar
OE:90:E6...,bar
E4:5F:01...,bar

我得到了这个看起来对我来说是正确的输出：

access_points: ['64:6D:6C...']
access_points: ['AC:37:28...']
access_points: ['C4:27:28...']
access_points: ['B8:C1:A2...']
clients: ['BC:2F:3D...']
clients: ['OE:90:E6...']
clients: ['E4:5F:01...']

所以，我想问题在于实际插入正确的数据。我认为如果将过程分为两个步骤，调试会更容易：首先根据您的喜好解析CSV（验证数据/结构）;然后尝试已知数量的插入物：

ap_rows = []
client_rows = []
with open("input.csv", newline="") as f:
    reader = csv.reader(f)

    table = []
    for row in reader:
        if not row:
            continue
        elif row[0] == "BSSID":
            table = ap_rows
        elif row[0] == "Station MAC":
            table = client_rows
        else:
            table.append(row[:-1])

print("Access Points:")
pprint.pprint(ap_rows, width=20)
print("Clients:")
pprint.pprint(client_rows, width=20)

# Access Points:
# [['64:6D:6C...'],
#  ['AC:37:28...'],
#  ['C4:27:28...'],
#  ['B8:C1:A2...']]
# Clients:
# [['BC:2F:3D...'],
#  ['OE:90:E6...'],
#  ['E4:5F:01...']]

# Do SQL stuff
...

赞(0）回复(0）举报 2023-03-23

我来回答

跳过CSV中的行并插入Sqlite

1条答案

相关问题

热门标签

最新问答