Movatterモバイル変換


[0]ホーム

URL:


Skip to content
DEV Community
Log in Create account

DEV Community

Cover image for Exploring sqlite3 in Python
≀Paulo Portela
≀Paulo Portela

Posted on • Edited on

Exploring sqlite3 in Python

Introduction

SQLite is a lightweight, embedded SQL database engine that is widely used for local data storage in various applications. In Python, thesqlite3 module provides a simple and efficient way to interact with SQLite databases. This chapter explores the capabilities ofsqlite3 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

importsqlite3# Connect to a file-based SQLite databaseconn=sqlite3.connect(database="example.db")# Connect to an in-memory SQLite database# conn = sqlite3.connect(database=":memory:")
Enter fullscreen modeExit fullscreen mode

Creating tables and defining table schema

importsqlite3# Connect to a file-based SQLite databaseconn=sqlite3.connect(database="example.db")# Create a cursor object to execute SQL queriescursor=conn.cursor()# Define table schema and create tablecursor.execute("""CREATE TABLE IF NOT EXISTS employees (                    id INTEGER PRIMARY KEY,                    name TEXT NOT NULL,                    age INTEGER,                    department TEXT                 )""")# Commit changes and close the cursorconn.commit()cursor.close()
Enter fullscreen modeExit fullscreen mode

Inserting data into tables

importsqlite3# Connect to a file-based SQLite databaseconn=sqlite3.connect(database="example.db")# Create a cursor object to execute SQL queriescursor=conn.cursor()# Insert data into the 'employees' tablecursor.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 cursorconn.commit()cursor.close()
Enter fullscreen modeExit fullscreen mode

Reading data from tables

importsqlite3# Connect to a file-based SQLite databaseconn=sqlite3.connect(database="example.db")# Create a cursor object to execute SQL queriescursor=conn.cursor()# Read data from the 'employees' tablecursor.execute("SELECT name, age, department FROM employees")rows=cursor.fetchall()forrowinrows:print(row)# Close the cursorcursor.close()
Enter fullscreen modeExit fullscreen mode

Output:

(1, 'John Doe', 30, 'HR')(2, 'Jane Smith', 35, 'Finance')
Enter fullscreen modeExit fullscreen mode

Updating data in tables

importsqlite3# Connect to a file-based SQLite databaseconn=sqlite3.connect(database="example.db")# Create a cursor object to execute SQL queriescursor=conn.cursor()# Update data in the 'employees' tablecursor.execute("UPDATE employees SET department ='IT' WHERE name ='John Doe'")# Commit changes and close the cursorconn.commit()cursor.close()
Enter fullscreen modeExit fullscreen mode

Removing data from tables

importsqlite3# Connect to a file-based SQLite databaseconn=sqlite3.connect(database="example.db")# Create a cursor object to execute SQL queriescursor=conn.cursor()# Delete data from the 'employees' tablex=cursor.execute("DELETE FROM employees WHERE name ='Jane Smith'")# Commit changes and close the cursorconn.commit()cursor.close()
Enter fullscreen modeExit 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 likeDBeaver Community offer comprehensive tools for interacting with SQLite database files in a graphical user interface, providing an alternative approach to database management.

Top comments(0)

Subscribe
pic
Create template

Templates let you quickly answer FAQs or store snippets for re-use.

Dismiss

Are you sure you want to hide this comment? It will become hidden in your post, but will still be visible via the comment'spermalink.

For further actions, you may consider blocking this person and/orreporting abuse

  • Location
    Portugal
  • Education
    ISEP
  • Work
    Senior Software Developer @ adidas
  • Joined

More from≀Paulo Portela

DEV Community

We're a place where coders share, stay up-to-date and grow their careers.

Log in Create account

[8]ページ先頭

©2009-2025 Movatter.jp