How to utilize the built-in database for data storage and manipulation
Databases are a great, secure, and reliable way to store data. All major relational databases have something in common — SQL — a language to manipulate databases, tables, and data. SQL is a broad topic to cover, especially when dealing with different database vendors, such as Microsoft, IBM, or Oracle, so let’s start with SQLite — the most lightweight database system.
So, what is SQLite? I’m glad you’ve asked. It’s a library that provides us with a database or relational database management system. The term ‘Lite’ means ‘Lightweight,’ which means it’s light for setup and administration in the database world.
Why should I care? Because you can get a database experience without the need for downloading software or creating a cloud database, and then figuring out how to connect with Python. It’s not the most secure option, sure, but it still beats CSVs and Excel files by a large margin, because everyone can’t alter the data.
Today’s article is structured as follows:
- Problem overview
- Creating tables
- Create, read, update, delete
- Test time
- Conclusion
So we’ll cover the basics — how to create tables, insert data, update data, delete data, fetch all data, and fetch data based on a condition. It’s everything you need to build applications.
Problem overview
Do you love movies? Yeah, me too. We’ll focus on just that today, by creating a database and a table to store movie titles and additional information. After we make the table, we’ll need to declare a couple of functions:
- To insert movies
- To get all movies
- To get a single movie
- To update a single movie
- To delete a single movie
It sounds like a lot, but it isn’t. We’ll use Python to communicate with the SQLite database, and we’ll fetch movie info from IMDB.com.
Awesome! Let’s get started!
Creating tables
We can’t store data in the database directly — we need tables. Databases store tables and tables store data. Our procedure is as follows — create a movies table only if it doesn’t exist already. That will require two SQL queries, one to check if the table with a given name exists, and the other to create a table.
First, we need to import the SQLite library, establish a database connection, and create a cursor. The following snippet does it:
import sqlite3
conn = sqlite3.connect('movies.db')
c = conn.cursor()
The movies.db database will get created if it doesn’t exist, and if it does, only a connection gets established.
Next, we’ll declare a function that checks if a table exists. The table name gets passed as a function parameter, and it returns True if the table exists, and False otherwise:
def table_exists(table_name):
c.execute('''SELECT count(name) FROM sqlite_master WHERE TYPE = 'table' AND name = '{}' '''.format(table_name))
if c.fetchone()[0] == 1:
return True
return False
The newer f-strings don’t work for some reason, so we have to dial back to the older syntax — not an issue, but keep that in mind. The next step is to use the declared function to check if a table exists, and to create it if it doesn’t. The following snippet does it:
if not table_exists('movies'):
c.execute('''
CREATE TABLE movies(
movie_id INTEGER,
name TEXT,
release_year INTEGER,
genre TEXT,
rating REAL
)
''')
Awesome! This section ends here, and we’ll get to the fun stuff next.
Create, read, update, delete
Okay, we have everything configured, and now is the time to work with data. To start, we need a function that inserts a movie to the table (the Create part). It is quite an easy one to write, as we need to execute a single INSERT statement and commit the transaction. Here’s the code:
def insert_movie(movie_id, name, release_year, genre, rating):
c.execute(''' INSERT INTO movies (movie_id, name, release_year, genre, rating) VALUES(?, ?, ?, ?, ?) ''', (movie_id, name, release_year, genre, rating))
conn.commit()
And that’s it! We’ll keep the testing part for later, and now we’ll continue with the Read part.
We’ll declare two functions here — first to get all movies and second to get only a single one, specified by the movie ID. You can easily condense the logic into a single function, but I’ve chosen to go with this approach.
Here’s the function for fetching all of the movies:
def get_movies():
c.execute('''SELECT * FROM movies''')
data = []
for row in c.fetchall():
data.append(row)
return data
And the snippet to fetch a single movie:
def get_movie(movie_id):
c.execute('''SELECT * FROM movies WHERE movie_id = {}'''.format(movie_id))
data = []
for row in c.fetchall():
data.append(row)
return data
Great! The Update part comes next. This one is trickier. We want to update elements according to a given movie ID, but what will we update? Do we declare a particular function for every field? That doesn’t sound right. We’ll perform the update with a dictionary.
Let me elaborate. Our update function will accept two parameters:
- movie ID — the ID of a movie you want to update
- update dictionary — key/value pairs to update
Keys in the update dictionary must be named according to the table columns, or the exception gets raised. Also, we need to handle the update of numeric and textual fields. It’s the trickiest function thus far, but you can handle it:
def update_movie(movie_id, update_dict):
valid_keys = ['name', 'release_year', 'genre', 'rating']
for key in update_dict.keys():
if key not in valid_keys:
raise Exception('Invalid field name!')
for key in update_dict.keys():
if type(update_dict[key]) == str:
stmt = '''UPDATE movies SET {} = '{}' WHERE movie_id = {}'''.format(key, update_dict[key], movie_id)
else:
stmt = '''UPDATE movies SET {} = '{}' WHERE movie_id = {}'''.format(key, update_dict[key], movie_id)
c.execute(stmt)
conn.commit()
See? It wasn’t that hard. Let’s finish with the Delete part. It is much simpler than the previous one, as we only need to execute a single statement and commit the transaction. Here’s the code:
def delete_movie(movie_id):
c.execute('''DELETE FROM movies WHERE movie_id = {}'''.format(movie_id))
conn.commit()
And that concludes this part. We’ll test our functions next.
Test time
To start, let’s insert a couple of movies:
insert_movie(1, 'Titanic', 1997, 'Drama', 7.8)
insert_movie(2, 'The Day After Tomorrow', 2004, 'Action', 6.4)
insert_movie(3, '2012', 2009, 'Action', 5.8)
insert_movie(4, 'Men in Black', 1997, 'Action', 7.3)
insert_movie(5, 'World War Z', 2013, 'Romance', 10)
Executing these five lines doesn’t output anything to the console, which is expected — we’re only inserting data to the table. Next, we’ll use our predefined function to grab all movies:
print(get_movies())
Image by author
Awesome! Let’s now get only a single movie:
print(get_movie(2))
Image by author
That was pretty easy. Now we’ll see how to update a movie. The last one inserted, World War Z, was intentionally given a rating of 10 and a Romance genre, so let’s change that:
update_movie(5, {'genre': 'Horror', 'rating': 7.0})
Image by author
Now things make sense. The only thing left to do is to delete a movie — let’s see how to do that:
delete_movie(3)
That doesn’t return anything — as expected. We can quickly get all of the movies to see if the delete function works:
Image by author
And that does it — everything works as advertised. Let’s wrap things up in the next section.
Before you go
I hope it was easy to follow along. We covered the basics and left a lot of things untouched. It’s still enough to build basic, database-driven applications or APIs, but more on APIs some other time.
Feel free to expand this, add new functions or tables, or to improve overall code quality.
Thanks for reading.
