Day 3 - Issue of "database locked" with SQLite

To See All Articles About Technology: Index of Lessons in Technology

How to resolve: sqlite3.OperationalError: database is locked

The "database is locked" error in SQLite occurs when a database file is accessed by multiple processes or connections simultaneously, causing contention. Here’s how to resolve it:

---

🔍 Possible Causes & Fixes

1. Ensure Proper Connection Closing

If you're using Python, ensure that the database connection is closed properly after executing queries.

python
import sqlite3

conn = sqlite3.connect("my_database.db")
cursor = conn.cursor()

# Execute your queries
cursor.execute("SELECT * FROM my_table;")

# Commit and close the connection
conn.commit()
conn.close()  # ✅ Always close the connection

👉 Fix: Always call conn.close() after executing queries.

---

2. Check for Unclosed Transactions

If a transaction (like INSERT, UPDATE, or DELETE) is left open without COMMIT, SQLite keeps the database locked.

sql
COMMIT;  -- Ensures any open transaction is completed

👉 Fix: Run COMMIT; in your SQL tool or application.

---

3. Close Other Processes Using the Database

Run the following SQL command in your SQLite session:

sql
PRAGMA busy_timeout = 5000;  -- Waits up to 5 seconds before failing

👉 Fix:

• If another process is using the DB file, close it (e.g., close DB Browser for SQLite, Python scripts, or any other app accessing the file).
• Restart your SQLite application.

---

4. Delete the .db-journal or .db-wal File

If SQLite crashed or was improperly closed, a journal file may still be locking the database.

1. Navigate to your database folder
2. Delete files ending in:
   • my_database.db-journal
   • my_database.db-wal

👉 Fix: Remove these files safely if no other process is using the database.

---

5. Open SQLite in Read-Write Mode

If another process is holding a write lock, try opening the DB in read-only mode:

python
conn = sqlite3.connect("file:my_database.db?mode=ro", uri=True)

👉 Fix: Use mode=ro if you don’t need to write to the database.

---

6. Kill Hanging Processes (Windows/Linux)

On Windows, find processes locking the file:

sh
tasklist | findstr sqlite

Kill a process using:

sh
taskkill /F /PID <process_id>

On Linux/macOS, find processes:

sh
lsof my_database.db

Kill the process:

sh
kill -9 <process_id>

👉 Fix: Restart any process holding the database.

---

🚀 Best Practices to Avoid "Database Locked" Issues

✅ Use PRAGMA busy_timeout:

sql
PRAGMA busy_timeout = 5000;  -- Waits 5 sec before failing

✅ Use a Single Database Connection
Avoid opening multiple connections in the same script.

✅ Close the Connection Properly
Always call conn.close() after database operations.

✅ Use WAL Mode for Better Concurrency
Enable Write-Ahead Logging (WAL) for better handling of multiple connections:

sql
PRAGMA journal_mode = WAL;

---

Try these fixes, and let me know if the issue persists! 🚀

... ... ... ... ... ... ... ... ... ... ... ...

print the pid for "ps aux | grep sqlite"

To print only the PID of processes related to SQLite using ps aux | grep sqlite, use this command:

sh
ps aux | grep sqlite | awk '{print $2}'

Explanation:

• ps aux → Lists all running processes.
• grep sqlite → Filters processes containing "sqlite".
• awk '{print $2}' → Extracts and prints only the PID (which is in the second column).

Alternative (More Precise)

If you want to exclude the grep command itself from the results:

sh
ps aux | grep '[s]qlite' | awk '{print $2}'

Example Output:

yaml
1234
5678

These are the PIDs of processes running SQLite.

👉 To kill a process, use:

sh
kill -9 <PID>

Let me know if you need more help! 🚀

... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ...

ps -ef what does it do?

The ps -ef command in Linux is used to display a detailed list of all running processes on the system.

---

🔍 Breakdown of ps -ef

• ps → Shows active processes.
• -e → Displays all processes (not just those owned by the current user).
• -f → Provides full format output (more details).

---

📌 Example Output

sh
UID       PID   PPID  C STIME TTY          TIME CMD
root       1      0   0 Feb24 ?        00:01:23 /sbin/init
user1   1050     1   0 10:30 ?        00:00:00 /usr/bin/gnome-session
user1   2080  1050   0 10:31 ?        00:00:01 /usr/bin/firefox

---

📌 Column Explanation

Column	Meaning
UID	User who started the process
PID	Process ID
PPID	Parent Process ID (which process started this one)
C	CPU usage percentage
STIME	Process start time
TTY	Terminal associated with the process (if any)
TIME	CPU time used
CMD	Command that started the process

---

🔍 Useful Variations

✅ Show only processes of a specific user:

sh
ps -ef | grep username

✅ Find a specific process (e.g., SQLite):

sh
ps -ef | grep sqlite

✅ Show hierarchical (parent-child) relationships:

sh
ps -ef --forest

---

Let me know if you need more details! 🚀😊

Day 2 - with SQLite while working on Translation App

To See All Articles About Technology: Index of Lessons in Technology

>>> Display table schema.

To display the schema of a table in SQLite, you can use the following methods:

---

1. Using PRAGMA table_info (Detailed Column Info)

sql
PRAGMA table_info(your_table_name);

Example:

sql
PRAGMA table_info(HindiToEnglishApp);

Output:

pgsql
cid | name       | type      | notnull | dflt_value | pk
----+-----------+----------+--------+------------+----
0   | id        | INTEGER  | 1      | NULL       | 1
1   | word      | TEXT     | 1      | NULL       | 0
2   | meaning   | TEXT     | 0      | NULL       | 0

---

2. Using sqlite_master (Show CREATE TABLE Statement)

sql
SELECT sql FROM sqlite_master WHERE type='table' AND name='your_table_name';

Example:

sql
SELECT sql FROM sqlite_master WHERE type='table' AND name='HindiToEnglishApp';

Output:

sql
CREATE TABLE HindiToEnglishApp (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    word TEXT NOT NULL,
    meaning TEXT
);

---

3. Using .schema (Command Line)

If you're using the SQLite command-line interface, you can run:

sh
.schema your_table_name

Example:

sh
.schema HindiToEnglishApp

This will return the CREATE TABLE statement of the specified table.

---

These methods will help you view the schema of a table in SQLite. Let me know if you need more details! 😊🚀

>>> How to execute a create statement sql file in sqlite?

You can execute a SQL file containing CREATE statements in SQLite using several methods. Here are two common approaches:

---

1. Using the SQLite Command-Line Interface

• Interactive Mode:

  1. Open your terminal and run:

     bash
     sqlite3 mydatabase.db
     

  2. At the SQLite prompt, execute:

     sql
     .read create_statements.sql
     

     This will execute all SQL statements in your file against mydatabase.db.

• Direct Execution from Command Line:
  You can also run the SQL file without entering interactive mode:

  bash
  sqlite3 mydatabase.db < create_statements.sql
  

---

2. Using Python's sqlite3 Module

If you prefer to execute the SQL file programmatically, you can use Python:

python
import sqlite3

# Connect to your SQLite database (or create it if it doesn't exist)
conn = sqlite3.connect('mydatabase.db')
cursor = conn.cursor()

# Open and read the SQL file
with open('create_statements.sql', 'r') as file:
    sql_script = file.read()

# Execute all the SQL statements in the file
cursor.executescript(sql_script)

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

---

Both methods allow you to execute a SQL file with CREATE statements effectively. Choose the one that best fits your workflow or environment.