I have two dataframes.

One is music.

And another dataframe called singer

Now I would like to create a database called musicten from these two dataframes using sqlite3.

What I done so far:

import sqlite3

conn = sqlite3.connect('musicten.db')  
c = conn.cursor()

c.execute('''
          CREATE TABLE IF NOT EXISTS singer
          ([Singer_ID] INTEGER PRIMARY KEY, [Singer] TEXT, [nationality] TEXT)
          ''')
          
c.execute('''
          CREATE TABLE IF NOT EXISTS music
          ([SONG_ID] INTEGER PRIMARY KEY, [SINGER_ID] INTEGER SECONDARY KEY, [name] TEXT, [Date] DATE, [EDITION] INTEGER)
          ''')
                     
conn.commit()





import sqlite3

conn = sqlite3.connect('musicten.db') 
c = conn.cursor()
                   
c.execute('''
          INSERT INTO singer (Singer_ID, Singer,nationality)

                VALUES
                (1,'JT Watson',' USA'),
                (2,'Rafinha','Brazil'),
                (3,'Juan Casa','Spain'),
                (4,'Kidi','USA'),
                (5,'Dede','USA')
          ''')

c.execute('''
          INSERT INTO music (Song_ID,Singer_ID, name, Date,Edition)

                VALUES
                (1,1,'LA',01/05/2009,1),
                (2,2,'Second',13/07/2009,1),
                (3,1,'Mexico',13/07/2009,1),
                (4,3,'Let's go',13/09/2009,1),
                (5,tuple([4,5]),'Hello',18/09/2009,1)
          ''')

conn.commit()

But this code seems not work to insert values to the dataframe.

SO my goal is to INSERT VALUES to the Table that the database has two tables with values.

CodePudding user response：

First, do not import sqlite3 the second time. Also, you still have an open connection.

Two issues with the SQL:
'Let''s go' (single quote character must be doubled/escaped
tuple([4,5]) => '(4,5)'