这是一篇美国的数据库python代写，需要画图和答题

 

Overview

Instructions

• There is an Ed discussion thread (https://edstem.org/us/courses/18760/discussion/1464598) that will specify completion and submission instructions.

Due Date, Completing the Exam and Rules

1. The fifinal exam is due at 11:59 PM on Monday, 08-May-2022. You are not allowed to use late days.
2. You may use on-line information and sources to answer questions. But,

a.You cannot simply cut and paste answers or code. Your answer must demonstrate that you understood the material and are capable of producing an answer from your understanding.

b.You must cite any sources of information that you used. This can simply be a comment in a text/markdown cell in your answer. For example, (Note: I used https://www.w3schools.com/sql/sql_check.asp to help me with the syntax for adding a check constraint).The following cell(s) load information about CU courses.

c.You do NOT need to cite lecture notes, recordings, slides, … You do not need to cite information from the recommended textbook or textbook slides.

3. You MUST NOT collaborate with ANYONE, including other students. You MAY speak with the professor or a TA to discuss the exam.

4. If you have questions, post them as PRIVATE question on Ed discussion and use the Category Exams->Final.
5. There is a pinned Ed discussion thread Midterm Clarififications (https://edstem.org/us/courses/18760/discussion/1464595) that the professor and TA will use to communicate updates and clarififications. Students are responsible for checking this post.

6. Individual questions provide explanations for how to complete the question.

Environment Setup and Test

1. This section tests your environment.
2. You will need to change the URLs and password in some of the cells below to match your confifiguration.
3. You may need to load data and copy databases. The relevant questions provide information.
4. You must read the comments in the setup cells. You will need objects and functions in the cells for some questions.

MySQL

In [1]:%load_ext sql

In [2]:# Connect to MySQL. Replace dbuser:dbuserdbuser with your MySQL user ID and password. %sql mysql+pymysql://dbuser:dbuserdbuser@localhost

In [3]:from sqlalchemy import create_engine

In [4]:# Create an SQL Alchemy engine for using MySQL. Replace dbuser:dbuserdbuser with your user ID and password. sql_engine = create_engine(“mysql+pymysql://dbuser:dbuserdbuser@localhost”)

In [5]:import pandas as pd

In [6]:import pymysql

In [7]:def get_mysql_connection(user_id, password, autocommit=True):

“””

Creates and return a connection to the local MySQL database.

:param user_id: The user ID for the connection.

:param password: Corresponding password.

:param autocommit: Should this connection use autocommit for executed statements. See the following

for explanation:

– https://pymysql.readthedocs.io/en/latest/modules/connections.html

– https://dev.mysql.com/doc/refman/5.6/en/innodb-autocommit-commit-rollback.html

“””

sql_conn = pymysql.connect(

user=user_id,

password=password,

host=“localhost”,

port=3306,

cursorclass=pymysql.cursors.DictCursor,

autocommit=autocommit)

return sql_conn

def close_connection(sql_connection):

“””

Closes a connection.

:param sql_connection: The connection to close.

“””

if sql_connection:

sql_connection.close()

In [8]: You must set these parameters to the values for you final submission and databases.

# Replace dff9 with your UNI

#

mysql_url = “mysql+pymysql://dbuser:dbuserdbuser@localhost”

final_schema = “dff9_s22_final”

course_info_table = “course_info”

course_info_file = “./course_info.json”

In [11]:read_and_save_json_file(file_name, schema_name, table_name):

df = pd.read_json(file_name)

eng = create_engine(mysql_url)

df.to_sql(table_name, schema=schema_name, con=eng, index=False, if_exists=“replace”)

In [10]:%sql create database if not exists dff9_s22_final

read_and_save_json_file(course_info_file, final_schema, course_info_table)

%sql use $final_schema

In [11]:%%sql

select

callnumber, schoolcode, coursetitle, coursesubtitle,

instructor1name, typename,

course, prefixname,

enrollmentstatus,

maxsize, numenrolled,

callnumber,

prefixlongname,

departmentcode, campuscode,

divisionname, departmentname,

typecode, term,

schoolname, meets1

from course_info

where instructor1name like “%Ferguson%Donald%”