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.