Warning - This site is moving to https://getthecodingbug.anvil.app
Topics covered
​ What is a Database ?
Tables, Rows (records) and Columns (fields)
SQL - Structured Query Language
Using SQL in Python with SQLite
Creating a database and table
Adding records to a table
Getting records from a table
Deleting records from a table
Updating records in a table
What is a Database ?
A database is a computerised system that makes it easy to search, select and store information. Databases are used in many different places.
Your school might use a database to store information about attendance or to store pupils' and teachers' contact information. Your library might also use a database to keep track of which books are available and which are on loan.
Tables, Rows (Records) and Columns (Fields)
A database is usually structured into one or more tables which hold similar types of data, for example, a table named 'customers' might hold information about a company's customers.
A table may contain zero, one or many rows (or records) of data, for example one for each customer.
A row may contain one or more columns (or fields), each of which can contain, for example, a piece of information about a single customer, say name, telephone, email etc.
SQL - Structured Query Language
Historically, many computer companies were competing and developing database systems, each different from one another. Fortunately, they did agree to develop and use a common language to build and interrogate databases, and it became a standard known as SQL - Structured Query Language.
Using SQL in Python with SQLite
There are many database engines, developed by different companies, but most can be driven using SQL.
We are going to use one called SQLite which is one of many which can be called from Python.
It should be possible to use the SQL commands used in this lesson in other languages using other databases on other computer systems.
Creating a database and table
We will use the 'CREATE TABLE' SQL command, which will create the database if it does not already exist.
The table is created with all the fields we want to describe our customer, with an additional one called 'id' which will be discussed later.
Copy' n' paste the Python script below and save it as 'sqlCreateTable.py'.
It has a function called 'createDBtable' for creating a database and a table called 'customers'.
# Using SQL
import sqlite3
def createDBtable():
try:
# Creates or opens a connection to a file called myDatabase.db
conn = sqlite3.connect('myDatabase.db')
# Get a cursor object
cursor = conn.cursor()
# Check if table users does not exist and create it
sql = 'CREATE TABLE IF NOT EXISTS customers ' + \
'(id INTEGER PRIMARY KEY, name TEXT, phone TEXT, email TEXT)'
cursor.execute(sql)
# Commit the change
conn.commit()
# Catch the exception
except Exception as e:
# Roll back any change if something goes wrong
conn.rollback()
print('ERROR: ' + str(e))
finally:
# Close the db connection
conn.close()
createDBtable() # Start Here
print('Finished')
Running the program should create a database with one table named 'customers'. If the program executed correctly, you should just see 'Finished' printed to the screen and if you look in the same folder you should see a file named 'myDatabase.db'. The 'customers' table is empty, by that, I mean it does not yet have any rows.
Adding rows to a table
Now we have a table, we can start adding rows of customer data to the 'customers' table.
To add rows to a table we use the 'INSERT INTO' SQL command.
First, use File / Save As, to save the above program as 'sqlAddRows.py'.
Next Copy the function 'addRowToDBtable' below and Paste it just under the 'import sqlite3' in your program.
def addRowToDBtable(name, phone, email):
try:
# Opens a connection to a file called myDatabase.db
conn = sqlite3.connect('myDatabase.db')
# Get a cursor object
cursor = conn.cursor()
# Insert a row into table
cursor.execute('INSERT INTO customers (name, phone, email) VALUES(?,?,?)',
(name, phone, email))
# Commit the change
conn.commit()
# Catch the exception
except Exception as e:
# Roll back any change if something goes wrong
conn.rollback()
print('ERROR: ' + str(e))
finally:
# Close the db connection
conn.close()
Finally, Copy the 'addRowToDBtable...' lines below, and Paste between the lines:
createDBtable() # Start Here
and
print('Finished')
in your program.
addRowToDBtable('Henry Blofeld', '0776378523', 'blowers88@owtlook.com')
addRowToDBtable('David Frost', '0776654321', 'david.frost@gmial.com')
addRowToDBtable('Susan Bartholemew', '0208395987', 'suebart@eol.com')
addRowToDBtable('Henry Blofeld', '0207669037', 'henryb99@yahou.com')
You could also make up your own names of customers and add them to the list.
Running the program should add the customer data to the 'customers' table, however, we still cannot 'see' any results, apart from the 'Finished' printed to the screen, if the program executed correctly.
Getting rows from a table
To be able to 'see' what data we have in our table, we need to use SQL to 'Query' our data. This is done with the 'SELECT' command.
First, use File / Save As, to save the above program as 'sqlGetRows.py'.
Next, Copy the function 'getRowsFromDBtable' below and Paste it just under the 'import sqlite3' in your program.:
def getRowsFromDBtable():
try:
# Opens a connection to a file called myDatabase.db
conn = sqlite3.connect('myDatabase.db')
# Get a cursor object
cursor = conn.cursor()
# Insert a row into table
sql = 'SELECT id, name, phone, email FROM customers ORDER BY phone'
cursor.execute(sql)
rows = cursor.fetchall()
for row in rows:
print(row)
# Commit the change
#conn.commit()
# Catch the exception
except Exception as e:
print('ERROR: ' + str(e))
finally:
# Close the db connection
conn.close()
Finally, Copy the 'getRowsFromDBtable()' line below, and Paste just before the line: print('Finished')
in your program, and comment-out the 'addRowToDBtable...' lines by inserting '# ' at the beginning of each line.
# addRowToDBtable('Henry Blofeld', '0776378523', 'blowers88@owtlook.com')
# addRowToDBtable('David Frost', '0776654321', 'david.frost@gmial.com')
# addRowToDBtable('Susan Bartholemew', '0208395987', 'suebart@eol.com')
# addRowToDBtable('Henry Blofeld', '0207669037', 'henryb99@yahou.com')
getRowsFromDBtable()
This time, running the program should result in a list of customers, sorted in phone number order.
Note: You could change the sort order by changing 'phone' to one of the other fields after the 'ORDER BY' part of the 'SELECT' command.
Expect something like this:
(4, 'Henry Blofeld', '0207669037', 'henryb99@yahou.com')
(3, 'Susan Bartholemew', '0208395987', 'suebart@eol.com')
(1, 'Henry Blofeld', '0776378523', 'blowers88@owtlook.com')
(2, 'David Frost', '0776654321', 'david.frost@gmial.com')
Finished
Deleting rows from a table
To remove rows from our table, we need to use 'DELETE' SQL command.
First, use File / Save As, to save the above program as 'sqlDelRows.py'.
Next, Copy the function 'delRowsFromDBtable' below and Paste it just under the 'import sqlite3' in your program.:
def delRowsFromDBtable(name):
try:
# Opens a connection to a file called myDatabase.db
conn = sqlite3.connect('myDatabase.db')
# Get a cursor object
cursor = conn.cursor()
# Delete a row from table
sql = 'DELETE FROM customers WHERE name = "' + name + '"'
cursor.execute(sql)
# Commit the change
conn.commit()
# Catch the exception
except Exception as e:
print('ERROR: ' + str(e))
finally:
# Close the db connection
conn.close()
Finally, Copy the 3 lines below, and Paste just before between the line: print('Finished') in your program.
This time, all rows which have a name matching 'Henry Blofeld' will be removed from the table.
This time, running the program should result in a list of customers, twice, once before the deletion and once afterwards. Expect something like this:
delRowsFromDBtable('Henry Blofeld')
print('After deletion')
getRowsFromDBtable()
(4, 'Henry Blofeld', '0207669037', 'henryb99@yahou.com')
(3, 'Susan Bartholemew', '0208395987', 'suebart@eol.com')
(1, 'Henry Blofeld', '0776378523', 'blowers88@owtlook.com')
(2, 'David Frost', '0776654321', 'david.frost@gmial.com')
After deletion
(3, 'Susan Bartholemew', '0208395987', 'suebart@eol.com')
(2, 'David Frost', '0776654321', 'david.frost@gmial.com')
Finished
Note: This has removed both records with the name matching 'Henry Blofeld', but suppose they were two different customers, who actually shared the same name.
A better approach would be to use the 'id' field, which uniquely identifies the customer, for example:
def delRowsFromDBtable(id):
...
sql = 'DELETE FROM customers WHERE id = "' + str(id) + '"'
...
delRowsFromDBtable(4)
Updating rows in a table
To update an existing row in a table we need to use the 'UPDATE' command and 'SET' the fields we want to change.
Suppose we made a typing error when entering a customer's name. This time we will use the 'id' to uniquely identify the record and change the name field on that record.
First, use File / Save As, to save the above program as 'sqlUpdRows.py'.
Next Copy the function 'updRowsFromDBtable' below and Paste it just under the 'import sqlite3' in your program.:
def updRowsFromDBtable(id, newName):
try:
# Opens a connection to a file called myDatabase.db
conn = sqlite3.connect('myDatabase.db')
# Get a cursor object
cursor = conn.cursor()
# Update a row in table
cursor.execute('UPDATE customers SET name = ? WHERE id = ? ''', (newName, id))
# Commit the change
conn.commit()
# Catch the exception
except Exception as e:
print('ERROR: ' + str(e))
finally:
# Close the db connection
conn.close()
Finally, Copy the 2 lines below, and Paste just before the last occurrence of the line: getRowsFromDBtable()
in your program
updRowsFromDBtable('2', 'David Frosty')
print('After update')
and comment-out the 2 lines:
delRowsFromDBtable('Henry Blofeld')
and
print('After deletion')
by inserting '# ' at the beginning of each line.
This time, running the program should result in 2 lists of customers, one before and one after the update.
Expect something like this, showing that we changed the name of the customer with the id of '2':
(3, 'Susan Bartholemew', '0208395987', 'suebart@eol.com')
(2, 'David Frost', '0776654321', 'david.frost@gmial.com')
After update
(3, 'Susan Bartholemew', '0208395987', 'suebart@eol.com')
(2, 'David Frosty', '0776654321', 'david.frost@gmial.com')
Finished