1. Installation¶

Setting up the conda env:

conda create -n sql python
conda activate sql
conda install ipython-sql sqlalchemy psycopg2 notebook pandas -c conda-forge

Sample dataset:

cities.csv

1.1. Using ipython-sql¶

%load_ext sql

import os

host = "localhost"
database = "sdb"
user = os.getenv('SQL_USER')
password = os.getenv('SQL_PASSWORD')

connection_string = f"postgresql://{user}:{password}@{host}/{database}"

%sql $connection_string

%%sql 

SELECT * from cities LIMIT 10

 * postgresql://postgres:***@localhost/sdb
10 rows affected.

id	name	country	latitude	longitude	population
1	Bombo	UGA	0.5833	32.5333	75000
2	Fort Portal	UGA	0.671	30.275	42670
3	Potenza	ITA	40.642	15.799	69060
4	Campobasso	ITA	41.563	14.656	50762
5	Aosta	ITA	45.737	7.315	34062
6	Mariehamn	ALD	60.097	19.949	10682
7	Ramallah	PSE	31.90294	35.20621	24599
8	Vatican City	VAT	41.90001	12.44781	832
9	Poitier	FRA	46.58329	0.33328	85960
10	Clermont-Ferrand	FRA	45.77998	3.08001	233050

%%sql 

SELECT * from cities

1.2. Using sqlalchemy¶

from sqlalchemy import create_engine

engine = create_engine(connection_string)

from sqlalchemy import inspect

insp = inspect(engine)
insp.get_table_names()

['cities',
 'spatial_ref_sys',
 'us_states',
 'cities_new',
 'cities_usa',
 'us_counties',
 'Customers',
 'us_cities',
 'countries',
 'us_cities_csv']

import pandas as pd

df = pd.read_sql('SELECT * from cities LIMIT 10', engine)

df

	id	name	country	latitude	longitude	population
0	1	Bombo	UGA	0.5833	32.5333	75000
1	2	Fort Portal	UGA	0.671	30.275	42670
2	3	Potenza	ITA	40.642	15.799	69060
3	4	Campobasso	ITA	41.563	14.656	50762
4	5	Aosta	ITA	45.737	7.315	34062
5	6	Mariehamn	ALD	60.097	19.949	10682
6	7	Ramallah	PSE	31.90294	35.20621	24599
7	8	Vatican City	VAT	41.90001	12.44781	832
8	9	Poitier	FRA	46.58329	0.33328	85960
9	10	Clermont-Ferrand	FRA	45.77998	3.08001	233050

About the Authors 2. SQL Basics