This Jupyter Notebook demonstrates how to interact with SQLite databases using Python, with a special focus on CRUD operations, SQL syntax, and real-world examples using country demographic data and Pokémon statistics.
🔸 pandas
🔸 sqlite3
🔸 requests
🔸 matplotlib.pyplot
A database is a structure where we store the necessary information for our applications, websites, or, more generally, for our programs. To access a database, we need a DBMS (Database Management System).
What is a DBMS (Database Management System)? A DBMS is a database management system. There are basically two types of DBMS: the first is relational databases (RDBMS), and the second is non-relational databases (NRDBMS). In this Jupyter notebook, we will focus on designing, coding, and implementing a relational database. Relational databases are an extremely efficient, powerful, and widely used way to create, read, update, and delete all types of data.
SQL with Python Python has many advantages for Data Analysts and Data Scientists. Its wide variety of open-source libraries, ease of learning, and versatility make it an incredibly useful tool for any Data Analyst. So, if we're using Python for data analysis, where does all this data come from?
Thanks to relational databases, we can access and modify the stored data using SQL with the help of an RDBMS.
SQL (Structured Query Language) is a language designed to communicate with databases. Each RDBMS uses a slightly different version of SQL. Therefore, SQL code written for one system may not work directly in another (usually requiring only small changes). However, the concepts, structures, and operations are largely the same.
All software applications interact with data most commonly through a database management system (DBMS). Some programming languages come with built-in modules to interact with a DBMS, while others require third-party packages. Python is a language that can interact with any database through SQL libraries. The most common SQL libraries are SQLite, MySQL, and PostgreSQL. Since SQLite does not require any external SQL modules to be installed in Python, it is probably the simplest database for Python use. Because databases are a vast topic that needs in-depth exploration, in this section, we will only examine the SQLite database.
Unlike other databases, SQLite does not require setting up a server, meaning it can be installed directly alongside any program. So how do we perform operations like table creation, data insertion/deletion, or using operators in this database?
🔹Create: Table creation and data insertion
🔹Read: Data retrieval using SELECT, filtering, sorting, limiting, and string operations
🔹Update: Modifying existing values
🔹Delete: Removing records
🔹SQL_SQLITE_Tutorial_and_Pokemon_Data_Analysis.ipynb: Main notebook containing all code and analysis.
🔹country_codes.xlxs: country data.
🔹pokemons.csv: Pokémon data.