1-DAV-202 Data Management 2023/24
Previously 2-INF-185 Data Source Integration
Difference between revisions of "Lsql"
(37 intermediate revisions by 2 users not shown) | |||
Line 1: | Line 1: | ||
<!-- NOTEX --> | <!-- NOTEX --> | ||
− | [[ | + | [[HWsql]] |
<!-- /NOTEX --> | <!-- /NOTEX --> | ||
− | This lecture introduces the basics of the Python programming language | + | This lecture introduces the basics of the Python programming language and the basics of working with databases using the SQL language and SQLite3 lightweight database system. |
− | |||
The next three lectures | The next three lectures | ||
− | * Computer science students will use Python, SQLite3 and several advanced Python libraries for complex data processing | + | * Computer science and data science students will use Python, SQLite3 and several advanced Python libraries for complex data processing. |
− | * Bioinformatics students will use several bioinformatics command-line tools | + | * Bioinformatics students will use several bioinformatics command-line tools. |
− | |||
==Overview, documentation== | ==Overview, documentation== | ||
+ | |||
+ | '''Outline of this lecture''' | ||
+ | * We introduce a simple data set. | ||
+ | * In a separate [[Python|tutorial]] for beginners in Python, we have several Python scripts for processing this data set. | ||
+ | * We introduce basics of working with SQLite3 and writing SQL queries. | ||
+ | * We look at how to combine Python and SQLite. | ||
'''Python''' | '''Python''' | ||
Line 17: | Line 21: | ||
* Advantages: powerful language features, extensive libraries | * Advantages: powerful language features, extensive libraries | ||
* Disadvantages: interpreted language, can be slow | * Disadvantages: interpreted language, can be slow | ||
− | * | + | * [https://perso.limsi.fr/pointal/_media/python:cours:mementopython3-english.pdf A very concise cheat sheet] |
− | * | + | * [https://docs.python.org/3/tutorial/ A more detailed tutorial] |
+ | * [[Python|Our tutorial]] | ||
'''SQL''' | '''SQL''' | ||
− | * | + | * SQL is a language for working with relational databases. It is covered in more detail in dedicated database courses. |
− | * We will cover basics of SQL and work with a simple DB system SQLite3 | + | * We will cover basics of SQL and work with a simple DB system SQLite3. |
− | * Typical database systems are complex, use server-client architecture. SQLite3 is a simple "database" stored in one file. | + | * Advantages of storing data in a database rather than CSV files and similar: |
− | * [https://www.sqlite.org/docs.html | + | ** A database can contain multiple tables and connect them through shared identifiers. |
+ | ** You can create indices which allow fast access to records matching your criteria without searching through all data. | ||
+ | ** A database system can compute complex queries without loading all data into memory. | ||
+ | * Typical database systems are complex, use server-client architecture. In contrast, SQLite3 is a simple "database" stored in one file. | ||
+ | ** It is best used by a single process at a time, although multiple processes can read concurrently. | ||
+ | ** It can be easily created, does not need extensive configuration typical for more complex database systems. | ||
+ | * [https://www.sqlite.org/docs.html SQLite3 documentation] | ||
* [https://www.w3schools.com/sql/default.asp SQL tutorial] | * [https://www.w3schools.com/sql/default.asp SQL tutorial] | ||
− | * | + | * [https://docs.python.org/3/library/sqlite3.html SQLite3 in Python documentation] |
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
==Dataset for this lecture== | ==Dataset for this lecture== | ||
− | * [https://www.imdb.com/ IMDb] is an online database of movies and TV series with user ratings | + | * [https://www.imdb.com/ IMDb] is an online database of movies and TV series with user ratings. |
− | * We have downloaded a preprocessed dataset of selected TV series ratings from [https://github.com/nazareno/imdb-series/ GitHub] | + | * We have downloaded a preprocessed dataset of selected TV series ratings from [https://github.com/nazareno/imdb-series/ GitHub]. |
− | * From this dataset, we have selected 10 series with high average number of voting users | + | * From this dataset, we have selected 10 series with high average number of voting users. |
− | * Data are two files in | + | * Data are two files in the CSV format: list of series, list of episodes. |
+ | * CSV stands for comma-separated values. | ||
File <tt>series.cvs</tt> contains one row per series | File <tt>series.cvs</tt> contains one row per series | ||
Line 54: | Line 56: | ||
File <tt>episodes.csv</tt> contains one row per episode: | File <tt>episodes.csv</tt> contains one row per episode: | ||
* Columns: (0) series id, (1) episode title, (2) episode order within the whole series, (3) season number, (4) episode number within season, (5) user rating, (6) the number of votes | * Columns: (0) series id, (1) episode title, (2) episode order within the whole series, (3) season number, (4) episode number within season, (5) user rating, (6) the number of votes | ||
− | * Here is a sample of 4 episodes from the Game of Thrones series | + | * Here is a sample of 4 episodes from the Game of Thrones series. |
− | * If the episode title contains a comma, the whole title is in quotation marks | + | * If the episode title contains a comma, the whole title is in quotation marks. |
<pre> | <pre> | ||
1,"Dark Wings, Dark Words",22,3,2,8.6,12714 | 1,"Dark Wings, Dark Words",22,3,2,8.6,12714 | ||
Line 63: | Line 65: | ||
</pre> | </pre> | ||
− | + | '''Notes''' | |
− | + | * A different version of this data was used already in the [[Lperl#The_first_input_file_for_today:_TV_series|lecture on Perl]]. | |
− | + | * A [[Python|Python tutorial]] shows several scripts for processing these files which are basically longer versions of some of the SQL queries below. | |
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | * | ||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
==SQL and SQLite== | ==SQL and SQLite== | ||
===Creating a database=== | ===Creating a database=== | ||
− | SQLite3 database is a file with your data stored in | + | SQLite3 database is a file with your data stored in a special format. To load our csv files to a SQLite database, run command: |
<syntaxhighlight lang="bash"> | <syntaxhighlight lang="bash"> | ||
sqlite3 series.db < create_db.sql | sqlite3 series.db < create_db.sql | ||
Line 215: | Line 98: | ||
.import episodes.csv episodes | .import episodes.csv episodes | ||
</syntaxhighlight> | </syntaxhighlight> | ||
− | * The two <tt>CREATE TABLE</tt> | + | * The two <tt>CREATE TABLE</tt> commands create two tables named <tt>series</tt> and <tt>episodes</tt>. |
− | * For each column (attribute) of the table we list | + | * For each column (attribute) of the table we list its name and type. |
− | * Commands starting with a dot are special SQLite3 commands, not part of SQL itself. Command <tt>.import</tt> reads | + | * Commands starting with a dot are special SQLite3 commands, not part of SQL itself. Command <tt>.import</tt> reads a CSV file and stores it in a table. |
Other useful SQLite3 commands; | Other useful SQLite3 commands; | ||
* <tt>.schema tableName</tt> (lists columns of a given table) | * <tt>.schema tableName</tt> (lists columns of a given table) | ||
− | * <tt>.mode column</tt> and <tt>.headers on</tt> (turn on human-friendly formatting | + | * <tt>.mode column</tt> and <tt>.headers on</tt> (turn on human-friendly formatting, not good for further processing) |
===SQL queries=== | ===SQL queries=== | ||
− | * Run <tt>sqlite3 series.db</tt> to get an SQLite command-line where you can interact with your database | + | * Run <tt>sqlite3 series.db</tt> to get an SQLite command-line where you can interact with your database. |
− | * Then type the | + | * Then type the queries below which illustrate the basic features of SQL. |
+ | * In these queries, we use uppercase for SQL keywords and lowercase for our names of tables and columns (SQL keywords are not case sensitive). | ||
<syntaxhighlight lang="sql"> | <syntaxhighlight lang="sql"> | ||
/* switch on human-friendly formatting */ | /* switch on human-friendly formatting */ | ||
Line 246: | Line 130: | ||
/* print all episodes with at least 50k votes, order by votes */ | /* print all episodes with at least 50k votes, order by votes */ | ||
SELECT title, votes FROM episodes | SELECT title, votes FROM episodes | ||
− | WHERE votes>50000 ORDER BY votes | + | WHERE votes>50000 ORDER BY votes DESC; |
/* join series and episodes tables, print 10 episodes | /* join series and episodes tables, print 10 episodes | ||
Line 253: | Line 137: | ||
FROM episodes AS e, series AS s | FROM episodes AS e, series AS s | ||
WHERE e.seriesId=s.id | WHERE e.seriesId=s.id | ||
− | ORDER BY votes | + | ORDER BY votes DESC |
+ | LIMIT 10; | ||
/* compute the number of series per channel, as prog2.py */ | /* compute the number of series per channel, as prog2.py */ | ||
− | SELECT channel, COUNT() | + | SELECT channel, COUNT() AS series_count |
FROM series GROUP BY channel; | FROM series GROUP BY channel; | ||
Line 264: | Line 149: | ||
</syntaxhighlight> | </syntaxhighlight> | ||
− | + | Parts of a typical SQL query: | |
+ | * <tt>SELECT</tt> followed by column names, or functions <tt>MAX</tt>, <tt>COUNT</tt> etc. These columns or expressions are printed for each row of the table, unless filtered out (see below). Individual columns of the output can be given aliases by keyword <tt>AS</tt>. | ||
+ | * <tt>FROM</tt> followed by a list of tables. Tables also can get aliases (<tt>FROM episodes AS e</tt>). | ||
+ | * <tt>WHERE</tt> followed by a condition used for filtering the rows. | ||
+ | * <tt>ORDER BY</tt> followed by an expression used for sorting the rows. | ||
+ | * <tt>LIMIT</tt> followed by the maximum number of rows to print. | ||
+ | |||
+ | More complicated concepts: | ||
+ | * <tt>GROUP BY</tt> allows to group rows based on common value of some columns and compute statistics per group (count, maximum, sum etc). | ||
+ | * If you list two tables in <tt>FROM</tt>, you will conceptually create all pairs of rows, one from one table, one from the other. These are then typically filtered in the <tt>WHERE</tt> clause to only those that have a matching ID (for example <tt>WHERE e.seriesId=s.id</tt> in one of the queries above). | ||
==Accessing a database from Python== | ==Accessing a database from Python== | ||
− | We will use sqlite3 library for Python to access data from the database and to process | + | We will use <tt>[https://docs.python.org/3/library/sqlite3.html sqlite3]</tt> library for Python to access data from the database and to process them further in the Python program. |
===read_db.py=== | ===read_db.py=== | ||
− | * The following script illustrates running a SELECT query and the | + | * The following script illustrates running a <tt>SELECT</tt> query and getting the results. |
+ | * To start using a database, we first create objects called Connection and Cursor. We use the cursor to run individual SQl queries. | ||
+ | * If we want to use particular values in the queries, which we have stored in variables, we use placeholders "?" in the query itself and pass the values to the <tt>execute</tt> command as a tuple. | ||
+ | ** sqlite3 module ensures that the values are passed to the database correctly. If we inserted the values directly into the query string, we could face problems if the values contain e.g. quotation marks leading to SQL syntax errors or even [https://en.wikipedia.org/wiki/SQL_injection SQL injection attacks]. | ||
+ | |||
<syntaxhighlight lang="Python"> | <syntaxhighlight lang="Python"> | ||
#! /usr/bin/python3 | #! /usr/bin/python3 | ||
Line 289: | Line 187: | ||
# retrieve results of the query | # retrieve results of the query | ||
for row in cursor: | for row in cursor: | ||
− | print("Episode \" | + | print(f"Episode \"{row[0]}\" votes {row[1]}") |
# close db connection | # close db connection | ||
Line 296: | Line 194: | ||
===write_db.py=== | ===write_db.py=== | ||
− | This script illustrates creating a new database containing a multiplication table | + | This script illustrates creating a new database containing a multiplication table. |
+ | * When we modify the database, it is important to run <tt>commit</tt> command to actually write them to the disk. | ||
<syntaxhighlight lang="Python"> | <syntaxhighlight lang="Python"> | ||
#! /usr/bin/python3 | #! /usr/bin/python3 | ||
Line 313: | Line 212: | ||
for a in range(1,11): | for a in range(1,11): | ||
for b in range(1,11): | for b in range(1,11): | ||
− | cursor.execute("INSERT INTO mult_table (a,b,mult) VALUES (?,?,?)", | + | cursor.execute("INSERT INTO mult_table (a, b, mult) VALUES (?, ?, ?)", |
− | (a,b,a*b)) | + | (a, b, a * b)) |
# important: save the changes | # important: save the changes | ||
Line 327: | Line 226: | ||
sqlite3 multiplication.db "SELECT * FROM mult_table;" | sqlite3 multiplication.db "SELECT * FROM mult_table;" | ||
</syntaxhighlight> | </syntaxhighlight> | ||
− | |||
− |
Latest revision as of 08:48, 14 March 2024
This lecture introduces the basics of the Python programming language and the basics of working with databases using the SQL language and SQLite3 lightweight database system.
The next three lectures
- Computer science and data science students will use Python, SQLite3 and several advanced Python libraries for complex data processing.
- Bioinformatics students will use several bioinformatics command-line tools.
Contents
Overview, documentation
Outline of this lecture
- We introduce a simple data set.
- In a separate tutorial for beginners in Python, we have several Python scripts for processing this data set.
- We introduce basics of working with SQLite3 and writing SQL queries.
- We look at how to combine Python and SQLite.
Python
- Popular programming language
- Advantages: powerful language features, extensive libraries
- Disadvantages: interpreted language, can be slow
- A very concise cheat sheet
- A more detailed tutorial
- Our tutorial
SQL
- SQL is a language for working with relational databases. It is covered in more detail in dedicated database courses.
- We will cover basics of SQL and work with a simple DB system SQLite3.
- Advantages of storing data in a database rather than CSV files and similar:
- A database can contain multiple tables and connect them through shared identifiers.
- You can create indices which allow fast access to records matching your criteria without searching through all data.
- A database system can compute complex queries without loading all data into memory.
- Typical database systems are complex, use server-client architecture. In contrast, SQLite3 is a simple "database" stored in one file.
- It is best used by a single process at a time, although multiple processes can read concurrently.
- It can be easily created, does not need extensive configuration typical for more complex database systems.
- SQLite3 documentation
- SQL tutorial
- SQLite3 in Python documentation
Dataset for this lecture
- IMDb is an online database of movies and TV series with user ratings.
- We have downloaded a preprocessed dataset of selected TV series ratings from GitHub.
- From this dataset, we have selected 10 series with high average number of voting users.
- Data are two files in the CSV format: list of series, list of episodes.
- CSV stands for comma-separated values.
File series.cvs contains one row per series
- Columns: (0) series id, (1) series title, (2) TV channel:
3,Breaking Bad,AMC 2,Sherlock,BBC 1,Game of Thrones,HBO
File episodes.csv contains one row per episode:
- Columns: (0) series id, (1) episode title, (2) episode order within the whole series, (3) season number, (4) episode number within season, (5) user rating, (6) the number of votes
- Here is a sample of 4 episodes from the Game of Thrones series.
- If the episode title contains a comma, the whole title is in quotation marks.
1,"Dark Wings, Dark Words",22,3,2,8.6,12714 1,No One,58,6,8,8.3,20709 1,Battle of the Bastards,59,6,9,9.9,138353 1,The Winds of Winter,60,6,10,9.9,93680
Notes
- A different version of this data was used already in the lecture on Perl.
- A Python tutorial shows several scripts for processing these files which are basically longer versions of some of the SQL queries below.
SQL and SQLite
Creating a database
SQLite3 database is a file with your data stored in a special format. To load our csv files to a SQLite database, run command:
sqlite3 series.db < create_db.sql
Contents of create_db.sql file:
CREATE TABLE series (
id INT,
title TEXT,
channel TEXT
);
.mode csv
.import series.csv series
CREATE TABLE episodes (
seriesId INT,
title TEXT,
orderInSeries INT,
season INT,
orderInSeason INT,
rating REAL,
votes INT
);
.mode csv
.import episodes.csv episodes
- The two CREATE TABLE commands create two tables named series and episodes.
- For each column (attribute) of the table we list its name and type.
- Commands starting with a dot are special SQLite3 commands, not part of SQL itself. Command .import reads a CSV file and stores it in a table.
Other useful SQLite3 commands;
- .schema tableName (lists columns of a given table)
- .mode column and .headers on (turn on human-friendly formatting, not good for further processing)
SQL queries
- Run sqlite3 series.db to get an SQLite command-line where you can interact with your database.
- Then type the queries below which illustrate the basic features of SQL.
- In these queries, we use uppercase for SQL keywords and lowercase for our names of tables and columns (SQL keywords are not case sensitive).
/* switch on human-friendly formatting */
.mode column
.headers on
/* print title of each series (as prog1.py) */
SELECT title FROM series;
/* sort titles alphabetically */
SELECT title FROM series ORDER BY title;
/* find the highest vote number among episodes */
SELECT MAX(votes) FROM episodes;
/* find the episode with the highest number of votes, as prog3.py */
SELECT title, votes FROM episodes
ORDER BY votes DESC LIMIT 1;
/* print all episodes with at least 50k votes, order by votes */
SELECT title, votes FROM episodes
WHERE votes>50000 ORDER BY votes DESC;
/* join series and episodes tables, print 10 episodes
* with the highest number of votes */
SELECT s.title, e.title, votes
FROM episodes AS e, series AS s
WHERE e.seriesId=s.id
ORDER BY votes DESC
LIMIT 10;
/* compute the number of series per channel, as prog2.py */
SELECT channel, COUNT() AS series_count
FROM series GROUP BY channel;
/* print the number of episodes and average rating per season and series */
SELECT seriesId, season, COUNT() AS episode_count, AVG(rating) AS rating
FROM episodes GROUP BY seriesId, season;
Parts of a typical SQL query:
- SELECT followed by column names, or functions MAX, COUNT etc. These columns or expressions are printed for each row of the table, unless filtered out (see below). Individual columns of the output can be given aliases by keyword AS.
- FROM followed by a list of tables. Tables also can get aliases (FROM episodes AS e).
- WHERE followed by a condition used for filtering the rows.
- ORDER BY followed by an expression used for sorting the rows.
- LIMIT followed by the maximum number of rows to print.
More complicated concepts:
- GROUP BY allows to group rows based on common value of some columns and compute statistics per group (count, maximum, sum etc).
- If you list two tables in FROM, you will conceptually create all pairs of rows, one from one table, one from the other. These are then typically filtered in the WHERE clause to only those that have a matching ID (for example WHERE e.seriesId=s.id in one of the queries above).
Accessing a database from Python
We will use sqlite3 library for Python to access data from the database and to process them further in the Python program.
read_db.py
- The following script illustrates running a SELECT query and getting the results.
- To start using a database, we first create objects called Connection and Cursor. We use the cursor to run individual SQl queries.
- If we want to use particular values in the queries, which we have stored in variables, we use placeholders "?" in the query itself and pass the values to the execute command as a tuple.
- sqlite3 module ensures that the values are passed to the database correctly. If we inserted the values directly into the query string, we could face problems if the values contain e.g. quotation marks leading to SQL syntax errors or even SQL injection attacks.
#! /usr/bin/python3
import sqlite3
# connect to a database
connection = sqlite3.connect('series.db')
# create a "cursor" for working with the database
cursor = connection.cursor()
# run a select query
# supply parameters of the query using placeholders ?
threshold = 40000
cursor.execute("""SELECT title, votes FROM episodes
WHERE votes>? ORDER BY votes desc""", (threshold,))
# retrieve results of the query
for row in cursor:
print(f"Episode \"{row[0]}\" votes {row[1]}")
# close db connection
connection.close()
write_db.py
This script illustrates creating a new database containing a multiplication table.
- When we modify the database, it is important to run commit command to actually write them to the disk.
#! /usr/bin/python3
import sqlite3
# connect to a database
connection = sqlite3.connect('multiplication.db')
# create a "cursor" for working with the database
cursor = connection.cursor()
cursor.execute("""
CREATE TABLE mult_table (
a INT, b INT, mult INT)
""")
for a in range(1,11):
for b in range(1,11):
cursor.execute("INSERT INTO mult_table (a, b, mult) VALUES (?, ?, ?)",
(a, b, a * b))
# important: save the changes
connection.commit()
# close db connection
connection.close()
We can check the result by running command
sqlite3 multiplication.db "SELECT * FROM mult_table;"