Python学习-PY4E作业

以下是在参加Coursea中的Python for EveryBody的专项课程时，完成系列作业时的代码笔记，整理如下。

1.Why Program?

ProblemWrite a program that uses a print statement to say ‘hello world’ as shown in ‘Desired Output’.
写一个Python脚本，输出hello world。Desired Outputhello worldCode

# the code below almost worksprint("hello world")

**2.3 Variables, expressions, and statements**ProblemWrite a program to prompt the user for hours and rate per hour using input to compute gross pay. Use 35 hours and a rate of 2.75 per hour to test the program (the pay should be 96.25). You should use input to read a string and float() to convert the string to a number. Do not worry about error checking or bad user data.
根据工作时间和每小时工资计算总工资。此处假定35小时，每小时2.75工资。必须使用input()和float()函数。Desired OutputPay: 96.25Code

# This first line is provided for youhrs_str = input("Enter Hours:")rat_str = input("Enter Ratio:")hrs_num = float(hrs_str)rat_str = float(rat_str)print("Pay:",hrs_num*rat_str)

**3.1 Conditional Execution**Problem Write a program to prompt the user for hours and rate per hour using input to compute gross pay. Pay the hourly rate for the hours up to 40 and 1.5 times the hourly rate for all hours worked above 40 hours. Use 45 hours and a rate of 10.50 per hour to test the program (the pay should be 498.75). You should use input to read a string and float() to convert the string to a number. Do not worry about error checking the user input - assume the user types numbers properly.
根据工作时间和每小时工资计算总工资。当工作时长小于40小时，每小时工资为10.50；对于超过部分给予正常工作的1.5倍工资。此处假定45小时。必须使用input()和float()函数。Desired Output498.75Code

# This first line is provided for youhrs_str = input("Enter Hours:")rat_str = input("Enter Ratio:")hrs_num = float(hrs_str)rat_num = float(rat_str)if hrs_num > 40 :    print(40*rat_num +(hrs_num-40)*rat_num *1.5)else:    print(hrs_num*rat_num )

**3.3 Conditional Execution**ProblemWrite a program to prompt for a score between 0.0 and 1.0. If the score is out of range, print an error. If the score is between 0.0 and 1.0, print a grade using the following table:If the user enters a value out of range, print a suitable error message and exit. For the test, enter a score of 0.85。
输入一个0到1的数字，然后根据上述等级表评定，需要处理范围异常。假设数字0.85.ScoreGrade>= 0.9 A>= 0.8B>= 0.7 C>= 0.6 D< FDesired OutputBCode

# This first line is provided for youscore_str = input("Enter Score: ")score_num = float(score_str)if score_num<0.0 or score_num>1:    print('Wrong Number')    exit()if score_num<0.6:    print('F')elif score_num<0.8:    print('C')elif score_num<0.9:    print('B')else:    print('A')

**4.6 Functions**ProblemWrite a program to prompt the user for hours and rate per hour using input to compute gross pay. Award time-and-a-half for the hourly rate for all hours worked above 40 hours. Put the logic to do the computation of time-and-a-half in a function called computepay() and use the function to do the computation. The function should return a value. Use 45 hours and a rate of 10.50 per hour to test the program (the pay should be 498.75). You should use input to read a string and float() to convert the string to a number. Do not worry about error checking the user input unless you want to - you can assume the user types numbers properly. Do not name your variable sum or use the sum() function.
根据工作时间和每小时工资计算总工资。当工作时长小于40小时，每小时工资为10.50；对于超过部分给予正常工作的1.5倍工资。此处假定45小时。必须将工资计算部分封装成名为computepay()的函数。必须使用input()和float()函数。Desired Output498.75Code

# This first line is provided for youdef computepay(hrs_num,rat_str):    if hrs_num > 40 :        return 40*rat_str+(hrs_num-40)*rat_str*1.5    else:        return hrs_num*rat_strhrs_str = input("Enter Hours:")rat_str = input("Enter Ratio:")hrs_num = float(hrs_str)rat_num = float(rat_str)print(computepay(hrs_num,rat_num))

**5.2 Loops and Iterations**ProblemWrite a program that repeatedly prompts a user for integer numbers until the user enters ‘done’. Once ‘done’ is entered, print out the largest and smallest of the numbers. If the user enters anything other than a valid number catch it with a try/except and put out an appropriate message and ignore the number. Enter 7, 2, bob, 10, and 4 and match the output below.
编写一个脚本程序等待用户输入数字。当输入为‘done’时，结束程序；并求解已输入的数字中的最大值和最小值。需要进行异常处理。假设依次输入7，2，bob，10，4。Desired OutputInvalid input
Maximum is 10
Minimum is 2

Code

# This first line is provided for youlargest = Nonesmallest = Nonewhile True:    num_str = input("Enter a number: ")    num_num = 0      if num_str == "done" :         break    try:        num_num = int(num_str)    except:        print('Invalid input')        continue    if largest is None or num_num>largest:        largest = num_num    if smallest is None or num_num<smallest:        smallest = num_numprint("Maximum is", largest)print("Minimum is", smallest)

6.5 Strings

ProblemWrite code using find() and string slicing (see section 6.10) to extract the number at the end of the line below. Convert the extracted value to a floating point number and print it out.
使用find()函数找出字符串末尾的数字。Desired Output0.8475

Code

# This first line is provided for youtext = "X-DSPAM-Confidence:    0.8475";index_before_num = text.find(':')number = float(text[index_before_num+1:])print(number)

6.5 Files

ProblemWrite a program that prompts for a file name, then opens that file and reads through the file, looking for lines of the form:X-DSPAM-Confidence: 0.8475Count these lines and extract the floating point values from each of the lines and compute the average of those values and produce an output as shown below. Do not use the sum() function or a variable named sum in your solution.You can download the sample data at http://www.py4e.com/code3/mbox-short.txt when you are testing below enter mbox-short.txt as the file name.
读取文件，找出以‘X-DSPAM-Confidence: ’开头的行，获取其中的数字，并求解平均值。输入文件名mbox-short.txt。Desired OutputAverage spam confidence: 0.750718518519

Code

# This first line is provided for you# Use the file name mbox-short.txt as the file namefname = input("Enter file name: ")fh = open(fname)total_value = 0total_count = 0for line in fh:    if not line.startswith("X-DSPAM-Confidence:") :         continue    index_before_number = line.find(':')+1    total_value = float(line[index_before_number:])+total_value    total_count = total_count+1print("Average spam confidence:",total_value/total_count)

8.4 Lists

ProblemOpen the file romeo.txt and read it line by line. For each line, split the line into a list of words using the split() method. The program should build a list of words. For each word on each line check to see if the word is already in the list and if not append it to the list. When the program completes, sort and print the resulting words in alphabetical order.You can download the sample data at http://www.py4e.com/code3/romeo.txt
读取romeo文件，统计字符。Desired Output[‘Arise’, ‘But’, ‘It’, ‘Juliet’, ‘Who’, ‘already’, ‘and’, ‘breaks’, ‘east’, ‘envious’, ‘fair’, ‘grief’, ‘is’, ‘kill’, ‘light’, ‘moon’, ‘pale’, ‘sick’, ‘soft’, ‘sun’, ‘the’, ‘through’, ‘what’, ‘window’, ‘with’, ‘yonder’]

Code

# This first line is provided for youfname = input("Enter file name: ")if len(fname) < 1 : fname = "romeo.txt"fh = open(fname)count = list()for line in fh:    line = line.strip()    words = line.split()    for word in words:        if word not in count:            count.append(word)print(sorted(count))

8.5 Lists

ProblemOpen the file mbox-short.txt and read it line by line. When you find a line that starts with ‘From ’ like the following line:From [e-mail] Sat Jan 5 09:14:16 2008You will parse the From line using split() and print out the second word in the line (i.e. the entire address of the person who sent the message). Then print out a count at the end.Hint: make sure not to include the lines that start with ‘From:’.You can download the sample data at http://www.py4e.com/code3/mbox-short.txt
逐行读取mbox-short.txt文件，寻找以‘From’开头的行，然后得到邮箱名称。Desired [e-mail]
[e-mail]
[e-mail]
[e-mail]
[e-mail]
[e-mail]
[e-mail]
[e-mail]
[e-mail]
[e-mail]
[e-mail]
[e-mail]
[e-mail]
[e-mail]
[e-mail]
[e-mail]
[e-mail]
[e-mail]
[e-mail]
[e-mail]
[e-mail]
[e-mail]
[e-mail]
[e-mail]
[e-mail]
[e-mail]
[e-mail]
There were 27 lines in the file with From as the first word

Code

# This first line is provided for youfname = input("Enter file name: ")if len(fname) < 1 : fname = "mbox-short.txt"fh = open(fname)count = 0for line in fh:    line = line.strip()    if not line.startswith('From '):        continue    words = line.split()    print(words[1])    count = count +1print("There were", count, "lines in the file with From as the first word")

9.4 Dictionaries

ProblemWrite a program to read through the mbox-short.txt and figure out who has the sent the greatest number of mail messages. The program looks for ‘From ’ lines and takes the second word of those lines as the person who sent the mail. The program creates a Python dictionary that maps the sender’s mail address to a count of the number of times they appear in the file. After the dictionary is produced, the program reads through the dictionary using a maximum loop to find the most prolific committer.
读取文件找到谁发送的邮件最多！Desired [e-mail] 5

Code

# This first line is provided for you# This first line is provided for youfname = input("Enter file name: ")if len(fname) < 1 : fname = "mbox-short.txt"fh = open(fname)count_dict = dict()for line in fh:    line = line.strip()    if not line.startswith('From '):        continue    words = line.split()    count_dict[words[1]] = 1 + count_dict.get(words[1],0)//提前用到了tuplesort_list = sorted([(v,k) for k,v in count_dict.items()],reverse=True)print(sort_list[0][1],sort_list[0][0])

9.4 Tuples

ProblemWrite a program to read through the mbox-short.txt and figure out the distribution by hour of the day for each of the messages. You can pull the hour out from the ‘From ’ line by finding the time and then splitting the string a second time using a colon.From [e-mail] Sat Jan 5 09:14:16 2008Once you have accumulated the counts for each hour, print out the counts, sorted by hour as shown below.
读取文件分析每天那些时刻有邮件！Desired [e-mail] 5

Code

# This first line is provided for you# This first line is provided for youfname = input("Enter file name: ")if len(fname) < 1 : fname = "mbox-short.txt"fh = open(fname)count_dict = dict()for line in fh:    line = line.strip()    if not line.startswith('From '):        continue    words = line.split()    times = words[5].split(':')    hours = times[0]    count_dict[hours] = 1 + count_dict.get(hours,0)count_list = sorted([(k,v) for k,v in count_dict.items()])for k,v in count_list:    print(k,v)

Regular Expressions

ProblemIn this assignment you will read through and parse a file with text and numbers. You will extract all the numbers in the file and compute the sum of the numbers.
读文件然后用正则表达式读出所有数字并相加。Desired Output无标准答案，每个人的文件都一样。

Code

import refile = open('regex_sum_35430.txt')file_data = file.read()numbers_str = re.findall('[0-9]+',file_data)total = 0for number_str in numbers_str:    total = total + int(number_str)print(total)

Network Programming：Request-Response Cycle

ProblemYou are to retrieve the following document using the HTTP protocol in a way that you can examine the HTTP Response headers.
http://data.pr4e.org/intro-short.txt
获取上述网址的元数据。Desired OutputHTTP/1.1 200 OK
Date: Sun, 01 Oct 2017 05:25:59 GMT
Server: Apache/2.4.7 (Ubuntu)
Last-Modified: Sat, 13 May 2017 11:22:22 GMT
ETag: “1d3-54f6609240717”
Accept-Ranges: bytes
Content-Length: 467
Cache-Control: max-age=0, no-cache, no-store, must-revalidate
Pragma: no-cache
Expires: Wed, 11 Jan 1984 05:00:00 GMT
Connection: close
Content-Type: text/plain

Code

import socketmysock = socket.socket(socket.AF_INET, socket.SOCK_STREAM)mysock.connect(('data.pr4e.org', 80))cmd = 'GET http://data.pr4e.org/intro-short.txt HTTP/1.0\r\n\r\n'.encode()mysock.send(cmd)while True:    data = mysock.recv(512)    if (len(data) < 1):        break    print(data.decode())mysock.close()

Network Programming：Scraping HTML Data with BeautifulSoup

ProblemScraping Numbers from HTML using BeautifulSoup In this assignment you will write a Python program similar to http://www.py4e.com/code3/urllink2.py. The program will use urllib to read the HTML from the data files below, and parse the data, extracting numbers and compute the sum of the numbers in the file.Actual data:
http://py4e-data.dr-chuck.net/comments_35432.html。 (Sum ends with 19)
用py脚本解析网页，获取其中的数字并相加。Desired Output2519

Code

from urllib.request import urlopenfrom bs4 import BeautifulSoupimport ssl# Ignore SSL certificate errorsctx = ssl.create_default_context()ctx.check_hostname = Falsectx.verify_mode = ssl.CERT_NONEurl = input('Enter - ')html = urlopen(url, context=ctx).read()# html.parser is the HTML parser included in the standard Python 3 library.# information on other HTML parsers is here:# http://www.crummy.com/software/BeautifulSoup/bs4/doc/#installing-a-parsersoup = BeautifulSoup(html, "html.parser")# Retrieve all of the anchor tagstags = soup('span')total = 0for tag in tags:    # Look at the parts of a tag    total = total + int(tag.contents[0])print(total)

Network Programming：Following Links in Python

ProblemIn this assignment you will write a Python program that expands on http://www.py4e.com/code3/urllinks.py. The program will use urllib to read the HTML from the data files below, extract the href= vaues from the anchor tags, scan for a tag that is in a particular position relative to the first name in the list, follow that link and repeat the process a number of times and report the last name you find.We provide two files for this assignment. One is a sample file where we give you the name for your testing and the other is the actual data you need to process for the assignmentSample problem: Start at http://py4e-data.dr-chuck.net/known_by_Fikret.html Find the link at position 3 (the first name is 1). Follow that link. Repeat this process 4 times. The answer is the last name that you retrieve.Sequence of names: Fikret Montgomery Mhairade Butchi Anayah Last name in sequence: AnayahActual problem: Start at: http://py4e-data.dr-chuck.net/known_by_Romilly.html Find the link at position 18 (the first name is 1). Follow that link. Repeat this process 7 times. The answer is the last name that you retrieve.Hint: The first character of the name of the last page that you will load is:
用py脚本解析网页，并获取所有的外链。Desired Output
http://py4e-data.dr-chuck.net/known_by_Romilly.html
http://py4e-data.dr-chuck.net/known_by_Halina.html
http://py4e-data.dr-chuck.net/known_by_Lorcan.html
http://py4e-data.dr-chuck.net/known_by_Prinay.html
http://py4e-data.dr-chuck.net/known_by_Fikret.html
http://py4e-data.dr-chuck.net/known_by_Margaret.html
http://py4e-data.dr-chuck.net/known_by_Ailee.html
http://py4e-data.dr-chuck.net/known_by_Banan.html

Code

import urllib.request, urllib.parse, urllib.errorfrom bs4 import BeautifulSoupimport ssldef findUrl(url,position):    html = urllib.request.urlopen(url, context=ctx).read()    soup = BeautifulSoup(html, 'html.parser')    # Retrieve all of the anchor tags    tags = soup('a')    return tags[position].get('href',None)# Ignore SSL certificate errorsctx = ssl.create_default_context()ctx.check_hostname = Falsectx.verify_mode = ssl.CERT_NONEcount_str = input('Enter count - ')count = int(count_str)position_str = input('Enter position - ')position = int(position_str)-1for i in range(count):    if i==0:        url_now = input('Enter - ')        print(url_now)        url_now = findUrl(url_now,position)        print(url_now)    else:        url_now = findUrl(url_now,position)         print(url_now)

Using Web Services：Extracting Data from XML

ProblemIn this assignment you will write a Python program somewhat similar to http://www.py4e.com/code3/geoxml.py. The program will prompt for a URL, read the XML data from that URL using urllib and then parse and extract the comment counts from the XML data, compute the sum of the numbers in the file.We provide two files for this assignment. One is a sample file where we give you the sum for your testing and the other is the actual data you need to process for the assignment.Sample data: http://py4e-data.dr-chuck.net/comments_42.xml (Sum=2553)Actual data: http://py4e-data.dr-chuck.net/comments_35434.xml (Sum ends with 30)You do not need to save these files to your folder since your program will read the data directly from the URL. Note: Each student will have a distinct data url for the assignment - so only use your own data url for analysis.
用py脚本xml文件。Desired Output2530

Code

import urllib.request, urllib.parse, urllib.errorimport xml.etree.ElementTree as ETurl = 'http://py4e-data.dr-chuck.net/comments_35434.xml'print('Retrieving', url)uh = urllib.request.urlopen(url)data = uh.read()print('Retrieved', len(data), 'characters')tree = ET.fromstring(data)comments_node = tree.findall('comments')comment_node = comments_node[0].findall('comment')total = 0for node in comment_node:    total = total + int(node.find('count').text)print(total)

Using Web Services：Extract Data from JSON

ProblemIn this assignment you will write a Python program somewhat similar to http://www.py4e.com/code3/json2.py. The program will prompt for a URL, read the JSON data from that URL using urllib and then parse and extract the comment counts from the JSON data, compute the sum of the numbers in the file and enter the sum below:We provide two files for this assignment. One is a sample file where we give you the sum for your testing and the other is the actual data you need to process for the assignment.Sample data: http://py4e-data.dr-chuck.net/comments_42.json (Sum=2553)Actual data: http://py4e-data.dr-chuck.net/comments_35435.json (Sum ends with 72)You do not need to save these files to your folder since your program will read the data directly from the URL. Note: Each student will have a distinct data url for the assignment - so only use your own data url for analysis.Data Format
用py脚本JSON文件。Desired Output2272

Code

import urllib.request, urllib.parse, urllib.errorimport jsonurl = 'http://py4e-data.dr-chuck.net/comments_35435.json'print('Retrieving', url)uh = urllib.request.urlopen(url)data = uh.read()info = json.loads(data.decode())print('User count:', len(info))comments_dic = info['comments']total = 0for item in comments_dic:    total = total + int(item['count'])print(total)

Using Web Services：Using the GeoJSON API

ProblemCalling a JSON APIIn this assignment you will write a Python program somewhat similar to http://www.py4e.com/code3/geojson.py. The program will prompt for a location, contact a web service and retrieve JSON for the web service and parse that data, and retrieve the first place_id from the JSON. A place ID is a textual identifier that uniquely identifies a place as within Google Maps.API End PointsTo complete this assignment, you should use this API endpoint that has a static subset of the Google Data:http://py4e-data.dr-chuck.net/geojson?This API uses the same parameters (sensor and address) as the Google API. This API also has no rate limit so you can test as often as you like. If you visit the URL with no parameters, you get a list of all of the address values which can be used with this API.To call the API, you need to provide address that you are requesting as the address= parameter that is properly URL encoded using the urllib.urlencode() fuction as shown in http://www.py4e.com/code3/geojson.pyTest Data / Sample Execution
用py脚本解析JSON文件。Desired OutputChIJt-dLJLqdGZURI-3_FboXV0c

Code

import urllib.request, urllib.parse, urllib.errorimport json# Note that Google is increasingly requiring keys# for this APIserviceurl = 'http://py4e-data.dr-chuck.net/geojson?'address = input('Enter location: ')url = serviceurl + urllib.parse.urlencode({'address': address})print('Retrieving', url)uh = urllib.request.urlopen(url)data = uh.read().decode()print('Retrieved', len(data), 'characters')try:    js = json.loads(data)except:    js = Noneif not js or 'status' not in js or js['status'] != 'OK':    print('==== Failure To Retrieve ====')    print(data)    exit()place_id = js["results"][0]['place_id']print(place_id)

Databases: Multi-Table Database

ProblemThis application will read the mailbox data (mbox.txt) and count the number of email messages per organization (i.e. domain name of the email address) using a database with the following schema to maintain the counts.
将文件中的数据写到数据库，并统计出现次数最多的组织。Desired Outputiupui.edu 536Code

import sqlite3import reconn = sqlite3.connect('orgdb.sqlite')cur = conn.cursor()cur.execute('''DROP TABLE IF EXISTS Counts''')cur.execute('''CREATE TABLE Counts (org TEXT, count INTEGER)''')fname = input('Enter file name: ')if (len(fname) < 1): fname = 'mbox-short.txt'fh = open(fname)for line in fh:    if not line.startswith('From: '):         continue    pieces = line.split()    email = pieces[1]    org = re.findall('@(.+)',email)[0]    cur.execute('SELECT count FROM Counts WHERE org = ? ', (org,))    row = cur.fetchone()    if row is None:        cur.execute('''INSERT INTO Counts (org, count)                VALUES (?, 1)''', (org,))    else:        cur.execute('UPDATE Counts SET count = count + 1 WHERE org = ?',                    (org,))    conn.commit()# https://www.sqlite.org/lang_select.htmlsqlstr = 'SELECT org, count FROM Counts ORDER BY count DESC LIMIT 10'for row in cur.execute(sqlstr):    print(str(row[0]), row[1])cur.close()

**1.Why Program?**Problem下载[tracks.zip](http://www.py4e.com/code3/tracks.zip)文件，然后构建四个表，如下：

CREATE TABLE Artist (    id  INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,    name    TEXT UNIQUE);CREATE TABLE Genre (    id  INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,    name    TEXT UNIQUE);CREATE TABLE Album (    id  INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,    artist_id  INTEGER,    title   TEXT UNIQUE);CREATE TABLE Track (    id  INTEGER NOT NULL PRIMARY KEY         AUTOINCREMENT UNIQUE,    title TEXT  UNIQUE,    album_id  INTEGER,    genre_id  INTEGER,    len INTEGER, rating INTEGER, count INTEGER);

Desired Output使用
SELECT Track.title, Artist.name, Album.title, Genre.name
FROM Track JOIN Genre JOIN Album JOIN Artist
ON Track.genre_id = Genre.ID and Track.album_id = Album.id
AND Album.artist_id = Artist.id ORDER BY Artist.name LIMIT 3
得到的结果如下：
Track Artist Album Genre
Chase the Ace AC/DC Who Made Who Rock
D.T. AC/DC Who Made Who Rock
For Those About To Rock (We
Salute You) AC/DC Who Made Who Rock Code

import xml.etree.ElementTree as ETimport sqlite3conn = sqlite3.connect('trackdb.sqlite')cur = conn.cursor()# Make some fresh tables using executescript()cur.executescript('''DROP TABLE IF EXISTS Artist;DROP TABLE IF EXISTS Genre;DROP TABLE IF EXISTS Album;DROP TABLE IF EXISTS Track;CREATE TABLE Artist (    id  INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,    name    TEXT UNIQUE);CREATE TABLE Album (    id  INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,    artist_id  INTEGER,    title   TEXT UNIQUE);CREATE TABLE Genre (    id  INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,    name    TEXT UNIQUE);                    CREATE TABLE Track (    id  INTEGER NOT NULL PRIMARY KEY         AUTOINCREMENT UNIQUE,    title TEXT  UNIQUE,    album_id  INTEGER,    genre_id  INTEGER,    len INTEGER, rating INTEGER, count INTEGER);''')fname = input('Enter file name: ')if ( len(fname) < 1 ) : fname = 'Library.xml'# <key>Track ID</key><integer>369</integer># <key>Name</key><string>Another One Bites The Dust</string># <key>Artist</key><string>Queen</string>def lookup(d, key):    found = False    for child in d:        if found : return child.text        if child.tag == 'key' and child.text == key :            found = True    return Nonestuff = ET.parse(fname)all = stuff.findall('dict/dict/dict')print('Dict count:', len(all))for entry in all:    if ( lookup(entry, 'Track ID') is None ) :         continue    name = lookup(entry, 'Name')    artist = lookup(entry, 'Artist')    album = lookup(entry, 'Album')    count = lookup(entry, 'Play Count')    rating = lookup(entry, 'Rating')    length = lookup(entry, 'Total Time')    genre = lookup(entry,'Genre')    if name is None or artist is None or album is None or genre is None :         continue    print(name, artist, album, count, rating, length, genre)    cur.execute('''INSERT OR IGNORE INTO Artist (name)         VALUES ( ? )''', ( artist, ) )    cur.execute('SELECT id FROM Artist WHERE name = ? ', (artist, ))    artist_id = cur.fetchone()[0]    cur.execute('''INSERT OR IGNORE INTO Album (title, artist_id)         VALUES ( ?, ? )''', ( album, artist_id ) )    cur.execute('SELECT id FROM Album WHERE title = ? ', (album, ))    album_id = cur.fetchone()[0]    cur.execute('''INSERT OR IGNORE INTO Genre (name)         VALUES ( ? )''', ( genre, ) )    cur.execute('SELECT id FROM Genre WHERE name = ? ', (genre, ))    genre_id = cur.fetchone()[0]    cur.execute('''INSERT OR REPLACE INTO Track        (title, album_id, genre_id, len, rating, count)         VALUES ( ?, ?, ?, ?, ? ,?)''',         ( name, album_id, genre_id,length, rating, count ) )    conn.commit()

**Databases: Many Students in Many Courses**Problem读取JSON数据，并写到数据库。Desired Output使用命令查询结果为：4161696D61736934323230代码如下：
SELECT hex(User.name || Course.title || Member.role ) AS X FROM
User JOIN Member JOIN Course ON User.id = Member.user_id AND
Member.course_id = Course.id ORDER BY X

Code

import jsonimport sqlite3conn = sqlite3.connect('rosterdb.sqlite')cur = conn.cursor()# Do some setupcur.executescript('''DROP TABLE IF EXISTS User;DROP TABLE IF EXISTS Member;DROP TABLE IF EXISTS Course;CREATE TABLE User (    id     INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,    name   TEXT UNIQUE);CREATE TABLE Course (    id     INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,    title  TEXT UNIQUE);CREATE TABLE Member (    user_id     INTEGER,    course_id   INTEGER,    role        INTEGER,    PRIMARY KEY (user_id, course_id))''')fname = input('Enter file name: ')if len(fname) < 1:    fname = 'roster_data.json'# [#   [ "Charley", "si110", 1 ],#   [ "Mea", "si110", 0 ],str_data = open(fname).read()json_data = json.loads(str_data)for entry in json_data:    name = entry[0];    title = entry[1];    role = entry[2];    print((name, title))    cur.execute('''INSERT OR IGNORE INTO User (name)         VALUES ( ? )''', ( name, ) )    cur.execute('SELECT id FROM User WHERE name = ? ', (name, ))    user_id = cur.fetchone()[0]    cur.execute('''INSERT OR IGNORE INTO Course (title)         VALUES ( ? )''', ( title, ) )    cur.execute('SELECT id FROM Course WHERE title = ? ', (title, ))    course_id = cur.fetchone()[0]    cur.execute('''INSERT OR REPLACE INTO Member        (user_id, course_id, role) VALUES ( ?, ?, ? )''',         ( user_id, course_id, role) )    conn.commit()