If you need and introduction to SQL and Databases, I recommend reading these articles before diving into this one:
SQLite is a database that comes pre-installed with Python, you just have to import it just like any other module and start using it.
Another great advantage of SQLite for learning is that it only has 5 types:
- null: indicates a non-value
- integer: represents integer numbers
- real: represents floating numbers
- text: represents strings
- blob: anything that doesn’t fit in the other types, like images, music files, videos, etc.
To start using SQLite, you have to import it with import sqlite3.
Then you open a connection and call the database anything you want, if the database doesn’t exist yet, it will be created.
We are going to call our database company.db.
For SQLite, the database is just a single file.
After that, you create a cursor, which is the object we are going to call the execute the commands in the database.
Since most commands in SQL are pretty extensive, we will use triple double-quotes for multiline strings, it will help with overall readability.
For the command to be actually executed you have to commit it using commit().
Finally, when you are done, you close() the connection with the database.
Let’s see how this works with an example.
Create Database and Table
Create a file ‘company_database.py’ with the following:
import sqlite3
connection = sqlite3.connect('company.db')
cursor = connection.cursor()
sql = """ CREATE TABLE employees (
name text,
job_title text,
salary real,
hire_date text
)"""
cursor.execute(sql)
connection.commit()
connection.close()This structure covers everything described before.
Import sqlite3, create a database named ‘company.db’ to store data about our company, create a cursor from connection, execute the command to create the table ’employees’ with four fields, then commit the transaction and finally, we close the connection.
The four fields are name, job_title, salary, and hire_date. All of them are of the text type, except for salary which has the type real.
We are going to use the structure throughout this article repeatedly.
Execute the code with:
python company_database.pyIf no error occurred, you will find a new file named ‘company.db’ alongside your program’s file ‘company_database.py’ in the same folder.
We are going to use the same file for every example from now on, keeping the basic structure.
Insert Data
In this next piece of code, we are going to ‘INSERT’ a record in our ’employees’ tables.
Notice how we have to use a ‘?’ to represent every field after the VALUES keyword.
The values are passed in the form of a tuple, notice that the format used for dates follows the pattern ‘yyyy-MM-dd HH:mm:ss’.
When calling execute(), the first argument is the SQL command, and the second is the tuple with the values of the new record.
import sqlite3
connection = sqlite3.connect('company.db')
cursor = connection.cursor()
sql = """ INSERT INTO employees (
name,
job_title,
salary,
hire_date
) VALUES (?, ?, ?, ?)"""
values = ('Bob', 'Engineer', 80000, '2007-09-22 00:00:00')
cursor.execute(sql, values)
connection.commit()
connection.close()To insert many records at once, you can create a list of tuples, one tuple for every new record, and instead of calling execute(), you call executemany().
import sqlite3
connection = sqlite3.connect('company.db')
cursor = connection.cursor()
sql = """ INSERT INTO employees (
name,
job_title,
salary,
hire_date
) VALUES (?, ?, ?, ?)"""
values = [
('Mary', 'Designer', 60000, '2012-09-05 00:00:00'),
('Sarah', 'Sales Manager', 98000, '2017-06-21 00:00:00'),
('Peter', 'IT Manager', 95000, '2013-09-05 00:00:00'),
('Brian', 'HR Manager', 92000, '2010-09-05 00:00:00')
]
cursor.executemany(sql, values)
connection.commit()
connection.close()Query Data
Now we have a database, a table, and a few records.
To see the records stored in a table, we use the SELECT command.
We use ‘*’ to retrieve all the fields at once.
To bring all the records from the query, we use fetchall().
import sqlite3
connection = sqlite3.connect('company.db')
cursor = connection.cursor()
sql = "SELECT * FROM employees"
cursor.execute(sql)
print(cursor.fetchall())
connection.commit()
connection.close()The result will come as a list of tuples, one tuple for each record.
[('Bob', 'Engineer', 80000.0, '2007-09-22 00:00:00'),
('Mary', 'Designer', 60000.0, '2012-09-05 00:00:00'),
('Sarah', 'Sales Manager', 98000.0, '2017-06-21 00:00:00'),
('Peter', 'IT Manager', 95000.0, '2013-09-05 00:00:00'),
('Brian', 'HR Manager', 92000.0, '2010-09-05 00:00:00')]Every record has a primary, an unique id.
SQLite creates these id’s automatically and autoincrements them for every new record.
They are implicit and are called ‘rowid’ by default.
To bring only some fields in a query, you can specify every field separated by commas.
Here we are using only three fieds: rowid, name, and salary.
import sqlite3
connection = sqlite3.connect('company.db')
cursor = connection.cursor()
sql = "SELECT rowid, name, salary FROM employees"
cursor.execute(sql)
print(cursor.fetchall())
connection.commit()
connection.close()The output has less information, as requested:
[(1, 'Bob', 80000.0), (2, 'Mary', 60000.0),
(3, 'Sarah', 98000.0), (4, 'Peter', 95000.0),
(5, 'Brian', 92000.0)]Since the result is a tuple, we can use brackets to reference each item in the tuple and format the output while iterating the list of tuples with a for loop.
import sqlite3
connection = sqlite3.connect('company.db')
cursor = connection.cursor()
sql = "SELECT name, salary FROM employees"
cursor.execute(sql)
records = cursor.fetchall()
for record in records:
print(f'Name: {record[0]}, Salary: {record[1]}')
connection.commit()
connection.close()The output will be:
Name: Bob, Salary: 80000.0
Name: Mary, Salary: 60000.0
Name: Sarah, Salary: 98000.0
Name: Peter, Salary: 95000.0
Name: Brian, Salary: 92000.0The WHERE clause allows you to filter the results of a query.
You can also combine it with operators like >=, <, AND, OR, and others to create more complex filters.
This query lists only employees hired after 2011.
import sqlite3
connection = sqlite3.connect('company.db')
cursor = connection.cursor()
sql = """SELECT * FROM employees
WHERE hire_date > '2011-01-01' """
cursor.execute(sql)
records = cursor.fetchall()
for record in records:
print(record)
connection.commit()
connection.close()You can see everyone on this list was hired after 2011:
('Mary', 'Designer', 60000.0, '2012-09-05 00:00:00')
('Sarah', 'Sales Manager', 98000.0, '2017-06-21 00:00:00')
('Peter', 'IT Manager', 95000.0, '2013-09-05 00:00:00')This query lists only employees hired after 2011 and whose salary is below 96,000.
import sqlite3
connection = sqlite3.connect('company.db')
cursor = connection.cursor()
sql = """SELECT * FROM employees
WHERE hire_date > '2011-01-01'
AND salary < 96000"""
cursor.execute(sql)
records = cursor.fetchall()
for record in records:
print(record)
connection.commit()
connection.close()You can see Sarah was excluded because her salary is 98,000.
('Mary', 'Designer', 60000.0, '2012-09-05 00:00:00')
('Peter', 'IT Manager', 95000.0, '2013-09-05 00:00:00')Update Data
Now that you know how to query your database, let’s see how to UPDATE a record.
The general syntax is UPDATE <table name> SET <field> = <new value> WHERE <filter>.
The WHERE clause is actually optional, but get used to always use it to avoid update every single row of your table by mistake, which is very dangerous and may seriously harm your data. This way, your update will only affect the records filtered by the WHERE.
Here we are saying that everyone whose salary is below 90,000 will get an increase of 10,000.
import sqlite3
connection = sqlite3.connect('company.db')
cursor = connection.cursor()
sql = """UPDATE employees SET salary = salary + 10000
WHERE salary < 90000
"""
cursor.execute(sql)
connection.commit()
cursor.execute("SELECT * FROM employees")
records = cursor.fetchall()
for record in records:
print(record)
connection.commit()
connection.close()Only Bob and Mary had an increase of 10000:
('Bob', 'Engineer', 90000.0, '2007-09-22 00:00:00')
('Mary', 'Designer', 70000.0, '2012-09-05 00:00:00')
('Sarah', 'Sales Manager', 98000.0, '2017-06-21 00:00:00')
('Peter', 'IT Manager', 95000.0, '2013-09-05 00:00:00')
('Brian', 'HR Manager', 92000.0, '2010-09-05 00:00:00')Order By
You can use ORDER BY to choose how to order the results of a query
The default is to sort records by rowid and ascending, i.e, from the smalller to the higher id.
Here we are going to sort by salary descending, that is, from the higher to the smaller salary.
We are also to list rowid explicitly to see how the result is ordered by the salary column, not the id.
import sqlite3
connection = sqlite3.connect('company.db')
cursor = connection.cursor()
cursor.execute("SELECT rowid, * FROM employees ORDER BY salary DESC")
records = cursor.fetchall()
for record in records:
print(record)
connection.commit()
connection.close()You can easily see Sarah makes the most with 98,000.
(3, 'Sarah', 'Sales Manager', 98000.0, '2017-06-21 00:00:00')
(4, 'Peter', 'IT Manager', 95000.0, '2013-09-05 00:00:00')
(5, 'Brian', 'HR Manager', 92000.0, '2010-09-05 00:00:00')
(1, 'Bob', 'Engineer', 90000.0, '2007-09-22 00:00:00')
(2, 'Mary', 'Designer', 70000.0, '2012-09-05 00:00:00')Limit
We have a very small database, but it is not uncommon to have thousands of records, and sometimes you just want to see a few of them to check the structure of the data.
Use LIMIT followed by the number of records you want.
In this case, we are just listing the first 3.
import sqlite3
connection = sqlite3.connect('company.db')
cursor = connection.cursor()
cursor.execute("SELECT rowid, * FROM employees LIMIT 3")
records = cursor.fetchall()
for record in records:
print(record)
connection.commit()
connection.close()You can see that records 4 and 5 were not included.
(1, 'Bob', 'Engineer', 90000.0, '2007-09-22 00:00:00')
(2, 'Mary', 'Designer', 70000.0, '2012-09-05 00:00:00')
(3, 'Sarah', 'Sales Manager', 98000.0, '2017-06-21 00:00:00')
Delete
To delete a record, the general syntax is DELETE FROM <table name> WHERE <filter>.
The WHERE clause is actually optional, but get used to always use it to avoid deleting records by mistake, which is very dangerous and may seriously harm your data. This way, your DELETE will only affect the records filtered by the WHERE.
Here we are deleting the record with Id 1, which is Bob.
import sqlite3
connection = sqlite3.connect('company.db')
cursor = connection.cursor()
cursor.execute("""DELETE FROM employees
WHERE rowid = 1
""")
connection.commit()
cursor.execute("SELECT rowid,* FROM employees")
records = cursor.fetchall()
for record in records:
print(record)
connection.commit()
connection.close()You can see Bob was deleted.
(2, 'Mary', 'Designer', 70000.0, '2012-09-05 00:00:00')
(3, 'Sarah', 'Sales Manager', 98000.0, '2017-06-21 00:00:00')
(4, 'Peter', 'IT Manager', 95000.0, '2013-09-05 00:00:00')
(5, 'Brian', 'HR Manager', 92000.0, '2010-09-05 00:00:00')Drop Table
To delete a whole table, use the DROP TABLE command indicating the name of the table you want to drop.
import sqlite3
connection = sqlite3.connect('company.db')
cursor = connection.cursor()
cursor.execute("DROP TABLE employees")
connection.commit()
connection.close()