An Introduction to SQLITE (and Python sqlite3 library)
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 !
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
dbextension 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
sqlite3library, this tutorial is for absolute begginner in sql). Here is the code (paste the content of
crtoolkit.pyin another file and then do
from yourfilename import DatabaseHandleror just paste it in your main file, as you want.
The first step will be to connect your database. For this use :
db = DatabaseHandler("youfilename.db") # Creating a new DbHandler instance db.connectToDb() # Will print an error if you messed up the spelling of the file name.
Now that you're connected to the database, you can achieve sql request using
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
SELECTwill select some elements of tables,
INSERTwill add elements (known as "row") to the table's rows and there is a lot more like
DELETEwhich delete some elements of tables. We'll start easily with the
How to use the
You can write it like that :
CREATE TABLE IF NOT EXISTS tableName ( column1Name column1type, column2Name column2Type, ... )
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
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 TABLE IF NOT EXISTS players ( id integer PRIMARY KEY, username text UNIQUE, highestScore INTEGER DEFAULT 0 )
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 :
db = DatabaseHandler("db.db") db.connectToDb() db.executeQueries( """ CREATE TABLE IF NOT EXISTS players ( id integer PRIMARY KEY, username text UNIQUE, highestScore INTEGER DEFAULT 0 ); """ ) db.commitChanges() # SUPER ULTRA MEGA HYPRA OVER IMPORTANT
Don't forgot the
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 :
SELECT colNames FROM tableName WHERE conditions
For example here we want to select the
players where the
username is equal to
Kevin. For this we'll use this request :
SELECT highestScore FROM players WHERE username = "Kevin"
Which give in Python :
db = DatabaseHandler("db.db") db.connectToDb() resultOfQuery = db.executeQueries( """ SELECT highestScore FROM players WHERE username = "Kevin" """ ) print(resultOfQuery)
The Result should look like somethings like this :
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 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 !
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 :
UPDATE tableName SET col1Name = col1Value, col2Name = col2Value, ... WHERE conditions
So if we want to set the highest score of Kevin to 5000 that's what we need to do :
We need to
players table to
WHERE the column
username is equal to
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 :
db = DatabaseHandler("db.db") db.connectToDb() db.executeQueries( """ UPDATE players SET highestScore = 5000 WHERE username = "Kevin" ); """ ) db.commitChanges() # SUPER ULTRA MEGA HYPRA OVER IMPORTANT
And that's it ! Another time : don't forget
db.commitChanges() otherwise your changes will not be saved.
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 :
DELETE FROM tableName WHERE conditions
So here we want to delete the row who have for username
Kevin from the
Another time, try to do it alone and then check the correction :
db = DatabaseHandler("db.db") db.connectToDb() db.executeQueries( """ DELETE FROM players WHERE username = "Kevin" ); """ ) db.commitChanges() # SUPER ULTRA MEGA HYPRA OVER IMPORTANT
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 :
INSERT INTO tableName (column1Name, column2Name, ...) VALUES (column1Value, column2Value, ...)
In this example, we want to
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 ....)
db = DatabaseHandler("db.db") db.connectToDb() db.executeQueries( """ INSERT INTO players (username) VALUES (0) ); """ ) db.commitChanges() # SUPER ULTRA MEGA HYPRA OVER IMPORTANT
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 !
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 :
Table Flights id INTEGER PRIMARY KEY name TEXT piloteId INTEGER FOREIGN KEY REFERENCES Pilots(id) flying TEXT DEFAULT "not" ......
Table Pilots id INTEGER PRIMARY KEY name TEXT ......
Flights piloteId column, we can get a specific pilot from the
Pilots table and that looks like this :
SELECT Pilots.name FROM Flights INNER JOIN Pilots ON Pilots.id = Flights.id
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 :
SELECT Pilots.name FROM Flights INNER JOIN Pilots ON Pilots.id = Flights.id AND Flights.flying = "is"
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.
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 !
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.