Answer to Question #288599 in Python for Asrith

Question

Assume that you have to create such an application for maintaining a database of book titles

and their costs.

Part 1: Write the script to create the required database and add data programmatically by

using the Insert query.

Part 2: Write a Python script connecting to the database created that has the following

features:

• A books table having the title, author, and price as fields.

• Accept input from the user for the title and quantity purchased by the customer.

• Fetch the price from the table by executing the Select query.

• Calculate the total amount and display it

Answer 1

db.close()
import sqlite3
>>> db = sqlite3.connect('data/test.db')
>>> cursor = db.cursor()
>>> cursor.execute('''CREATE TABLE books(id INTEGER PRIMARY KEY,
...                    title TEXT, author TEXT, price TEXT, year TEXT)
...                ''')
>>> db.commit()

>>> import sqlite3
>>> db = sqlite3.connect('data/test.db')
>>> cursor = db.cursor()
>>> title1 = 'Learning Python'
>>> author1 = 'Mark Lutz'
>>> price1 = '$36.19'
>>> year1 ='Jul 6, 2013'
>>> 
>>> title2 = 'Two Scoops of Django: Best Practices For Django 1.6'
>>> author2 = 'Daniel Greenfeld'
>>> price2 = '$34.68'
>>> year2 = 'Feb 1, 2014'

>>> cursor.execute('''INSERT INTO books(title, author, price, year)
...                   VALUES(?,?,?,?)''', (title1, author1, price1, year1))

>>> cursor.execute('''INSERT INTO books(title, author, price, year)
...                   VALUES(?,?,?,?)''', (title2, author2, price2, year2))

>>> db.commit()
>>> title3 = 'Python Cookbook'
>>> author3 = 'David Beazley'
>>> price3 = '$30.29'
>>> year3 = 'May 29, 2013'

>>> cursor.execute('''INSERT INTO books(title, author, price, year)
...                   VALUES(:title, :author, :price, :year)''',
...                   {'title':title3, 'author':author3, 'price':price3, 'year':year3})
<sqlite3.Cursor object at 0x7f1d2717d650>
>>> 
>>> db.commit()
>>> title4 = 'The Quick Python Book'
>>> author4 = 'Naomi R. Ceder'
>>> price4 = '$16.39'
>>> year4 = 'Jan 15, 2010'
>>> 
>>> title5 ='Python Testing'
>>> author5 ='David Sale'
>>> price5 = '$38.20'
>>> year5 = 'Sep 2, 2014'

>>> books = [(title4,author4, price4, year4),
...          (title5,author5, price5, year5)]
>>> cursor.executemany('''INSERT INTO books(title, author, price, year) VALUES(?,?,?,?)''', books)
>>> db.commit()
sqlite> .tables
books

sqlite> SELECT * FROM books;
1|Learning Python|Mark Lutz|$36.19|Jul 6, 2013
2|Two Scoops of Django: Best Practices For Django 1.6|Daniel Greenfeld|$34.68|Feb 1, 2014
3|Python Cookbook|David Beazley|$30.29|May 29, 2013
4|The Quick Python Book|Naomi R. Ceder|$16.39|Jan 15, 2010
5|Python Testing|David Sale|$38.20|Sep 2, 2014

sqlite> .mode column
sqlite> .headers on
sqlite> SELECT * FROM books;
id          title            author      price       year       
----------  ---------------  ----------  ----------  -----------
1           Learning Python  Mark Lutz   $36.19      Jul 6, 2013
2           Two Scoops of D  Daniel Gre  $34.68      Feb 1, 2014
3           Python Cookbook  David Beaz  $30.29      May 29, 201
4           The Quick Pytho  Naomi R. C  $16.39      Jan 15, 201
5           Python Testing   David Sale  $38.20      Sep 2, 2014
sqlite>