Working with SQLITE on COLAB

Execute SQL statement directly in Colab Cell

Load Sqlite Extension

%load_ext sql

Connect to Sqlite Database

%%sql
sqlite:///test.db

Execute SQL statement

create table demo(id int, title varchar);
insert into demo values (1,'pqr'),(2,'stu');
select * from demo;

colab example

Load remote CSV and save into SQLITE table

Load the remote CSV into a Pandas DataFrame

# load the remote CSV into a Pandas DataFrame
import pandas as pd
df_cfemotion = pd.read_csv('https://archive.org/download/crowdflower/text_emotion.csv', on_bad_lines='skip', encoding='latin-1')
df_cfemotion

Create connection to sqlite db and send csv data to sqlite table

#connect to sqlite db and send csv data to sqlite table
import sqlite3 as sql
conn = sql.connect('/content/cfemotion.db')
df_cfemotion.to_sql('tb_emotion', conn)

Connect to sqlite db and read from sqlite table

#connect to sqlite db and read from sqlite table
import sqlite3 as sql
conn = sql.connect('/content/cfemotion.db')
df_cfemotion = pd.read_sql('SELECT * FROM tb_emotion', conn)
df_cfemotion

colab example