How to use SQLite in Python
• 1 minSQL 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.