How To Quickly SQLite in Python

Image from Wikipedia

Everyone’s looking for someone that knows SQL. It’s easy to understand since SQL reads like plain English, but it’s all the practice. I, like many others, did a lot of practice on HackerRank’s easy and medium problems, but I never had too much experience making my own database or seeing actual SQL statements used. I got the chance to use SQLite after finally dealing with some projects that dealt with a good amount of data. This is going to be a quick start to using SQLite in Python and some insights I got from my experience, but first what is SQLite and why use it?

SQLite is just another type of SQL database. A lot of the differences between each “SQL” you hear is nuances of syntax and minor limitations between capabilities. I don’t want to go too in-depth in since I don’t have experience using all different types of SQL. One thing I did hear, though, is SQLite is good for smaller databases you’d want to to store data. Luckily for us SQLite is really easy to deal with in Python as well.

First and foremost, to actually use SQLite, we want to use the library sqlite3. So make you use install that onto your machine before trying anything out. When we actually have sqlite3, of course we’d want to make the database we are going to use! A really convenient feature that I’ve encountered in this library is kind of “skipping” the creating step by going straight to connecting to a database. Using:

import sqlite3
conn = sqlite3.connect('Your_Database_Name.db')

Sqlite3 will try to connect to the database that you gave it, and if there’s no database with that name in your directory, then it’ll create one and connect to it. You can connect to the database as many times as you want, but there are going to be implications if you are manipulating the database in more than one connection. For now, it won’t be a problem, so the next thing we’d want to do is create a table in our database. When working with SQL, I like to think about the query I’m going to use, so I’d simply want to do something like this:

query = """CREATE TABLE TableName (variable1 TYPE, variable2 TYPE, variable3 TYPE);"""

The query is simple to understand, but some helpful additions that I have used are expanding the first part to “CREATE TABLE IF NOT EXISTS TableName” or adding a primary key variable (“variable1 TYPE PRIMARY KEY”). There’s also a builtin AUTOINCREMENT for the primary key, but that should be used only if you really need it since it’ll end up costing some CPU, memory, etc. We can actually use the queries by using the function “execute”.

conn.execute(query)

After creating the table we would want to insert data into the table with a quick query like:

query = """INSERT INTO TableName Values (Value1, Value2, Value3);"""

You can also add to the query by putting the columns of the table after “TableName” to make it more readable. You can also make the (Value1, Value2, Value3) into (?, ?, ?) and give the values later on in the execute function. Putting it all together, connecting, creating, and inserting into a table would look like:

import sqlite3
conn = sqlite3.connect('Your_Database_Name.db')
create_query = """CREATE TABLE TableName (variable1 TYPE, variable2 TYPE, variable3 TYPE);"""
insert_query = """INSERT INTO TableName Values (?, ?, ?);"""
test_data = [Value1, Value2, Value3]
conn.execute(create_query)
conn.execute(insert_query, test_data)

We would of course be inserting multiple times and we could also write and execute queries to delete tables, delete entries, create joins, etc. After acting on the database, it’s good practice to commit the changes and close the connection by:

conn.commit()
conn.close()

Conclusion

This was a quick guide to SQLite3 in Python. One thing I didn’t include in this was the use of a cursor, which to my understanding is useful for multiple connections to the same or other tables. This makes is supposed to make it easier to keep track of connections. Most of the code I’ve seen uses a cursor and so the example on top would look like this:

import sqlite3
conn = sqlite3.connect('Your_Database_Name.db')
curs = conn.cursor()
create_query = """CREATE TABLE TableName (variable1 TYPE, variable2 TYPE, variable3 TYPE);"""
insert_query = """INSERT INTO TableName Values (?, ?, ?);"""
test_data = [Value1, Value2, Value3]
curs.execute(create_query)
curs.execute(insert_query, test_data)

You can get by without knowing exactly what a cursor is, but feel free to do more research. Hope this was helpful!