This is an introduction in SQLite in Python. You will learn how to create a database in your Python application. But also how to access and edit your database data.
You need to import the Python module "sqlite3". (Command "import sqlite3").
All SQLite operations are done through the Python module "sqlite3" which connects to the SQLite DB and adheres to the Python DB API.
Creating the database
A connection to the database is created by the method "connect" which returns a SQLite connection object. This SQLite connection object is used to access the "cursor" and to commit the changes on the database.
The "cursor" is used run SQLite queries through the function "execute", which are used to create or access database data. The method execute() returns also database rows, if it is used to query data from the database.
This database rows can be accessed through the methods fetchall(), fetchone() and fetchmany().
import sqlite3
try:
sqliteCon = sqlite3.connect('Datebase_python.db')
query_create_table = '''CREATE TABLE mytable (
id INTEGER PRIMARY KEY,
prename TEXT NOT NULL,
surname TEXT NOT NULL,
countrycode INTEGER NOT NULL UNIQUE,
birthday datetime,
expenses REAL NOT NULL);'''
cursor = sqliteCon.cursor()
print("Connected to the database")
cursor.execute(query_create_table)
sqliteCon.commit()
print("Database created")
cursor.close()
except sqlite3.Error as error:
print("Error while creating the table - ", error)
finally:
if (sqliteCon):
sqliteCon.close()
print("database connection is closed")
The method commit() must be called on the SQLite connection object to apply (persist) the changes on the database.
If you are done with accessing your database, then do not forget to close the cursor and the SQLite connection through the method close().
Important SQLite database types
These database types (and their Python equivalents) are used to save values in your SQLite databases:
- NULL: – Python: none - A null value.
- INTEGER: – Python: int - Numeric values. eg.: 1,2,3,4
- REAL: – Python: float - A floating-point value e.g.: 2.94
- TEXT: – Python: str - Text string which uses UTF-8, UTF-16BE or UTF-16LE encoding
- BLOB: – Python: bytes - A blob of data (binary data) used to store images and files
Information about the available Error classes in SQLite
In this example the class "Error" was used which returns all type of occured errors related to SQLite, your database and the database connection.
The class "Error" is a subclass of "Exception" and also the base class for other type of errors and error classes.
Other error classes:
sqlite3.DatabaseError
This is a base class for errors that are related to the database. An example for an error: You did not define a database file.
This class "DatabaseError" has the following important subclasses:
- sqlite3.IntegrityError
Errors regarding the integrity of the database. For example: a foreign key check fails. - sqlite3.ProgrammingError
Errors that occur because of programming errors in your SQL query. For example: The table that you want to created already exists. - sqlite3.OperationalError
Errors regarding the database's operation and connection. For example: The database server is down or disconnected accidently.
Accessing (Query) data from the database
You can query data from your SQLite database through the method execute() which gets a SQL command as an argument. Here we use the SQL command "SELECT * FROM".
sqliteCon = sqlite3.connect('Datebase_python.db')
cursor = sqliteCon.cursor()
query_select_all = "SELECT * FROM mytable"
cursor.execute(query_select_all)
tablerows = cursor.fetchall()
print("Number of all rows: ", len(tablerows))
print("All rows in the table mytable: ")
for row in tablerows:
print("Id: ", row[0])
print("Prename: ", row[1])
print("Surname: ", row[2])
print("Country Code: ", row[3])
print("Birthday: ", row[4])
print("Expenses: ", row[4])
print("------\n")
cursor.close()
sqliteCon.close()The method fetchall() returns the database rows of the database query (variable "query_select_all").
Insert data into the database
Add a new row into your table by the SQL command "INSERT INTO".
sqliteCon = sqlite3.connect('Datebase_python.db')
cursor = sqliteCon.cursor()
query_insert_into = """INSERT INTO mytable
(id, prename, surname, countrycode, birthday, expenses)
VALUES
(1,'Max','Johnson','43','12-12-1990',5045.20)"""
count = cursor.execute(query_insert_into)
sqliteCon.commit()
cursor.close()The method execute() executes the SQL command and returns the number of inserted rows.
You can get the number of commited (inserted, deleted or changed) rows through this method "rowcount":
cursor.rowcount
Delete data from the database
This is done similar as in the chapter ("Insert data into the database") above.
You can also pass arguments (parameters), through the method the method execute(). These parameters do store your values for the SQL command. The table column and the value (marked in the SQL command by the question mark "?") is added in own parentheses in the method execute().
sqliteCon = sqlite3.connect('Datebase_python.db')
cursor = sqliteCon.cursor()
query_delete_row = "DELETE FROM mytable WHERE id = ?"
cursor.execute(query_delete_row, (id, 5))
sqliteCon.commit()
cursor.close()
sqliteCon.close()
If you want to update a row, then use the SQL command "UPDATE" instead. Example (an excerpt):
query_update_row = "UPDATE mytable SET name=:namevalue WHERE id = :idnumber"
cursor.execute(query_update_row, {"Peter": namevalue, "5": idnumber})
sqliteCon.commit()In this example the parameters (arguments) are passed through the placeholders (namevalue and idnumber).
This was an introduction into the programming of SQLite databases in Python. If you want to know more about this topic, then please check the links below.
More about about SQLite3 / Documentation of SQLite3:
https://docs.python.org/3/library/sqlite3.html
DB Browser - GUI tool to view and manage the SQLite database:
https://sqlitebrowser.org/dl/