Introduction to SQLite
SQLite is a self-contained, serverless, zero-
configuration, transactional SQL database engine. It is
lightweight and efficient, making it ideal for embedded
systems, mobile applications, and data warehousing.
Setting up SQLite in Python
Before we dive into the code, you’ll need to make sure
that you have SQLite installed on your system.
Fortunately, SQLite is included in the standard Python
library, so you should already have it installed. [check
more in the documentation here]
To check if SQLite is installed on your system, open up
a terminal window and type the following command:
sqlite3 --version
Creating a database and table
To create a database, you need to connect to it using the
connect() method, which returns a connection object.
You can then create a cursor object using the cursor()
method, which allows you to execute SQL statements.
import sqlite3
# Connect to a database (create if not exists)
conn = [Link]('[Link]')
# Create a cursor object to execute SQL statements
cur = [Link]()
To create the table, we’re using the [Link]()
method to execute a SQL statement. We're using quotes
to write the SQL statement inside [Link](). You can
also use triple quotes to create a multi-line string, which
allows you to write the SQL statement on multiple lines
for readability.
After we’ve created the table, we’re calling the
[Link]() method to save the changes to the
database.
# Create a table
[Link]("CREATE TABLE customer
(custid INT PRIMARY KEY NOT NULL,
name TEXT NOT NULL,
age INT NOT NULL,
email TEXT NOT NULL UNIQUE
city TEXT NOT NULL);")
# Commit the changes
[Link]()
Notice that the name, ageand email columns are
required and must contain text/int/text values. The
email column is also marked as UNIQUE, which means
that SQLite will prevent any two rows from having the
same email address. The custid column is an integer
primary key, which means that SQLite will
automatically assign a unique integer value to each row
Inserting data into the table
Now that we have a table in our database, we can start
adding data to it. To insert data into the table, you can
use the execute() method. This time, we’re using an
INSERT INTO statement to insert a new row into the
customer table.
# Insert a single row
[Link]("INSERT INTO customer (custid, name,
age, email, city) \
VALUES (1, 'Alice', 25, 'alice@[Link],
'city1')")
# Commit the changes
[Link]()
Inserting multiple values?
Option 1
[Link]("INSERT INTO customer (custid, name,
age, email, city) \
VALUES (2, 'Bob', 30, 'bob@[Link]','city2'),\
(3, 'Charlie', 35,'charlie@[Link]','city2')")
Option 2 You can also use the executemany() method to
insert multiple rows at once. We’re using question
marks (?) as placeholders for the values that we want to
insert.
# Insert multiple rows
data = [
(4, 'John', 50, 'john@[Link]','city1'),
(5, 'Don', 59, 'Don@[Link]','city3'),
(6, 'Paul', 75, 'Paul@[Link]','city3')
]
[Link]("INSERT INTO stocks VALUES (?, ?,
?, ?, ?,?)", data)
Retrieving data from the table
To retrieve data from a table, you can use the execute()
method with the SQL SELECT statement. The SELECT
statement is used to retrieve data from one or more
tables in a database.
# Retrieve data
[Link]("SELECT * FROM customer")
# Fetch all rows
rows = [Link]()
# Print the rows
for row in rows:
print(row)
we retrieved all the data from the customer table using
the SELECT * FROM customer statement. The
fetchall() method returns a list of tuples, where each
tuple represents a row in the table. We then looped
through the list and printed each row.
You can also retrieve data based on specific conditions
using the WHERE clause. For example, to retrieve all
rows where the name column is 'John', you can use the
following statement:
[Link]("SELECT * FROM customer WHERE
name ='John'")
[Link]("SELECT custid,city FROM customer
WHERE name ='John'")
You can also retrieve specific columns using the
SELECT statement. For example, to retrieve only the
name and email columns, you can use the following
statement:
[Link]("SELECT name, email FROM customer")
The LIMIT command is used to limit the number of
rows returned by a query. It is useful when you want to
retrieve a small subset of data from a large table. For
example, to retrieve the first 5 rows from the customer
table, you can use the following statement:
[Link]("SELECT * FROM customer LIMIT 5")
The ORDER BY command is used to sort the data
returned by a query in ascending or descending order.
You can sort by one or more columns in the table. For
example, to retrieve all rows from the customer table
sorted by the name column, you can use the following
statement:
[Link]("SELECT * FROM customer ORDER BY
name")
By default, the result of the query with order by would
be in ascending order, to retrieve the result in
descending order use DESC at the end. This will
retrieve all rows from the customer table sorted by the
name column in descending order.
[Link]("SELECT * FROM customer ORDER BY
name DESC")
The GROUP BY command is used to group the data
returned by a query based on one or more columns in
the table. You can then use aggregate functions, such as
SUM, AVG, MIN, and MAX, to calculate summary
statistics for each group. For example, to calculate the
total quantity and average age of customers for each
city in the customer table, you can use the following
statement:
[Link]("SELECT city, AVG(age) FROM customer
GROUP BY city")
This will group the data in the customer table by the
city column and calculate the average of the age column
for each group.
The JOIN command is used to combine data from two
or more tables based on a common column. You can use
different types of joins, such as INNER JOIN, LEFT
JOIN, RIGHT JOIN, and FULL OUTER JOIN, to
control how the data is combined. To understand joins
better, let’s first create another table called ‘orders’.
# Create the orders table
[Link]('''CREATE TABLE IF NOT EXISTS orders
(id INTEGER PRIMARY KEY, customer_id
INTEGER, product TEXT, quantity INTEGER, price
REAL)''')
# Insert data into the orders table
[Link]("INSERT INTO orders (customer_id,
product, quantity, price) VALUES (1, 'Product A', 2,
10.99)")
[Link]("INSERT INTO orders (customer_id,
product, quantity, price) VALUES (2, 'Product B', 1,
19.99)")
Inner Join: This join returns only the rows that have
matching values in both tables. For example:
# Inner join
[Link]("SELECT * FROM customers JOIN
orders \
ON [Link] = orders.customer_id")
# Fetch all rows
rows = [Link]()
# Print the rows
for row in rows:
print(row)
This query returns only the rows that have matching
custid values in the customer table and customer_id
values in the orders table.
Left Join: This join returns all the rows from the left
table and the matching rows from the right table. If
there are no matching rows in the right table, the result
will contain null values for those columns. For
example:
# Left join
[Link]("SELECT * FROM customers LEFT JOIN
orders \
ON [Link] = orders.customer_id")
# Fetch all rows
rows = [Link]()
# Print the rows
for row in rows:
print(row)
This query returns all the rows from the customer table,
and the matching rows from the orders table, but also
includes any customers who have not yet made any
orders. For those customers who have not made any
orders, the columns from the orders table will contain
null values.
Right Join: This join returns all the rows from the right
table and the matching rows from the left table. If there
are no matching rows in the left table, the result will
contain null values for those columns. For example:
# Right join
[Link]("SELECT * FROM customers RIGHT
JOIN orders \
ON [Link] = orders.customer_id")
# Fetch all rows
rows = [Link]()
# Print the rows
for row in rows:
print(row)
This query returns all the rows from the orders table,
and the matching rows from the customer table, but also
includes any orders that do not have a corresponding
customer in the customer table. For those orders
without a corresponding customer, the columns from
the customer table will contain null values.
Full Outer Join: This join returns all the rows from both
tables. If there are no matching rows in one of the
tables, the result will contain null values for those
columns. For example:
# Full join
[Link]("SELECT * FROM customers FULL
OUTER JOIN orders \
ON [Link] = orders.customer_id")
# Fetch all rows
rows = [Link]()
# Print the rows
for row in rows:
print(row)
This query returns all the rows from both the customer
and orders tables, and includes any rows that do not
have a corresponding row in the other table. For those
rows without a corresponding row in the other table, the
columns from the other table will contain null values.
Updating and deleting data from the table
To update or delete data from the table, you can use the
execute() method with the SQL UPDATE or DELETE
statement.
# Update data
[Link]("UPDATE customer SET city = 'city4'
WHERE name = 'bob'")
# Delete data
[Link]("DELETE FROM customer WHERE custid
= 5")
# Commit the changes
[Link]()
Once you are done with all your querying in your
database you need to close the connection using close()
on the connection variable like this:-
# Close the connection
[Link]()
Using SQLite with pandas
Pandas is a popular Python library for data analysis. It
provides a high-level interface for data manipulation
and analysis. You can use the pandas.read_sql_query()
function to read data from an SQLite database into a
pandas DataFrame.
import sqlite3
import pandas as pd
# Connect to a database (create if not exists)
conn = [Link]('[Link]')
# Read data into a pandas DataFrame
df = pd.read_sql_query("SELECT * FROM customer
WHERE city = 'city1'", conn)
# Print the DataFrame
print(df)
You can also write your dataframe records to sql
database from an existing csv file by first converting it
into a dataframe and then using to_sql() function
#fetching csv file
df = pd.read_csv('chicago_data.csv')
#creating table from dataframe with [Link]() on the
connection established
df.to_sql("chicago_socioeco_data", conn,
if_exists='replace', index=False, method="multi")
Chicago_socioeconomic data is available at
[Link]
As now you ha your data, you an apply the sql queries
as learned above on it in pandas using .read_sql_query()
Here are few examples for your reference:-
# Execute an SQL query and return the results with only
5 rows using limit
pd.read_sql_query('SELECT * \
FROM chicago_socioeco_data \
limit 5', conn)
# Count of rows
pd.read_sql_query('select count(*) as Total_Rows \
from chicago_socioeco_data', conn)
#To extract the number of community areas in Chicago,
#which have a hardship index greater than 50.0,
#you can apply the following query
pd.read_sql_query("select
count(community_area_name) \
from chicago_socioeco_data \
where hardship_index > 50.0", conn)
# Extracting the maximum value of hardship index in
this dataset:-
pd.read_sql_query("select MAX(hardship_index) \
from chicago_socioeco_data", conn)
# extracting the community area which has the highest
hardship index:-
pd.read_sql_query("SELECT community_area_name \
FROM chicago_socioeco_data\
where hardship_index=98.0", conn)
Remember, the query fetched in pandas will result in a
dataframe, this means, you can leaverage the full
functionality of pandas on it. Also you can visualize the
fetched data using matplotlib or any other visualizing
library.
#Finding the correlation between the two variables-
#per_capita_income and hardship_index of the chicago
data
import [Link] as plt
import seaborn as sns
corr_df = pd.read_sql_query("SELECT
per_capita_income_, hardship_index \
FROM
chicago_socioeco_data",conn)
plot =
[Link](x='per_capita_income_',y='hardship_ind
ex', data=corr_df)
[Link](plot)