Adventures in Machine Learning

Mastering SQLite3: Creating inserting joining and displaying data

Are you looking to create a database to store your data? SQLite3 is a popular database management system that is widely used for its simplicity and low resource requirements.

In this article, we will discuss how to create a database and tables within SQLite3, and how to join tables and display the results.

Creating a New Database and Tables

To start, let us create a new database using SQLite3. Open your terminal or command prompt and navigate to the directory where you want to create your database.

1. Creating a Database

Type the following command to create a new database file:

sqlite3 mydatabase.db

This creates a new file called “mydatabase.db” in the current working directory. Now that we have a new database, let us create two tables within it:

2. Creating Tables

CREATE TABLE users (
      id INTEGER PRIMARY KEY,
      name TEXT,
      email TEXT UNIQUE,
      age INTEGER
  );

  CREATE TABLE orders (
      id INTEGER PRIMARY KEY,
      user_id INTEGER REFERENCES users (id),
      item_name TEXT,
      price REAL,
      quantity INTEGER
  );

The first table we created is called “users” and has four columns – “id”, “name”, “email”, and “age”.

The “id” column is the primary key, which means it uniquely identifies each row in the table. The “name” and “email” columns hold text values, and the “age” column holds an integer value.

The second table we created is called “orders” and has five columns – “id”, “user_id”, “item_name”, “price”, and “quantity”. The “id” column is the primary key, and we have added a foreign key constraint to the “user_id” column that references the “id” column in the “users” table.

This means that we can link each order to a user in the “users” table. The “item_name” and “price” columns hold text and real values, respectively, and the “quantity” column holds an integer value.

Defining Columns and Inserting Data into the Tables

Now that we have created our tables, we can start adding data to them. To add data to the “users” table, we can use the following SQL command:

1. Inserting Data into “users” Table

INSERT INTO users (id, name, email, age) VALUES
      (1, 'John Doe', '[email protected]', 30),
      (2, 'Jane Doe', '[email protected]', 25);

This command adds two rows to the “users” table.

The first row has an “id” of 1, a “name” of “John Doe”, an “email” of “[email protected]”, and an “age” of 30. The second row has an “id” of 2, a “name” of “Jane Doe”, an “email” of “[email protected]”, and an “age” of 25.

2. Inserting Data into “orders” Table

To add data to the “orders” table, we can use the following SQL command:

INSERT INTO orders (id, user_id, item_name, price, quantity) VALUES
      (1, 1, 'iPhone', 999.99, 1),
      (2, 2, 'iPad', 799.99, 2);

This command adds two rows to the “orders” table. The first row has an “id” of 1, a “user_id” of 1 (corresponding to John Doe in the “users” table), an “item_name” of “iPhone”, a “price” of 999.99, and a “quantity” of 1.

The second row has an “id” of 2, a “user_id” of 2 (corresponding to Jane Doe in the “users” table), an “item_name” of “iPad”, a “price” of 799.99, and a “quantity” of 2.

Joining Tables and Displaying Results

Now that we have our data in the database, we might want to extract some information from it by joining our tables together. To join the “users” and “orders” tables together, we can use the following SQL command:

1. Joining “users” and “orders” Tables

SELECT * FROM users
      JOIN orders ON users.id = orders.user_id;

This command returns all columns from both tables where the “id” column in the “users” table matches the “user_id” column in the “orders” table.

2. Displaying Results in a User-Friendly Format

The result is a table that shows the user information alongside their respective orders:

id | name      | email             | age | id | user_id | item_name | price  | quantity
-------------------------------------------------------------------------------------
1  | John Doe | [email protected] | 30  | 1  | 1       | iPhone    | 999.99 | 1
2  | Jane Doe | [email protected] | 25  | 2  | 2       | iPad      | 799.99 | 2

To display the results in a more user-friendly format, we can use the popular Python library pandas. In your Python environment, import pandas and sqlite3, and execute the following code:

import pandas as pd
import sqlite3

conn = sqlite3.connect('mydatabase.db')
df = pd.read_sql_query('SELECT * FROM users JOIN orders ON users.id = orders.user_id', conn)
print(df)

This code reads the “users” and “orders” tables from the “mydatabase.db” SQLite3 database, joins them together using an SQL command, and stores the result in a pandas DataFrame called “df”.

Finally, it displays the DataFrame using the “print” statement.

In conclusion, SQLite3 is a powerful database management system that is perfect for small to medium-sized projects.

We have covered how to create a new database, create tables, add data to them, and join tables together using a common column. We also showed how to use pandas to display the results of a SQL query in a user-friendly format.

With these skills, you should be able to begin working with SQLite3 and harness its power to handle your data.

Remember, proper database management is essential for any project that involves data, and SQLite3 is one of the best tools for handling databases with ease.

Popular Posts