What is a database?
A database is an organised collection of data. On the server the database resides as a file. Because the database is saved on disk (or other permanent storage), it persists after the program ends, hence information is not lost. Also, because it is on permanent storage, it can store far more data than memory in the computer. With recent development in distributed computing, databases could be organised in multiple computers, which makes possible the storing of massive amount of information, required for big data analytics and machine learnings which work on huge data sets.
In Python, we can use “indexes” or “keys” to find specific data in an array or dictionary. Likewise, databases can be organised to map keys to values, very much like the way dictionaries work in Python programming. Database software vendors compete to make the job of inserting and accessing large amount of data quickly and precisely. The technique of building indexes in database allows computer to go quickly to a particular entry to get information, so that the speed performance of database operations could be boosted.
Database concepts
In this, we will use SQLite3 to understand the various database concepts. SQLite3 also comes built into Python so it will be very convenient for our learning purpose.
When we look at a database, we can imagine it to be like a spreadsheet with multiple sheets. Each of these sheets is analogous to a table. In database terminology, we call the logical structure of a database to be the schema of the database. Hence, a spreadsheet is like a representation of one database schema.
The primary data structures in a database schema are: tables, rows and columns (see Figure 2.1). The formal terms for these structures are relation, tuple and attribute, respectively.
In relational databases like SQLite3, a defined structure upfront is required, unlike say Python lists or dictionaries. To store data in a database table, we need to tell the database in advance the names of each of the columns in the table, and the type of data that will be stored in each column. This step is also known as defining the Database Schema.
Defining the schema/structure in the beginning is necessary so that the database software knows where information is stored and can efficiently access it. This information, together with the data such as the rows and columns of the tables are stored in a *.sqlite file.
The following code creates a database file, music.sqlite, and a table named Tracks with two columns in the database:
import sqlite3
conn = sqlite3.connect('music.sqlite')
cur = conn.cursor()
cur.execute('DROP TABLE IF EXISTS Tracks')
cur.execute('CREATE TABLE Tracks (title TEXT, plays INTEGER)')
conn.close()The connect operation above makes a “connection” to the specified database file in the current directory. If no such file exists, a new one is created. A cursor is a file handle used to perform operations on the data stored in the database file. Calling cursor() is conceptually similar to calling open() when dealing with text files.
Once we have a cursor, we can use the execute() method to execute commands on the contents of the database.
Structured Query Language or SQL
SQLite3 uses the database language known as Structured Query Language (SQL) for its operations. This set of standardised database commands allows us to use it to work on database products offered by many different vendors.
https://en.wikipedia.org/wiki/SQL In the code example above, the SQL command
DROP TABLE IF EXISTS Tracks
removes the Tracks table from the database if it exists. The next command
CREATE TABLE Tracks (title TEXT, plays INTEGER)creates a table named Tracks with a text column named title and an integer column named plays.
In SQL, these database table creation commands are also known as Data Definition Language.
Basic CRUD (Create, Read, Update, Delete) Operations using SQL
The basic functions in any computer data processing operations are to Create, Read, Update and Delete data. The following commands show the relevant syntax in SQL, in the context of manipulating the Tracks table that we created in the preceding example: .
Create - Insert a row of data into the Tracks table which has the columns “title” and “plays”
INSERT INTO Tracks (title, plays) values ('Rainbow connection', 20);Read - Get all rows from Tracks table where column / attribute “plays” = 20
SELECT * FROM Tracks WHERE plays=20;Update - In the Tracks table, in rows where column / attribute “title” = ‘Rainbow connection”, set the attribute “plays” to 5
UPDATE Tracks SET plays=5 WHERE title='Rainbow connection';Update - In the Tracks table, delete all rows where column / attribute “plays” = 5
DELETE FROM Tracks WHERE plays=5;Using these SQL commands, we can write the following program to insert some data into our Track table:
import sqlite3
conn = sqlite3.connect('music.sqlite')
cur = conn.cursor()
cur.execute('INSERT INTO Tracks (title, plays) VALUES (?, ?)',
('Thunderstruck', 20))
cur.execute('INSERT INTO Tracks (title, plays) VALUES (?, ?)',
('My Way', 15))
conn.commit()
print('Tracks:')
cur.execute('SELECT title, plays FROM Tracks')
for row in cur:
print(row)The last SQL command 'SELECT title, plays FROM Tracks' selects all rows from the table and then uses a loop to display the values in the column title and plays.
We can also manipulate the cursor. If we want it to just return one value, we can use the fetchone() method. The following codes will print one row of data, instead of using a loop to print all the rows that match the query:
cur.execute('SELECT title, plays FROM Tracks')
row = cur.fetchone()
print(row)If you need any help with Data Programming Database Assignment Help then you can send your assignment requirement details at:
realcode4you@gmail.com