如何将列表数据插入SQLite

Question

我在一个文本文件中有数据，并从中提取了特定的键和值。

Python文件

data = []
with open('datatest.txt', 'r', encoding='utf-8') as f:
for count, line in enumerate(f, start=1):
if count % 2 == 0:
data.append((json.loads(json.loads(line)['message'])['first_name'],
json.loads(json.loads(line)['message'])['last_name'],
json.loads(json.loads(line)['message'])['id'],
json.loads(json.loads(line)['message'])['phone']))

Datatest.txt文件的内容

{"index": {"_type": "_doc", "_id": "wJgr4m8BAObvGO9GQ4_E"}}
{"message":"{"_":"user","pFlags":{"contact":true},"flags":2175,"id":379951860,"access_hash":"6967195540985199805","first_name":"پژوا","last_name":"روزبهی","username":"mramtd2","phone":"989157145632","photo":{"_":"userProfilePhoto","photo_id":"1631880813210609625","photo_small":{"_":"fileLocation","dc_id":4,"volume_id":"448413446","local_id":476387,"secret":"655623158723369503"},"photo_big":{"_":"fileLocation","dc_id":4,"volume_id":"448413446","local_id":476389,"secret":"13993366131879811943"}},"status":{"_":"userStatusOffline","was_online":1558046876}}","phone":"989157145632","@version":"1","typ":"telegram_contacts","access_hash":"6967195540985199805","id":379951860,"@timestamp":"2020-01-26T13:50:12.793Z","path":"/home/user/mirror_01/users_5d65f610ec18aa615a5f580c.log","username":"mramtd2","type":"redis","flags":2175,"host":"ubuntu","imported_from":"telegram_contacts"}
{"index": {"_type": "_doc", "_id": "wZgr4m8BAObvGO9GQ4_E"}}
{"message":"{"_":"user","pFlags":{"contact":true},"flags":2167,"id":104168655,"access_hash":"4049515388751185251","first_name":"فرنود","last_name":"پولادی","phone":"989122802780","photo":{"_":"userProfilePhoto","photo_id":"447400966949546277","photo_small":{"_":"fileLocation","dc_id":1,"volume_id":"806513597","local_id":18178,"secret":"17864732085601779143"},"photo_big":{"_":"fileLocation","dc_id":1,"volume_id":"806513597","local_id":18180,"secret":"15235849821352229632"}},"status":{"_":"userStatusOffline","was_online":1564097832}}","phone":"989122802780","@version":"1","typ":"telegram_contacts","access_hash":"4049515388751185251","id":104168655,"@timestamp":"2020-01-26T13:50:12.793Z","path":"/home/user/mirror2/users_5d38e984ce828ee03a4789ee.log","type":"redis","flags":2167,"host":"ubuntu","imported_from":"telegram_contacts"}
{"index": {"_type": "_doc", "_id": "wpgr4m8BAObvGO9GQ4_E"}}
{"message":"{"_":"user","pFlags":{"contact":true},"flags":2167,"id":370718330,"access_hash":"10906080075127684980","first_name":"گلنسا","last_name":"پریی","phone":"989139505268","photo":{"_":"userProfilePhoto","photo_id":"1592223103833974699","photo_small":{"_":"fileLocation","dc_id":4,"volume_id":"434224163","local_id":12578,"secret":"12430588603747451378"},"photo_big":{"_":"fileLocation","dc_id":4,"volume_id":"434224163","local_id":12580,"secret":"5910708978456395305"}},"status":{"_":"userStatusOffline","was_online":1561114212}}","phone":"989139505268","@version":"1","typ":"telegram_contacts","access_hash":"10906080075127684980","id":370718330,"@timestamp":"2020-01-26T13:50:12.793Z","path":"/home/user/mirror_01/users_5d65f610ec18aa615a5f580c.log","type":"redis","flags":2167,"host":"ubuntu","imported_from":"telegram_contacts"}
{"index": {"_type": "_doc", "_id": "w5gr4m8BAObvGO9GQ4_E"}}
{"message":"{"_":"user","pFlags":{"contact":true},"flags":2175,"id":421775832,"access_hash":"8579095166420383836","first_name":"دلکش","last_name":"چالاکی","username":"Betym11356","phone":"989126159776","photo":{"_":"userProfilePhoto","photo_id":"1811513405139429339","photo_small":{"_":"fileLocation","dc_id":4,"volume_id":"455032489","local_id":485722,"secret":"12099548278164429458"},"photo_big":{"_":"fileLocation","dc_id":4,"volume_id":"455032489","local_id":485724,"secret":"313431783423483716"}},"status":{"_":"userStatusOffline","was_online":1564084837}}","phone":"989126159776","@version":"1","typ":"telegram_contacts","access_hash":"8579095166420383836","id":421775832,"@timestamp":"2020-01-26T13:50:12.793Z","path":"/home/user/mirror2/users_5d38e984ce828ee03a4789ee.log","username":"Betym11356","type":"redis","flags":2175,"host":"ubuntu","imported_from":"telegram_contacts"}
{"index": {"_type": "_doc", "_id": "p5gr4m8BAObvGO9GQ4_B"}}
{"message":"{"_":"user","pFlags":{"contact":true},"flags":2103,"id":171873228,"access_hash":"13248517514388945915","first_name":"ویس","last_name":"تورتکی","phone":"989372499383","photo":{"_":"userProfilePhoto","photo_id":"738189893774190623","photo_small":{"_":"fileLocation","dc_id":4,"volume_id":"439634863","local_id":87854,"secret":"241528711325634206"},"photo_big":{"_":"fileLocation","dc_id":4,"volume_id":"439634863","local_id":87856,"secret":"6022220707203794397"}}}","phone":"989372499383","@version":"1","typ":"telegram_contacts","access_hash":"13248517514388945915","id":171873228,"@timestamp":"2020-01-26T13:50:12.792Z","path":"/home/user/mirror_01/users_5d65f610ec18aa615a5f580c.log","type":"redis","flags":2103,"host":"ubuntu","imported_from":"telegram_contacts"}
{"index": {"_type": "_doc", "_id": "qJgr4m8BAObvGO9GQ4_B"}}
{"message":"{"_":"user","pFlags":{"contact":true},"flags":2175,"id":54581106,"access_hash":"16670232207411597190","first_name":"آزاده","last_name":"نازچهری","username":"mehdiomidii","phone":"989121447728","photo":{"_":"userProfilePhoto","photo_id":"234424065705749301","photo_small":{"_":"fileLocation","dc_id":4,"volume_id":"446735579","local_id":362838,"secret":"9556479517471012490"},"photo_big":{"_":"fileLocation","dc_id":4,"volume_id":"446735579","local_id":362840,"secret":"8435444578550263608"}},"status":{"_":"userStatusRecently"}}","phone":"989121447728","@version":"1","typ":"telegram_contacts","access_hash":"16670232207411597190","id":54581106,"@timestamp":"2020-01-26T13:50:12.792Z","path":"/home/user/mirror2/users_5d38e984ce828ee03a4789ee.log","username":"mehdiomidii","type":"redis","flags":2175,"host":"ubuntu","imported_from":"telegram_contacts"}
{"index": {"_type": "_doc", "_id": "qZgr4m8BAObvGO9GQ4_B"}}

现在我想把data列表值插入到SQLite中。我该怎么做？

Answer 1

您需要一个包含以下列的表(例如用户(：id、firstName、lastName、userId、phone。

然后：

for record in data:
first_name, last_name, user_id, phone = record
cur.execute(
'INSERT INTO users (firstName, lastName, userId, phone)'
' VALUES (?, ?, ? ,?)',
(first_name, last_name, user_id, phone))