Import-Export CSV Data in SQLite Database

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

Export the data of a table as CSV

To export data from an SQLite table to a CSV file, you can use the following methods:

---

📌 Method 1: Using SQLite Command Line

1. Open your SQLite database in the terminal:

   sh
   sqlite3 my_database.db
       

2. Set output mode to CSV:

   sql
   .mode csv
       

3. Set the output file name:

   sql
   .output my_table_data.csv
       

4. Run the query to export:

   sql
   SELECT * FROM my_table;
       

5. Exit SQLite:

   sql
   .quit
       

✅ Now, my_table_data.csv will contain your table data in CSV format.

---

📌 Method 2: Using SQL Query

If you're inside SQLite, run:

sql
.mode csv
    .headers on
    .output my_table_data.csv
    SELECT * FROM my_table;
    

This exports data with column headers.

---

📌 Method 3: Using Python

If you prefer Python, use the csv module:

python
import sqlite3
    import csv
    
    # Connect to SQLite database
    conn = sqlite3.connect("my_database.db")
    cursor = conn.cursor()
    
    # Execute query
    cursor.execute("SELECT * FROM my_table")
    
    # Fetch data
    rows = cursor.fetchall()
    column_names = [desc[0] for desc in cursor.description]
    
    # Write to CSV
    with open("my_table_data.csv", "w", newline="") as f:
        writer = csv.writer(f)
        writer.writerow(column_names)  # Write headers
        writer.writerows(rows)         # Write data
    
    # Close connection
    conn.close()
    
    print("Data exported successfully!")
    

✅ This will create my_table_data.csv with column headers.

---

📌 Check Your Exported CSV

After exporting, verify the data using:

sh
cat my_table_data.csv
    

or

sh
head my_table_data.csv
    

---

Let me know if you need further help! 🚀😊

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

Import the CSV data into an SQLite table

To import CSV data into an SQLite table, use one of the following methods:

---

📌 Method 1: Using SQLite Command Line

1. Open your SQLite database in the terminal:

   sh
   sqlite3 my_database.db
       

2. Enable CSV mode:

   sql
   .mode csv
       

3. Import the CSV file into the table:

   sql
   .import my_data.csv my_table
       

4. Verify the data:

   sql
   SELECT * FROM my_table LIMIT 5;
       

✅ Your CSV data is now imported into my_table.

---

📌 Method 2: Using Python

If you prefer Python, use the csv module:

python
import sqlite3
    import csv
    
    # Connect to SQLite database
    conn = sqlite3.connect("my_database.db")
    cursor = conn.cursor()
    
    # Create table (if not exists)
    cursor.execute("""
    CREATE TABLE IF NOT EXISTS my_table (
        id INTEGER PRIMARY KEY,
        name TEXT,
        age INTEGER,
        city TEXT
    )
    """)
    
    # Open CSV file and insert data
    with open("my_data.csv", "r") as f:
        reader = csv.reader(f)
        next(reader)  # Skip header row
        for row in reader:
            cursor.execute("INSERT INTO my_table (id, name, age, city) VALUES (?, ?, ?, ?)", row)
    
    # Commit changes and close connection
    conn.commit()
    conn.close()
    
    print("CSV data imported successfully!")
    

✅ This script reads my_data.csv and inserts it into my_table.

---

📌 Method 3: Using Pandas (If You Have Large Data)

If you're dealing with large CSV files, use pandas:

python
import sqlite3
    import pandas as pd
    
    # Load CSV into Pandas DataFrame
    df = pd.read_csv("my_data.csv")
    
    # Connect to SQLite
    conn = sqlite3.connect("my_database.db")
    
    # Import DataFrame into SQLite table
    df.to_sql("my_table", conn, if_exists="replace", index=False)
    
    # Close connection
    conn.close()
    
    print("CSV data imported successfully!")
    

✅ Best for handling large datasets efficiently.

---

📌 Verify Import

Run this in SQLite to check your data:

sql
SELECT * FROM my_table LIMIT 5;
    

---

Let me know if you need further help! 🚀😊