Learn SQLite with Python in 3 minutes!!

SQLite is a database that is written in C. It is simple and easy to use for projects. I just started using it with Python a couple of weeks ago for some sort of small projects. Today, I'm going to teach you using SQLite with Python while creating a database for the students containing their basic information. It's just a pretty quick one, so let's get started!!

Setting up

Before we start, let's just import the Python built-in SQLite module by typing import sqlite3 at the beginning of our index.py so now we can use SQLite.

# /* index.py

import sqlite3

Creating and/or Connecting to DataBase

After importing the module we need to use, it's time to connect our code to the database. To make this happen, simply type connect = sqlite.connect(dbname.db):

connect = sqlite.connect('student.db')

Once we save and run our index.py, it will automatically create and connect to the database, with the name we give in it, and has a file extension of .db. Next, we'll create a cursor that is able to fetch or upload something to the database. Add csr = connect.cursor() in our code so our index.py now should looks like this:

Create a Table

An SQLite table is where you save your data inside your DB. We're getting their basic information such as first name, last name, and age. In creating a table, follow the syntax:

# /* index.py

cursr.execute("CREATE TABLE tablename (dataname datatype)")

connect.commit() # committing changes
connect.close() # closing connections

As you notice we have some capitalized words, these are the keywords we use to create the table. In adding their data attribute, we also configure their datatype. In SQLite, we have 5 data types which are:

5 SQLite Datatypes

1. null => None
2. integer => int
3. real => float
4. text => str
5. blob => files (image, files, etc.)

Now, our whole code should look like this:

Notice that we create a table through our cursor, we use it also in adding, fetching, etc. And don't forget to .commit() on every end of your configuration and .close() on the end of your code.

Adding Data on Table

In adding data, you need to identify the name of the table where you want to save these objects you've uploaded. For a better understanding, you can look at this syntax:

# /* index.py

cursr.execute("INSERT INTO tablename VALUES (data)")

connect.commit() # committing changes
connect.close() # closing connections

After putting all the required fields, you can save and run the file, in that way the data will be saved into the students table in student.db.

Selecting and Fetching Data

You have 2 options in selecting the data. Select data manually or Search specific data:

1. Select data mannualy - also has 2 types:

A. Get everything of everyone - fetch all student data for every student
Syntax: cursr.execute("SELECT * FROM tablename")

B. Get selected of everyone - fetch fname only of the students
Syntax: cursr.execute("SELECT dataname FROM tablename")

1. Search specific data - use the WHERE keyword in searching. You can search object attributes or rowid to search objects. To know the rowid of an object, simply run rowIds = cursor.execute("SELECT rowid,* FROM tablename").fetchall() then print rowIds and there you can see every object's format in tuple form like (rowid, data).

Once you get the data, you can finally fetch them so you can access them, print them, or anything.

# /* index.py

print(cursr.fetchone()) # fetch 1st object only
print(cursr.fetchmany(2)) # fetch number of objects depend on range you entered
print(cursr.fetchall()) # fetch all

Update Data

Syntax: cursr.execute("UPDATE tablename SET dataname = data WHERE dataname = data")
Let's use the data and table we added earlier as an example, let's say we're updating the age of someone who has the fname of Michael to 19 in the table of students. To do that, we're using the WHERE keyword that I introduced earlier.

# /* index.py
cursr.execute("UPDATE students SET age = 19 WHERE fname = 'Michael'")

cnt.commit()
cnt.close()

Our code now looks like this:

Delete Data

The DELETE method in SQLite uses WHERE keyword to identify what object it should erase.

Delete Table

Now if you want to delete a whole table, here it is:

These are just some of the many methods and lessons of SQLite you can use. It's so fun and easy to use with Python. If you want to know more about SQLite with Python, you can check SQLite Documentation. That's the best place to know more about this tech. Thanks for reading and have a nice day!!

My Accounts
Twitter: Codeit_Michael
Github: Codeit-Michael
LinkedIn: Michael Maranan