Connection to Databases

Soo did I mention that I have a database exam coming soon that I have to study for but have no motivation to study for? Welp, I guess I just found a way to “Study” for it. Let’s kill 2 birds with 1 stone by improving this website and practising databases at the same time!

With my jokes database being a .txt file and my app page information stored in a JSON it will be perfect if I manage to insert them all into a database and load the database from there instead of storing them in ram all the time.

This can improve my database skills and reduce the ram usage on the server. Win-win situation ezpz.

Welp, now it’s time to actually do it. What database should I use? After taking the course on Databases (And learning about Postgres) in school, I have decided against using Postgres as I will need to set up another server and connect to it using python. (I’m a lazy man so that is definitely not my thing).

Soooo I decided to use sqlite3. It is very lightweight and it reads a .db file directly instead of having to set up another server that python has to connect to. Here is a snippet of the code on how to use it.

import sqlite3

# Open the DB
with sqlite3.connect('<path to file>') as db:

    # Initialize the cursor
    cur = db.cursor()

    # Run operations here
    .....

By using the above code as a reference, I made a base database class which have this function implemented so that I will not have to define it in the future. By inheriting from the base class, I can access the functions inside without having to reimplement it again (Thank you Object-oriented Programming and Software Engineering)

Hmm, one big problem that I have missed out on. How do I migrate the data from my old files to the database?? Welp, I guess it is time for more python code to be used

query = "INSERT INTO Table Values (?)" # Use prepared statements to prevent SQL Injection

with open('data') as file:
    for joke in file.readlines():

        # Iterate through the jokes and add them
        cur.execute(query, joke.strip())

One thing to take note when inserting anything into a database is SQL Injection.

NOTE #

Do not use string concatenation just by itself as it allows the attacker to inject other SQL Commands and run whatever they want. This can result in undesired outcomes like losing your data or the attacker inserting different databases of their own.

I basically did the same thing for the app information and it is time for me to test if the database is working correctly.

Oh no, the jokes are turning up as tuples when they are fetched from the database….. What should I do???

List of different plans #

1. Redo my base class and implement my fetching methods from the database differently.
2. Mutate my Database for fetching jokes to unwrap the tuple

So initially, I thought that the first one was a better idea….. However, after implementing it, my application database that is inheriting from the base class is unable to function as expected anymore cries.

I had to revert the changes back and do the 2nd method instead. I overrode the same method of the base class (OOP masters will be proud) to un-nest the tuple and to release the jokes correctly.

Soo at the end of the day was that enough practice for my Database course? Not really….. Most of it was just programming and it is not really much on SQL other than SELECT * FROM table and INSERT INTO TABLE xxxx. Not exactly what the database course is testing… Welp, hopefully I can start revision and study for the module soon. Till next time!