Source Code: How To Use SQLite Database In Python For Beginners

Buy Me a Coffee? Your support is much appreciated!

demo.py

import sqlite3
import pandas as pd # pip install pandas

# Create a database
connection = sqlite3.connect('student.db')
# connection = sqlite3.connect(':memory:')

# Create a table
sql_create_table = """
CREATE TABLE students (
    student_id TEXT PRIMARY KEY,
    student_name TEXT NOT NULL,
    gender TEXT,
    major TEXT,
    year_enrolelr INTEGER,
    email TEXT UNIQUE
)
"""
cursor = connection.cursor()
try:
    cursor.execute(sql_create_table)
    connection.commit()
except Exception as e:
    print(e)
    connection.rollback()


# Insert records
cursor = connection.cursor()
cursor.execute("INSERT INTO students VALUES ('A10011','student_name1','M','Computer Science',2022,'email11@gmail.com')")
cursor.execute("INSERT INTO students VALUES ('A10013','student_name3','M','English',2018,'email13@gmail.com')")
cursor.execute("INSERT INTO students VALUES ('A10012','student_name2','F','Biology',2021,'email12@gmail.com')")
cursor.execute("INSERT INTO students VALUES ('A10014','student_name4','M','Accounting',2018,'email14@gmail.com')")
cursor.execute("INSERT INTO students VALUES ('A10015','student_name5','F','Marketing',2019,'email15@gmail.com')")
cursor.execute("INSERT INTO students VALUES ('A10016','student_name6','F','Sociology',2022,'email16@gmail.com')")
connection.commit()

# Query records
sql_query = """
SELECT * FROM students LIMIT 5
"""
rows = cursor.execute(sql_query)
columns = [col[0] for col in rows.description]
records = rows.fetchall()
connection.close()
print(columns)
print(records)

df = pd.DataFrame(records, columns=columns)
print(df.to_csv('data.csv'))

# Using WITH statement to auto close connections
from contextlib import closing
import sqlite3
import pandas as pd # pip install pandas

with closing(sqlite3.connect('demo.db')) as connection2:
    with closing(connection.cusor()) as cursor2:
        cursor2.execute("INSERT INTO students VALUES ('A10001','student_name1','M','Computer Science',2022,'email1@gmail.com')")
        cursor2.execute("INSERT INTO students VALUES ('A10003','student_name3','M','English',2018,'email3@gmail.com')")
        cursor2.execute("INSERT INTO students VALUES ('A10002','student_name2','F','Biology',2021,'email2@gmail.com')")
        cursor2.execute("INSERT INTO students VALUES ('A10004','student_name4','M','Accounting',2018,'email4@gmail.com')")
        cursor2.execute("INSERT INTO students VALUES ('A10005','student_name5','F','Marketing',2019,'email5@gmail.com')")
        cursor2.execute("INSERT INTO students VALUES ('A10006','student_name6','F','Sociology',2022,'email6@gmail.com')")
        connection.commit()

        rows2 = cursor2.execute()
        columns2 = [col[0] for col in rows2.description]
        records2 = rows.fetchall()
        df = pd.DataFrame(records, columns=columns)
print(df)