Connect Python to SQLite Databases Part 1 | Learn Python From Scratch 8 | Kovolff

In real world applications it is best to separate data from the code. The best way to do this is to place your data in a database.

A potent database for smaller projects is SQLite. The whole database is in one file. Despite the lite moniker SQLite databases are potent beasts - Do not underestimate them.

There are a lot of GUI (Graphic User Interface) tools enabling you to easily create and modify SQLite databases.

One such excellent open source tool is DB Browser for SQLite:

Before Being able to call a database from Python we need to know some SQL basics.

SELECT * FROM [conversion_factors]
This returns all data from all columns (aka fields) from the table conversion_factors

In SQL it is good practice to enclose your table and field names in square brackets [].
It is also good practice to write SQL commands such SELECT and FROM in all caps.

SELECT [convert_from], [convert_to], [factor] FROM [conversion_factors]
This returns all data from just the above comma separated columns.

To filter data, in other words not return all data, you need to use the WHERE statement:

SELECT [convert_from], [convert_to], [factor] FROM [conversion_factors]
WHERE [convert_from] = ‘mile’

This returns the above four columns, but only that data that has mile in its [convert_from] field.
Because mile is a literal value, it is enclosed in single quotes - similarly to literals in Python strings.

You can use multiple filters in your WHERE statement. These filters can be combined either with AND or OR.

AND: Data returned must fulfill all filters.
OR: Data returned must fulfill at least one filter

For our Unit converter we basically need just one value, the factor.
So assuming the user wishes to convert from mile to kilometre our query should look like this

SELECT [factor] FROM [conversion_factors]
WHERE [convert_from] = ‘mile’ AND [convert_to] = ‘kilometre’

#python #sqlite #databases

[iainaldous]

Are you able to post the database in sqlite so I can check what I have built vs. yours as I am following along please? Just so as I progress through the video course I don't make any errors. Thankyou!