SQLite CRUD using Python

sqlite-crud-python.md

SQLite CRUD using Python

Basic Structure

import sqlite3

# Open SQLIte database named mydb.db
conn = sqlite3.connect("mydb.db") 

# Create a cursor for executing queries
curr = conn.cursor()

# Code Start

# Code End

conn.close()

Select Query

# run queries
res = curr.execute("SELECT * FROM users")

# Fetch single row
data = res.fetchone()

# Fetch 5 rows
data = res.fetchmany(5)

# Fetch all rows
data = res.fetchall()

# loop through all rows without fetch
for row in res:
  print(row)  # row will have one row in each iteration

Note: For any Queries which will result in change in database in anyway will not be saved unless committed.

Insert Query

res = curr.execute("INSERT INTO users (username,password) VALUES ('admin','admin')")

# Commit the results
conn.commit()

Update Query

res = curr.execute("UPDATE users SET (password = 'password') WHERE (username='admin')")

# Commit the results
conn.commit()

Delete Query

res = curr.execute("DELETE FROM users WHERE username='admin'")

# Commit the results
conn.commit()