Database (3 Part Series)

1 Exploring sqlite3 in Python
2 Interfacing with MSSQL Databases in Python
3 Working with Oracle Databases in Python

Introduction

SQLite is a lightweight, embedded SQL database engine that is widely used for local data storage in various applications. In Python, the sqlite3 module provides a simple and efficient way to interact with SQLite databases. This chapter explores the capabilities of sqlite3 in Python, covering topics such as database creation, table manipulation, data insertion and retrieval, as well as data modification and deletion.

Topics

• Difference between in-memory and file-based SQLite databases
• Creating a database and establishing a connection
• Creating tables and defining table schema
• Inserting data into tables
• Reading data from tables (inner join, left join)
• Updating data in tables
• Removing data from tables

Difference between in-memory and file-based SQLite databases

• In-memory SQLite databases reside entirely in the system’s RAM and are not persisted to disk. They offer high performance but are suitable only for temporary data storage.
• File-based SQLite databases are stored as files on the disk. They provide persistent data storage and are ideal for long-term data retention.

Creating a database and establishing a connection

import sqlite3

# Connect to a file-based SQLite database conn = sqlite3.connect(database="example.db")

# Connect to an in-memory SQLite database # conn = sqlite3.connect(database=":memory:") 

Enter fullscreen mode Exit fullscreen mode

Creating tables and defining table schema

import sqlite3

# Connect to a file-based SQLite database conn = sqlite3.connect(database="example.db")

# Create a cursor object to execute SQL queries cursor = conn.cursor()

# Define table schema and create table cursor.execute("""CREATE TABLE IF NOT EXISTS employees ( id INTEGER PRIMARY KEY, name TEXT NOT NULL, age INTEGER, department TEXT )""")

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

Enter fullscreen mode Exit fullscreen mode

Inserting data into tables

import sqlite3

# Connect to a file-based SQLite database conn = sqlite3.connect(database="example.db")

# Create a cursor object to execute SQL queries cursor = conn.cursor()

# Insert data into the 'employees' table cursor.execute("INSERT INTO employees (name, age, department) VALUES (?, ?, ?)", ('John Doe', 30, 'HR'))
cursor.execute("INSERT INTO employees (name, age, department) VALUES (?, ?, ?)", ('Jane Smith', 35, 'Finance'))

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

Enter fullscreen mode Exit fullscreen mode

Reading data from tables

import sqlite3

# Connect to a file-based SQLite database conn = sqlite3.connect(database="example.db")

# Create a cursor object to execute SQL queries cursor = conn.cursor()

# Read data from the 'employees' table cursor.execute("SELECT name, age, department FROM employees")
rows = cursor.fetchall()

for row in rows:
    print(row)

# Close the cursor cursor.close()

Enter fullscreen mode Exit fullscreen mode

Output:

(1, 'John Doe', 30, 'HR')
(2, 'Jane Smith', 35, 'Finance')

Enter fullscreen mode Exit fullscreen mode

Updating data in tables

import sqlite3

# Connect to a file-based SQLite database conn = sqlite3.connect(database="example.db")

# Create a cursor object to execute SQL queries cursor = conn.cursor()

# Update data in the 'employees' table cursor.execute("UPDATE employees SET department = 'IT' WHERE name = 'John Doe'")

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

Enter fullscreen mode Exit fullscreen mode

Removing data from tables

import sqlite3

# Connect to a file-based SQLite database conn = sqlite3.connect(database="example.db")

# Create a cursor object to execute SQL queries cursor = conn.cursor()

# Delete data from the 'employees' table x = cursor.execute("DELETE FROM employees WHERE name = 'Jane Smith'")

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

Enter fullscreen mode Exit fullscreen mode

Conclusion

The sqlite3 module in Python provides a convenient interface for working with SQLite databases, allowing developers to create, manipulate, and query databases seamlessly. By mastering the functionalities of sqlite3, developers can build robust and efficient data storage solutions for a wide range of applications, from small-scale projects to enterprise-level systems.

Additionally, for those who prefer visual management of SQLite databases, applications like DBeaver Community offer comprehensive tools for interacting with SQLite database files in a graphical user interface, providing an alternative approach to database management.

Database (3 Part Series)

1 Exploring sqlite3 in Python
2 Interfacing with MSSQL Databases in Python
3 Working with Oracle Databases in Python

原文链接：Exploring sqlite3 in Python