SQLite - freeCodeCamp.org

A Brief Introduction to SQLite

Mark Mahoney — Fri, 19 Sep 2025 14:02:06 +0000

SQLite is one of the most underappreciated tools in a developer's toolkit. It's a full-featured relational database that runs directly in your application. No server setup. No configuration files. No network protocols. Just a simple library that gives you the power of an ACID compliant RDBMS right where you need it.

SQLite powers more applications than you might think. It's in every smartphone, most web browsers, and countless desktop applications. Your phone probably has hundreds of SQLite databases on it right now. Despite handling billions of databases worldwide, many developers aren't familiar with all of the cool things that you can do with SQLite.

This tutorial introduces SQLite through practical examples in C/C++, Python, and Java. You can pick and choose the languages that suit your needs. No language wars here. You'll learn how to integrate SQLite into real applications. Whether you're building a desktop app, a web API, or just need local data storage without the drama of a full database server, SQLite has your back.

Code Playbacks

Code playbacks are a unique way to learn about programming. They are guided walkthroughs of code, allowing you to see not just the code itself but also the thought process behind it. This approach helps you understand not only what the code does, but why it was written that way. Here is a short video to show how to move through a code playback:

By registering on Playback Press, you'll gain access to an AI assistant that can answer your questions about the code. This makes learning even more interactive and personalized. Watch this video to see how to work with it:

A Brief Introduction to SQLite

You can find my entire collection of SQLite code playbacks in my free book, "Programming with SQLite".

Here's what you'll learn:

Chapter 1: Database Design and SQL

In this chapter, I cover the basics of relational database design and SQL. I keep it simple and practical. If you'd like more introductory SQL content like this go to my Intro SQL book. If you'd like some SQL problems to work through, check out 30 Worked SQL Examples. If you already dream in SELECT statements, skip ahead to the chapter that best suits your needs.

Chapter 2: SQLite in C/C++

In this chapter, I discuss how to use the low level SQLite API from a C or C++ program. You have a lot of power when using the API and I cover ACID transactions. Yes, we're going to talk about pointers and memory management. Even if you're not a C/C++ programmer and haven't touched a pointer since college, I recommend looking at this chapter. Understanding what's happening under the hood will make the other chapters clearer. Plus, you can impress your friends at parties by casually mentioning you know how database transactions really work.

Chapter 3: SQLite in Python

Learn how to use SQLite in any Python program including Flask web apps. No ORMs hiding what's really happening. Just clean, direct database access. I cover how to query and create SQLite databases and then show how to build an API using Flask. By the end, you'll have a working web API that didn't require installing PostgreSQL, configuring connection pools, or sacrificing a weekend to database administration.

Chapter 4: SQLite in Java

In this final chapter I give an example in Java using JDBC. Because sometimes you need to write enterprise code, and SQLite works there too. Who says you need Oracle for everything?

4.1 Using a SQLite Database in a Java Program

Conclusion

Ready to explore SQLite? Start with the first playback and see how fun database programming can be. Each example builds on the previous one, giving you practical experience with real code. Real code solving real problems.

I'd love to hear your thoughts! Feel free to share your comments, questions, or feedback via email: mark@playbackpress.com. Your input helps me improve and create even better content.

If you've found this tutorial helpful, consider supporting my work through GitHub Sponsors. Your contributions help cover hosting costs and keep Playback Press free for everyone. Thank you for helping me continue creating educational resources for the developer community!

An Animated Introduction to SQL – Learn to Query Relational Databases

Mark Mahoney — Wed, 16 Apr 2025 15:43:24 +0000

In this tutorial, you’ll learn about the Structured Query Language (SQL), the standard language used to query relational databases.

SQL is not a traditional programming language. In Python or Java, you write step-by-step instructions that tell the computer exactly how to do something. This is called imperative programming.

SQL works differently. First, you understand what data is stored in your tables. Then you write a query describing what data you want and you give it to a database management system. The database management system figures out how to get it. This declarative style is part of what makes SQL powerful (and fun to use).

You don’t need to be a programmer to learn SQL, but having some programming experience helps. Concepts like conditional logic and comparing values to see if something is true or false will feel familiar if you’ve coded before.

If you want an introduction to the basics, check out some of my programming language tutorials listed below.

Tutorial Structure

This tutorial is built around three books of interactive code playbacks I use in my database courses:

Each section of this tutorial includes worked examples where I show how I write SQL step-by-step, explaining my thought process along the way. You’ll see me experiment, refine, and build queries piece by piece, just like a real developer would. I rely heavily on Entity-Relationship Diagrams and Schemas to help me visualize the data stored in the databases.

Here’s how the tutorial is organized:

Part 1: A Whirlwind Tour of SQL

(from Database Design and SQL for Beginners)

I start by exploring a simple database for a fictional pet adoption center called the Paw Prints Adoption Center. I introduce essential concepts like:

Entity-Relationship (ER) diagrams
Schemas
Table structure and relationships

This foundation sets the stage for everything that follows. If you're new to database design, spend some time here before moving on.

Part 2: Core SQL Concepts and Keywords

(also from Database Design and SQL for Beginners)

I cover the most important SQL keywords and ideas. Each topic is explained in its own playback with examples:

CREATE TABLE, ALTER TABLE
SELECT, FROM, WHERE, JOIN
ORDER BY, GROUP BY, HAVING
INSERT, UPDATE, DELETE
Nested queries, Common Table Expressions, and set operations (UNION, INTERSECT, EXCEPT)
Indexes and transactions

This section works as a reference. If you're stuck on a query or forget what a keyword does, come back here.

Part 3: Practice Problems

(from Worked SQL Examples)

Practice is how you really learn. I’ve included 36 practice problems using the Paw Prints database and a new university database. Each problem has an animated playback showing how I worked through the solution step-by-step.

Try writing your own query before watching the solution. Struggling with the problem first will help you learn much more than just watching me provide the answer.

These problems build your skills gradually and help reinforce the use of ER diagrams and schemas in real scenarios.

Part 4: Using SQLite in Programs

(from Programming with SQLite)

In this final optional section, I show how to connect SQL to real code. You’ll learn how to use the SQLite database in:

C/C++
Python and Flask
Java

Running Queries

A database management system (DBMS) is the software used to manage and query data in a database. Many DBMSs require significant configuration and often a separate server to respond to query requests. Setting these up can be challenging for newcomers.

SQLite is a simple DBMS that doesn’t require much setup. It’s a great tool to start with. It doesn’t need a standalone server and stores the entire database in a single file.

To make it easier to view and edit your databases, I recommend using DB Browser for SQLite. It’s a free, open-source tool with a simple interface and all of SQLite’s functionality built in. You can open database files, browse tables, run queries, and edit data using an intuitive user interface. It’s especially useful when you’re learning and want to quickly see how your queries affect the data.

Optional: Web-Based Alternative to DB Browser

If you prefer not to install any software, you can use a web-based tool like SQLite Viewer or SQLite Online. These let you upload a .sqlite file, run queries, and explore a database from your browser.

SQLite Viewer: A simple, read-only viewer. Good for inspecting tables and testing basic queries.
SQLite Online: A full-featured SQLite IDE. You can create databases, upload files, run queries, and even save your work.

Both tools are great for quick experiments or checking your work without installing anything.

Code Playbacks

This tutorial is not a traditional video or static text. Each section includes links to interactive code playbacks that animate how the code or query was built, step-by-step. You can pause and rewind to see each change as it happens.

Each playback includes a narrative, screenshots, whiteboard-style drawings, and self-grading multiple-choice questions to reinforce what you’ve learned.

If you haven’t seen a code playback before, don’t worry. They’re easy to use and allow you to see how queries evolve over time. Here’s a short video showing how to view a code playback:

Playback Press

Playback Press is the platform where I publish my interactive code walkthroughs. Each book includes step-by-step animations, AI tutoring, and built-in quizzes.

I also created Storyteller, the free, open-source tool that powers these playbacks.

AI Tutor

While you're viewing a code playback, you can ask an AI tutor questions about the queries. It gives clear, focused answers and doesn’t rush you. You can also ask it to create new self-grading multiple-choice questions to test your understanding.

To use the AI tutor and quizzes, create a free account on Playback Press and add one of the books to your bookshelf.

Part 1: A Whirlwind Tour of SQL
Part 2: A Beginner’s Reference to SQL
Part 3: Practice Problems — Paw Prints and University Databases
Part 4: Using SQLite in Programs
Conclusion
Comments and Feedback

Part 1: A Whirlwind Tour of SQL

When someone is asked to manage some data, most people's first instinct is to use a spreadsheet. Spreadsheets are easy to use and flexible. But as your data grows more complex, they start to show some weaknesses.

One major issue is redundant data. When the same piece of information appears in multiple places, there's a risk that one copy might change while the others stay the same. This can lead to inconsistencies, errors, and confusing results.

Relational databases help solve this by organizing data in a structured way that reduces redundancy by design. Before building a database, it’s helpful to model the data using an Entity-Relationship (ER) diagram.

Entity-Relationship Diagrams

An ER diagram is a planning tool used to visualize the structure of a database. It helps you figure out what kinds of data you need to store and how those pieces of data relate to each other.

Entities are the main objects or concepts in your system, like Person, Course, or Dog. Entities have attributes that describe them. A Person might have name, date of birth, and address attributes, for example.
Relationships describe how entities are connected. For example, a Person might adopt a Dog, or a Student might enroll in a Course.

By laying this out in a diagram, you can clearly see what data is being stored and how the entities are related to each other. This makes it easier to design the tables in your database correctly.

Here is an example of an ER diagram used in this part of the tutorial:

Schemas

A schema is another way to describe the structure of a database. It shows the same information as the ER diagram, but in a more technical and precise format focused on how the data will actually be stored. Each element of a schema will become a table in a database.

Instead of lines connecting boxes, a schema uses primary keys and foreign keys:

A primary key uniquely identifies each row in a table. Primary keys have a solid underline.
A foreign key refers to the primary key in another table, linking the two together. Foreign keys have a dashed underline.

Here is an example of a schema used in this part of the tutorial:

While an ER diagram is more visual and conceptual, a schema is more concrete and closer to the actual implementation in the DBMS. You’ll see both used throughout this tutorial as I move from planning to writing SQL.

Try It: Explore a Relational Database in Action

To see how these concepts work in practice, take a look at the following three code playbacks. They walk through the design of a relational database for a fictional pet adoption center called Paw Prints. These examples will help you understand how entities, relationships, and schemas come together in a real database and how to write simple SQL queries to explore that data.

Start with the first playback and move through all three in order:

Database Design and Simple SQL: Introduces the Paw Prints database and shows how to write basic SQL queries.
One-to-Many Relationships and More SQL: Covers one-to-many relationships and how to join related tables.
Many-to-Many Relationships and Even More SQL: Shows how to handle many-to-many relationships using join tables and more advanced queries.

As you watch, pause to make sure you understand how the data is structured and how each SQL query is written. You can always refer back to this section later if something in the next chapters isn’t clear.

Part 2: A Beginner’s Reference to SQL

This section takes a closer look at the core SQL commands introduced in the whirlwind tour. Each playback focuses on one topic and shows how to use it through step-by-step examples. I continue to use the Paw Prints database in these examples.

Think of this as a reference section. You don’t need to go through everything in order but you may want to go through them all at least once before beginning to practice in part 3. Come back here whenever you need a refresher on a particular SQL concept.

Here are the key concepts we’ll cover:

CREATE TABLE and ALTER TABLE

Learn how to define tables in a relational database. This playback shows how to create tables from scratch and how to change them later using ALTER TABLE.

INSERT

See how to add new rows of data to a table. This example shows how to use the INSERT command and make sure your data matches the table structure.

SELECT

This playback introduces the SELECT keyword in SQL. You’ll learn how to retrieve specific columns (or attributes) from a table and see what the result set looks like.

FROM

Explore how the FROM clause specifies which tables your data comes from. This sets the stage for combining data from multiple sources using a Cartesian product. You’ll also see how to JOIN tables together.

WHERE

Learn how to filter results using conditions. The WHERE clause helps narrow down the rows returned by a query. This playback also shows how to join tables by matching foreign keys to primary keys.

UPDATE and DELETE

Learn how to change existing data in the database with UPDATE and remove data using DELETE. You’ll also see how to avoid accidental changes by using WHERE conditions carefully.

ORDER BY

Sort your results using ORDER BY. You’ll learn how to control the order of your output using one or more attributes.

Aggregate Operators, GROUP BY, and HAVING

Group rows and calculate summary values using aggregate functions like COUNT, AVG, MIN, MAX, and SUM. This playback also shows how to use GROUP BY and HAVING to work with grouped results.

Nested Queries with IN and Common Table Expressions

Learn how to use nested queries – queries inside other queries – to build more flexible logic. This playback also shows how to write cleaner queries using Common Table Expressions (CTEs).

UNION, INTERSECT, EXCEPT

See how to combine the results of multiple queries. This example shows how UNION, INTERSECT, and EXCEPT help you work with data from different queries as if it were one set.

Transactions

Learn how to group multiple SQL commands into a single transaction, so they all succeed or fail together. Transactions help protect your data from partial updates.

CREATE INDEX

Improve query performance using indexes. This playback shows how to create an index on one or more columns and explains why that makes certain queries run faster.

Part 3: Practice Problems — Paw Prints and University Databases

Now it’s time to apply what you’ve learned.

Below are six practice problems that use the Paw Prints database from earlier examples. If you haven't recreated it yourself, here is a link to the SQLite file dogsFinal.sqlite. Each one asks a specific question that requires you to write a SQL query to find the answer. Try solving each one on your own before watching the solution.

Don’t worry if you don’t get it right on the first try. Writing SQL often involves trial and error, even for experienced developers. The goal is to think through the problem and make progress, not to be perfect. Start small and build your queries up through an iterative process.

Click each link to view the playback after you've made your attempt:

Which Dogs Have Had the Most Visits?

Figure out how to count visits for each dog and sort them to find the most frequently visited ones.

Number of Adoptions and Average Age

Find the total number of adoptions and the average age of adopted dogs. You'll need to filter the data appropriately.

Locations with Least/Most Aggressive Dogs

Use grouping to compare aggression levels across locations and determine where the most and fewest aggressive dogs are housed.

Average Time to Adoption By Location

Calculate the average time it takes for dogs to be adopted, broken down by location.

Finding Available Capacity at Each Location

Determine how much space is left at each shelter location by comparing total capacity to current occupancy.

Who Visited then Adopted an Aggressive Dog

This complex query asks you to track user actions over time, first visiting, then adopting an aggressive dog. A good challenge!

University Database

Next, you’ll work with a more complex database that models a university’s course and grading system. You’ll use it to analyze real-world relationships between students, professors, courses, and departments.

Download the SQLite version of the database here:
studentGrades.sqlite

Here are the entities in the database:

Students
Sections
Courses
Professors
Departments

And here are the relationships between them:

Each student takes zero or more sections. (Every student receives a grade.)
Each section has zero or more students taking it.
Each section is an instance of a course.
Each course has zero or more sections.
Each section is taught by a single professor.
Each professor teaches zero or more sections.
Each professor belongs to zero or more departments.
Each department has zero or more professors.
Each department has, at most, one professor who is its chairperson.
Each professor may chair, at most, one department.
Each course is offered by a department.
Each department offers zero or more courses.

The code playbacks highlight how I use ER diagrams and schemas to help me build my queries. You can preview them here:

Try each query on your own before watching the solution.

Part 4: Using SQLite in Programs

So far, you’ve learned how to write SQL queries and design relational databases. In this optional section, you’ll see how to use SQLite in actual code. Each playback walks through a working program that reads from and writes to a SQLite database.

Why Use SQLite in Programs?

SQLite’s simplicity makes it a great choice for quick projects, prototypes, and small applications. You don’t need to run a server, and everything is stored in a single file.

These playbacks show how to embed SQL directly into your code, so your programs can store and retrieve data as part of their normal workflow. I really love using it in my projects!

Here’s an overview of the playbacks:

C/C++

The C++ SQLite API

Shows how to set up and use the SQLite C++ API. You’ll open a database, run basic queries, and handle results.

An Object-Oriented Auction Program

Builds a more complex C++ program that tracks bids, items, and users. This example shows how to structure a real-world application with SQLite.

SQLite Transactions

Explains how to group multiple database operations into a single transaction. This protects your data from partial updates.

Python and Flask

Querying a SQLite Database

Connects a Python script to a SQLite database, runs queries, and processes the results using Python’s built-in libraries.

Creating SQLite Databases

Shows how to create new SQLite databases directly from Python, defining tables and inserting initial data.

Flask Basics

Introduces Flask. You’ll see how to build a simple web application that can serve pages and connect to your SQLite database.

Creating an API

Takes Flask further by creating a RESTful API that interacts with a SQLite database, letting you perform CRUD operations on a SQLite database.

Java

Using a SQLite Database in a Java Program

Shows how to integrate the SQLite driver into a Java application. You’ll learn how to open a connection, run queries, and handle exceptions properly.

Conclusion

By now, you’ve learned how to read and write real SQL. You’ve seen how to design a database, how to write queries that retrieve and modify data, and how to tackle increasingly complex questions using the tools SQL gives you.

If you worked through the practice problems you’ve already done what many developers do on the job: examine unfamiliar data, figure out the relationships, and write queries to answer real questions.

SQL is a skill that gets sharper the more you use it. Don’t worry if you still feel a little unsure. Repetition and exploration will build your confidence. Keep experimenting, break things, and try to fix them. That’s how you really learn.

If you’re planning to use SQL in your own projects, try building a small database from scratch or connecting SQLite to one of your programs. You’ll learn a lot by seeing what questions your own data brings up.

Thanks for reading. I hope these playbacks helped make SQL a little easier to understand. Good luck with your next SQL project.

Comments and Feedback

You can find all of these code playbacks and more in one of my free books:

Comments and feedback are welcome anytime: mark@playbackpress.com.

If you'd like to support my work and help keep Playback Press free for all, consider donating using GitHub Sponsors. I use all of the donations for hosting costs. Your support helps me continue creating educational content like this. Thank you!

How to Work with SQLite in Python – A Handbook for Beginners

Ashutosh Krishna — Wed, 02 Oct 2024 09:44:37 +0000

SQLite is one of the most popular relational database management systems (RDBMS). It’s lightweight, meaning that it doesn’t take up much space on your system. One of its best features is that it’s serverless, so you don’t need to install or manage a separate server to use it.

Instead, it stores everything in a simple file on your computer. It also requires zero configuration, so there’s no complicated setup process, making it perfect for beginners and small projects.

SQLite is a great choice for small to medium applications because it’s easy to use, fast, and can handle most tasks that bigger databases can do, but without the hassle of managing extra software. Whether you're building a personal project or prototyping a new app, SQLite is a solid option to get things up and running quickly.

In this tutorial, you'll learn how to work with SQLite using Python. Here’s what we’re going to cover in this tutorial:

How to Set Up Your Python Environment
How to Create an SQLite Database
How to Create Database Tables
How to Insert Data into a Table
How to Query Data
How to Update and Delete Data
How to Use Transactions
How to Optimize SQLite Query Performance with Indexing
How to Handle Errors and Exceptions
How to Export and Import Data [Bonus Section]
Wrapping Up

This tutorial is perfect for anyone who wants to get started with databases without diving into complex setups.

How to Set Up Your Python Environment

Before working with SQLite, let’s ensure your Python environment is ready. Here’s how to set everything up.

Installing Python

If you don’t have Python installed on your system yet, you can download it from the official Python website. Follow the installation instructions for your operating system (Windows, macOS, or Linux).

To check if Python is installed, open your terminal (or command prompt) and type:

python --version

This should show the current version of Python installed. If it’s not installed, follow the instructions on the Python website.

Installing SQLite3 Module

The good news is that SQLite3 comes built-in with Python! You don’t need to install it separately because it’s included in the standard Python library. This means you can start using it right away without any additional setup.

How to Create a Virtual Environment (Optional but Recommended)

It’s a good idea to create a virtual environment for each project to keep your dependencies organized. A virtual environment is like a clean slate where you can install packages without affecting your global Python installation.

To create a virtual environment, follow these steps:

First, open your terminal or command prompt and navigate to the directory where you want to create your project.
Run the following command to create a virtual environment:

python -m venv env

Here, env is the name of the virtual environment. You can name it anything you like.

Activate the virtual environment:

# Use the command for Windows
env\Scripts\activate

# Use the command for macOS/Linux:
env/bin/activate

After activating the virtual environment, you’ll notice that your terminal prompt changes, showing the name of the virtual environment. This means you’re now working inside it.

Installing Necessary Libraries

We’ll need a few additional libraries for this project. Specifically, we’ll use:

pandas: This is an optional library for handling and displaying data in tabular format, useful for advanced use cases.
faker: This library will help us generate fake data, like random names and addresses, which we can insert into our database for testing.

To install pandas and faker, simply run the following commands:

pip install pandas faker

This installs both pandas and faker into your virtual environment. With this, your environment is set up, and you’re ready to start creating and managing your SQLite database in Python!

How to Create an SQLite Database

A database is a structured way to store and manage data so that it can be easily accessed, updated, and organized. It’s like a digital filing system that allows you to efficiently store large amounts of data, whether it’s for a simple app or a more complex system. Databases use tables to organize data, with rows and columns representing individual records and their attributes.

How SQLite Databases Work

Unlike most other database systems, SQLite is a serverless database. This means that it doesn’t require setting up or managing a server, making it lightweight and easy to use. All the data is stored in a single file on your computer, which you can easily move, share, or back up. Despite its simplicity, SQLite is powerful enough to handle many common database tasks and is widely used in mobile apps, embedded systems, and small to medium-sized projects.

How to Create a New SQLite Database

Let’s create a new SQLite database and learn how to interact with it using Python’s sqlite3 library.

Connecting to the Database

Since sqlite3 is pre-installed, you just need to import it in your Python script. To create a new database or connect to an existing one, we use the sqlite3.connect() method. This method takes the name of the database file as an argument. If the file doesn’t exist, SQLite will automatically create it.

import sqlite3

# Connect to the SQLite database (or create it if it doesn't exist)
connection = sqlite3.connect('my_database.db')

In this example, a file named my_database.db is created in the same directory as your script. If the file already exists, SQLite will just open the connection to it.

Creating a Cursor

Once you have a connection, the next step is to create a cursor object. The cursor is responsible for executing SQL commands and queries on the database.

# Create a cursor object
cursor = connection.cursor()

Closing the Connection

After you’ve finished working with the database, it’s important to close the connection to free up any resources. You can close the connection with the following command:

# Close the database connection
connection.close()

However, you should only close the connection once you’re done with all your operations.

When you run your Python script, a file named my_database.db will be created in your current working directory. You’ve now successfully created your first SQLite database!

How to Use Context Manager to Open and Close Connections

Python provides a more efficient and cleaner way to handle database connections using the with statement, also known as a context manager. The with statement automatically opens and closes the connection, ensuring that the connection is properly closed even if an error occurs during the database operations. This eliminates the need to manually call connection.close().

Here’s how you can use the with statement to handle database connections:

import sqlite3

# Step 1: Use 'with' to connect to the database (or create one) and automatically close it when done
with sqlite3.connect('my_database.db') as connection:

    # Step 2: Create a cursor object to interact with the database
    cursor = connection.cursor()

    print("Database created and connected successfully!")

# No need to call connection.close(); it's done automatically!

From now on, we’ll use the with statement in our upcoming code examples to manage database connections efficiently. This will make the code more concise and easier to maintain.

How to Create Database Tables

Now that we’ve created an SQLite database and connected to it, the next step is to create tables inside the database. A table is where we’ll store our data, organized in rows (records) and columns (attributes). For this example, we’ll create a table called Students to store information about students, which we’ll reuse in upcoming sections.

To create a table, we use SQL's CREATE TABLE statement. This command defines the table structure, including the column names and the data types for each column.

Here’s a simple SQL command to create a Students table with the following fields:

id: A unique identifier for each student (an integer).
name: The student's name (text).
age: The student's age (an integer).
email: The student's email address (text).

The SQL command to create this table would look like this:

CREATE TABLE Students (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    age INTEGER,
    email TEXT
);

We can execute this CREATE TABLE SQL command in Python using the sqlite3 library. Let’s see how to do that.

import sqlite3

# Use 'with' to connect to the SQLite database and automatically close the connection when done
with sqlite3.connect('my_database.db') as connection:

    # Create a cursor object
    cursor = connection.cursor()

    # Write the SQL command to create the Students table
    create_table_query = '''
    CREATE TABLE IF NOT EXISTS Students (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT NOT NULL,
        age INTEGER,
        email TEXT
    );
    '''

    # Execute the SQL command
    cursor.execute(create_table_query)

    # Commit the changes
    connection.commit()

    # Print a confirmation message
    print("Table 'Students' created successfully!")

IF NOT EXISTS: This ensures that the table is only created if it doesn’t already exist, preventing errors if the table has been created before.
connection.commit(): This saves (commits) the changes to the database.

When you run the Python code above, it will create the Students table in the my_database.db database file. You’ll also see a message in the terminal confirming that the table has been created successfully.

If you’re using Visual Studio Code, you can install the SQLite Viewer extension to view SQLite databases.

Data Types in SQLite and Their Mapping to Python

SQLite supports several data types, which we need to understand when defining our tables. Here’s a quick overview of common SQLite data types and how they map to Python types:

SQLite Data Type	Description	Python Equivalent
INTEGER	Whole numbers	`int`
TEXT	Text strings	`str`
REAL	Floating-point numbers	`float`
BLOB	Binary data (e.g., images, files)	`bytes`
NULL	Represents no value or missing data	`None`

In our Students table:

id is of type INTEGER, which maps to Python’s int.
name and email are of type TEXT, which map to Python’s str.
age is also of type INTEGER, mapping to Python’s int.

How to Insert Data into a Table

Now that we have our Students table created, it’s time to start inserting data into the database. In this section, we’ll cover how to insert both single and multiple records using Python and SQLite, and how to avoid common security issues like SQL injection by using parameterized queries.

How to Insert a Single Record

To insert data into the database, we use the INSERT INTO SQL command. Let’s start by inserting a single record into our Students table.

Here’s the basic SQL syntax for inserting a single record:

INSERT INTO Students (name, age, email) 
VALUES ('John Doe', 20, 'johndoe@example.com');

However, instead of writing SQL directly in our Python script with hardcoded values, we’ll use parameterized queries to make our code more secure and flexible. Parameterized queries help prevent SQL injection, a common attack where malicious users can manipulate the SQL query by passing harmful input.

Here’s how we can insert a single record into the Students table using a parameterized query:

import sqlite3

# Use 'with' to open and close the connection automatically
with sqlite3.connect('my_database.db') as connection:
    cursor = connection.cursor()

    # Insert a record into the Students table
    insert_query = '''
    INSERT INTO Students (name, age, email) 
    VALUES (?, ?, ?);
    '''
    student_data = ('Jane Doe', 23, 'jane@example.com')

    cursor.execute(insert_query, student_data)

    # Commit the changes automatically
    connection.commit()

    # No need to call connection.close(); it's done automatically!
    print("Record inserted successfully!")

The ? placeholders represent the values to be inserted into the table. The actual values are passed as a tuple (student_data) in the cursor.execute() method.

How to Insert Multiple Records

If you want to insert multiple records at once, you can use the executemany() method in Python. This method takes a list of tuples, where each tuple represents one record.

To make our example more dynamic, we can use the Faker library to generate random student data. This is useful for testing and simulating real-world scenarios.

from faker import Faker
import sqlite3

# Initialize Faker
fake = Faker(['en_IN'])

# Use 'with' to open and close the connection automatically
with sqlite3.connect('my_database.db') as connection:
    cursor = connection.cursor()

    # Insert a record into the Students table
    insert_query = '''
    INSERT INTO Students (name, age, email) 
    VALUES (?, ?, ?);
    '''
    students_data = [(fake.name(), fake.random_int(
        min=18, max=25), fake.email()) for _ in range(5)]

    # Execute the query for multiple records
    cursor.executemany(insert_query, students_data)

    # Commit the changes
    connection.commit()

    # Print confirmation message
    print("Fake student records inserted successfully!")

In this code:

Faker() generates random names, ages, and emails for students. Passing the locale([‘en_IN’]) is optional.
cursor.executemany(): This method allows us to insert multiple records at once, making the code more efficient.
students_data: A list of tuples where each tuple represents one student’s data.

How to Handle Common Issues: SQL Injection

SQL injection is a security vulnerability where attackers can insert or manipulate SQL queries by providing harmful input. For example, an attacker might try to inject code like '; DROP TABLE Students; -- to delete the table.

By using parameterized queries (as demonstrated above), we avoid this issue. The ? placeholders in parameterized queries ensure that input values are treated as data, not as part of the SQL command. This makes it impossible for malicious code to be executed.

How to Query Data

Now that we’ve inserted some data into our Students table, let’s learn how to retrieve the data from the table. We'll explore different methods for fetching data in Python, including fetchone(), fetchall(), and fetchmany().

To query data from a table, we use the SELECT statement. Here’s a simple SQL command to select all columns from the Students table:

SELECT * FROM Students;

This command retrieves all records and columns from the Students table. We can execute this SELECT query in Python and fetch the results.

How to Fetch All Records

Here’s how we can fetch all records from the Students table:

import sqlite3

# Use 'with' to connect to the SQLite database
with sqlite3.connect('my_database.db') as connection:

    # Create a cursor object
    cursor = connection.cursor()

    # Write the SQL command to select all records from the Students table
    select_query = "SELECT * FROM Students;"

    # Execute the SQL command
    cursor.execute(select_query)

    # Fetch all records
    all_students = cursor.fetchall()

    # Display results in the terminal
    print("All Students:")
    for student in all_students:
        print(student)

In this example, the fetchall() method retrieves all rows returned by the query as a list of tuples.

All Students:
(1, 'Jane Doe', 23, 'jane@example.com')
(2, 'Bahadurjit Sabharwal', 18, 'tristanupadhyay@example.net')
(3, 'Zayyan Arya', 20, 'yashawinibhakta@example.org')
(4, 'Hemani Shukla', 18, 'gaurikanarula@example.com')
(5, 'Warda Kara', 20, 'npatil@example.net')
(6, 'Mitali Nazareth', 19, 'sparekh@example.org')

How to Fetch a Single Record

If you want to retrieve only one record, you can use the fetchone() method:

import sqlite3

# Use 'with' to connect to the SQLite database
with sqlite3.connect('my_database.db') as connection:

    # Create a cursor object
    cursor = connection.cursor()

    # Write the SQL command to select all records from the Students table
    select_query = "SELECT * FROM Students;"

    # Execute the SQL command
    cursor.execute(select_query)

    # Fetch one record
    student = cursor.fetchone()

    # Display the result
    print("First Student:")
    print(student)

Output:

First Student:
(1, 'Jane Doe', 23, 'jane@example.com')

How to Fetch Multiple Records

To fetch a specific number of records, you can use fetchmany(size):

import sqlite3

# Use 'with' to connect to the SQLite database
with sqlite3.connect('my_database.db') as connection:

    # Create a cursor object
    cursor = connection.cursor()

    # Write the SQL command to select all records from the Students table
    select_query = "SELECT * FROM Students;"

    # Execute the SQL command
    cursor.execute(select_query)

    # Fetch three records
    three_students = cursor.fetchmany(3)

    # Display results
    print("Three Students:")
    for student in three_students:
        print(student)

Output:

Three Students:
(1, 'Jane Doe', 23, 'jane@example.com')
(2, 'Bahadurjit Sabharwal', 18, 'tristanupadhyay@example.net')
(3, 'Zayyan Arya', 20, 'yashawinibhakta@example.org')

How to Use `pandas` for Better Data Presentation

For better data presentation, we can use the pandas library to create a DataFrame from our query results. This makes it easier to manipulate and visualize the data.

Here’s how to fetch all records and display them as a pandas DataFrame:

import sqlite3
import pandas as pd

# Use 'with' to connect to the SQLite database
with sqlite3.connect('my_database.db') as connection:
    # Write the SQL command to select all records from the Students table
    select_query = "SELECT * FROM Students;"

    # Use pandas to read SQL query directly into a DataFrame
    df = pd.read_sql_query(select_query, connection)

# Display the DataFrame
print("All Students as DataFrame:")
print(df)

Output:

All Students as DataFrame:
   id                  name  age                        email
0   1              Jane Doe   23             jane@example.com
1   2  Bahadurjit Sabharwal   18  tristanupadhyay@example.net
2   3           Zayyan Arya   20  yashawinibhakta@example.org
3   4         Hemani Shukla   18    gaurikanarula@example.com
4   5            Warda Kara   20           npatil@example.net
5   6       Mitali Nazareth   19          sparekh@example.org

The pd.read_sql_query() function executes the SQL query and directly returns the results as a pandas DataFrame.

How to Update and Delete Data

In this section, we’ll learn how to update existing records and delete records from our Students table using SQL commands in Python. This is essential for managing and maintaining your data effectively.

Updating Existing Records

To modify existing records in a database, we use the SQL UPDATE command. This command allows us to change the values of specific columns in one or more rows based on a specified condition.

For example, if we want to update a student's age, the SQL command would look like this:

UPDATE Students 
SET age = 21 
WHERE name = 'Jane Doe';

Now, let’s write Python code to update a specific student's age in our Students table.

import sqlite3

# Use 'with' to connect to the SQLite database
with sqlite3.connect('my_database.db') as connection:
    cursor = connection.cursor()

    # SQL command to update a student's age
    update_query = '''
    UPDATE Students 
    SET age = ? 
    WHERE name = ?;
    '''

    # Data for the update
    new_age = 21
    student_name = 'Jane Doe'

    # Execute the SQL command with the data
    cursor.execute(update_query, (new_age, student_name))

    # Commit the changes to save the update
    connection.commit()

    # Print a confirmation message
    print(f"Updated age for {student_name} to {new_age}.")

In this example, we used parameterized queries to prevent SQL injection.

How to Delete Records from the Table

To remove records from a database, we use the SQL DELETE command. This command allows us to delete one or more rows based on a specified condition.

For example, if we want to delete a student named 'Jane Doe', the SQL command would look like this:

DELETE FROM Students 
WHERE name = 'Jane Doe';

Let’s write Python code to delete a specific student from our Students table using the with statement.

import sqlite3

# Use 'with' to connect to the SQLite database
with sqlite3.connect('my_database.db') as connection:
    cursor = connection.cursor()

    # SQL command to delete a student
    delete_query = '''
    DELETE FROM Students 
    WHERE name = ?;
    '''

    # Name of the student to be deleted
    student_name = 'Jane Doe'

    # Execute the SQL command with the data
    cursor.execute(delete_query, (student_name,))

    # Commit the changes to save the deletion
    connection.commit()

    # Print a confirmation message
    print(f"Deleted student record for {student_name}.")

Important Considerations

Conditions: Always use the WHERE clause when updating or deleting records to avoid modifying or removing all rows in the table. Without a WHERE clause, the command affects every row in the table.
Backup: It’s good practice to back up your database before performing updates or deletions, especially in production environments.

How to Use Transactions

A transaction is a sequence of one or more SQL operations that are treated as a single unit of work. In the context of a database, a transaction allows you to perform multiple operations that either all succeed or none at all. This ensures that your database remains in a consistent state, even in the face of errors or unexpected issues.

For example, if you are transferring money between two bank accounts, you would want both the debit from one account and the credit to the other to succeed or fail together. If one operation fails, the other should not be executed to maintain consistency.

Why Use Transactions?

Atomicity: Transactions ensure that a series of operations are treated as a single unit. If one operation fails, none of the operations will be applied to the database.
Consistency: Transactions help maintain the integrity of the database by ensuring that all rules and constraints are followed.
Isolation: Each transaction operates independently of others, preventing unintended interference.
Durability: Once a transaction is committed, the changes are permanent, even in the event of a system failure.

When to Use Transactions?

You should use transactions when:

Performing multiple related operations that must succeed or fail together.
Modifying critical data that requires consistency and integrity.
Working with operations that can potentially fail, such as financial transactions or data migrations.

How to Manage Transactions in Python

In SQLite, transactions are managed using the BEGIN, COMMIT, and ROLLBACK commands. However, when using the sqlite3 module in Python, you typically manage transactions through the connection object.

Starting a Transaction

A transaction begins implicitly when you execute any SQL statement. To start a transaction explicitly, you can use the BEGIN command:

cursor.execute("BEGIN;")

However, it’s usually unnecessary to start a transaction manually, as SQLite starts a transaction automatically when you execute an SQL statement.

How to Commit a Transaction

To save all changes made during a transaction, you use the commit() method. This makes all modifications permanent in the database.

connection.commit()

We have already used the commit() method in the above provided examples.

Rolling Back a Transaction

If something goes wrong and you want to revert the changes made during a transaction, you can use the rollback() method. This will undo all changes made since the transaction started.

connection.rollback()

Example of Using Transactions in Python

To illustrate the use of transactions in a real-world scenario, we’ll create a new table called Customers to manage customer accounts. In this example, we’ll assume each customer has a balance. We will add two customers to this table and perform a funds transfer operation between them.

First, let's create the Customers table and insert two customers:

import sqlite3

# Create the Customers table and add two customers
with sqlite3.connect('my_database.db') as connection:
    cursor = connection.cursor()

    # Create Customers table
    create_customers_table = '''
    CREATE TABLE IF NOT EXISTS Customers (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT NOT NULL UNIQUE,
        balance REAL NOT NULL
    );
    '''
    cursor.execute(create_customers_table)

    # Insert two customers
    cursor.execute(
        "INSERT INTO Customers (name, balance) VALUES (?, ?);", ('Ashutosh', 100.0))
    cursor.execute(
        "INSERT INTO Customers (name, balance) VALUES (?, ?);", ('Krishna', 50.0))

    connection.commit()

Now, let’s perform the funds transfer operation between Ashutosh and Krishna:

import sqlite3


def transfer_funds(from_customer, to_customer, amount):
    with sqlite3.connect('my_database.db') as connection:
        cursor = connection.cursor()

        try:
            # Start a transaction
            cursor.execute("BEGIN;")

            # Deduct amount from the sender
            cursor.execute(
                "UPDATE Customers SET balance = balance - ? WHERE name = ?;", (amount, from_customer))
            # Add amount to the receiver
            cursor.execute(
                "UPDATE Customers SET balance = balance + ? WHERE name = ?;", (amount, to_customer))

            # Commit the changes
            connection.commit()
            print(
                f"Transferred {amount} from {from_customer} to {to_customer}.")

        except Exception as e:
            # If an error occurs, rollback the transaction
            connection.rollback()
            print(f"Transaction failed: {e}")


# Example usage
transfer_funds('Ashutosh', 'Krishna', 80.0)

In this example, we first created a Customers table and inserted two customers, Ashutosh with a balance of ₹100, and Krishna with a balance of ₹50. We then performed a funds transfer of ₹80 from Ashutosh to Krishna. By using transactions, we ensure that both the debit from Ashutosh's account and the credit to Krishna's account are executed as a single atomic operation, maintaining data integrity in the event of any errors. If the transfer fails (for example, due to insufficient funds), the transaction will roll back, leaving both accounts unchanged.

How to Optimize SQLite Query Performance with Indexing

Indexing is a powerful technique used in databases to improve query performance. An index is essentially a data structure that stores the location of rows based on specific column values, much like an index at the back of a book helps you quickly locate a topic.

Without an index, SQLite has to scan the entire table row by row to find the relevant data, which becomes inefficient as the dataset grows. By using an index, SQLite can jump directly to the rows you need, significantly speeding up query execution.

How to Populate the Database with Fake Data

To effectively test the impact of indexing, we need a sizable dataset. Instead of manually adding records, we can use the faker library to quickly generate fake data. In this section, we’ll generate 10,000 fake records and insert them into our Students table. This will simulate a real-world scenario where databases grow large, and query performance becomes important.

We will use the executemany() method to insert the records as below:

import sqlite3
from faker import Faker

# Initialize the Faker library
fake = Faker(['en_IN'])


def insert_fake_students(num_records):
    """Generate and insert fake student data into the Students table."""
    fake_data = [(fake.name(), fake.random_int(min=18, max=25),
                  fake.email()) for _ in range(num_records)]

    # Use 'with' to handle the database connection
    with sqlite3.connect('my_database.db') as connection:
        cursor = connection.cursor()

        # Insert fake data into the Students table
        cursor.executemany('''
        INSERT INTO Students (name, age, email) 
        VALUES (?, ?, ?);
        ''', fake_data)

        connection.commit()

    print(f"{num_records} fake student records inserted successfully.")


# Insert 10,000 fake records into the Students table
insert_fake_students(10000)

By running this script, 10,000 fake student records will be added to the Students table. In the next section, we'll query the database and compare the performance of queries with and without indexing.

How to Query Without Indexes

In this section, we’ll query the Students table without any indexes to observe how SQLite performs when there are no optimizations in place. This will serve as a baseline to compare the performance when we add indexes later.

Without indexes, SQLite performs a full table scan, which means that it must check every row in the table to find matching results. For small datasets, this is manageable, but as the number of records grows, the time taken to search increases dramatically. Let’s see this in action by running a basic SELECT query to search for a specific student by name and measure how long it takes.

First, we’ll query the Students table by looking for a student with a specific name. We’ll log the time taken to execute the query using Python’s time module to measure the performance.

import sqlite3
import time


def query_without_index(search_name):
    """Query the Students table by name without an index and measure the time taken."""

    # Connect to the database using 'with'
    with sqlite3.connect('my_database.db') as connection:
        cursor = connection.cursor()

        # Measure the start time
        start_time = time.perf_counter_ns()

        # Perform a SELECT query to find a student by name
        cursor.execute('''
        SELECT * FROM Students WHERE name = ?;
        ''', (search_name,))

        # Fetch all results (there should be only one or a few in practice)
        results = cursor.fetchall()

        # Measure the end time
        end_time = time.perf_counter_ns()

        # Calculate the total time taken
        elapsed_time = (end_time - start_time) / 1000

        # Display the results and the time taken
        print(f"Query completed in {elapsed_time:.5f} microseconds.")
        print("Results:", results)


# Example: Searching for a student by name
query_without_index('Ojasvi Dhawan')

Here’s the output:

Query completed in 1578.10000 microseconds.
Results: [(104, 'Ojasvi Dhawan', 21, 'lavanya26@example.com')]

By running the above script, you'll see how long it takes to search the Students table without any indexes. For example, if there are 10,000 records in the table, the query might take 1000-2000 microseconds depending on the size of the table and your hardware. This may not seem too slow for a small dataset, but the performance will degrade as more records are added.

We use time.perf_counter_ns() to measure the time taken for the query execution in nanoseconds. This method is highly accurate for benchmarking small time intervals. We convert the time to microseconds(us) for easier readability.

Introducing the Query Plan

When working with databases, understanding how queries are executed can help you identify performance bottlenecks and optimize your code. SQLite provides a helpful tool for this called EXPLAIN QUERY PLAN, which allows you to analyze the steps SQLite takes to retrieve data.

In this section, we’ll introduce how to use EXPLAIN QUERY PLAN to visualize and understand the inner workings of a query—specifically, how SQLite performs a full table scan when no index is present.

Let’s use EXPLAIN QUERY PLAN to see how SQLite retrieves data from the Students table without any indexes. We’ll search for a student by name, and the query plan will reveal the steps SQLite takes to find the matching rows.

import sqlite3


def explain_query(search_name):
    """Explain the query execution plan for a SELECT query without an index."""

    with sqlite3.connect('my_database.db') as connection:
        cursor = connection.cursor()

        # Use EXPLAIN QUERY PLAN to analyze how the query is executed
        cursor.execute('''
        EXPLAIN QUERY PLAN
        SELECT * FROM Students WHERE name = ?;
        ''', (search_name,))

        # Fetch and display the query plan
        query_plan = cursor.fetchall()

        print("Query Plan:")
        for step in query_plan:
            print(step)


# Example: Analyzing the query plan for searching by name
explain_query('Ojasvi Dhawan')

When you run this code, SQLite will return a breakdown of how it plans to execute the query. Here’s an example of what the output might look like:

Query Plan:
(2, 0, 0, 'SCAN Students')

This indicates that SQLite is scanning the entire Students table (a full table scan) to find the rows where the name column matches the provided value (Ojasvi Dhawan). Since there is no index on the name column, SQLite must examine each row in the table.

How to Create an Index

Creating an index on a column allows SQLite to find rows more quickly during query operations. Instead of scanning the entire table, SQLite can use the index to jump directly to the relevant rows, significantly speeding up queries—especially those involving large datasets.

To create an index, use the following SQL command:

CREATE INDEX IF NOT EXISTS index-name ON table (column(s));

In this example, we will create an index on the name column of the Students table. Here’s how you can do it using Python:

import sqlite3
import time


def create_index():
    """Create an index on the name column of the Students table."""
    with sqlite3.connect('my_database.db') as connection:
        cursor = connection.cursor()

        # SQL command to create an index on the name column
        create_index_query = '''
        CREATE INDEX IF NOT EXISTS idx_name ON Students (name);
        '''

        # Measure the start time
        start_time = time.perf_counter_ns()

        # Execute the SQL command to create the index
        cursor.execute(create_index_query)

        # Measure the start time
        end_time = time.perf_counter_ns()

        # Commit the changes
        connection.commit()

        print("Index on 'name' column created successfully!")

        # Calculate the total time taken
        elapsed_time = (end_time - start_time) / 1000

        # Display the results and the time taken
        print(f"Query completed in {elapsed_time:.5f} microseconds.")


# Call the function to create the index
create_index()

Output:

Index on 'name' column created successfully!
Query completed in 102768.60000 microseconds.

Even though creating the index takes this long (102768.6 microseconds), it's a one-time operation. You will still get substantial speed-up when running multiple queries. In the following sections, we will query the database again to observe the performance improvements made possible by this index.

How to Query with Indexes

In this section, we will perform the same SELECT query we executed earlier, but this time we will take advantage of the index we created on the name column of the Students table. We'll measure and log the execution time to observe the performance improvements provided by the index.

import sqlite3
import time


def query_with_index(student_name):
    """Query the Students table using an index on the name column."""
    with sqlite3.connect('my_database.db') as connection:
        cursor = connection.cursor()

        # SQL command to select a student by name
        select_query = 'SELECT * FROM Students WHERE name = ?;'

        # Measure the execution time
        start_time = time.perf_counter_ns()  # Start the timer

        # Execute the query with the provided student name
        cursor.execute(select_query, (student_name,))
        result = cursor.fetchall()  # Fetch all results

        end_time = time.perf_counter_ns()  # End the timer

        # Calculate the elapsed time in microseconds
        execution_time = (end_time - start_time) / 1000

        # Display results and execution time
        print(f"Query result: {result}")
        print(f"Execution time with index: {execution_time:.5f} microseconds")


# Example: Searching for a student by name
query_with_index('Ojasvi Dhawan')

Here’s what we get in the output:

Query result: [(104, 'Ojasvi Dhawan', 21, 'lavanya26@example.com')]
Execution time with index: 390.70000 microseconds

We can observe a significant reduction in execution time compared to when the query was performed without an index.

Let’s analyze the query execution plan for the query with the index on the name column of the Students table. If you execute the same script again to explain the query, you’ll get the below output:

Query Plan:
(3, 0, 0, 'SEARCH Students USING INDEX idx_name (name=?)')

The plan now shows that the query uses the index idx_name, significantly reducing the number of rows that need to be scanned, which leads to faster query execution.

Comparing Performance Results

Now, let's summarize the performance results we obtained when querying with and without indexes.

Execution Time Comparison

Query Type	Execution Time (microseconds)
Without Index	1578.1
With Index	390.7

Performance Improvement Summary

The query with the index is approximately 4.04 times faster than the query without the index.
The execution time improved by about 75.24% after adding the index.

Best Practices for Using Indexes

Indexes can significantly enhance the performance of your SQLite database, but they should be used judiciously. Here are some best practices to consider when working with indexes:

When and Why to Use Indexes

Frequent Query Columns: Use indexes on columns that are frequently used in SELECT queries, especially those used in WHERE, JOIN, and ORDER BY clauses. This is because indexing these columns can drastically reduce query execution time.
Uniqueness Constraints: When you have columns that must hold unique values (like usernames or email addresses), creating an index can enforce this constraint efficiently.
Large Datasets: For tables with a large number of records, indexes become increasingly beneficial. They enable quick lookups, which is essential for maintaining performance as your data grows.
Composite Indexes: Consider creating composite indexes for queries that filter or sort by multiple columns. For example, if you often search for students by both name and age, an index on both columns can optimize such queries.

Potential Downsides of Indexes

While indexes provide significant advantages, there are some potential downsides:

Slower Insert/Update Operations: When you insert or update records in a table with indexes, SQLite must also update the index, which can slow down these operations. This is because each insert or update requires additional overhead to maintain the index structure.
Increased Storage Requirements: Indexes consume additional disk space. For large tables, the storage cost can be substantial. Consider this when designing your database schema, especially for systems with limited storage resources.
Complex Index Management: Having too many indexes can complicate database management. It may lead to situations where you have redundant indexes, which can degrade performance rather than enhance it. Regularly reviewing and optimizing your indexes is a good practice.

Indexes are powerful tools for optimizing database queries, but they require careful consideration. Striking a balance between improved read performance and the potential overhead on write operations is key. Here are some strategies for achieving this balance:

Monitor Query Performance: Use SQLite’s EXPLAIN QUERY PLAN to analyze how your queries perform with and without indexes. This can help identify which indexes are beneficial and which may be unnecessary.
Regular Maintenance: Periodically review your indexes and assess whether they are still needed. Remove redundant or rarely used indexes to streamline your database operations.
Test and Evaluate: Before implementing indexes in a production environment, conduct thorough testing to understand their impact on both read and write operations.

By following these best practices, you can leverage the benefits of indexing while minimizing potential drawbacks, ultimately enhancing the performance and efficiency of your SQLite database.

How to Handle Errors and Exceptions

In this section, we’ll discuss how to handle errors and exceptions when working with SQLite in Python. Proper error handling is crucial for maintaining the integrity of your database and ensuring that your application behaves predictably.

Common Errors in SQLite Operations

When interacting with an SQLite database, several common errors may arise:

Constraint Violations: This occurs when you try to insert or update data that violates a database constraint, such as primary key uniqueness or foreign key constraints. For example, trying to insert a duplicate primary key will trigger an error.
Data Type Mismatches: Attempting to insert data of the wrong type (for example, inserting a string where a number is expected) can lead to an error.
Database Locked Errors: If a database is being written to by another process or connection, trying to access it can result in a "database is locked" error.
Syntax Errors: Mistakes in your SQL syntax will result in errors when you try to execute your commands.

How to Use Python's Exception Handling

Python’s built-in exception handling mechanisms (try and except) are essential for managing errors in SQLite operations. By using these constructs, you can catch exceptions and respond appropriately without crashing your program.

Here’s a basic example of how to handle errors when inserting data into the database:

import sqlite3


def add_customer_with_error_handling(name, balance):
    """Add a new customer with error handling."""
    try:
        with sqlite3.connect('my_database.db') as connection:
            cursor = connection.cursor()
            cursor.execute(
                "INSERT INTO Customers (name, balance) VALUES (?, ?);", (name, balance))
            connection.commit()
            print(f"Added customer: {name} with balance: {balance}")

    except sqlite3.IntegrityError as e:
        print(f"Error: Integrity constraint violated - {e}")

    except sqlite3.OperationalError as e:
        print(f"Error: Operational issue - {e}")

    except Exception as e:
        print(f"An unexpected error occurred: {e}")


# Example usage
add_customer_with_error_handling('Vishakha', 100.0)  # Valid
add_customer_with_error_handling('Vishakha', 150.0)  # Duplicate entry

In this example:

We catch IntegrityError, which is raised for violations like unique constraints.
We catch OperationalError for general database-related issues (like database locked errors).
We also have a generic except block to handle any unexpected exceptions.

Output:

Added customer: Vishakha with balance: 100.0
Error: Integrity constraint violated - UNIQUE constraint failed: Customers.name

Best Practices for Ensuring Database Integrity

Use Transactions: Always use transactions (as discussed in the previous section) when performing multiple related operations. This helps ensure that either all operations succeed or none do, maintaining consistency.
Validate Input Data: Before executing SQL commands, validate the input data to ensure it meets the expected criteria (for example, correct types, within allowable ranges).
Catch Specific Exceptions: Always catch specific exceptions to handle different types of errors appropriately. This allows for clearer error handling and debugging.
Log Errors: Instead of just printing errors to the console, consider logging them to a file or monitoring system. This will help you track issues in production.
Graceful Degradation: Design your application to handle errors gracefully. If an operation fails, provide meaningful feedback to the user rather than crashing the application.
Regularly Backup Data: Regularly back up your database to prevent data loss in case of critical failures or corruption.
Use Prepared Statements: Prepared statements help prevent SQL injection attacks and can also provide better performance for repeated queries.

How to Export and Import Data [Bonus Section]

In this section, we will learn how to export data from an SQLite database to common formats like CSV and JSON, as well as how to import data into SQLite from these formats using Python. This is useful for data sharing, backup, and integration with other applications.

Exporting Data from SQLite to CSV

Exporting data to a CSV (Comma-Separated Values) file is straightforward with Python’s built-in libraries. CSV files are widely used for data storage and exchange, making them a convenient format for exporting data.

Here’s how to export data from an SQLite table to a CSV file:

import sqlite3
import csv

def export_to_csv(file_name):
    """Export data from the Customers table to a CSV file."""
    with sqlite3.connect('my_database.db') as connection:
        cursor = connection.cursor()

        # Execute a query to fetch all customer data
        cursor.execute("SELECT * FROM Customers;")
        customers = cursor.fetchall()

        # Write data to CSV
        with open(file_name, 'w', newline='') as csv_file:
            csv_writer = csv.writer(csv_file)
            csv_writer.writerow(['ID', 'Name', 'Balance'])  # Writing header
            csv_writer.writerows(customers)  # Writing data rows

        print(f"Data exported successfully to {file_name}.")

# Example usage
export_to_csv('customers.csv')

How to Export Data to JSON

Similarly, you can export data to a JSON (JavaScript Object Notation) file, which is a popular format for data interchange, especially in web applications.

Here’s an example of how to export data to JSON:

import json
import sqlite3


def export_to_json(file_name):
    """Export data from the Customers table to a JSON file."""
    with sqlite3.connect('my_database.db') as connection:
        cursor = connection.cursor()

        # Execute a query to fetch all customer data
        cursor.execute("SELECT * FROM Customers;")
        customers = cursor.fetchall()

        # Convert data to a list of dictionaries
        customers_list = [{'ID': customer[0], 'Name': customer[1],
                           'Balance': customer[2]} for customer in customers]

        # Write data to JSON
        with open(file_name, 'w') as json_file:
            json.dump(customers_list, json_file, indent=4)

        print(f"Data exported successfully to {file_name}.")


# Example usage
export_to_json('customers.json')

How to Import Data into SQLite from CSV

You can also import data from a CSV file into an SQLite database. This is useful for populating your database with existing datasets.

Here's how to import data from a CSV file:

import csv
import sqlite3


def import_from_csv(file_name):
    """Import data from a CSV file into the Customers table."""
    with sqlite3.connect('my_database.db') as connection:
        cursor = connection.cursor()

        # Open the CSV file for reading
        with open(file_name, 'r') as csv_file:
            csv_reader = csv.reader(csv_file)
            next(csv_reader)  # Skip the header row

            # Insert each row into the Customers table
            for row in csv_reader:
                cursor.execute(
                    "INSERT INTO Customers (name, balance) VALUES (?, ?);", (row[1], row[2]))

        connection.commit()
        print(f"Data imported successfully from {file_name}.")


# Example usage
import_from_csv('customer_data.csv')

How to Import Data into SQLite from JSON

Similarly, importing data from a JSON file is simple. You can read the JSON file and insert the data into your SQLite table.

Here's how to do it:

import json
import sqlite3


def import_from_json(file_name):
    """Import data from a JSON file into the Customers table."""
    with sqlite3.connect('my_database.db') as connection:
        cursor = connection.cursor()

        # Open the JSON file for reading
        with open(file_name, 'r') as json_file:
            customers_list = json.load(json_file)

            # Insert each customer into the Customers table
            for customer in customers_list:
                cursor.execute("INSERT INTO Customers (name, balance) VALUES (?, ?);", (customer['Name'], customer['Balance']))

        connection.commit()
        print(f"Data imported successfully from {file_name}.")


# Example usage
import_from_json('customer_data.json')

Wrapping Up

And that’s a wrap! This guide has introduced you to the fundamentals of working with SQLite in Python, covering everything from setting up your environment to querying and manipulating data, as well as exporting and importing information. I hope you found it helpful and that it has sparked your interest in using SQLite for your projects.

Now it's time to put your newfound knowledge into practice! I encourage you to create your project using SQLite and Python. Whether it’s a simple application for managing your library, a budgeting tool, or something unique, the possibilities are endless.

Once you’ve completed your project, share it on Twitter and tag me! I’d love to see what you’ve created and celebrate your accomplishments.

You can find all the code from this tutorial on GitHub. Thank you for following along, and happy coding!

Generate Table of Contents for your freeCodeCamp articles for free using the TOC Generator tool.

How to Use SQLite with Python

Eesa Zahed — Tue, 21 Feb 2023 21:41:41 +0000

Databases are a crucial component in software development. After all, we need to collect data in a location where we can digitally access it for reading, writing, updating, and deleting.

In this tutorial, you'll learn how to use SQLite with Python. Learning SQLite is a great way to learn how databases operate and how to perform basic CRUD (create, read, update, delete) operations.

Many software developer positions involve working with databases, and if you ever consider creating a full-scale application (such as a social media app or an online game), you'll definitely need a database too.

This tutorial will explain many basic concepts and simple operations, so that you can understand how to work with databases better.

What is SQLite?

SQLite is an embedded SQL (Structured Query Language) database engine library that works with many languages.

According to the official website, SQL text is compiled into bytecode, which is then run by a virtual machine. Therefore, it is extremely fast and can efficiently handle complex queries.

A SQLite database is stored as a disk file, similar to a CSV (comma-separated values) file. But SQLite has many advantages over using a CSV file:

It is written using C. C is a statically-typed, compiled language which is much faster than most languages, including Python.
It’s lightweight, so it performs better and faster than reading from a CSV file.
It’s easy to set up
It can handle more complex queries.
It’s more useful to learn, in case you are ever tasked with using SQL or MySQL in the future.

How to Setup SQLite

Here is an example of using SQLite with Python. I’m using Replit’s online IDE, but you are welcome to follow along on any IDE you like.

First, I’ll create a Python project with a main.py file. I’ll be using CS50’s SQL library, which you can install by running pip3 install cs50.

The first step is to create a database.db file in the root directory, which you can do by entering the following command in the terminal:

touch database.db

At this point, the following code should be added to main.py:

from cs50 import SQL

db = SQL("sqlite:///database.db")

How to create a database table

The next step is to create a table in the database. SQL stores data in tables, which are similar to tables found in Excel or Google Sheets. The code for this is:

db.execute("CREATE TABLE IF NOT EXISTS users (name TEXT, age NUMBER, fav_food STRING)")

To break this down, db is the database that the data is written to. Next, a command gets executed. If the table users doesn’t exist, create a table with the name users, with the column names name, age, and fav_food, with the data types for each value specified.

How to write to the database

You can use the INSERT operation to add a user.

db.execute("INSERT INTO users (name, age, fav_food) VALUES(?, ?, ?)", "eesa", 14, "pizza")

The value “eesa” gets inserted into the name column, the value 14 is inserted into the age column, and the value “pizza” is inserted into the fav_food column.

The code for adding another user (in this case, Bob), would be this:

db.execute("INSERT INTO users (name, age, fav_food) VALUES(?, ?, ?)", "bob", 20, "burgers")

How to read from the database

After this, we can attempt to read all the users from the database. You can do this by running the following code.

people = db.execute("SELECT * FROM users")
print(people) # [{'name': 'eesa', 'age': 14, 'fav_food': 'pizza'}]

The code above is fairly straightforward. The * in the SELECT statement selects everything that’s in the database.

To only select specific values, you can use the DISTINCT statement. Say for example, you only want the favorite food of each user. You can do this by running the following code:

people = db.execute("SELECT DISTINCT fav_food FROM users")
print(people)

You can also separate values using commas in a SELECT DISTINCT query:

people = db.execute("SELECT DISTINCT age, fav_food FROM users")
print(people)

What if we wanted to just read the data for Bob, and ignore everyone else? You can do this by using the SQL WHERE Clause:

people = db.execute("SELECT * FROM users WHERE name='bob'")
print(people)

How about for more complex queries? You can do this using the AND, OR and NOT syntax. You can separate WHERE clauses with these keywords for more complex queries.

people = db.execute("SELECT * FROM users WHERE name='bob' AND age=20")
print(people)

This will print out the data for Bob, because Bob is 20.

How to update a row in the database

To update a row, you can use the UPDATE statement like this:

db.execute("UPDATE users SET fav_food='shawarma' WHERE name='eesa'")

How to delete a row in the database

To delete a row, use the DELETE Syntax (as you might’ve guessed). It looks like this:

db.execute("DELETE FROM users WHERE name='bob'") # goodbye bob

people = db.execute("SELECT * FROM users")
print(people) # [{'name': 'eesa', 'age': 14, 'fav_food': 'shawarma'}]

To delete all the rows in the table, just remove the WHERE clause:

db.execute("DELETE FROM users") # :(

people = db.execute("SELECT * FROM users")
print(people) # []

Conclusion

And that’s it for now. For more information on SQLite, I'd recommend checking out the official documentation. I wish you the best in creating amazing things!

Feel free to check out my GitHub and Replit to view my projects.

If you'd like to reach out, my email address is eszhd1 (at) gmail.com

How to Build a Node.js Database using Prisma and SQLite

Gaël Thomas — Wed, 11 Aug 2021 15:24:42 +0000

Lately I've been seeing many tweets and articles about Prisma. It's a modern ORM (Object-Relational-Mapping tool) that works with Node.jsand TypeScript.

Yes, this library will help you build and manage your Node.js database – and it's compatible with TypeScript! It will generate all the types of your entities automatically.

The schema definition is easy to read by humans – no more headache there. You will see how that works in the coming sections.

Also, the ORM works well with Next.js, GraphQL, Nest.Js, Express.js, Apollo, and Hapi.

To summarize, Prisma is a modern ORM that plays nice with all the trending tech stacks.

That is why I decided to try it and to replace my previous database management library: TypeORM.

"Prisma helps app developers build faster and make fewer errors with an open source ORM for PostgreSQL, MySQL and SQLite." – Prisma homepage

Let's build a simple Twitter database using Node, Prisma, and SQLite

Time to practice. I will show you how to build your first Node.js database using Prisma. To keep this introduction accessible, we will use Node with SQLite.

SQLite is a self-contained database engine. This means that you don't need to configure a database on your computer. The project will run by itself if you follow the steps of this tutorial.

If, in the future, you want to use Prisma with a PostgreSQL database, here is a tutorial on creating a PostgreSQL database using Docker-Compose.

Prerequisites

Node.js (12.2 or higher)

Before getting started, take the time to double-check if you have version 12.2 or higher of Node.js. If you don't, just update your Node before starting the next section.

Note: If you want to check your Node.js version, you can type: node -v in a terminal. The output will be the version.

Basic SQL knowledge

Even though I'm taking a simple approach to this new library, I recommend that you have basic SQL knowledge to fully understand the tutorial.

Note: You don't need to be an expert! Only the basics like creating a table and making some requests are essential here.

How to Set Up a Basic Twitter Project

First, you need to create a new folder for this project and move into it:

$ mkdir minimalistic-twitter
$ cd minimalistic-twitter

Then, we will install all the mandatory dependencies such as TypeScript and Prisma.

$ npm init -y
$ npm install prisma typescript ts-node @types/node --save-dev
$ npm install @prisma/client

From now, you should see a node_modules folder and a package.json file in your repository.

Before moving on to the Prisma initialization, the last configuration step is to create a configuration for TypeScript at the repository root.

To do so, you can create a tsconfig.json and paste the following configuration:

{
  "compilerOptions": {
    "sourceMap": true,
    "outDir": "dist",
    "strict": true,
    "lib": ["esnext"],
    "esModuleInterop": true
  }
}

There we go! It's time to use Prisma in our project. In the minimalistic-twitter folder, you can use the following command to prompt the Prisma help output.

$ npx prisma

Now, the last step before building our minimalistic Twitter app is to initialize the database configuration.

We will use the init command but with a --datasource-provider parameter to set the database type. Otherwise, by default, init will create a PostgreSQL database.

$ npx prisma init --datasource-provider sqlite

When the command finishes executing, you should find in your repository a .env file and a prisma folder with a schema.prisma file inside of it.

The schema.prisma file contains all the instructions to connect to your database. Later it will also include the instructions to generate your database tables.

The .env file contains all the environment variable that your project needs to run. For Prisma, the only variable is DATABASE_URL. Its value is set to ./dev.db .

The dev.db file will be the self-contained database file.

Project tree after the project initialization

If you have the same output, congrats, it means that your project is ready! 🎉

How to Build Our First Model – User

Our basic Twitter database will consist of two main entities:

A user entity with the user information and its tweets
A tweet entity with the tweet content and its author

First, we'll focus on user entity creation. Each of them has:

an id
a unique email (two users can't have the same email)
a username
a list of tweets

With Prisma, if we want to define a new schema (model), we need to do it in the schema.prisma file.

To define an entity, we will use the model instruction as below. You can reproduce it after the database connection instruction in your schema.prisma file.

// After the database connection

model User {
  // We set an `id` variable
  // With an `Int` type (number)
  // Then, we set the Prisma decorators:
  // - @id (because it's an ID)
  // - @default(autoincrement()) (default value is auto-incremented)
  id Int @id @default(autoincrement())

  // We set an `email` variable
  // With a `String` type
  // Then, we set the Prisma decorator:
  // - @unique (because we want the user to be unique
  // based on the email - two users can't have the same)
  email String @unique

  // We set a `username` variable
  // With a `String` type
  username String

  // We set a `tweets` variable
  // With a `Tweet[]` type (one-to-many relationship)
  // Because each user can have between
  // 0 and an infinite number of tweets
  tweets Tweet[]
}

As you might notice, we don't have the Tweet model yet. It will be our next step.

How to Build Our Second Model – Tweet

Now that we have users, we need tweets. Let's follow the same process as before, but this time for the Tweet entity.

Each of them has:

an id
a creation date
a text
an userId (tweet's author)

Below, you will find the entity. You can reproduce it after the User model declaration in your schema.prisma file.

// After the database connection

// After the User model

model Tweet {
  // We set an `id` variable
  // With an `Int` type (number)
  // Then, we set the Prisma decorators:
  // - @id (because it's an ID)
  // - @default(autoincrement()) (default value is auto-incremented)
  id Int @id @default(autoincrement())

  // Save the tweet creation time
  createdAt DateTime @default(now())

  // We set a `text` variable
  // With a `String` type
  text String

  // We set a `userId` variable
  // With an `Int` type (number)
  // It will link the `id` of the `User` model
  userId Int

  // We set a `user` variable
  // With a `User` type (many-to-one relationship)
  // Because each tweet has an author
  // This author is a `User`
  // We link the `User` to a `Tweet` based on:
  // - the `userId` in the `Tweet` model
  // - the `id` in the `User` model
  user User @relation(fields: [userId], references: [id])
}

How to Generate our First Database Migration

The first thing we need to do before using our database is to generate it. To do so, we will use another command of the Prisma CLI. This command will allow us to create migrations.

If we have a look at the documentation about the migrate command, we'll see the following:

"Prisma Migrate is an imperative database schema migration tool that enables you to: Keep your database schema in sync with your Prisma schema as it evolves _and m_aintain existing data in your database." – Prisma migrate documentation

The idea here is to save our first database implementation. You can do it by typing the command below in your terminal:

npx prisma migrate dev --name initialize

Note: You can enter the name of your choice after the --name parameter. Keep in mind that the migration name is helpful to remember the changes you made.

If your migration command is successful, it means that all the instructions in schema.prisma are correct. ✅

Your project tree should now be similar to the image below (except for the migration hash).

Project tree after the migration generation

Note: In the migration.sql file, you will find the SQL queries to generate your database.

Your database is ready! 🚀 It's time to try it, add some users, and let them tweet.

How to Test our Node JS SQLite Project

So, will the users now be able to tweet? Let's try to run some queries on our database. We'll create an index.ts file at the repository root, and we'll write some instructions into it.

First, we need to import and initialize the database connection. Based on the Prisma Quickstart documentation, we create a prisma variable to interact with the database and a function to write our test code:

import { PrismaClient } from "@prisma/client";

const prisma = new PrismaClient();

async function main() {}

main()
  .catch((e) => {
    throw e;
  })
  .finally(async () => {
    await prisma.$disconnect();
  });

We're ready to fill the main function with some instructions.

import { PrismaClient } from "@prisma/client";

const prisma = new PrismaClient();

async function main() {
  // We create a new user
  const newUser = await prisma.user.create({
    data: {
      email: "hello@herewecode.io",
      username: "gaelgthomas", // <- it's also my Twitter username 😄
    },
  });

  console.log("New User:");
  console.log(newUser);

  // We create a new tweet and we link it to our new user
  const firstTweet = await prisma.tweet.create({
    data: {
      text: "Hello world!",
      userId: newUser.id,
    },
  });

  console.log("First tweet:");
  console.log(firstTweet);

  // We fetch the new user again (by its unique email address)
  // and we ask to fetch its tweets at the same time
  const newUserWithTweets = await prisma.user.findUnique({
    where: {
      email: "hello@herewecode.io",
    },
    include: { tweets: true },
  });

  console.log("User object with Tweets:");
  console.dir(newUserWithTweets);
}

main()
  .catch((e) => {
    throw e;
  })
  .finally(async () => {
    await prisma.$disconnect();
  });

Note: If you want to discover the different instructions you can use, a good Prisma documentation page is the CRUD one.

Time to run the index.ts file.

Before doing it, open your package.json file and search for the scripts section. You will need to add a command to start the project using ts-node.

If you want, you can replace your scripts section with the following code:

"scripts": {
  "dev": "ts-node ./index.ts",
  "test": "echo \"Error: no test specified\" && exit 1"
},

Then, in your terminal, you can type the command below and read the output to see if everything is working well:

$ npm run dev

Note: In the command above, we run the dev script from our package.json.

NPM test output using Prisma

It's working! You got your first user and tweet. 👏 Now that you've set up your first database using Prisma, you can add some features to it. Here are some ideas:

add more information in the User entity (birthday, address, biography, and so on)
add a like system (each tweet can have likes, each user can have a liked tweet list)

The Code is available on Github – Node JS with Prisma and SQLite

If you want to get the complete code, you can find it on my GitHub.

-> GitHub: Prisma SQLite example

Thanks for reading until the end!

I hope you will use Prisma in one of your next projects. 🎉

I'm starting to tweet more consistently. If you want to get more tips and resources about web programming -> Find me on my Twitter 🐦

Python SQL – How to use the SQLite, MySQL, and PostgreSQL Databases with Python

freeCodeCamp — Thu, 25 Mar 2021 19:38:59 +0000

By Daniel Chae

One of my greatest joys as a developer is learning how different technologies intersect.

Over the years I've had the opportunity to work with different types of software and tools. Of the many tools I've used, Python and Structured Query Language (SQL) are two of my favorites.

In this article I'm going to share with you how Python and the different SQL databases interact.

I'll talk about the most popular databases, SQLite, MySQL, and PostgreSQL. I'll explain the key differences of each database and the corresponding use cases. And I'll end the article with some Python code.

The code will show you how to write a SQL query to pull data from a PostgreSQL database and store the data in a pandas data frame.

If you aren't familiar with relational databases (RDBMS), I suggest you check out Sameer's article on basic RDBMS terminology here. The rest of the article will use terms referenced in Sameer's article.

Popular SQL Databases

SQLite

SQLite is best known for being an integrated database. This means that you don't have to install an extra application or use a separate server to run the database.

If you're creating an MVP or don't need a ton of data storage space, you'll want to go with a SQLite database.

The pros are that you can move faster with a SQLite database relative to MySQL and PostgreSQL. That said, you'll be stuck with limited functionality. You won't be able to customize features or add a ton of multi-user functionality.

MySQL/PostgreSQL

There are distinct differences between MySQL and PostgreSQL. That said, given the context of the article, they fit into a similar category.

Both database types are great for enterprise solutions. If you need to scale fast, MySQL and PostgreSQL are your best bet. They'll provide long-term infrastructure and bolster your security.

Another reason they're great for enterprises is that they can handle high performance activities. Longer insert, update, and select statements need a lot of computing power. You'll be able to write those statements with less latency than what a SQLite database would give you.

Why Connect Python and a SQL Database?

You might be wondering, "why should I care about connecting Python and a SQL database?"

There are many use cases for when someone would want to connect Python to a SQL database. As I mentioned earlier, you might be working on a web application. In this case, you'd need to connect a SQL database so you can store the data coming from the web application.

Perhaps you work in data engineering and you need to build an automated ETL pipeline. Connecting Python to a SQL database will allow you to use Python for its automation capabilities. You'll also be able to communicate between different data sources. You won't have to switch between different programming languages.

Connecting Python and a SQL database will also make your data science work more convenient. You'll be able to use your Python skills to manipulate data from a SQL database. You won't need a CSV file.

How Python and SQL Databases Connect

Python and SQL databases connect through custom Python libraries. You can import these libraries into your Python script.

Database-specific Python libraries serve as supplemental instructions. These instructions guide your computer on how it can interact with your SQL database. Otherwise, your Python code will be a foreign language to the database you're trying to connect to.

How to Setup the Project

Let's take a PostgreSQL database, AWS Redshift, for example. First, you'll want to import the psycopg library. It's a universal Python library for PostgreSQL databases.

#Library for connecting to AWS Redshift
import psycopg

#Library for reading the config file, which is in JSON
import json

#Data manipulation library
import pandas as pd

You'll notice we also imported the JSON and pandas libraries. We imported JSON because creating a JSON config file is a secure way to store your database credentials. We don't want anyone else eyeing those!

The pandas library will enable you to use all of pandas' statistical capabilities for your Python script. In this instance, the library will enable Python to store the data your SQL query returns into a data frame.

Next, you'll want to access your config file. The json.load() function reads the JSON file so you can access your database credentials in the next step.

```setup (continued) config_file = open(r"C:\Users\yourname\config.json") config = json.load(config_file)


Now that your Python script can access your JSON config file, you'll want to create a database connection. You'll need to read and use the credentials from your config file:

con = psycopg2.connect(dbname= "db_name", host=config[hostname], port = config["port"],user=config["user_id"], password=config["password_key"]) cur = con.cursor()


You just created a database connection! When you imported the psycopg library, you translated the Python code you wrote above to speak to the PostgreSQL database (AWS Redshift). 

In it of itself, AWS Redshift would not understand the above code. But because you imported the psycopg library, you now speak a language AWS Redshift can understand. 

The nice thing about Python is that it has libraries for SQLite, MySQL, and PostgreSQL. You'll be able to integrate the technologies with ease.

### How to Write a SQL Query

_Feel free to download the [European Soccer Data](https://www.kaggle.com/hugomathien/soccer) to your PostgreSQL database. I'll be using its data for this example._  

The database connection you created in the last step lets you write SQL to then store the data in a Python-friendly data structure. Now that you've established a database connection, you can write a SQL query to start pulling data:

```sql query
query = "SELECT *
         FROM League
         JOIN Country ON Country.id = League.country_id;"

The work is not done yet, though. You need to write some additional Python code that executes the SQL query:

#Runs your SQL query
execute1 = cur.execute(query)
result = cur.fetchall()

Then you need to store the returned data in a pandas data frame:

#Create initial dataframe from SQL data
raw_initial_df = pd.read_sql_query(query, con)
print(raw_initial_df)

You should get a pandas data frame (raw_initial_df) that looks something like this:

There's a Database for Everybody

SQLite, MySQL, and PostgreSQL all have their pros and cons. The one you select should depend on your project or company's needs. You should also consider what you need now versus several years down the road.

The important thing to remember is that Python can integrate with each database type.

This article scratches the surface for what's possible with connecting Python to a SQL database. I love seeing the ways software intersect and combine to add incredible value.

If you want more of this type of content, you can find me at Course to Hire! I want to help more people learn how to code and land a job in tech. Please reach out for any questions or if you just want to say hi :)

Learn How to Use SQLite Databases With Python

Beau Carnes — Wed, 13 May 2020 18:41:54 +0000

SQLite is a very easy to use database engine included with Python. SQLite is open source and is a great database for smaller projects, hobby projects, or testing and development.

We've released a full video course to help you learn the basics of using SQLite3 with Python. You’ll learn how to create databases and tables, add data, sort data, create reports, pull specific data, and more. The course is taught by John Elder from Codemy.com.

For this course you should already have a basic knowledge of Python programming, but you don’t need to know anything at all about databases or SQLite.

In this course, you will learn:

what is a database
how to install Python
how to install git bash terminal
how to use sqlite in a Python program
how to connect to database in Python
how to create a database
how to create a table
how to insert one record into table
how to insert many records into table
understanding data types
how to select data from table
how to format your results
how to use the where clause
how to use the like clause and wildcards
how to use AND and OR
how to update records
how to limit and order results
how to delete records
how to delete (drop) a table and backups
and more!

At the end of the course you will learn how to build a very basic app to help reinforce all the things from the course.

You can watch the video course below or on the freeCodeCamp.org YouTube channel (1.5 hour watch).

How to use Streams, BLoCs, and SQLite in Flutter

freeCodeCamp — Wed, 17 Apr 2019 19:58:12 +0000

By Eric Grandt

Recently, I’ve been working with streams and BLoCs in Flutter to retrieve and display data from an SQLite database. Admittedly, it took me a very long time to make sense of them. With that said, I’d like to go over all this in hopes you’ll walk away feeling confident in using them within your own apps. I’ll be going into as much depth as I possibly can and explaining everything as simply as possible.

In this post, we’ll be making a simple app from start to finish that makes use of streams, BLoCs, and an SQLite database. This app will allow us to create, modify, and delete notes. If you haven’t done so yet, create a new barebones Flutter app using flutter create APPNAME. It'll be a lot easier to understand all this if you start fresh. Then, later on, implement what you learned into your existing apps.

The first order of business is creating a class to handle the creation of our tables and to query the database. To do this properly, we need to add sqflite and path_provider as dependencies in our pubspec.yaml file.

In case it doesn’t run automatically, run flutter packages get to retrieve the packages. Once it finishes, create a data folder and a database.dart file within it. This class will create a singleton so we can access the database from other files, open the database, and run queries on that database. I've included comments to explain some of the code.

Create another folder, models, and add one file to it: note_model.dart. Here's a great tool to easily make models: https://app.quicktype.io/#l=dart.

NOTE: Keep in mind that models do NOT have to copy the columns in the table. For example, if you have a user id stored in a table as a foreign key, the model probably shouldn’t contain that user id. Instead, the model should use that user id in order to retrieve an actual User object.

With our note model created, we can add the final functions to our database file that’ll handle all note related queries.

Let’s get started with streams and BLoCs now. If this is your first time working with these, it can be quite daunting. I promise you though that streams and BLoCs are exceptionally simple once you get past the learning phase.

The first thing we need is a blocs folder within the data folder. This folder will contain all our BLoCs, as the name suggests. Let's create the files for each BLoC: bloc_provider.dart, notes_bloc.dart, and view_note_bloc.dart. One BLoC per page and one to provide the BLoCs to those pages.

The bloc_provider is in charge of easily providing our pages with the necessary BLoC and then disposing of it when necessary. Every time we want to use a BLoC, we'll be using the bloc_provider.

Whenever we need a BLoC on one of our pages, we’ll utilize the BlocProvider like this:

Let’s create our notes BLoC which will handle retrieving all our notes and adding new notes to the database. Since our BLoCs are page specific, this BLoC will only be used on the notes page. I’ve commented the code to explain what’s going on.

With the notes BLoC created, we have everything we need to create our notes page. This page will display all our notes, and allow us to add new ones. We’ll put the code for our notes page into main.dart. Once again, I've commented on all the necessary pieces of code to explain what's going on.

Now we need a way to view, edit, save, and delete the notes. This is where the view note BLoC and the view note page come into play. We’ll start with view_note_bloc.dart.

Now we can build the actual page to allow us to interact with our notes. The code for this page is going in view_note.dart.

Final app using streams, BLoCs, and SQLite

That’s all it takes to work with streams, BLoCs, and SQLite. Using them, we’ve created a super simple app that allows us to create, view, edit, and delete notes. I hope this walkthrough has made you more confident in working with streams. You’ll now be able to implement them into your own apps with ease. If you have any questions, please leave a comment as I’d love to answer them. Thanks for reading.

View the full code here: https://github.com/Erigitic/flutter-streams

SQLite - freeCodeCamp.org

A Brief Introduction to SQLite

Code Playbacks

A Brief Introduction to SQLite

Chapter 1: Database Design and SQL

Chapter 2: SQLite in C/C++

Chapter 3: SQLite in Python

Chapter 4: SQLite in Java

Conclusion

An Animated Introduction to SQL – Learn to Query Relational Databases

Tutorial Structure

Part 1: A Whirlwind Tour of SQL

Part 2: Core SQL Concepts and Keywords

Part 3: Practice Problems

Part 4: Using SQLite in Programs

Running Queries

Optional: Web-Based Alternative to DB Browser

Code Playbacks

Playback Press

AI Tutor

Table of Contents

Part 1: A Whirlwind Tour of SQL

Entity-Relationship Diagrams

Schemas

Try It: Explore a Relational Database in Action

Database Design and Simple SQL: Introduces the Paw Prints database and shows how to write basic SQL queries.

One-to-Many Relationships and More SQL: Covers one-to-many relationships and how to join related tables.

Many-to-Many Relationships and Even More SQL: Shows how to handle many-to-many relationships using join tables and more advanced queries.

Part 2: A Beginner’s Reference to SQL

Part 3: Practice Problems — Paw Prints and University Databases

University Database

Part 4: Using SQLite in Programs

Why Use SQLite in Programs?

C/C++

Python and Flask

Java

Conclusion

Comments and Feedback

How to Work with SQLite in Python – A Handbook for Beginners

How to Set Up Your Python Environment

Installing Python

Installing SQLite3 Module

How to Create a Virtual Environment (Optional but Recommended)

Installing Necessary Libraries

How to Create an SQLite Database

How SQLite Databases Work

How to Create a New SQLite Database

Connecting to the Database

Creating a Cursor

Closing the Connection

How to Use Context Manager to Open and Close Connections

How to Create Database Tables

Data Types in SQLite and Their Mapping to Python

How to Insert Data into a Table

How to Insert a Single Record

How to Insert Multiple Records

How to Handle Common Issues: SQL Injection

How to Query Data

How to Fetch All Records

How to Fetch a Single Record

How to Fetch Multiple Records

How to Use pandas for Better Data Presentation

How to Update and Delete Data

Updating Existing Records

How to Delete Records from the Table

Important Considerations

How to Use Transactions

Why Use Transactions?

When to Use Transactions?

How to Manage Transactions in Python

Starting a Transaction

How to Commit a Transaction

Rolling Back a Transaction

Example of Using Transactions in Python

How to Optimize SQLite Query Performance with Indexing

How to Populate the Database with Fake Data

How to Query Without Indexes

Introducing the Query Plan

How to Create an Index

How to Query with Indexes

How to Use `pandas` for Better Data Presentation