sqlite3 basics in Python

Using databases is a really effective way to store data. In python, we can import sqlite3 to get access to all of the database features.

Connecting a database

To perform any actions using sqlite3, firstly we need to set up our cursor()

with sqlite3.connect("FILE_NAME.db") as db:	
        cursor = db.cursor()

This then creates a .db (database) file which can be altered using our cursor.

Creating a table

With our cursor command, we can now make our first table in the file. To do this we'll need to get familiar with the .execute() command. By using this function, it will allow us to do anything with the database, as long as it contains a valid query.

cursor.execute("""							
CREATE TABLE IF NOT EXISTS TABLE_NAME(
userID INTEGER PRIMARY KEY,
username VARCHAR(20) NOT NULL,
password VARCHAR() NOT NULL);
""")

We can split this block of code into separate lines. In the top line we call our cursor with the .execute() function to tell python that we want to do something with the database.

CREATE TABLE IF NOT EXISTS TABLE_NAME(

Here we're passing a query that checks the database file if the table TABLE_NAME can be found and if not it creates it.

userID INTEGER PRIMARY KEY,

Our first column is going to be the userID, which is the primary key. This basically keeps track of all of the data stored in the database and giving it a number. This could be quite useful for databases containing hundreds of arrays of personal information.

username VARCHAR(20) NOT NULL,
password VARCHAR() NOT NULL;

The rest of the code represents all of the other columns that we want to include in our table. We also gave it two queries to work with. The first one being VARCHAR(20). This tells the database that any data that enters here will be made out of both characters and integers. We also limit the size of the data to 20 characters and tell it that the values cannot be left empty. The next line is very similar, with the only difference being that we didn't limit the size of the password column. This means that the password array can hold as many characters as you want. 

Using the table

Once we had a table set up, we can do various things with it.

Inserting data into the table

We can insert data into the table using another command with our .execute() function. There are two ways of inserting data: either hardcode it into the query, which will always insert the same data into the table, or using variables from python.

INSERT INTO TABLE_NAME(username,password) VALUES("John Smith", "ILikeDogs123")

Here, we're inserting the values "John Smith" into the username column and the value "ILikeDogs123" into the password column. This will place those values every time the query is ran

insertData = '''INSERT INTO TABLE_NAME(username,score) VALUES(?,?)'''
cursor.execute(insertData,[(VARIABLE_1),(VARIABLE_2)])
db.commit()

In this way, we're entering data from  VARIABLE_1 into the column username and then the data from VARIABLE_2 into the password column. When we do it like this, we can enter different values every time the query is run, as yo can change the data in the variable.

Getting data from the table

Reading values from a table is very simple. There are also quite a lot of queries we can use in the .execute() function to help us look for a specific search. The most simplest method to read from a table would be just to print the whole table:

cursor.execute("SELECT * FROM TABLE_NAME")

If you ran the code above, it would just print every value that was stored in the table at the given time. We can also look for specific data using variables in Python:

find_user = ("SELECT * FROM TABLE_NAME WHERE username = ? AND password = ?")
cursor.execute(find_user,[(username),(password)])

Here, we're looking for the row that has the data from the variable username and the data from variable password in the row username and password. To do that, we used the WHERE query.  We can also limit how many of a certain thing we want from:

cursor.execute("SELECT * FROM TABLE_NAME LIMIT 1;")

This will only pick the top value in the table. If we wanted it to be any random value from the table we would use the ORDER BY query, where we would then specify how we wanted to order it:

cursor.execute("SELECT * FROM TABLE_NAME ORDER BY RANDOM() LIMIT 1;")