Transcript Databases and python

Databases and python
Mr S Hall
Credit to Mark Tranter for code+ Dr Sue Sentance for the idea
SQL – the language of databases
SQL is a standard language for communicating with databases.
It is completely independent of programming language or implementation. It is not casesensitive. Understanding SQL can help you with any database work you do, regardless of
whether you are using Python or not.
There are a few key SQL commands that you need to know which are:
• Create Table
• Select … From … Where
• Insert Into
• Delete
• Update
Python and Databases
• Sqlite 3 comes free with Python and enables you to create and work with
databases. An sqlite3 database is stored locally on your machine.
• When working with Python and databases, there are a few key commands that
you will use a lot.
These involve:
• opening the database,
• sending it commands
• saving changes.
Inside these commands we need to put the SQL statements that we will send to
the database.
• We use the with statement when opening the database as this ensures that if
the program crashes, the database is closed and not left in an open state (thus
preventing errors).
Key commands for working with Python and databases are:
sqlite.connect(<database
name>)
Forms the connection with the database
cursor = db.cursor()
cursor.execute(<sql
statements>)
This identifies the cursor for the
database which tells Python where in the
database you are looking at the time
This is used to pass all sql statements to
the database
db.commit()
This saves the changes made
Data types in sqlite3
Each value stored in an SQLite database (or manipulated by the database
engine) has one of the following storage classes:
• NULL. The value is a NULL value.
• INTEGER. The value is a signed integer, stored in 1, 2, 3, 4, 6, or 8 bytes
depending on the magnitude of the value.
• REAL. The value is a floating point value, stored as an 8-byte IEEE floating point
number.
• TEXT. The value is a text string, stored using the database encoding (UTF-8,
UTF-16BE or UTF-16LE).
• BLOB. The value is a blob of data, stored exactly as it was input.
• This is only a subset of the data types used by SQL in general. There are many
more so if you progress to using MySQL (for example, to create a server-side
database) you will need to be familiar with others. If you use other data types
with sqlite they will always map to the five given here.
Part one of code
This code is going to create a menu based system that uses lists .The first thing we are going to do is to import sql lite
The next thing we are going to do is create a function to provide options for choice for end user when they first open the
program.This is in the form of a dictionary.As when you look up a word spelling or meaning a dictionary is there to reference
what a number entered by an end user does
The next line of code creates an empty database
Part two-create opening print statement
Part three-validation with referral to options
Now the variable is to be set - what the user inputs will dictate what option is then referred to using the dictionary style of
design of the code
Try and except is there to make sure that end user cant enter an input that is not equal to a number in the dictionary
[choice]= input : is requesting an input from the end user (which should relate to the list of options)
try:
print(opts[choice]())
except KeyError:
this is the validation to make sure user enters one of the choices in the opt list dictionary –if not it
will show a print statement error
Opts is the dictionary-choice is the user input that should relate to a number in the dictionary
Print(opts[choice]() ) says print the number that relates to the dictionary
Part four: create the functions
What we are now going to do is create the functions.The functions we need to create are :
makeFilmTable
findYear
findGenre
find(search, term)
Pretty- Prepares the result for printing.
delFilm(): Removes a film from the database
addFilm(): adds a record to the database
sortYear(): Returns all the films, sorted by year
showAll(n = '') Prints all the films in the database
What the functions will enable us to do
Please select an option:
1 - Add a new film.
2 - Delete a film.
3 - Show all films.
4 - Sort films by year.
5 - Find films by year.
6 - Find films by genre.
q - Exit.
Your choice:
Part 5 –the first function- def getNum
The first function we are defining validates the integer (or input the user is going to make)
Notice that the end user must type in a whole number as specified
While loop is used so that if the end user enters wrong data the loop will take them back to start
So if a whole number is not entered a ValueError will be generated- it will loop to start and not allow the end user to go
any further
Part 6- the second function- def makeFilmTable
This is the sql element
The function creates a structure of a
database which the information will be
stored in
Db.commit() saves the data to the
database that is entered in our program
by our end user
the cursor is essential to navigate around the database
The sql needs to be put this inside a cursor.execute() statement for it to be carried out by Python
Part 7- define functions to find year to find genre
Notice that the function for Find is created after these two functions that refer to it .This function deals with creating a
capacity for end user to enter a number that relates to a selection in the dictionary previously created .getNum is the
validation while loop function we put in at start
Again refers to Find function we are going to create in a minute , this time it enables an end user to enter a genre for the film
data that they wish to store
Part 8-define the find function
The find function is a helper function for the find functions
Notice that it uses db.commit() to save data to our database we created earlier, and that it has features discussed previously
such as cursor =db.cursor()
Db.commit- saves all changes made
The ? Is used to indicate that this is data we do not know yet
Cursor.execute -This is used to pass all sql statements to the database
with connect("films.db") as db:- forms the connection with the database
Cursor =db.cursor() identifies the cursor for the database ,which tells Python where in the database you are looking at the
time
Part 8-define the formatting of the print
The first line sets up the headers for the table.
The next puts in 45 equals signs to go below the header.
Then we use join() to go through each record in results and format it with the same spacing's we used for the
headers.
This deals with setting the amount of characters that can be entered within each field type of the database
Ie id- would have a max of 3 characters-title –would be less than 15
Part 9-define delete film function
Db.commit- saves all changes made
The ? Is used to indicate that this is data we do not know yet
Cursor.execute -This is used to pass all sql statements to the database
with connect("films.db") as db:- forms the connection with the database
Cursor =db.cursor() identifies the cursor for the database ,which tells Python where in the database you are looking at the time
Part 10-define add film function
Db.commit- saves all changes made
The ? Is used to indicate that this is data we do not know yet
Cursor.execute -This is used to pass all sql statements to the database
with connect("films.db") as db:- forms the connection with the database
Cursor =db.cursor() identifies the cursor for the database ,which tells Python where in the database you are
looking at the time
11-define sortYear function
12-define showAll function
Db.commit- saves all changes made
The ? Is used to indicate that this is data we do not know yet
Cursor.execute -This is used to pass all sql statements to the database
with connect("films.db") as db:- forms the connection with the database
Cursor =db.cursor() identifies the cursor for the database ,which tells Python where in the database you are looking at the
time
13- if_main!