import sqlite3

conn = sqlite3.connect('chinook.db')

cur = conn.cursor()

# treat the cursor object cur as an iterator

cur.execute('SELECT * from albums')

# call fetchone() method / or fetchall() method

print(cur.fetchone())

# iterating over each rows

for row in cur.execute('SELECT * from albums'):

    print(row)

cur.execute('SELECT * from customers')

print(cur.fetchone())

# add where clause

ArtistId = ('',)

# using ? is more secure than using %s

cur.execute('SELECT * from albums where ArtistId = ?',ArtistId)

print(cur.fetchall())

# using %s

ArtistId = ('',)

# using ? is more secure than using %s

cur.execute('SELECT * from albums where ArtistId = %s' % ArtistId)

print(cur.fetchall())

cur.execute('SELECT * from artists')

print(cur.fetchall())

# insert value

cur.execute('INSERT OR REPLACE INTO artists values (276, "Jay Zhou")')

cur.execute('SELECT * from artists')

print(cur.fetchall())

# insert a list of records -- here we use executemany to insert another 3 singers

newArtists = [(278, 'Eason Chan'),

              (279, 'Yoga Lin'),

              (280, 'Jane Zhang'),]

# print(type(newArtists))

cur.executemany('INSERT OR IGNORE INTO artists values (? , ?)' , newArtists)

for row in cur.execute('SELECT * from artists'):

    print(row)

# using commit() to save those transactions / commiting those transations

conn.commit()

conn.close()