Skip to content

SQLite Guide @ Coding:

Wanting to get your hands dirty with databases? Well starting out with SQLite is a good idea.

Note

The way we are teach SQLite is different. This is because we want you to avoid fearing about SQL Injection(s). We do belive that teaching this method saves new coders time.

1. Create a DB file.

  • Create a database.db file.

2. Connecting:

To get started you must connect to your database and setup a "cursor".

  1. In your Python file import the sqlite module. (a built-in module after Python 2.5 and is actually called sqlite3)
  2. Using the the sqlite module do: database = sqlite3.connect("database.db")
  3. The add on another line: curosr = database.cursor()

Code Example:

import sqlite3 

database = sqlite3.connect("database.db")
cursor = database.cursor()

3. Creating a Table:

Now that you have a cursor, you are able to execute SQL Commands. The first instruction should be to create a table if there is no tables. 

cursor.execute("CREATE TABLE IF NOT EXISTS register(name STRING, attendance BOOLEAN)")
  • Boolean is either True or False

4. Modifying, Adding and Removing:

Adding:

  • To add a row of data to a database add INSERT INTO then the table name then in brackets the value names (name, attendance) then add VALUES (?, ?) in the speech marks.
  • Then add a comma and add in brackets the data you want to add in the correct order 
name = "John"
attendance = False 
cursor.execute("INSERT INTO register (name, attendance) VALUES (?, ?)", (name, attendance))

Deleting:

  • To delete data from the table do DELETE FROM then the database name. But just doing this will delete all the rows of data. To stop this we need to add a WHERE statement. 

name = "Laura"
cursor.execute("DELETE FROM register where name = ?", (name))
In the example above we delete the row(s) of dat where the name is "Laura".

Warning

If "Laura" was added in all lower cases then the database would not delete that row. It is case senstive. We reccomend you make all string variable lower case. Simply add: 

name = name.lower()

Modifying

  • To modify a the table do: UPDATE register SET attendance = ? WHERE name = ?.
    • The WHERE statement prevents every single row of data being updated.