📝 How to Use SQLite in Python (Without Installing a Database Server)
When developers think of databases, they usually think of PostgreSQL, MySQL, or MongoDB. They think of servers, ports, and complex installations. But if you are building a lightweight app, a local script, or an AI tool on constrained hardware (like an Android phone via Termux), you don't need a database server. You need SQLite. SQLite is a C library that provides a lightweight disk-based…
Developers often associate databases with heavy-duty options like PostgreSQL, MySQL, or MongoDB. They envision servers, ports, and intricate setups. However, when crafting a lightweight application, a local script, or an AI tool on resource-restricted hardware such as an Android phone via Termux, there is no need for a database server. SQLite suffices.
SQLite is a C library that offers a lightweight, disk-based database. It does not require a separate server process and allows interaction via a nonstandard SQL variant. The best part? It is integrated into Python. Here is how to utilize it within 30 seconds.
Firstly, the built-in module. The sqlite3 library is part of Python's Standard Library, so there is no need for additional installation. Simply import it using `import sqlite3`.
Secondly, connecting to the database and creating a table. Upon attempting to connect to a database file, SQLite automatically generates it if it does not exist. To create a table named "users" with fields for id, name, and email, execute the following command:
```
conn = sqlite3.connect('app.db')
cursor = conn.cursor()
cursor.execute('CREATE TABLE IF NOT EXISTS users (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT NOT NULL, email TEXT NOT NULL UNIQUE)')
conn.commit()
```
Thirdly, insert data securely. Do not use f-strings for inserting data into SQL as they expose your application to SQL injection attacks. Instead, utilize the '?' placeholder. To add a new user, utilize the following command:
```
cursor.execute('INSERT INTO users (name, email) VALUES (?, ?)', ('Ezenwa', 'ezenwa@example.com'))
conn.commit()
```
Next, fetching data. After saving data, query it using `fetchall()` or `fetchone()`. Retrieve all rows from the "users" table using the following command:
```
cursor.execute('SELECT id, name, email FROM users')
rows = cursor.fetchall()
for row in rows:
print(row)
```
Lastly, always remember to close the connection when your script is complete to conserve system resources. Close the connection using the command `conn.close()`.
Why choose SQLite? If you are developing a mobile application, a command-line tool, or an AI script running on a local machine, SQLite is an excellent choice. It is zero-configuration, extremely fast for local data, and does not consume the memory that a comprehensive database server like PostgreSQL would. Have you integrated SQLite into your Python projects? Share your experiences below!
Written by urgent.news from Dev.to's reporting — not their text. Machine-written — may contain errors; check the original before relying on it.
