Creating Permanent View on Jupyter

Q&A DQ Courses
#1

On mission https://app.dataquest.io/m/190/building-and-organizing-complex-queries/4/creating-views
I am trying to create a permanent view in Jupyter from the chinook database. This is my code

import sqlite3    
conn = sqlite3.connect('chinook.db')
    q14 = '''CREATE VIEW customer_gt_90_dollars AS
        SELECT c.*
        FROM chinook.customer c
        INNER JOIN invoice i ON i.customer_id = c.customer_id
        GROUP BY 1
        HAVING SUM(i.total) > 90;

    SELECT * FROM customer_gt_90_dollars;
        '''
    result_14 = pd.read_sql(q14, conn)
    result_14.head(10)

This is the error i received: “OperationalError: view customer_gt_90_dollars cannot reference objects in database chinook”.

Any assistance will be highly appreciated.

1 Like
#2

The mission explains that a quirk of the dataquest interface is that if you need to reference tables or views as [database].[table or view name]. However, on a local machine or jupyter project you can drop the database reference.

You could try creating the view by querying FROM customer c instead of FROM chinook.customer c.

2 Likes
#3

Thanks for response, after trying the code, i still received an error as seen below

image
image.png1112×554 27.1 KB

Thinking i deciphered the error, i tried this code and it still returned an error as shown below

image
image.png998×560 26.6 KB

1 Like
#4

Hey @Uchencho,

The error OperationalError: view customer_gt_90_dollars cannot reference objects in database chinook comes from

sql FROM chinook.customer c

Because you have read the database as chinook, there is no table with the name chinook.customer, only customer table exists.

To fix the bug:

Change

sql FROM chinook.customer c

To:

sql FROM customer c

Create VIEW issue:

However, you cannot create a view multiple time when you run the script. The code below will fix the problem:

CREATE VIEW IF NOT EXISTS

Command issues:
CREATE are commands, you need to execute instead of query.

   def run_command(c):
        with sqlite3.connect(db) as conn:
            # Auto commit changes
            conn.isolation_level = None
            conn.execute(c)

You only can execute one command at time. Hence, you need to separate the SQL statement into a CREATE statement and SELECT statement.

create_view = """
    CREATE VIEW IF NOT EXISTS customer_gt_90_dollars AS
        SELECT c.*
        FROM customer c
        INNER JOIN invoice i ON i.customer_id = c.customer_id
        GROUP BY 1
        HAVING SUM(i.total) > 90;
    """
see_view = """
    SELECT * FROM customer_gt_90_dollars;
    """

run_command(create_view)
print(show_tables())

The entire code as follows:

import sqlite3
import pandas as pd
from pandas import read_sql
db = "./chinook.db"

def run_query(q):
    with sqlite3.connect(db) as conn:
        return read_sql(q, conn)

def run_command(c):
    with sqlite3.connect(db) as conn:
        # Auto commit changes
        conn.isolation_level = None
        conn.execute(c)

def show_tables():
    q = """
    SELECT
        name,
        type
    FROM
        sqlite_master
    WHERE
        type IN ("table", "view");
    """
    return run_query(q)

create_view = """
    CREATE VIEW IF NOT EXISTS customer_gt_90_dollars AS
        SELECT c.*
        FROM customer c
        INNER JOIN invoice i ON i.customer_id = c.customer_id
        GROUP BY 1
        HAVING SUM(i.total) > 90;
    """
see_view = """
    SELECT * FROM customer_gt_90_dollars;
    """

run_command(create_view)
print(show_tables())
print(run_query(see_view).head())
3 Likes
#5

Works, thanks a lot. I really appreciate it @alvinctk

2 Likes