How to use SQLite in Python

SQL is an extremely powerful language and it is one of the tools that every Data Scientist should have.

So how to use SQL in Python?

1. What kind of database
2. Create a connection to your database
3. Make a query against the database
4. Create a table
5. Insert data into the database
6. Commit the changes to save them
7. Commit the changes to save them
8. Display the results

1. What kind of database

SQL databases come in many shapes and forms.

Some of the most famous ones are :

1. MySQL
2. Postgresql
3. SQLite
4. MariaDB
5. etc...

Some are better than others for large volumes but for the sake of this article, we are going to use SQLite, which is one of the most lightweight.

2. Create a connection to your database

To create a connection we need to use the sqlite3 library

import sqlite3
con = sqlite3.connect('example.db')

Now the connection has been created with the con object.

3. Make a query against the database

Once a connection is established we can create a cursor object and call its execute() method to make a query.

4. How to create a table

To illustrate the execute() command, let's create a table.

Let's create a table that will store revenues observations.

# Create a table
cur.execute('''CREATE TABLE revenues
               (date text, amount text, source text)''')

5. Insert data into the database

Now that our table has been created let's insert data.

# Insert a row of data
cur.execute("INSERT INTO revenues VALUES ('2020-11-16','123314','shopify')")

6. Commit the changes to save them

In order to have these changes populated in the database we neet to commit them using the connection that we previously created.

# Save (commit) the changes
con.commit()

7. We close the connection to avoid problems with the database

# We can also close the connection if we are done with it.
# Just be sure any changes have been committed or they will be lost.
con.close()

You are now all set! You now know how to use SQLite with Python.