The SQLite database engine

According to the SQLite home page (https://sqlite.org/index.html), SQLite is a self-contained, high-reliability, embedded, full-featured, public-domain SQL database engine.

SQLite is optimized for use in embedded applications. It is simple to use and quite fast. We need to use the sqlite3 Python module to integrate SQLite with Python. The sqlite3 module is bundled with Python 3, so there is no need to install it. 

We will use the data from the European Soccer Database (https://github.com/hugomathien/football-data-collection) for demonstrative purposes. We assume that you already have a SQL server installed and started:

1. The first step after importing sqlite3 is to create a connection to the database using the connect method:

import sqlite3 
import pandas as pd
connection = sqlite3.connect('database.sqlite')
print("Database opened successfully")

1. The European Soccer Database consists of eight tables. We can use read_sql to read the database table or SQL query into the DataFrame. This prints a list of all the tables in the database:

tables = pd.read_sql("SELECT * FROM sqlite_master WHERE 
        type='table';", connection)
print(tables)

1. Let's read data from the Country table:

countries = pd.read_sql("SELECT * FROM Country;", connection)
countries.head()

1. We can use SQL queries on tables. In the following example, we select players whose height is greater than or equal to 180 and whose weight is greater than or equal to 170:

selected_players = pd.read_sql_query("SELECT * FROM Player WHERE
         height >= 180 AND weight >= 170 ", connection)
print(selected_players)

1. Finally, do not forget to close the connection using the close method:

connection.close()

If you made any changes in the database, you will need to use the commit() method.