import sys
import csv
import sqlite3
import fileinput

con = sqlite3.connect(':memory:')
cur = con.cursor()

cur.execute('''
CREATE TABLE courses (
     id integer primary key AUTOINCREMENT, 
     title varchar(200), 
     author varchar(200),
     lessons varchar(200),
     ctime varchar(200)
    );
''')

cur.execute('''
CREATE TABLE tags (
     id integer primary key AUTOINCREMENT, 
     tag varchar(200)
);
''')

cur.execute('''
CREATE TABLE courses_tags (
     id integer primary key AUTOINCREMENT, 
     course_id varchar(200), 
     tag_id varchar(200)
);
''')

alltags = {}
with fileinput.input() as csvfile:
    reader = csv.reader(csvfile)
    for row in reader:
        *rec, tags = row
        cur.execute("insert into courses(title, author, lessons, ctime) values (?, ?, ?, ?)", rec)
        course_id = cur.lastrowid
        taglist = tags.split(', ')
        for tag in taglist:
            if tag not in alltags:
                cur.execute("INSERT INTO tags(tag) VALUES (?)", (tag, ))
                alltags[tag] = cur.lastrowid
            cur.execute("INSERT INTO courses_tags(course_id, tag_id) VALUES (?, ?)", (course_id, alltags[tag]))
con.commit()
qr = '''
SELECT courses.id, courses.title , GROUP_CONCAT(tags.tag) as tags FROM courses
JOIN courses_tags ct on courses.id = ct.course_id
JOIN tags on tags.id = ct.tag_id GROUP BY courses.id;
'''
cur.execute(qr)
for row in cur:
    print(row)

con.close()