How to Create, Update, and Query an SQLite Database in Python?

4.8/5 - (5 votes)

Problem Formulation and Solution Overview

This article will show you how to create, update and query a SQLite database using Python.

ℹ️The SQLite database is a great choice! It is self-contained, easy to use, easy to learn, and works on other devices, such as game consoles, mobile phones, and so on!

To make it more interesting, we have the following running scenario:

KarTech, a used car dealership in Toronto, currently has its inventory of cars saved as a CSV file format. However, they would like this imported into a database. For simplicity, they selected SQLite.

To follow along with this article, download and place the cars.csv file into the current working directory:


💬 Question: How would we write code to create and work with a SQLite Database?

We can accomplish this task by performing the following these steps:

  • Preparation
  • Declare Variables
  • Create SQLite Database and Table
  • Retrieve Table Column Names
  • Execute Select Query
  • Execute Insert Query
  • Execute Delete Query
  • Close Connections

Preparation

The first step is to import and install the required library to ensure the code in this article will run error-free.

If the pandas library is not installed. Click here for instructions.

Next, create a Python file in the current working directory called cars-sql.py and paste the following code to the top of this file.

import pandas as pd
import sqlite3
from os.path import exists as file_exists

The first line in the above code snippet calls in the pandas library, which is required to access and manipulate the cars.csv file downloaded earlier.

The following line imports Python’s built-in sqlite3 library required to access and manipulate the SQLite database.

The last line also imports Python’s built-in os library that checks for the existence of the Database file.

Save this file.

10 Minutes to Pandas in 5 Minutes (Okay 8)

Declare Variables

This section creates and assigns variables for the Database, its associated Table, and the cars.csv file downloaded earlier.

Paste the following lines to the bottom of the cars-sql.py file.

dbase_name = 'cars.db'
table_name = 'inventory'
csv_file = 'cars.csv'

Save this file.


Create Database and Table

This section does all the heavy lifting! It creates a Database if it does not already exist and attempts to connect to the same!

Paste the following lines to the bottom of the cars-sql.py file.

if file_exists(dbase_name):
    conn = sqlite3.connect(dbase_name)
else:
    conn = sqlite3.connect(dbase_name)
    df = pd.read_csv('cars.csv', sep=';')
    df.to_sql(f'{table_name}', conn)
cur = conn.cursor()

The first line in the above code snippet checks to see if the Database exists in the current working directory (cars.db). If this file exists, a Connection Object is created (conn), allowing access to the Database (shown below).

Connection Object: <sqlite3.Connection object at 0x000001E8653DAB40>

If the Database does not exist, code execution falls inside the else statement where the following occurs:

  • A Database is created, and a Connection object to the same saves to conn.
  • The cars.csv file is read in (notice the separator character). The results save to the DataFrame, df.
  • Then the contents of df are imported into the table specified earlier (inventory).

The last line creates a Cursor Object (cur) from the Connection Object (conn). The Cursor Object allows access to commands, such as SQL, which can be applied against the Database and/or Table.

Cursor Object: <sqlite3.Cursor object at 0x0000024D38B562C0>

Save this file.

If successful, the cars.db SQLite database should now reside in the current working directory.


Retrieve Table Column Names

This section shows how to retrieve a list of the Column Names in the Table.

Paste the following lines to the bottom of the cars-sql.py file.

cols = conn.execute('SELECT * FROM inventory')
conn.commit()

col_names = list(map(lambda x: x[0], cols.description))

The first two (2) lines in the above code snippet execute and commit a SQL command that receives all rows from the inventory Table. A Cursor Object is created from these results and saved to cols. If output to the terminal, the following would display.

Cols Object: <sqlite3.Cursor object at 0x0000022582C9CBC0>

The following lines access and retrieve the cols.description field using a lambda, a map(), and a list. This action retrieves the Field Names from the Table and saves the results to tbl_fields. If output to the terminal, the following would display.

['index', 'Car', 'MPG', 'Cylinders', 'Displacement', 'Horsepower', 'Weight', 'Acceleration', 'Model', 'Origin']

Save this file.

💡Note: The commit() command saves changes invoked by a SQL query to the database. Click here to learn more.

Let's Play Finxter - The Lambda Function in Python

Execute Select Query

This section shows how to create and execute a SELECT query.

Paste the following lines to the bottom of the cars-sql.py file.

When executing a SELECT query, this task can be performed using the read_sql() function and passing it two (2) arguments, a SQL statement and a Connection Object (conn).

query = pd.read_sql(f'SELECT Car, MPG, Cylinders FROM {table_name} LIMIT 5', conn)

This SQL query selects and retrieves three (3) columns and five (5) rows from the inventory table. The results save to query. If output to the terminal, the following would display.

   Car                                                    MPG      Cylinders
0  Chevrolet Chevelle Malibu                18.0             8
1  Buick Skylark 320                             15.0             8
2  Plymouth Satellite                             18.0             8
3  AMC Rebel SST                                16.0             8
4  Ford Torino                                        17.0             8

Save this file.

💡 Note: The above method is commonly used for SELECT queries as we are not altering the Database or the Table, such as performing an update, insert, delete, drop, or alter command. Click here to learn more about SQL.


Execute Insert Query

This section outlines how to create and execute an INSERT query

Paste the following lines to the bottom of the cars-sql.py file.

cur.execute('''INSERT INTO inventory
             VALUES('Citroen', '28.0', '8', '', '', '', '', '', '', '')''')

print(cur.lastrowid)
conn.commit()

The first line in the above code snippet creates and executes a SQL statement that INSERTS one (1) record into the inventory Table.

The following line retrieves the Row ID and is output to the terminal. If this returns an integer value, then the INSERT was successful.

408

Execute Delete Query

This section outlines how to create and execute a DELETE query

Paste the following lines to the bottom of the cars-sql.py file.

cur.execute('''DELETE FROM inventory WHERE MPG = 28.0''')
conn.commit()

The first line in the above code snippet creates and executes a SQL statement that DELETES all records matching the criteria from the inventory Table.

The following line commits the changes to the Database.

💡Note: Run a SELECT query to see if the record(s) were successfully deleted.


Close the Connections

The final step is to close any open connections.

Paste the following lines to the bottom of the cars-sql.py file.

cur.close()
conn.close()

The Entire Script

import pandas as pd
import sqlite3
from os.path import exists as file_exists

dbase_name = 'cars.db'
table_name = 'inventory'
csv_file = 'cars.csv'

if file_exists(dbase_name):
    conn = sqlite3.connect(dbase_name)
else:
    conn = sqlite3.connect(dbase_name)
    df = pd.read_csv('cars.csv', sep=';')
    df.to_sql(f'{table_name}', conn)
cur = conn.cursor()


# Get Column Names
cols = conn.execute('SELECT * FROM inventory')
conn.commit()
col_names = list(map(lambda x: x[0], cols.description))

# SELECT
query = pd.read_sql(f'SELECT Car, MPG, Cylinders FROM {table_name} LIMIT 5', conn)
print(query)

# INSERT
cur.execute('''INSERT INTO inventory
             VALUES('Citroen', '28.0', '8', '', '', '', '', '', '', '')''')

print(cur.lastrowid)
conn.commit()

# DELETE
cur.execute('''DELETE FROM inventory WHERE MPG = 28.0''')
conn.commit()

cur.close()
conn.close()

Summary

This article has shown you how to work with a SQLite Database.

Good Luck & Happy Coding!