Working with SQLITE3 database via Python lists and dictionaries

 1. Загрузка записи из словаря

(.env) boris@boris-All-Series:~/SQLITE01$ cat sqlite5Insert.py

import sqlite3

import sys

con = sqlite3.connect("student.db")

cursor = con.cursor()

students1 = {"id":19,"firstname": "Дмитрий","middlename": "Петрович", \

            "lastname": "Сергеев","speciality": "Data science", \

            "birthday": "12/12/2005", "entrydate": "07/09/2007", \

            "rating":209, "project": "проект01"}

con.execute("INSERT INTO students1 VALUES (:id,:firstname,:middlename, :lastname,:speciality,:birthday,:entrydate,:rating,:project )", students1)

con.execute("commit")

2. Select используя Пайтон список

(.env) boris@boris-All-Series:~/SQLITE01$ cat sqlite5Query.py

import sqlite3

import sys

con = sqlite3.connect("student.db")

cursor = con.cursor()

id_list = [7, 9, 11, 14, 19]

id_tuple = tuple(id_list)

query = 'SELECT id,firstname,lastname,speciality,birthday \

       FROM students1 WHERE id IN {};'.format(id_tuple)

cursor.execute(query)

for row in cursor.fetchall():

        id,firstname, lastname, speciality,birthday = row

        print('%s %s %s %s %s' % (id,firstname,lastname,speciality,birthday))

        print('\n')

(.env) boris@boris-All-Series:~/SQLITE01$ python3 sqlite5Query.py

7 Борис Харитон компьютерная безопасность 2005-11-01

9 Иван Амбарцумян робототехника 2002-08-10

11 Андрей Хорьков программист  на  С++ 2004-07-09

14 Антон Кравцов программист на С++ 2004-07-09

19 Дмитрий Сергеев Data science 12/12/2005