mysql - 如果文本文件的一列中有多个项目，那么如何使用 python 将其导入到 SQL 中？

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

我能够在一个表中加载 ISBN 和书籍:(文本文件由制表符分隔)

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

但是，如果列有多个值(如“作者”和“流派”中的值)该怎么办

Answer 1

规范化模式的示例:

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