how to create the table with data from the open file in python?

Question

import sqlite3


def setup_tracks(db, data_file):
    '''(str, file) -> NoneType
    Create and populate the Tracks table with
    the data from the open file data_file.'''
    data_file.readlines()
    # connect to the database
    con = sqlite3.connect(db)
    # create a cursor
    cur = con.cursor()
    # Create the Tracks table
    cur.execute('CREATE TABLE tracks'+'(Title TEXT, id INTEGER, Time INTEGER)')
    for line in data_file:
        data = line.strip().split(',')
        Title = data[0].strip()
        id = int(data[1].strip())
        s = int(data[2].strip().split(':'))
        Time = int(s[0]*60 + int(s[1]))

        cur.execute('INSERT INTO tracks VALUES(?, ?, ?)'+(Title, id, Time))

        cur.close()
        con.commit()
        con.close()

There is file names tracks.csv. There are three columns which areenter link description here title, id, time. If I run this function, I get some errors say that 'db can not be defined'. I hope anybody can help me out with this.

thank you!

@Jasin try with my solution – eyllanesc Dec 02 '16 at 01:18 — eyllanesc, Dec 02 '16 at 01:18

mcmxl · Answer 1 · 2016-12-02T03:53:39.947

Here is my edit of the program along with some observations.

import sqlite3

def setup_tracks(db, data_file):
    lines = data_file.readlines()
    # connect to the database
    con = sqlite3.connect(db)
    # create a cursor
    cur = con.cursor()
    # Create the Tracks table
    sql = '''create table tracks (Title text, id int, Time int)'''
    cur.execute(sql)

    for line in lines[1:]:
        data = line.strip().split(',')
        Title = data[0].strip()
        id = int(data[1].strip())
        s = data[2].strip().split(':')
        Time = int(s[0]) * 60 + int(s[1])
        Title = Title.replace("'", "''")

        #This line is not working right.
        cur.execute('INSERT INTO tracks VALUES(?, ?, ?)'+(Title, id, Time))

    con.commit()
    con.close()

filename = 'tracks.csv'
f = open(filename, 'r')
setup_tracks('data.db', f)

how to create the table with data from the open file in python?

1 Answers1