ntacts Database) Study the books.sql script provided in the ch17 examples folder's sql subfolder. Save the script as addressbook.sql and modify it to create a single table named contacts. The table should contain an auto-incremented id column and text columns for a person's first name, last name and phone number. make a Python script demonstrating: 1. insert contacts into the database, 2. query the database to list all the contacts and contacts with specific last name, 3. update a contact and 4. delete a contact. books.sql code: import panda as pd import sqlite3 connection = sqlite3.connect('books.db') pd.options.display.max_columns = 10 pd.read_sql("SELECT * FROM authors",connection) df = pd.read_sql( "SELECT * FROM authors",connection) DROP TABLE IF EXISTS author_ISBN; DROP TABLE IF EXISTS titles;
SQL
SQL stands for Structured Query Language, is a form of communication that uses queries structured in a specific format to store, manage & retrieve data from a relational database.
Queries
A query is a type of computer programming language that is used to retrieve data from a database. Databases are useful in a variety of ways. They enable the retrieval of records or parts of records, as well as the performance of various calculations prior to displaying the results. A search query is one type of query that many people perform several times per day. A search query is executed every time you use a search engine to find something. When you press the Enter key, the keywords are sent to the search engine, where they are processed by an algorithm that retrieves related results from the search index. Your query's results are displayed on a search engine results page, or SER.
Program should follow in term
(Contacts
books.sql code:
import panda as pd
import sqlite3
connection = sqlite3.connect('books.db')
pd.options.display.max_columns = 10
pd.read_sql("SELECT * FROM authors",connection)
df = pd.read_sql( "SELECT * FROM authors",connection)
DROP TABLE IF EXISTS author_ISBN;
DROP TABLE IF EXISTS titles;
DROP TABLE IF EXISTS authors;
CREATE TABLE authors (
id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
first TEXT NOT NULL,
last TEXT NOT NULL
);
CREATE TABLE titles (
isbn TEXT NOT NULL,
title TEXT NOT NULL,
edition INTEGER NOT NULL,
copyright TEXT NOT NULL,
PRIMARY KEY (isbn)
);
CREATE TABLE author_ISBN (
id INTEGER NOT NULL,
isbn TEXT NOT NULL,
PRIMARY KEY (id, isbn),
FOREIGN KEY (id) REFERENCES authors(id) ON DELETE CASCADE,
FOREIGN KEY (isbn) REFERENCES titles (isbn) ON DELETE CASCADE
);
PRAGMA foreign_keys = ON;
INSERT INTO authors (first, last)
VALUES
('Paul','Deitel'),
('Harvey','Deitel'),
('Abbey','Deitel'),
('Dan','Quirk'),
('Alexander', 'Wald');
INSERT INTO titles (isbn,title,edition,copyright)
VALUES
('0135404673','Intro to Python for CS and DS',1,'2020'),
('0132151006','Internet & WWW How to Program',5,'2012'),
('0134743350','Java How to Program',11,'2018'),
('0133976890','C How to Program',8,'2016'),
('0133406954','Visual Basic 2012 How to Program',6,'2014'),
('0134601548','Visual C# How to Program',6,'2017'),
('0136151574','Visual C++ How to Program',2,'2008'),
('0134448235','C++ How to Program',10,'2017'),
('0134444302','Android How to Program',3,'2017'),
('0134289366','Android 6 for Programmers',3,'2016');
INSERT INTO author_ISBN (id,isbn)
VALUES
(1,'0134289366'),
(2,'0134289366'),
(5,'0134289366'),
(1,'0135404673'),
(2,'0135404673'),
(1,'0132151006'),
(2,'0132151006'),
(3,'0132151006'),
(1,'0134743350'),
(2,'0134743350'),
(1,'0133976890'),
(2,'0133976890'),
(1,'0133406954'),
(2,'0133406954'),
(3,'0133406954'),
(1,'0134601548'),
(2,'0134601548'),
(1,'0136151574'),
(2,'0136151574'),
(4,'0136151574'),
(1,'0134448235'),
(2,'0134448235'),
(1,'0134444302'),
(2,'0134444302'...
Trending now
This is a popular solution!
Step by step
Solved in 5 steps with 4 images
