Intro to SQLite3 With Python's Flask

 SQLITE3

Enter SQLite3. This is essentially an excel spreadsheet for your website. It allows you to store tables, columns, and rows of data that is saved to disk. My previous dictionary would get deleted in my server restarted. SQLite3 will allow my to save my database even if my server restarts. This was, I can save my URL’s and keys indefinitely.

To setup SQLite3 for my Bitly-clone website, I need to do three things:

1. A schema.sql file that defines the structure of my database.
2. A models.py file that defines my functions to inset, query, and update entries in my database.
3. Updates to my main python code so that storage in the my_links dictionary is replaced with storage in the SQLite3 database.

Let’s go through each…

A SCHEMA.SQL FILE THAT DEFINES THE STRUCTURE OF MY DATABASE.

The first step is to define the structure of my database using a schema.sql file that I create in the root directory of my flask app. The contents on the schema.sql file looks like this:

<span class="s1">drop table if exists link;</span>

<span class="s1">    create table link (</span>

<span class="s1">        id integer primary key autoincrement,</span>

<span class="s1">        key text not null,</span>

<span class="s1">        url text not null,</span>

<span class="s1">        hits integer not null</span>

<span class="s1">);</span>

This SQL code says to create a database called “link” with four columns, id, key, url, and hits. If you think of the SQLite3 database like an excel spreadsheet, then we have a spreadsheet table with the four columns, and each row will store entries into the database.

Next, we need to use this schema.sql file to create our database by running the following command from the terminal while in the root directory of the app:

<span class="s1">sqlite3 database.db &lt; schema.sql</span>

This tells SQLite3 to create a file called database.db which is built using the structure we outlined in schema.sql.

A MODELS.PY FILE THAT DEFINES MY FUNCTIONS TO INSET, QUERY, AND UPDATE ENTRIES IN MY DATABASE.

Now that we’ve created our empty database in the database.db file, I need to create python functions to interact with that database. I’m going to create a query, insert, and update function that will allow my to do those three respective things. My three functions look like this:

import sqlite3 as sql

<span class="s1">def insert_link(key,url,hits):</span>

<span class="s1">    with sql.connect("database.db") as con:</span>

<span class="s1">        cur = con.cursor()</span>

<span class="s1">        cur.execute("INSERT INTO link (key,url,hits) VALUES (?,?,?)", (key,url,hits))</span>

<span class="s1">        con.commit()</span>

<span class="s1">def query_hits_and_url_for_link(key):</span>

<span class="s1">    with sql.connect("database.db") as con:</span>

<span class="s1">        cur = con.cursor()</span>

<span class="s1">        key = str(key)</span>

<span class="s1">        cur.execute("SELECT * FROM link WHERE key=?", (key,))</span>

<span class="s1">        link_data = cur.fetchall()</span>

<span class="s1">        print link_data</span>

<span class="s1">        return link_data</span>

<span class="s1">def update_hits(key):</span>

<span class="s1">    with sql.connect("database.db") as con:</span>

<span class="s1">        cur = con.cursor()</span>

<span class="s1">        cur.execute("UPDATE link SET hits = (hits + 1) WHERE key=key")</span>

<span class="s1">        con.commit()</span>

If we just look at the insert_link function and go through it line-by-line…

<span class="s1">with sql.connect("database.db") as con:</span>

This connects to the SQL database.

<span class="s1">cur = con.cursor()</span>

Creates a cursor object called “cur”.

<span class="s1">cur.execute("INSERT INTO link (key,url,hits) VALUES (?,?,?)", (key,url,hits))</span>

This line executes our SQL statement and sends it to the database.

con.close()

Finally, we commit our changes to the database.

The other two functions follow similar logic.

UPDATES TO MY MAIN PYTHON CODE SO THAT STORAGE IN THE MY_LINKS DICTIONARY IS REPLACED WITH STORAGE IN THE SQLITE3 DATABASE.

Before I had my SQLite3 database, my main python code would insert URL’s and key’s into a dictionary. Now we need to replace that logic with python code that enters the URL’s and keys into the database.

When a user enters a URL into tinyjeff.herokuapp.com, I know enter that URL and key into my database by executing the insert_url function described above:

<span class="s1">models.insert_link(key, url, 0)</span>

Now, when a user enters www.jeffreymaxim.com to my website, and my python code generates a key of “98rDv64O”, this insert_key function will insert the URL and key into my database as rows. The 0 parameter in the insert_key function tells my database that this URL initially has zero hits.

The query function described above and the update_hits function described above can similarly be used to lookup information from my database and to update information in my database when this is required.

CONCLUSION

SQLite3 allows you to create a more permanent database for your website to store data even if your server resets, or even if a user refreshes a webpage. You can use it to store data in an excel like fashion. Follow my directions above to try to implement a SQLite3 database in your python and flask website!

Liked what you read? checkout Intro and Python bootcamp courses offered by Byte Academy.