Problem Formulation and Solution Overview
ℹ️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:
To follow along with this article, download and place the cars.csv file into the current working directory:
- 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.
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
Connectionobject to the same saves toconn. - The
cars.csvfile is read in (notice the separator character). The results save to the DataFrame,df. - Then the contents of
dfare 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
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.
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.
CarMPG 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.
408Execute 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!