SQL Magic

Date: February 24th 2018
Last updated: February 25th 2018

Install pip3

sudo apt-get install python3-pip
pip3 install --upgrade pip

Create virtualenv

mkvirtualenve sqlmagic -p python3

Install libs

pip install jupyter
pip install ipython-sql
pip install pandas

Connect to sql magic

%load_ext sql

Create DB

%sql sqlite:///foo.db

Create table

%%sql 
CREATE TABLE test (name);
INSERT INTO test VALUES("Ray");

Select

%sql SELECT * FROM test

Table info

%sql pragma table_info(test)

Create data

import pandas as pd
pd.DataFrame(
    ['tom',
     'helen',
     'spot',
     'steve'], 
    columns=["name"]
).to_csv("dummydata.csv", index=False)

Import data

dat = pd.read_csv('dummydata.csv')
%sql PERSIST dat

Insert data

%%sql 
INSERT INTO TEST 
SELECT name from dat;

Checks

%sql SELECT * FROM test;