SQLite DB in python

 SQLite is a self-contained, serverless, and lightweight relational database engine. Unlike traditional client-server databases, it operates directly as a C-library embedded within an application, storing the entire database in a single, standard cross-platform file

# SQLite Viewer vs code extention to graphical view of sqlite db

Quick Python Example

import sqlite3

# Connect to database (creates file if it doesn't exist)

conn = sqlite3.connect('my_database.db')

cursor = conn.cursor()

# Create a table

cursor.execute('''CREATE TABLE IF NOT EXISTS users (id INTEGER PRIMARY KEY, name TEXT)''')

# Insert data

cursor.execute("INSERT INTO users (name) VALUES ('Bob')")

conn.commit()

# Query

cursor.execute("SELECT * FROM users")

print(cursor.fetchall())

# Close connection

conn.close()

//////////////////create a database and table

import sqlite3

DB_NAME = "studentdb.db"

def get_connection():

    return sqlite3.connect(DB_NAME)

def create_tables():

    conn = get_connection()

   

    cursor = conn.cursor()

    cursor.execute("""

    CREATE TABLE IF NOT EXISTS departments(

        id INTEGER PRIMARY KEY AUTOINCREMENT,

        name TEXT NOT NULL

    )

    """)

    cursor.execute("""

    CREATE TABLE IF NOT EXISTS subjects(

        id INTEGER PRIMARY KEY AUTOINCREMENT,

        name TEXT NOT NULL,

        department_id INTEGER

    )

    """)

    cursor.execute("""

    CREATE TABLE IF NOT EXISTS students(

        id INTEGER PRIMARY KEY AUTOINCREMENT,

        name TEXT NOT NULL,

        age INTEGER,

        department_id INTEGER

    )

    """)

    conn.commit()

    conn.close()

create_tables()