How To Add An Retrive Data: Sqlite3

A simple script to retrive data from sqlite with python.

import sqlite3

conn = sqlite3.connect('test.s3db')
cur = conn.cursor()
cur.execute('''DROP TABLE IF EXISTS people''')
cur.execute('''CREATE TABLE IF NOT EXISTS people
                (id INTEGER,
                 name TEXT,
                 surname TEXT,
                 age INTEGER,
                 alone INTEGER DEFAULT 0);''')

def add_people(id, name, surname, age, alone=0):
    global cur, conn
    cur.executemany('INSERT INTO people (id, name, surname, age, alone) VALUES (?,?,?,?,?)', [(id, name, surname, age, alone)])
    conn.commit()

add_people(2,'JOHNSON','Mike',75)
add_people(1, 'SMITH','James',45)

def select_age_of(names):
    for name in names:
        cur.execute('''SELECT age FROM people WHERE surname = (?)''', (name, ))
        print(name, cur.fetchall()[0])

select_age_of(("Mike", "James"))
conn.close()

Making the code more usable

In the next version you can also choose which data of the user you need to know.

import sqlite3


def add_people(id, name, surname, age, alone=0):
    cur.executemany('INSERT INTO people (id, name, surname, age, alone) VALUES (?,?,?,?,?)', [(id, name, surname, age, alone)])
    conn.commit()


def select_data_of(names, data="age"):
    select = []
    for name in names:
        cur.execute(f'''SELECT [{data}] FROM people WHERE surname = (?)''', (name, ))
        select.append(cur.fetchall()[0])
    return select


with sqlite3.connect('test.s3db') as conn:
    cur = conn.cursor()
    cur.execute('''DROP TABLE IF EXISTS people''')
    cur.execute('''CREATE TABLE IF NOT EXISTS people
                    (id INTEGER,
                     name TEXT,
                     surname TEXT,
                     age INTEGER,
                     alone INTEGER DEFAULT 0);''')
    add_people(2,'JOHNSON','Mike',75)
    add_people(1, 'SMITH','James',45)
    data = select_data_of(("Mike", "James"), data="age")
    print(data)

# output:
# [(75,), (45,)]

In another post we will see how to implement a GUI that will make us do the query.