How to use SQLite in Python
Land Your First Data Science Job
A proven roadmap to prepare for $75K+ entry-level data roles. Perfect for Data Scientist ready to level up their career.
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?
- What kind of database
- Create a connection to your database
- Make a query against the database
- Create a table
- Insert data into the database
- Commit the changes to save them
- Commit the changes to save them
- Display the results
1. What kind of database
SQL databases come in many shapes and forms.
Some of the most famous ones are :
- MySQL
- Postgresql
- SQLite
- MariaDB
- 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.
Land Your First Data Science Job
A proven roadmap to prepare for $75K+ entry-level data roles. Perfect for Data Scientist ready to level up their career.
Related Articles
Continue your learning journey with these related topics
Master Data Science in Days, Not Months 🚀
Skip the theoretical rabbit holes. Get practical data science skills delivered in bite-sized lessons – Approach used by real data scientist. Not bookworms. 📚