By Konstantinos Patronas — 15 Mar 2022

Python: SQLite tutorial

What is SQLite?

Photo by Sunder Muthukumaran on Unsplash

What is SQLite?

SQLite is a lightweight embedded database, SQLite does not have a standalone process and does not follow a client-server model, but instead, SQLite is embedded as a library and becomes part of an application. The application interacts with the database using functions that directly manipulate the database contents in a file or in-memory if persistence is not necessary.

How I use SQLite with Python?

Python has built-in SQLite support, but first, we need to import the required libraries.import sqlite3

Then we need to create or use an existing database; the following code will create a file database.db if the file does not exist.

connection = sqlite3.connect('database.db')

The next step is to create a cursor; a cursor is a method that allows us to execute SQL queries.cursor = connection.cursor()

How i create tables in SQLite?

Assuming we don’t have any tables in our database, let’s create one.cursor.execute('CREATE TABLE pets(id integer PRIMARY KEY, name text, birthday text);')
connection.commit()

cursor.execute() : The execute method allows us to execute SQL queries
CREATE TABLES pets : instructs SQLite to create a table with the name pets.
id integer PRIMARY KEY : id is the name of the column and has a datatype of integer, it is also our table primary key.
name text : name will hold the name of the pet and has a datatype of text.
birthday text : birthday will hold the date that our pet born, the time formats that SQLite supports areYYYY-MM-DD
YYYY-MM-DD HH:MM
YYYY-MM-DD HH:MM:SS
YYYY-MM-DD HH:MM:SS.SSS
YYYY-MM-DDTHH:MM
YYYY-MM-DDTHH:MM:SS
YYYY-MM-DDTHH:MM:SS.SSS
HH:MM
HH:MM:SS
HH:MM:SS.SSS
now: (now returns the current date/time in utc)

How I insert data into a table?

To insert data into a table we use the INSERT statementpet1 = (1,'Markus','2012–01–06')
pet2 = (2,'Ted','2010–01–03')cursor.execute('INSERT INTO pets(id,name,birthday) VALUES(?,?,?)',pet1)cursor.execute('INSERT INTO pets(id,name,birthday) VALUES(?,?,?)',pet2)
connection.commit()

pet1 and pet2 : are tuples with the data we want to insert to table pets
INSERT INTO pets(id,name,birthday) VALUES(?,?,?) : This SQL statements will insert the values of tuple into the table pets, what actually does is replace the “?” with each element of the tuple.
connection.comit() : When we have done inserting the data into the table we need to commit our changes.

How to i insert many rows at once?

In the previous example for each tuple we had to run an INSERT statement, we can simplify this using the executemany() function.data = [(3,'Tom','2011-01-01'),(4,'Jerry','2015-01-01')]
cursor.executemany('INSERT INTO pets(id,name,birthday) VALUES(?,?,?);',data)
connection.commit()

How i rollback my changes?

There might be circumstances (an error for example) and we don't to save changes we did in our database, then instead of a commit() function we need to execute the rollback() function.try:
pet5 = ('A','Mike','2010–01–01')
cursor.execute('INSERT INTO pets(id,name,birthday) VALUES(?,?,?);',pet5)
connection.commit()
except sqlite3.Error as ex:
print(str(ex))
connection.rollback()

pet5 : In this tuple we have made a mistake intentionally, first element of the tuple should be an integer and not a character, something that will cause SQLite to throw an exception
except sqlite3.Error as ex: this is the exception handler for an SQLite error, if it catches an error every change in our database had done after a commit will be discarded by the rollback() function.

How i update rows in a table?

Let’s make some changes to the existing rows; in SQL, we can change the contents of rows using the UPDATE statement, assuming that we made a mistake when we enter our data into the table, the pet with the name ‘Markus’ has the wrong birthday, is not ‘2012–01–06’ but ‘2014–01–06’cursor.execute('UPDATE pets SET birthday = "2014-01-06" where name = "Markus";')
connection.commit()

UPDATE: the SQL statement that instructs SQLite to update columns of a row, note that if we don't have a WHERE condition which specifies some criteria on which rows the update will performed it will update all rows of the table.

How i retrieve rows from a table?

Now we want to verify the change in the birthday of Markus, to do this we use the SELECT statementrows = cursor.execute('SELECT * FROM pets;').fetchall()
for row in rows:
print(row)

Output:(1, 'Markus', '2014-01-06')
(2, 'Ted', '2010–01–03')
(3, 'Tom', '2011-01-01')
(4, 'Jerry', '2015-01-01')

fetchall() : The fetchall() function instructs SQLite to return all rows in memory, instead of having to iterate over the cursor object.

How i delete rows?

We can use the DELETE statement to delete rows; if a WHERE clause is not used with a DELETE statement, it will delete all table rows.cursor.execute('DELETE FROM pets;')
connection.commit()

personally i don't like the idea of deleting rows from a table, you might decide later that you actually need those data, i recommend that instead of deleting just adding an extra column which indicates to consider the row “logically” deleted.

How do i know how many rows affected by a statement?

When we do INSERT, UPDATE or DELETE operations is good to know how many rows will be affected before a commit or a rollback, to get the number we can use the rowcount function.print(cursor.execute('DELETE FROM pets;').rowcount)

Output:4

rowcount : returns the number of rows affected by a query

Note: to get the row count of a SELECT statement is better just to use the fetchall() function first and get the length of results, if you try to use rowcount in a SELECT query without using the fetchall() function and the result has a lot of rows it might return -1 because SQLite is not sure for the full count of rows yet.rows = cursor.execute('SELECT * FROM pets;').fetchall()
print(len(rows))

How i check if a table exists?

Very often you will need to know if a table exists or not, in order to decide if you need to create or drop a tablecursor.execute('CREATE TABLE IF NOT EXISTS pets(id integer PRIMARY KEY, name text, birthday text);')

How i drop a table?cursor.execute('DROP TABLE pets;')

I hope you found the article easy to read and help you to your journey in Python!