Day 2 - with SQLite and A bit of Linux

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.

---

A couple of Linux queries

Can I delete : ~/.cache/pip

Yes, you can delete the ~/.cache/pip directory. This directory is used by pip to cache packages and metadata to speed up subsequent installations. Deleting it will not harm your system, but it will mean that pip will need to re-download any packages you install in the future, which might take a bit longer.

To delete the directory, you can use the following command in your terminal:

sh
rm -rf ~/.cache/pip

Make sure to double-check the command to avoid accidentally deleting the wrong files or directories.

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

Delete __pycache__ from all subdirectories

To delete all __pycache__ directories (including all their contents) from the current directory and its subdirectories, use the following find command:

bash
find . -type d -name "__pycache__" -exec rm -rf {} +

Explanation:

• .: Start searching from the current directory.
• -type d: Search for directories only.
• -name "__pycache__": Match directories named __pycache__.
• -exec rm -rf {} +: Executes the rm -rf command on each found directory ({} represents the directory name). The + batches the deletions into fewer rm calls, making it more efficient.

Important: This command permanently deletes the __pycache__ directories, so double-check you’re in the correct directory before running it.