Learn to Code via Tutorials on Repl.it!

← Back to all posts
An Introduction to SQLITE (and Python sqlite3 library)
h
CRdev

Welcome to this Tutorial !

To start up with, I want to apologize for my bad english (not my native language). Now that this is said, let's start the tutorial with the summary of how he is structured :

  • Part 1 : DON'T USE ON REPLIT
  • Part 2 : Create your first table
  • Part 3 : SELECT, UPDATE, DELETE, INSERT
  • Part 4 : Multiple Tables SELECT
  • Part Bonus : Playground !

Setting up repl.it [DON'T USE ON REPLIT]

  • The first step is to create a new python repl.
    Once this is done, go to the Shell panel (at the right of the Console one) and write the following command : touch database.db (call it whatever you want but keep the db extension as it stands for database.
    Once the file is creating, he should appear on the right of your Files Browser.

  • Now that this is done, you'll have to copy and paste a code that I've written for you (only if you're a begginer, otherwise just use the sqlite3 library, this tutorial is for absolute begginner in sql). Here is the code (paste the content of crtoolkit.py in another file and then do from yourfilename import DatabaseHandler or just paste it in your main file, as you want.

Creating your first table

The first step will be to connect your database. For this use :

Now that you're connected to the database, you can achieve sql request using db.executeQueries("queries").

  • But what is a query ?
    A query is some instruction that you can pass to the database for it to do them. There is a bunch of queries that exists, for example SELECT will select some elements of tables, INSERT will add elements (known as "row") to the table's rows and there is a lot more like DELETE which delete some elements of tables. We'll start easily with the CREATE query.

  • How to use the CREATE query ?
    You can write it like that :

This instruction will create a table called tableName only if it didn't already exists. The columnName can be whatever you need and the type can be either NULL, INTEGER, REAL, TEXT, BLOB and some more depending of the sql versions. All types are pretty logical except BLOB which, in some words, store exactly what is passed without and modification.
An important point is what we call primary key, these rows can only have one time each values, that's what we call UNIQUE in sql and are in general autoincrement which means that we don't have to give the column a value when we INSERT something (you see, the KEYWORDS are pretty intuitive, rights ? ;) )

  • Create our first table (finally)
    Let's create a simple table, for an arcade game. That'll store the username and the score of it.
    If we thinks about it for 10 seconds, we want the username to be a TEXT, the score to be an INTEGER (except if you have a floating score) and as we don't want users to have the same username (so that we can easilly edit the highest score of someone if he beat it.) Let's sum up this in an sql CREATE request :

We add an id by default because we'll maybe need it one day (obviously in this example it's not really useful but it's a good habits to take to add it). I've add another keyword for you : DEFAULT, using this, we can add user without specifying a score and it'll be to 0 by default. Now we can do this request in python using the code I've share with you :

Don't forgot the db.commitChanges() !!!

SELECT, UPDATE, DELETE, INSERT

SELECT keyword

Imagine : Kevin score 5000 at your game. You want to know what was his previous highest score and check if 5000 is greater than it or not (to update it), you'll need to use the SELECT keyword. Syntax :

For example here we want to select the highestScore of players where the username is equal to Kevin. For this we'll use this request :

Which give in Python :

The Result should look like somethings like this : [[0,]] with 0 corresponding to the highest score of Kevin. Why 2 lists ? In fact you can search for multiple columns (you can even use SELECT * FROM ... to get every columns of a table) and you can get multiple results, here we get only one as username is set to UNIQUE so can't have double iterations or more. That's why you get two lists : A list of response and each response is a list of column that you've asked for. Use resultOfQuery[0][0] if you want to get Kevin's score.
We'll comeback to SELECT in the last section of this tutorial to go deeper in it, that'll be funny you'll se :D !

UPDATE keyword

Imagine : Using SELECT you checked the highest Score of Kevin and you've seen that his highest Score is only 4000 whereas he score 5000 ! You want to update Kevin's high score in the database and that's done using .... UPDATE ! Syntax :

So if we want to set the highest score of Kevin to 5000 that's what we need to do :
We need to UPDATE the players table to SET the highestScore to 5000 only WHERE the column username is equal to Kevin.
Try to do it alone and then read the correction.
We'll do it in python directly, the sql only version is just what is in the brackets ( FYI it's calld docstrings when they're 3 brackets :D) if you want it. Correction :

And that's it ! Another time : don't forget db.commitChanges() otherwise your changes will not be saved.

DELETE keyword

Imagine : Kevin refunds your game ( not good Kevin), there is no point about keeping Kevin to your database, so you want to DELETE him, erase it forever :

"You delete my game from your hard drive, I delete you from my database." - Aristote.

The syntax of the DELETE statement (or query) is like that :

So here we want to delete the row who have for username Kevin from the players table.
Another time, try to do it alone and then check the correction :

INSERT keyword

Imagine : Kevin can't leave without your game and re bought it ( at 200% of the original price as a revenge ), now you need to INSERT Kevin to your database as he is not it in right now. For this you'll use the INSERT keyword which have the following syntax :

In this example, we want to INSERT into players a row that have Kevin as a username and that's all, because highestScore already have 0 set as a DEFAULT value and id is a primary key and by so, autoincrementing.
Try to do it alone (super hard ....)

Multiple tables SELECT

This section is for the fun only and most of you won't have to use these multilines SELECT queries but nevermind, it's still good to know !

In a SELECT keyword, you can pass multiple table and the good method is with foreign key.
Imagine an airport which has 2 tables : flights and pilots with theses columns :

and

Using the Flights piloteId column, we can get a specific pilot from the Pilots table and that looks like this :

This request will print the name of each pilots that have the flights programmed, now we can do even more, for example printing the name of pilots who are in a flight that is flying only :

And that's it ! Obviously you can do more complexe SELECT (even with 2,3,4,5,100 columns) but remember : in computer sciences, simpler is better.

BONUS : Playground

Using the repl that is linked to this tutorial, you can test your request for free on a database, it'll tell you if you do an error so that you can train yourself before going in the wild of programming in SQL !

Thanks you for your time ! Don't hesitate to upvote if you find it useful ! ❤️

Voters
365DaysOfCode
RtoonTV
CRdev
Comments
hotnewtop
xfinnbar

Don't use SQLite on a repl, you'll experience data loss if the editor isn't open. Replit does not save files modified programmatically while the editor is not open.

Instead, you should use the built-in replit database, or my replit compatible database solution, ScarletDB.

CRdev

@xfinnbar Even on always-on repl ?
Thanks, I'll remove the replit part so

xfinnbar

@CRdev I think so, but I'm not sure.