Execute PostgreSQL Query Using Jupyter Notebook | SQL In Statistics

Configure PostgreSQL With Jupyter Notebook

First need to install:

#!pip install sqlalchemy==1.3.9
#!pip install ibm_db_sa
!pip install ipython-sql

Now installing psycopg2

!pip install psycopg2-binary

Adding Postgresql, Username , password and database

#%load_ext sql
%sql postgresql://username:Password@localhost/databasename

Now it connect with PostgreSQL.

Make a connection:

import psycopg2 as pg
import pandas.io.sql as psql
conn = pg.connect(database="nav**",user="postgres", password="Naveen@***")

Test the Query

%sql SELECT * FROM table1;

Output:

 * postgresql://postgres:***@localhost/naveen
2 rows affected.

Query1:

List the average length of the colums in the database.

%sql SELECT length(name) AS "Length" FROM table1;

Output:

* postgresql://postgres:***@localhost/naveen 2 rows affected.

Out[52]: length

Query 1:

List the Film Title (film.title) and language (language.name) for each film in the database

Query 2:

List each film and the name of actors in that film, in order of film title then actor last name.

Query 3:

List each customer's id, name, postal code, and phone number.

Query 4:

List the film title with the customer name who rented the film, in order of film title

Query 5:

List a unique (DISTINCT) set of film titles with the staff last name who rented out the film.

realcode4you@gmail.com

RealCode4You