# SQLite

![](/files/-MhMrp1wy9jItBfE__WA)

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

##

## CRUD

```python
# 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

```python
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

```python
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)
```


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://yo-sarawut.gitbook.io/snippet/database/sqlite.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.