Source: Deep Learning on Medium
Python and the SQL Language
SQL is a language used for communication with relational databases. Management database applications use it in its base. These applications change a few things between them. Still, it maintains that its interpreter understands the native SQL, because of this, we are capable of using any management application and perform the necessary requests for our tasks.
I know, some of you may think, this gets every time more tricky, we have to learn another language? But it isn’t that the beauty of programming? All the tools that allow us to choose what to fit best to do our job?
Nevertheless, SQL is a pretty simple language (we can complicate it a lot, though). It has certain reserved words that we will see below as examples. With them, we can: create tables, drop tables, for each table, do searches, insert, delete, update, etc. In fact, it is common the direct work with the database because it is faster in return the results.
Knowing that it is faster to understand through exercises, let’s do that.
We are going to use the same management database application we used in previous posts, SQLite. SQLite is simple, it creates itself as a file in the same folder we are working and allows us, easily, to explain the use of the language without entering in details about the management database app (for info about the management database apps stay tuned)
On the other hand, we yes want to get into more difficult problems. Last time we work with just one table, now, we will work with several of them. Let’s think about a classroom with two groups of students, group A and group B. Every group has several students and a professor.
Let’s start by importing the SQLite package
Now, let’s create all the tables we are going to use further. Look how before creating the tables with reserved word CREATE we execute DROP TABLE IF EXISTS, this last command is reserved too for SQL and checks if the table exists and if it does remove it before creating it. In case we don’t do this check, the program throws us an error.
Having all our tables created we need to check them using some database client, in here, I am going to use Navicat
As you could see, we are using a column named id with type INTEGER in each table. This column is called the Primary Key and usually is created as an integer, and we specify to the management database application that we need an auto-increment option in it. This column is used for the management database app and also by us when we want to work on top a specified data inside one of our tables. This column identifies every data inside each table.
Now, we can insert new data in our new tables
Let’s check again with Navicat.
Seeing the data inserted, I realized we have two mistakes, we insert the student Victor as Victo and also the student Vanessa is not a student. She is a teacher. So, we need to update one of them and remove the other to again insert later in the real place
Let’s check again
In this post, we see the use of CREATE, DROP TABLE, INSERT, UPDATE, DELETE. These reserved words allow us to do tasks, as we see above. This is the core of a program that uses a database (which is the most common) because of this type of request against the database for the management of the data of the general application. Sometimes, we execute them ourselves and on other occasions for tools that the framework we use, provide. The real power of databases is when we want to search info and return it, we will see that in the next posts.
Follow us in our social media: