Challenge code: AS47 (50 points)
The Library
Python has SQL handling available in the standard libraries, so all we have to do to get access is: import sqlite3
Connecting to a database
The next command is to connect to a Database:
conn = sqlite3.connect('Fastcars.sqlite')
From here on in, we can just use conn to call this functionality.
Hint: This will either create a new database or load an existing DB.
Hint 2: You can choose any file extension you like. On Windows SQL might be better.
conn = sqlite3.connect('Fastcars.sqlite')
From here on in, we can just use conn to call this functionality.
Hint: This will either create a new database or load an existing DB.
Hint 2: You can choose any file extension you like. On Windows SQL might be better.
Using Functions
Functions enable us to break up code in a program. This is very useful for our Database. Please be aware that if you want variables to pass between functions then this needs some extra code.
The command to Define a function is: def create_top50_db():
We do not need to pass any variables, so this is all we need. Remember all the data is going to be in our database.
To call a function simply: create_top50_db()
The command to Define a function is: def create_top50_db():
We do not need to pass any variables, so this is all we need. Remember all the data is going to be in our database.
To call a function simply: create_top50_db()
Creating a Table
with conn:
This command is important, because even if Python crashes it ensures that the file gets closed properly.
conn.execute("CREATE TABLE Cars(id INTEGER PRIMARY KEY, Name TEXT, TopSpeed INT)")
This command includes both Python and SQL. The part inside the speech marks is SQL.
CREATE TABLE Cars - This tells SQLite to create the table
id INTEGER PRIMARY KEY - This creates an automatic primary key to ensure every record is unique.
Name TEXT - This creates the field Name of data type text
TopSpeed INT - This creates the field TopSpeed with field type Integer
This command is important, because even if Python crashes it ensures that the file gets closed properly.
conn.execute("CREATE TABLE Cars(id INTEGER PRIMARY KEY, Name TEXT, TopSpeed INT)")
This command includes both Python and SQL. The part inside the speech marks is SQL.
CREATE TABLE Cars - This tells SQLite to create the table
id INTEGER PRIMARY KEY - This creates an automatic primary key to ensure every record is unique.
Name TEXT - This creates the field Name of data type text
TopSpeed INT - This creates the field TopSpeed with field type Integer
Adding Data
conn.execute("INSERT INTO Cars(Name,TopSpeed) VALUES('Porsche 911 Carrera 4s',185)")
This is the simplest way to add data, just type it directly into your Python program.
conn.execute - Calls sqlite
INSERT INTO Cars - Tells sqlite to insert a new row into the table Cars
(Name,TopSpeed) - These are the fields we want to add. Remember the primary key will take care of itself.
VALUES('Porsche 911 Carrera 4s',185) - These are the values we want to put into the row.
This is the simplest way to add data, just type it directly into your Python program.
conn.execute - Calls sqlite
INSERT INTO Cars - Tells sqlite to insert a new row into the table Cars
(Name,TopSpeed) - These are the fields we want to add. Remember the primary key will take care of itself.
VALUES('Porsche 911 Carrera 4s',185) - These are the values we want to put into the row.
Final code
Just remember this is still Python so Indentation is important! Also remember you can only MAKE a database once, so if something goes wrong you may need to use the file manager and delete the database.
Challenge: Add your own car!
The OTHer 48...
You can sit there and type the rest if you want or you could copy & paste them...