5 Best Ways to Utilize SQL Using Python and SQLite

Rate this post

πŸ’‘ Problem Formulation: Accessing and manipulating databases is a common requirement in software development. This article explores how you can use SQL with Python and SQLite to handle databases. We aim to provide examples where the input is a Python command to perform a database operation and the output is the intended result upon the database, be it data retrieval, insertion, update, or deletion.

Method 1: Basic Database Connection and Table Creation

This method involves establishing a connection to a SQLite database and creating a new table. It provides a solid foundation for any database operation, as one must first connect to the database before performing subsequent actions such as manipulating tables or records.

Here’s an example:

import sqlite3

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

# Create a cursor object using the cursor method
cursor = conn.cursor()

# Create table
cursor.execute('''CREATE TABLE stocks
             (date text, trans text, symbol text, qty real, price real)''')

# Commit the changes and close the connection
conn.commit()
conn.close()

Output: A table named ‘stocks’ is created with five columns in the database ‘example.db’.

This example demonstrates the creation of a new table called ‘stocks’ in an SQLite database called ‘example.db’. A cursor object is obtained from the connection to execute SQL commands. Once used, the transaction is committed, and the database connection is closed.

Method 2: Inserting Data into Tables

After creating a table, the next step is often to insert data into it. This method shows how to add new records to a SQLite database table using Python’s sqlite3 module.

Here’s an example:

import sqlite3

conn = sqlite3.connect('example.db')
cursor = conn.cursor()

# Insert a row of data
cursor.execute("INSERT INTO stocks VALUES ('2023-01-05','BUY','RHAT',100,35.14)")

# Save (commit) the changes
conn.commit()

# Close the connection
conn.close()

Output: A new record is inserted into the ‘stocks’ table.

Here, sqlite3 is used to connect to an existing database, and a single row of data is inserted into the ‘stocks’ table. The commit method ensures that the changes are saved before the database connection is closed.

Method 3: Querying Data

Frequently, the purpose of a database is to store data that can later be queried. This method demonstrates how to retrieve data from a SQLite table using SELECT statements in Python.

Here’s an example:

import sqlite3

conn = sqlite3.connect('example.db')
cursor = conn.cursor()

# Select all rows from the 'stocks' table
cursor.execute('SELECT * FROM stocks WHERE trans="BUY"')
print(cursor.fetchall())

conn.close()

Output: Retrieves and prints all rows from the ‘stocks’ table where the ‘trans’ column is ‘BUY’.

The example uses a simple SELECT statement to retrieve all rows where the transaction type is ‘BUY’ from the ‘stocks’ table. The fetchall method fetches all the matching rows as a list of tuples.

Method 4: Updating and Deleting Data

This method shows how to update and delete records in a SQLite database using Python. These operations are crucial for maintaining the accuracy and relevance of data within a database.

Here’s an example:

import sqlite3

conn = sqlite3.connect('example.db')
cursor = conn.cursor()

# Update price in the 'stocks' table
cursor.execute("UPDATE stocks SET price=35.99 WHERE symbol='RHAT'")

# Delete record from 'stocks' table
cursor.execute("DELETE FROM stocks WHERE qty < 50")

# Commit changes and close connection
conn.commit()
conn.close()

Output: Updates the price of all ‘RHAT’ symbols to 35.99 and deletes rows where the quantity is less than 50.

The code snippet first updates the price for all records of a certain stock symbol and then deletes any records where the stock quantity is below a certain threshold. Such operations are necessary for keeping the data up-to-date and relevant.

Bonus One-Liner Method 5: Using with statement

For more conciseness and better management of resources, Python’s with statement can be used. It ensures that the database connection is properly closed after its suite of code completes, even if an error occurs.

Here’s an example:

import sqlite3

with sqlite3.connect('example.db') as conn:
    conn.execute("INSERT INTO stocks VALUES ('2023-01-10','SELL','XYZ',150,42.00)")

Output: A record is inserted without explicitly closing the connection.

Here, the with statement is used for handling the database connection, where it automatically closes the connection when the block of code exits. This single line inserts a new record into the table, showcasing how simple database operations can be concise yet functional.

Summary/Discussion

  • Method 1: Connecting and Creating Tables. Strengths: It is the foundational step for database operations. Weaknesses: Requires separate commands to perform any action other than creation.
  • Method 2: Inserting Data. Strengths: Straightforward way to populate tables with data. Weaknesses: Manual repetition is tedious for inserting multiple records.
  • Method 3: Querying Data. Strengths: Essential for data analysis and retrieval. Weaknesses: Requires understanding of SQL querying syntax and logic.
  • Method 4: Updating and Deleting Data. Strengths: Allows for data accuracy and cleanup. Weaknesses: Can lead to data loss if not used carefully.
  • Bonus One-Liner Method 5: with Statement. Strengths: Clean and resource-efficient. Weaknesses: Less explicit control over the connection lifecycle.