Learn SQLite with Python in 3 minutes!!
Tags: database, python, programming, beginners
Published: 2022-04-10 15:57:02

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.
```python
# /* 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)`:
```python
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:

![set-up](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/rutn8tnf2weg3dkmw5rg.png)


***
### 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:
```python
# /* 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:

![create-table](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/870v69ur2n1dw3o4iutu.png)
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:
```python
# /* 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`.

![add-data](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/vruolh480lw7dmvkyp3r.png)


***
### 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")`


![getting](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/1pgcl2rt76wp95dwbvwx.png)

2. 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)`.

![fetching](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ufx2lgdu5goue8pfw6wt.png)

Once you get the data, you can finally fetch them so you can access them, print them, or anything.
```python
# /* 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.
```python
# /* index.py
cursr.execute("UPDATE students SET age = 19 WHERE fname = 'Michael'")

cnt.commit()
cnt.close()
```

Our code now looks like this:

![update-data](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/4x7h6nlc3nepr06sc39k.png)


***
### Delete Data
The `DELETE` method in SQLite uses `WHERE` keyword to identify what object it should erase.

![delete-data](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/b6gg0sj0nmd9txwr4nw0.png)


***
### Delete Table
Now if you want to delete a whole table, here it is:

![delete-table](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ae85bweehs8273jg9wn0.png)


***
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](https://docs.python.org/3/library/sqlite3.html). That's the best place to know more about this tech. Thanks for reading and have a nice day!!

My Accounts
Twitter: [Codeit_Michael](https://twitter.com/Codeit_Michael)
Github: [Codeit-Michael](https://github.com/Codeit-Michael)
LinkedIn: [Michael Maranan](https://www.linkedin.com/in/michael-maranan-08a12b231/)