How to use sqlite3 to operate SQLite database in Python

Preface

In Python development,SQLiteIt is a lightweight relational database, itNo need to install a database server separately, all data is stored in oneSeparate filesIt is suitable for small applications, mobile development, data analysis and other scenarios. Python provides built-insqlite3Module, which facilitates us to operate SQLite databases. This article will introduce in detailsqlite3The use of modules includesDatabase connection, table operations, data addition, deletion, modification and query (CRUD), and commonTransaction ManagementandParameterized query, help you quickly master the methods of Python operating SQLite databases.

1. Why choose SQLite?

✅ Zero configuration: Python built-in support, no additional database services are required.

✅ File storage: The database is just one.dbFiles are easy to manage.

✅ Lightweight and efficient: Suitable for small applications, test environments, and mobile storage.

✅ SQL Support: Supports SQL syntax and is easy to migrate with other databases.

📌 SQLite applicable scenarios：

• Desktop application(For example, browser and notepad applications store user data).
• Mobile apps(Native database for Android, iOS).
• Data Analysis(Storing small datasets, replacing CSV files).

2. Connect to SQLite database

2.1 Connect or create a database

use()Connect to the database:

import sqlite3

# Connect to the database (create if it does not exist)conn = ("")

print("Database connection is successful")

📌 Features：

• ifExist, connect it, otherwiseAutomatically createThis file.

2.2 Get Cursor

Database operations require useCursor object (cursor)：

cursor = ()

📌 effect：

• (sql)Used to execute SQL statements.
• ()Get the query results.

3. Create a table (CREATE TABLE)

useCREATE TABLEStatement creates a database table:

('''
    CREATE TABLE IF NOT EXISTS users (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT NOT NULL,
        age INTEGER,
        city TEXT
    )
''')
()  # Submit changesprint("Table creation successfully")

📌 Analysis：

• id INTEGER PRIMARY KEY AUTOINCREMENT: The primary key increases by itself.
• TEXT NOT NULL：nameMust have a value.
• IF NOT EXISTS: Avoid repeated creation of tables.

4. Insert data (INSERT INTO)

4.1 Insert a single piece of data

("INSERT INTO users (name, age, city) VALUES (?, ?, ?)",
               ("Alice", 25, "New York"))
()
print("Insert data successfully")

📌 Notice：

• ? PlaceholderAvoid SQL injection.
• () Submit transactions, otherwise the data will not be saved.

4.2 Insert multiple pieces of data (executemany())

users = [
    ("Bob", 30, "San Francisco"),
    ("Charlie", 28, "Los Angeles"),
    ("David", 35, "Seattle")
]

("INSERT INTO users (name, age, city) VALUES (?, ?, ?)", users)
()
print("Batch insertion successful")

📌 Applicable to：

• Bulk writing data, avoid multiple timesexecute()Improve efficiency.

5. Query data (SELECT)

5.1 Query all data

("SELECT * FROM users")
rows = ()

for row in rows:
    print(row)

📌 fetchall() Get all data,returnList：

(1, 'Alice', 25, 'New York')
(2, 'Bob', 30, 'San Francisco')
(3, 'Charlie', 28, 'Los Angeles')

5.2 Query a single piece of data (fetchone())

("SELECT * FROM users WHERE name = ?", ("Alice",))
user = ()
print(user)

📌 fetchone() Get a single piece of data, suitable forQuery unique record。

6. Update data (UPDATE)

("UPDATE users SET age = ? WHERE name = ?", (26, "Alice"))
()
print("Data update successfully")

📌 make sure：

• SET age = ?Update onlyageColumns to avoid mistaken modification of other data.

7. Delete data (DELETE)

("DELETE FROM users WHERE name = ?", ("David",))
()
print("Data deletion succeeded")

📌 Be careful：

• No WHERE condition will delete all data！
• FirstSELECTConfirm whether the data exists.

8. Transaction management (commit() and rollback())

8.1 commit() commit() commit transaction

()  # Ensure data is written to the database

📌 After each INSERT, UPDATE, or DELETE, commit() must ensure data persistence。

8.2 rollback() Rollback transactions

try:
    ("UPDATE users SET age = ? WHERE name = ?", (100, "Charlie"))
    raise Exception("Simulate exception")  # Simulation error    ()
except Exception as e:
    ()  # Roll back when an error occurs    print("Transaction rollback:", e)

📌 effect：

• If the operation fails, roll back to the last timecommit(), prevent errors from affecting the database.

9. Close the database connection

()
()
print("Database connection closed")

📌 Best Practices：

• After all database operations are completed, the connection should be closed, free up resources.

10. Automatically manage connections in combination with with statements

Provided by Pythonwithgrammar,Automatically close database connection：

with ("") as conn:
    cursor = ()
    ("SELECT * FROM users")
    print(())

📌 Advantages：

• Automatic commit()
• Automatic rollback() when an exception occurs
• Automatically close when exiting conn

11. Use Row to access data in a dictionary

defaultfetchall()returnTuples：

("SELECT * FROM users")
print(())  # (1, 'Alice', 25, 'New York')

Can be used insteadMake data availableDictionary access：

conn.row_factory =   # Set up a row factorycursor = ()

("SELECT * FROM users")
row = ()
print(row["name"], row["age"])  # Alice 25

📌 Applicable to：

• Improve readability,avoidrow[0]Such index access method.

12. Conclusion

sqlite3It is a built-in database module in Python, suitable forSmall applications, tests, data storageetc. Master these operations and you canEasily manage SQLite databases, improve development efficiency!

Summarize

This is the end of this article about how to use sqlite3 to operate SQLite databases in Python. For more related content on Python sqlite3 to operate SQLite, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!