Like this tutorial? Sign up for the mailing list to be notified of site updates and an upcoming course on Python For Traders.
Now that we have created some tables to store our data, let’s populate these tables with some data and write some SQL to query this data.
First, let’s learn the basic CRUD operations. CRUD stands for Create, Read, Update, and Delete. These operations are the building blocks for millions of web and mobile applications. If you can design user interfaces, create API’s, and write queries that perform these basic operations, you can build your own applications or be employed for your entire career.
We will first use the command line to get familiar with SQL queries. Later we will use Python to execute SQL statements to populate our database and update our data on a schedule. Let’s connect to our stock database using the SQLite command line:
To create records in our database, we will use the INSERT statement.
sqlite> INSERT INTO stock (symbol, company) VALUES ('AAPL', 'Apple'); sqlite> INSERT INTO stock (symbol, company) VALUES ('MSFT', 'Microsoft'); sqlite> INSERT INTO stock (symbol, company) VALUES ('TSLA', 'Tesla'); sqlite> INSERT INTO stock (symbol, company) VALUES ('AMD', 'Advanced Micro Devices');
The INSERT statements above create 4 rows in our stock table. An INSERT statement begins with INSERT INTO followed by the table name. In this case, we are creating records in the stock table. In parentheses, we specify the columns we are inserting data into, follow by the VALUES keyword and the values we are inserting into these columns in parentheses. Since we are inserting strings of text, we use quotation marks around these values. The semicolon indicates the end of the first statement.
You might remember that our table also has an id column. Since we specified this column as the PRIMARY KEY for our table, SQLite will automatically assign a unique number to each row that is inserted. We do not need to specify a value for id in this case.
Now that we have Created some rows, how do we Read them and verify they are there? The SELECT statement is used to retrieve records from our database tables. Let’s write a SQL query to SELECT all records from our stock table.
sqlite> SELECT * FROM stock; 1|AAPL|Apple 2|MSFT|Microsoft 3|TSLA|Tesla 4|AMD|Advanced Micro Devices
You’ll also notice the id values in the first column. Notice how each record has a unique id. We will reference this id in the rows of our stock_price table.
The wildcard * is used to select all of the columns. If you want to select specific columns, just list out the column names like so:
sqlite> SELECT id, symbol FROM stock; 1|AAPL 4|AMD 2|MSFT 3|TSLA
Also, notice that we selected all of the rows in our table. Most of the time you don’t want to Read all of your database records at once. Usually, you just want to read a single record or one page of records at a time. To SELECT only certain rows, you can use a WHERE clause:
sqlite> SELECT company FROM stock WHERE symbol = 'TSLA'; Tesla
This will select only the rows matching the conditions of the WHERE clause. There are many other ways to slice and dice our data. We won’t go over them all since there are thousands of ways to select data, but below are a few common SQL statements that you will use regularly. You can probably intuitively see what they do:
sqlite> SELECT company FROM stock WHERE symbol LIKE 'A%'; Apple Advanced Micro Devices sqlite> SELECT * FROM stock LIMIT 3; 1|AAPL|Apple 2|MSFT|Microsoft 3|TSLA|Tesla sqlite> SELECT symbol FROM stock where id = 2; MSFT sqlite> SELECT * FROM stock WHERE company LIKE '%icro%'; 2|MSFT|Microsoft 4|AMD|Advanced Micro Devices sqlite> SELECT * FROM stock ORDER BY company DESC; 3|TSLA|Tesla 2|MSFT|Microsoft 1|AAPL|Apple 4|AMD|Advanced Micro Devices sqlite> SELECT COUNT(*) FROM stock; 4
Let’s say the symbol for Apple stock changes. While this seems unlikely, stock symbols change all of the time. For example, Google changed its name to Alphabet and created two separate symbols, GOOG and GOOGL. We can use the UPDATE statement to update records in our database. Let’s update the company name for Apple to Apple Inc.
sqlite> UPDATE stock SET company = 'Apple Inc.' WHERE id = 1; sqlite> SELECT * FROM stock; 1|AAPL|Apple Inc. 2|MSFT|Microsoft 3|TSLA|Tesla 4|AMD|Advanced Micro Devices
Here we simply specify the name of the table we are updating, the columns that are being updated, and the new values to set for these columns. It is important to use a WHERE clause to specify which records are being updated. If you ran this query without a WHERE clause, then all company names in the database would be updated to Apple Inc.!
We have now learned how to Create, Read, and Update records in our database. The last CRUD operation is DELETE. To delete a record, we simply specify what table we are deleting records from, and use a WHERE clause to specify which records in that table we are deleting. Let’s say Tesla actually did go bankrupt and we decided to delete it from our stock database:
sqlite> DELETE FROM stock WHERE id = 3;
sqlite> SELECT * FROM stock;
4|AMD|Advanced Micro Devices
In this example, we are deleting Tesla from our database using its primary key. When we were creating our tables, we could have also specified how SQLite should clean up references to this stock. Since we are storing stock prices in our stock_price table, we could tell SQLite that it should automatically delete all price records for a stock if the symbol is deleted from the stock table. We will discuss the topic of database constraints more in later tutorials.
We have learned the basics of CRUD operations using SQL and can now successfully query and manage data in a single database table. Since we want to build a stock database with historical prices, it would be very tedious to write SQL statements one at a time to insert each record into our database tables. In the next tutorial, we will write Python code to obtain a universe of stock data and dynamically execute SQL queries to insert thousands of records into our database tables.