---
tags: 109-1, lecture
---

# Database

CRUD

## SQL

[SQL tutorial](https://www.w3schools.com/sql/default.asp)

[Python sqlite](https://docs.python.org/3/library/sqlite3.html)

### prerequisite

$ pip install pandas tabulate

`print_db.py`

import sqlite3
import pandas as pd
from tabulate import tabulate

DB_NAME = "example.db"

db = sqlite3.connect(DB_NAME)
cursor = db.cursor()
cursor.execute("SELECT name FROM sqlite_master WHERE type='table';")
tables = cursor.fetchall()
for table_name in tables:
    table_name = table_name[0]
    if table_name.startswith("sqlite"):
        continue
    table = pd.read_sql_query("SELECT * from %s" % table_name, db)
    header = table.columns.to_list()
    print()
    print(f"Table: {table_name}")
    print()
    print(tabulate(table, header, tablefmt="grid"))
    print()
    print("=" * 40)
cursor.close()
db.close()

`reset_db.py`

import os
import sqlite3

DB_NAME = "example.db"

if os.path.exists(DB_NAME):
    os.remove(DB_NAME)

conn = sqlite3.connect(DB_NAME)

c = conn.cursor()

c.execute('''CREATE TABLE accounts
    (id integer primary key autoincrement, username text, password text)
''')

c.execute('''CREATE TABLE messages
    (id integer primary key autoincrement,
     user_from text,
     user_to text,
     message text)
''')

conn.commit()
conn.close()

`insert.py`

import sqlite3

DB_NAME = "example.db"

conn = sqlite3.connect(DB_NAME)
c = conn.cursor()

# insert single row
c.execute("INSERT INTO accounts VALUES (?,?,?)",
          (None, "admin", "admin_password"))

# insert multiple rows
messages = [
    (None, "Alice", "Bob", "Hello"),
    (None, "Admin", "Bob", "You are banned!"),
    (None, "Alice", "Bob", "Ah!"),
]
c.executemany("INSERT INTO messages VALUES (?,?,?,?)", messages)

conn.commit()
conn.close()

`execute.py`

import sqlite3

DB_NAME = "example.db"

conn = sqlite3.connect(DB_NAME)
c = conn.cursor()

c.execute("SELECT * FROM messages")
print(c.fetchall())

conn.commit()
conn.close()

#### SQL injection

' OR 1=1 --

## Mongo

[Official Documentation](https://docs.mongodb.com/manual/)

[Mongoose](https://mongoosejs.com/)

$ docker pull mongo
$ docker run --name mongo -d mongo --serviceExecutor adaptive
$ docker exec -it mongo bash

[SQL analogy](https://docs.mongodb.com/manual/reference/sql-comparison/)

https://docs.mongodb.com/manual/tutorial/getting-started/