SQLite

- Document : https://www.sqlite.org/docs.html - https://www.sqlitetutorial.net/

CRUD

# CRUD (Create/Insert, Read/Select, Update, Delete)
import random
import sqlite3
def create_db():
    with sqlite3.connect("basic.sqlite") as con:
        sql_cmd = """
            create table person(
              id integer PRIMARY KEY AUTOINCREMENT,
              gender text,
              weight real,
              height real
            )
        """
        con.execute(sql_cmd)

def insert_demo():
    with sqlite3.connect("basic.sqlite") as con:
        sql_cmd = """
            insert into person(gender, weight, height) values('F', 45, 160);
        """
        con.execute(sql_cmd)
def insert_demo2(params):
    with sqlite3.connect("basic.sqlite") as con:
        sql_cmd = """
            insert into person(gender, weight, height) values(?, ?, ?);
        """
        con.execute(sql_cmd, params)
def update_demo():
    with sqlite3.connect("basic.sqlite") as con:
        sql_cmd = """
            update person set weight = weight * 2.2
        """
        con.execute(sql_cmd)
def delete_demo():
    with sqlite3.connect("basic.sqlite") as con:
        sql_cmd = """
            delete from person where gender = 'M'
        """
        con.execute(sql_cmd)
def select_demo():
    with sqlite3.connect("basic.sqlite") as con:
        sql_cmd = """
            select * from person
        """
        for row in con.execute(sql_cmd):
            print(row)


if __name__ == '__main__':
    # create_db()
    # insert_demo()
    # insert_demo2(('M', 70, 170))
    # for _ in range(10):
    #     g = random.choice('MF')
    #     w = random.randrange(35, 80)
    #     h = random.randrange(135, 180)
    #     insert_demo2((g, w, h))
    # update_demo()
    delete_demo()
    select_demo()

Create & Insert

import sqlite3

def create_table_demo():
    try:
        with sqlite3.connect("sample.sqlite") as con:
            sql_cmd = """
            create table medal(
                country text primary key,
                gold integer,
                silver integer,
                bronze integer);
            """
            con.execute(sql_cmd)
    except Exception as e:
        print("Error -> {}".format(e))

def insert_demo():
    try:
        with sqlite3.connect("sample.sqlite") as con:
            sql_cmd = """
            insert into medal values('usa',46,37,38)
            """
            con.execute(sql_cmd)
    except Exception as e:
        print("Error -> {}".format(e))

def insert_demo2():
    try:
        with sqlite3.connect("sample.sqlite") as con:
            sql_cmd = """
            begin;
            insert into medal values('gbr',27,23,17);
            insert into medal values('chn',26,18,26);
            insert into medal values('rus',19,17,19);
            COMMIT;
            """
            con.executescript(sql_cmd)
    except Exception as e:
        print("Error -> {}".format(e))
def select_demo():
    try:
        with sqlite3.connect("sample.sqlite") as con:
            sql_cmd = """
            select * from medal
            """
            for row in con.execute(sql_cmd):
                print(row)
    except Exception as e:
        print("Error -> {}".format(e))

if __name__ == '__main__':
    # create_table_demo()
    # insert_demo()
    insert_demo2()
    select_demo()

Select

import sqlite3

def demo_select1():
    try:
        with sqlite3.connect("db.sqlite") as con:
            con.row_factory = sqlite3.Row
            sql_cmd = """
            select * from person where gender = 'M'
            """
            cursor = con.execute(sql_cmd)
            for row in cursor:
                print("{} {} {} {}".format(row["obs"], row["gender"], row["height"], row["weight"]))
    except Exception as e:
        print('Error -> {}'.format(e))

def demo_select2(sex:str, h):
    try:
        with sqlite3.connect("db.sqlite") as con:
            con.row_factory = sqlite3.Row
            sql_cmd = """
            select * from person 
              where gender = ? and height > ?
            """
            # cursor = con.execute(sql_cmd, [sex, h])
            # cursor = con.execute(sql_cmd, (sex, h))
            # cursor = con.execute(sql_cmd, ('M', 180))
            # for row in cursor:
            for row in con.execute(sql_cmd, ('M', 180)):
                print("{} {} {} {}".format(row["obs"], row["gender"], row["height"], row["weight"]))
    except Exception as e:
        print('Error -> {}'.format(e))

if __name__ == '__main__':
    # demo_select1()
    demo_select2('F', 180)