Tuesday, March 4, 2025

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.

Posted by at No comments:
Labels: ,

Saturday, March 1, 2025

Day 1 - Exploring SQLite using Translation App's DB

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

How to run SQLite on Windows?

To run SQLite on Windows, follow these steps:

1. Download SQLite

  1. Go to the official SQLite website:
    🔗 https://www.sqlite.org/download.html
  2. Scroll down to the "Precompiled Binaries for Windows" section.
  3. Download "SQLite Tools for Windows" (ZIP file).
  4. Extract the ZIP file to a folder (e.g., C:\sqlite).

2. Add SQLite to System PATH (Optional, for easier access)

  1. Copy the extracted folder path (e.g., C:\sqlite).
  2. Search for "Environment Variables" in Windows.
  3. Under System Variables, find and edit the Path variable.
  4. Click New and paste C:\sqlite.
  5. Click OK to save changes.

Now, you can run sqlite3 from any command prompt.

3. Run SQLite

Option 1: Using Command Prompt

  1. Open Command Prompt (cmd).
  2. Navigate to the SQLite folder (if you didn't add it to PATH):
    sh
    cd C:\sqlite
  3. Start SQLite by running:
    sh
    sqlite3
  4. To open an existing database or create a new one:
    sh
    sqlite3 my_database.db
  5. To check tables in the database:
    sql
    .tables
  6. To exit SQLite:
    sh
    .exit

Option 2: Using SQLite GUI Tools (Optional)

If you prefer a graphical interface, you can download a GUI tool:

This tool allows you to easily create and manage SQLite databases without using the command line.

Option 3: Using SQLite with Python

If you want to interact with SQLite in Python, use the sqlite3 module:

python
import sqlite3

# Connect to a database (or create one)
conn = sqlite3.connect("my_database.db")

# Create a cursor
cursor = conn.cursor()

# Create a table
cursor.execute("CREATE TABLE IF NOT EXISTS users (id INTEGER PRIMARY KEY, name TEXT);")

# Insert data
cursor.execute("INSERT INTO users (name) VALUES ('Alice');")
conn.commit()

# Query data
cursor.execute("SELECT * FROM users;")
print(cursor.fetchall())

# Close connection
conn.close()

Now you have SQLite running on Windows! 🎉 Let me know if you need more help. 🚀

Show all tables.

To show all tables in an SQLite database, use the following SQL query:

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

Explanation:

  • sqlite_master: This is a system table in SQLite that stores information about all database objects (tables, indexes, views, triggers).
  • WHERE type='table': Filters the results to show only tables (excluding indexes, views, etc.).

Alternative (Using .tables in SQLite CLI)

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

sh
.tables

This will list all the tables in the current database.

Let me know if you need help with something specific! 😊

Get table schema using SQL.

To get the schema of a table in SQLite, you can use the following SQL command:

sql
PRAGMA table_info(your_table_name);

Explanation:

  • PRAGMA table_info(your_table_name): Returns details about the table structure, including:
    • Column ID (cid)
    • Column Name (name)
    • Data Type (type)
    • Nullable Constraint (notnull)
    • Default Value (dflt_value)
    • Primary Key Status (pk)

Example Usage:

sql
PRAGMA table_info(HindiToEnglishApp);

Output Example:

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

Alternative: Using sqlite_master

To get the CREATE TABLE statement that defines a table's schema, use:

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 Example:

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

This query retrieves the exact SQL statement that was used to create the table.

Let me know if you need more details! 😊

Viewing All Tables


Using sqlite_master table
sqlite> SELECT name FROM sqlite_master WHERE type='table';

attempted_hindi_to_eng_ques
attempt_log
ques_info
hindi_to_english_ques_bank
user_login


Using CLI

sqlite> .tables

attempt_log                  ques_info                  
attempted_hindi_to_eng_ques  user_login                 
hindi_to_english_ques_bank



Viewing the create statement for different tables


sqlite> SELECT sql FROM sqlite_master WHERE type='table' AND name='user_login';

CREATE TABLE user_login (
username text PRIMARY KEY,
password text NOT NULL
)

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

CREATE TABLE ques_info (
pair_id INTEGER PRIMARY KEY,
chapter INTEGER NOT NULL,
title TEXT NOT NULL,
    subject TEXT not null,
grade INTEGER NOT NULL,
    FOREIGN KEY (pair_id) REFERENCES hindi_to_english_ques_bank (pair_id) 
)

sqlite> SELECT sql FROM sqlite_master WHERE type='table' AND name='hindi_to_english_ques_bank';

CREATE TABLE hindi_to_english_ques_bank (
pair_id INTEGER PRIMARY KEY,
hindi text NOT NULL,
english text NOT NULL UNIQUE
)

sqlite> SELECT sql FROM sqlite_master WHERE type='table' AND name='attempted_hindi_to_eng_ques';

CREATE TABLE attempted_hindi_to_eng_ques (
username text not null,
pair_id integer not null,
flag integer not null DEFAULT 0,
PRIMARY KEY (username, pair_id)
FOREIGN KEY (pair_id) REFERENCES hindi_to_english_ques_bank (pair_id)
)

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

CREATE TABLE attempt_log (
    attempt_time DATETIME NOT NULL,
    username TEXT NOT NULL,
pair_id INTEGER NOT NULL,
    result BOOLEAN NOT NULL,
    FOREIGN KEY (pair_id) REFERENCES hindi_to_english_ques_bank (pair_id) 
)


Viewing table data


sqlite> select * from ques_info;

1|1.1|Ram and Mango|English|1
2|1.1|Ram and Mango|English|1
3|1.1|Ram and Mango|English|1
4|1.1|Ram and Mango|English|1
5|1.1|Ram and Mango|English|1
6|1.2|To|English|1
7|1.2|To|English|1
8|1.2|To|English|1
9|1.2|To|English|1
10|1.2|To|English|1
11|1.3|Sense Organs|English|1
12|1.3|Sense Organs|English|1
13|1.3|Sense Organs|English|1
14|1.3|Sense Organs|English|1
15|1.3|Sense Organs|English|1
...

sqlite> select * from hindi_to_english_ques_bank;

1|छत पर मत चढ़|Do Not Climb On The Roof
2|राम आम खाता है|Ram Eats Mango
3|वह इंग्लिश पढ़ता है|He Reads English
4|राम ने आम खाया|Ram Ate Mango
5|राम आम खा रहा है|Ram Is Eating Mango
6|वे आम खाते हैं|They Eat Mango
7|राम द्वारा आम खाया जाता है|Mango Is Eaten By Ram
8|हाथ ताली बजाने के लिए|Hands To Clap
9|पैर चलने के लिए|Legs To Walk
10|नाक सूँघने के लिए|Nose To Smell
11|मुँह बातचीत के लिए|Mouth To Talk
12|सिर सोचने के लिए|Head To Think
13|आँखें देखने के लिए|Eyes To See
14|दो छोटे हाथ बजाएं ताली ताली ताली|Two Little Hands Go Clap Clap Clap
15|दो छोटे पैर बजाएं टैप टैप टैप|Two Little Legs Go Tap Tap Tap
Tags: Database,Technology,
Posted by at No comments:
Labels: ,
Subscribe to: Posts (Atom)