If I have more than one item in a column of a text file then how can I import it in SQL using python?

Question

ISBN    BOOK             Author                                Genre
123456 Child dreams     John and Helda                        Educational, emotional
435678  Emotions         Dr. Fedrick Patrins and Veela manok   Educational, emotional, creative, awareness

I am able to load ISBN and Book in one table:(text file is separated by tab)

with open('file path') as xyz:
    read=csv.reader(xyz, delimiter='\t')
    total_books = iter(read)`enter code here`
    next(total_books)
 for row in total_books:
      cursor.execute"INSERT INTO book_titles VALUES (%s, %s)", (row[0], row[1]))
                db.commit()

But, what if column has more than one values like in Author and Genre

Are you saying in the first row that Educational, emotional are separated by a comma and a tab? If so then it's not a tab separated file. — P.Salmon, Feb 21 '19 at 15:11
What I mean is that columns are tab separated i.e ISBN, Book, Author, Genre are tab separated — Tayto, Feb 21 '19 at 15:25

score 0 · Answer 1 · answered Feb 21 '19 at 16:07

An example of a normalized schema:

books
book_id ISBN    TITLE           
      1 123456  Child dreams    
      2 435678  Emotions        

authors                    
author_id name
       11 John 
       12 Helda                     
       13 Dr. Fedrick Patrins 
       14 Veela Manok   

genres
genre_id genre
     101 educational
     102 emotional
     103 creative
     104 awareness

book_author
id book_id author_id
1        1 11
2        1 12
3        2 13
4        2 14

book_genre
book_id genre_id
      1 101
      1 102
      2 101
      2 102
      2 103
      2 104

If I have more than one item in a column of a text file then how can I import it in SQL using python?

1 Answers1