Pedro Delfino Pedro Delfino - 3 months ago 21
Python Question

What does cur.fetchone()[0] do?

I am using the following code for an educational purpose:

import json
import sqlite3

conn = sqlite3.connect('rosterdb.sqlite')
cur = conn.cursor()


# Do some setup
cur.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 = raw_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, role

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()


What does cur.fetchone()[0] do?
Why the cur.fetchnone()[0] is not necessary when populating the Member table?

You can download the json file which is read by the program at:

https://pr4e.dr-chuck.com/tsugi/mod/sql-intro/roster_data.php?PHPSESSID=7530ba71982b2e10c3f61bd0cb1bfcb5

Answer

cur.fetchone() retrieves one result row for the query that was executed on that cursor. A row is always a sequence of 1 or more columns, and [0] indexes that row to get the first element.

It only makes sense to retrieve results from a SELECT query; in INSERT adds data to the database and there are no results to retrieve for that operation.

The SELECT queries retrieve extra information to insert into the Member table. The id columns in the User and Course tables are autoincrementing, so each time you insert a row a new id is generated for those new rows. The SELECT statements retrieve these id values so they can be used in the Member table.

Comments