1. Using SQL

1.1. Surfer Notes

• The Surf Federation has the following data: surfer’s number, name, country, average grade, style, age.

• How to use this information?

• For example, I want to know all surfers over 25.

• Using a database! In this case, SQL.

• Previously, a database was created with a table called surfers.

• This table has the information of the surf tournament participants organized in the following columns: id, name, country, average, style, age.

• id is an integer that identifies the contestant.

• name is a string with his/her name and nickname (if any).

• country shows the country he/she is representing in the tournament.

• average is the average score obtained in the tournament.

• style is the style of his/her surfboard.

• age is an integer that shows the contestant’s age.

In this exercise we will read the data from the table surfers and display only those over 25 with an SQL statement (known as a query). Currently, the query returns all the data in the surfers table.

RunResetLoad History

xxxxxxxxxx

 

1

# the connection method of sqlite3 has been imported for use

2

# one can do this by the statement

3

from sqlite3 import connect

4

​

5

# initiate the connection and connect to the predefined surfers table

6

database = connect('surfers_en.db')

7

​

8

​

9

# create a cursor and execute the query

10

cursor = database.cursor()

11

query = 'SELECT * FROM surfers'

12

​

13

cursor.execute(query)

14

# fetch the data

15

rows = cursor.fetchall()

16

# print the data

17

if(rows!=None):

18

   for row in rows:

19

      print("ID    :",row['id'])

20

      print("Name :",row['name'])

21

      print("Country  :",row['country'])

22

      print("Average  :",row['media'])

23

      print("Style :",row['style'])

24

      print("Age  :",row['age'])

Activity: 1.1.1 ActiveCode (ac_42_1_en)

You have attempted 1 of 2 activities on this page