hw4_sql_basics
Creating databases¶
Your boss wants you to import some CSV data into a database so all the analysts can process the data. The 2 files are:
Copyright By PowCoder代写 加微信 powcoder
customers.csv that contains customer information
activities.csv that contains daily user activities.
Please create a database called demo.db using sqlite3 with 2 tables named customers and activities respectively.
You should have a sqlite3.Connection object called conn
You should have a sqlite3.Cursor object called cursor
Boolean values should be stored as TEXT
You should follow the class example where no primary keys are set and we do not set default values.
Warning, do not close the connection in the cell below.
If you created the table incorrectly or want to run the CREATE TABLE command multiple times, you may have to use DROP TABLE {table} to erase the table first.
from glob import glob
import pandas as pd
fns = glob(“./*.csv”)
print(fns)
[‘./customers.csv’, ‘./activities.csv’]
### TEST FUNCTION: test_create_table
# DO NOT REMOVE THE LINE ABOVE
customers = pd.read_csv(“customers.csv”)
activities = pd.read_csv(“activities.csv”)
conn=sqlite3.connect(“demo.db”)
cursor = conn.cursor()
—————————————————————————
NameError Traceback (most recent call last)
Cell In [1], line 6
4 customers = pd.read_csv(“customers.csv”)
5 activities = pd.read_csv(“activities.csv”)
—-> 6 demo.db
7 conn=sqlite3.connect(“demo.db”)
8 cursor = conn.cursor()
NameError: name ‘demo’ is not defined
Exploring the databases and tables¶
Please use your cursor variable from above and write the SQL query that will return all the names of the tables in the demo.db database.
Please create a variable, tables, that is a list of strings where the string values are the table names (order does not matter).
Please use tables to create a dictionary called schema. schema should have keys corresponding to the table names and values corresponding to the output from the SQL query PRAGMA table_info(‘{TABLE_NAME}’)
### TEST FUNCTION: test_explore_db
# DO NOT REMOVE THE LINE ABOVE
Making some queries¶
Please answer all of the following questions using SQL queries, there is no need to call fetchall(). We will use your query in the testing.
How many different users visited on “2022/01/02”? A visiting user is someone who has a row in the activities table. Please assign the SQL query, as a string, to a variabled called query1.
### TEST FUNCTION: test_sql1
# DO NOT REMOVE THE LINE ABOVE
query1 = “””
Making some queries¶
How many different subscribers visited on “2022/01/02”? Please assign the SQL query, as a string, to a variabled called query2. Hint: check ‘subscriber’ defined in ‘customer’ and use JOIN
### TEST FUNCTION: test_sql2
# DO NOT REMOVE THE LINE ABOVE
query2 = “””
Makeing some queries¶
For each day, please calculate, in the following order:
the number of unique visitors
the total pageviews across all users
the total likes across all users
the total writes across all users
the total activities across all users, total activities = total pageviews + total likes + total writes.
Please assign the query the answer to a variabled called query3.
### TEST FUNCTION: test_sql3
# DO NOT REMOVE THE LINE ABOVE
connecton=sqlite3.connect(“demo.db”)
cursor=connection.cursor()
query3 = “””
activities.date,
COUNT(DISTINCT(activities.name)),
SUM(activities.pageviews),
SUM(COALESCE(activities.like,0)),
SUM(activities.writes),
SUM(COALESCE(activities.pageviews,COALESCE))
From activities
GROUP BY date
output=cursor.execute(query).fetchall()
程序代写 CS代考 加微信: powcoder QQ: 1823890830 Email: powcoder@163.com