Import and Export Data between CSV and SQLite Database using SQL

Pubblicato il: 03 marzo 2024
sul canale di: sql and python

111

Webpage:
https://sqlandpy.com/2024/03/03/impor...

In this tutorial, we will learn how to import data from a CSV file into an SQLite database using SQL commands, and also how to export data from an SQLite database to a CSV file. This process is commonly used when working with structured data and performing database operations.

Prerequisites:
To follow along with this tutorial, you should have a basic understanding of SQL and have SQLite installed on your system. Additionally, you should have a CSV file containing the data you wish to import.

Step 1: Installing SQLite:
If you don't have SQLite installed on your system, you can download it from the official SQLite website (https://www.sqlite.org/download.html) and follow the installation instructions for your operating system.

Step 2: Creating an SQLite Database:
To create an SQLite database, open a command prompt or terminal and navigate to the directory where you want to create the database file.
In my case: C:\Data\sqlite
Where I have the SQLite exe installed.

Then, run the following command:

'''bash
sqlite3 mydatabase.db
'''

This will create a new SQLite database file named 'mydatabase.db'. You can replace 'mydatabase' with your preferred name.

Step 3: Importing Data from CSV into an SQLite Database:
Once you have the SQLite database created, you can use SQL commands to import data from a CSV file. The SQLite command to import data from a CSV file is 'IMPORT'.

'''sql
.mode csv
.import 'C:\Data\sample_data\california_housing_test.csv' tablename
'''

The '.mode csv' command sets the mode to CSV, and the '.import' command imports the data from the CSV file into the specified table in the SQLite database. Replace the path with the actual path to your CSV file, and 'tablename' with the desired name for the table.

Step 4: Querying the SQLite Database:
After importing the data, you can perform various SQL queries to manipulate and analyze the data in the SQLite database. Here are a few useful SQL commands:

'SELECT' to Retrieve data from one or more tables.
'INSERT INTO' to Insert new records into a table.
'UPDATE' to Modify existing records in a table.
'DELETE FROM' to Remove records from a table.
1.
Select * From tablename
-- Drop Table tablename

2.
INSERT INTO tablename
(longitude, latitude, housing_median_age, total_rooms, total_bedrooms, population, households, median_income, median_house_value)
VALUES('-200.000000', '20.000000', '20.000000', '20.000000', '20.000000', '20.000000', '20.000000', '2.000000', '200000.000000');

INSERT INTO tablename
(longitude, latitude, housing_median_age, total_rooms, total_bedrooms, population, households, median_income, median_house_value)
VALUES('-300.000000', '30.000000', '30.000000', '3000.000000', '300.000000', '300.000000', '300.000000', '3.000000', '300000.000000');

3.
Select * From tablename Where longitude = '-200.000000'

Update tablename Set latitude = '40.000000' Where longitude = '-200.000000'

Select * From tablename Where longitude = '-200.000000'

4.
Select * From tablename

Delete From tablename Where longitude = '-300.000000'

Select * From tablename

Step 5: Exporting Data from SQLite Database to CSV:
To export data from an SQLite database to a CSV file, we can use the '.output' and '.mode' commands in SQLite.

'''sql
.mode csv
.headers on
.output 'C:\Data\sample_data\california_housing_test_backup_sql.csv'
SELECT * FROM tablename;
'''

Check the CSV.

The '.mode csv' command sets the output mode to CSV, '.headers on' includes column headers in the output, and '.output' specifies the path and filename for the exported CSV file. Replace the path with the desired path and filename, and 'tablename' with the name of the table you want to export.

Conclusion:
In this tutorial, we learned how to import data from a CSV file into an SQLite database using SQL commands. We also explored basic SQL operations for querying the database and exporting data from an SQLite database to a CSV file. This knowledge will be valuable for managing structured data and performing database operations using SQL.

Remember to adapt the file paths and table names to match your specific case. Additionally, explore the SQLite documentation for more advanced SQL operations and options available for working with databases.

In questa pagina del sito puoi guardare il video online Import and Export Data between CSV and SQLite Database using SQL della durata di ore minuti seconda in buona qualità , che l'utente ha caricato sql and python 03 marzo 2024, condividi il link con amici e conoscenti, su youtube questo video è già stato visto 111 volte e gli è piaciuto 0 spettatori. Buona visione!

994