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

Published: 04 November 2020
on channel: 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:
https://sqlitebrowser.org/

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

On this page of the site you can watch the video online Connect Python to SQLite Databases Part 1 | Learn Python From Scratch 8 | Kovolff with a duration of hours minute second in good quality, which was uploaded by the user kovolff 04 November 2020, share the link with friends and acquaintances, this video has already been watched 61 times on youtube and it was liked by 2 viewers. Enjoy your viewing!