Once we have created a database and added the necessary tables we can turn our attention to populating it. Data can be added to any table in the database through use of the
insert into SQL statement.
The insert statement is as follows:
INSERT INTO Product(Name,Price) VALUES ('Espresso',1.55)
Notice that you do not need to provide a values for ProductID - this is generated by SQLite automatically.
Making use of parameters
insert into statement above is hard-coded - the values provided can not change. When programming we generally want to avoid hard coded statements so that we can reuse the code with other values at a later date.
SQLite provides a provides a way to pass parameters to SQL statements called qmark style. Rewriting the previous statement with qmarks would result in:
INSERT INTO Product(Name,Price) VALUES (?,?)
Aside from allowing us to reuse our statement, qmark style helps prevent SQL injection attacks, which regular Python string methods such as
format() do not guard against.
Adding some data
The video below demonstrates how to add data to your database:
Use the above video to create a insert statement to add data to the product table.
Once you have added the data from the video add the following data to the product table:
Checking that everything worked
One problem that comes from developing a database in code is that it can be a bit akward to check whether what you have just done has worked successfully - how can we tell whether the products were inserted correctly?
Thankfully there are programs that exist to enable us to inspect the structure of a database file. One of these programs in called SQLite Inspector and we will use it repeated just to keep an eye on the changes we make to the database.
The video below demonstrates how to use SQLite Inspector:
- Download and install SQLite Inspector
- Use the above video help you inspect your database structure.
So far you have discovered how to create a database and its structure and then how to insert data into individual tables. There are other important SQL commands that you must learn - we will look at updating (amending) and deleting existing data next.
- 1. Introduction to databases
- 2. Creating the data model
- 3. Inserting data
- 4. Updating and deleting data
- 5. Searching and sorting data
- 6. Product Management Menu
- 7. Introduction to relational databases
- 8. Creating a data model with relationships
- 9. Referential integrity
- 10. Completing the coffee shop