Lately, it seems that data is getting BIGGER.
Everywhere you look, engineers are collecting BIG data.
They are storing them in BIG tables.
And then they make a BIG deal about their BIG app.
But what if you are an engineer with more Modest aspirations?
Someone who has a modest amount of data..
and does not need a BIG solution for a small problem?
Then today is your lucky day,
because we are going to learn about SQLite
and how Python gives you an itsy bitsy module
for working with teeny tiny datastores.
“SQL Lite”, or “S-Q-L Lite” if you prefer,
is the most widely used database engine on planet Earth.
SQLite was created in the year 2000 AD by Dr. Richard Hipp.
In the years since, it has increased in popularity to such a degree
that it is on every computer..
inside every smartphone..
and is probably standing behind you as I speak..
Every respectable programming language has an API for SQLite:
C, C#, JavaScript, and of course - Python.
We are going to focus on using the Python API.
In another video, on another timeline,
we will talk about SQLite more broadly.
But not here and not today.
Import the SQLite3 module.
The 3 at the end is not because we are using Python version 3,
but because we are using SQLite version 3.
To create a new database,
call the 'connect' function
and pass in the path to the new database.
And you should always disconnect from a database
- it is both polite and right.
This is the simplest Python program that uses SQLite:
One: Import the SQLite3 module.
Two: Connect to a new or existing database.
Three: Close the connection.
Run..
Our database has been created and is ready for use.
Caution:
If you try to connect to a database file that does not exist,
then one will be created for you.
So if you misspell the name of an existing database,
surprise - you now have a second database.
SQLite stores data in files.
The new file created by SQLite
contains a whopping zero bytes.
I applaud the efficiency.
Next, let us create some tables and add some data.
To do this, first create a cursor
and add the code to close it.
Next, create a table;
we will call it ‘people’.
Commit this query and then run…
Be careful with your query.
We only want to create this table if it does not yet exist.
If you try to create a table that already exists…
You get an error.
Undo to return to the error-free code.
Here we used a cursor to execute the SQL query.
You can also call the ‘execute’ method directly on the connection object.
The connection object creates a cursor and uses it to execute the query.
It will also return the cursor for data access.
Our SQLite database needs data.
So let us insert the data it needs…
Here is a string with some names.
Time is coin, and our focus is on SQLite,
not string parsing.
So we will use an AI assistant to prepare our test data.
"I will give you a list of names.
Please parse it into a Python expression as a list of tuples
where each tuple contains the first name and last name."
Evaluate…
Copy…
Paste…
We can insert all data with a single method:
“execute many”.
The first argument is our SQL query.
Notice the question mark placeholders.
The second argument is our list of test data.
For the “execute many” method,
the second argument must be an iterable.
When you call the “execute many” method,
a transaction is created for you.
This transaction is not committed until you call the “commit” method.
Request: Kindly close your eyes.
Purpose: To initiate a memory test based on the recent visual input.
Expressing gratitude.
Question: Did we call the commit method on the ‘cursor’ or the ‘connection’?
Brief pause…
Update: Memory test complete.
Instruction: You may now open your eyes.
Acknowledging cooperation.
The commit method is called on the connection object - not the cursor.
How can you remember this?
With humor:
Why did the cursor and the connection go to couples therapy?
Because the cursor kept making changes but the connection would not commit.
When we created the table in our SQLite database,
we specified that the first and last name columns were of type TEXT.
In SQLite, there are 4 data types:
INTEGER, REAL, TEXT, and BLOB.
A fifth member of this list is NULL.
Is this a type?
Or is it a void
- a placeholder for nothingness that sits between a black hole and the edge of the universe…
I digress
These SQLite types have a correspondence with Python types:
An integer is mapped to ‘int’.
REAL data corresponds to ‘float’.
TEXT is converted to ‘string’.
And a BLOB is treated as ‘bytes’.
‘NULL’ is paired with ‘None’.
If we look through the photos of our time together,
you will see we have thus far used two methods to execute queries:
“execute” and “execute many”
Those were good times.
There is a third method of some importance:
execute script.
This method is used to run
- as the name suggests
- a SQL script.
You pass in the script as a string, not a file.
We will create a file called ‘test.sql’
and use it to create a basic table and insert 5 rows into it.
By putting the COMMIT at the end,
we will not need to call “commit” on the connection object.
Let us now use this script.
First: import sqlite3
Next,
call ‘connect’ to create a database called ‘members.db’
Create a cursor for executing queries.
We will load the contents of our SQL file…
Next, execute this script.
Reminder: no need to commit.
Close our database objects.
RUN…
Let us see if everything went according to plan.
When you execute a query using a cursor, it returns a cursor.
We print each row to verify that all data is there.
Run…
Things look to be in order.
There is something you should know.
The ‘execute’ method returns a cursor.
So it may appear there are now two cursors.
But if you print both cursor objects and run…
You will see they are the same object.
While highly readable,
the ‘results’ variable is unnecessary.
If you so choose,
you can simply execute the query and then iterate over the cursor.
Earlier we inserted data using question mark placeholders.
When we used our SQL script, we did not.
Can I offer a logical explanation for this difference?
I can...
The SQL script is a static text file.
When building a SQL query in Python,
you need to beware of SQL injection attacks.
Here is an example to illustrate the threat.
Suppose you are doing a simple SELECT query against the members table.
If a bad actor passes you this string...
Then the SQL query would be this:
The dash-dash is a comment, so this is a valid query.
When executed, this query will return the full contents of the table.
Not good.
Very not good.
But if you use the placeholder approach and pass the user input in as a separate parameter,
then the query is constructed with safeguards in place.
In short:
be careful
be cautious
use placeholders.
The essential features of SQLite in Python are these:
Import the SQLite3 module.
Use the ‘connect’ method to open an existing database or create a new one.
The database is a single file.
Use the connection object to create a cursor object.
Execute queries using one of the execute methods.
If you so choose, you can use execute queries directly on the connection object.
Before you choose, weigh brevity versus clarity.
When constructing a SQL query,
separate the query template from the parameters.
This safeguards against SQL injection attacks.
Close...
It is quite unnecessary to over-engineer your data storage needs.
Put your infrastructure on a diet and use SQLite today.
It is the most used database in the world,
and we all know that following the crowd is a smart thing to do.
SQLite is small in size,
available at very little cost - in fact, it’s free
and it’s a *snap* – to use with Python.
In a world of multi-region cloud-based metered databases,
it is nice to know that Python and SQLite are there with open arms...
and open source.