In this blog we'll see a brief but very useful introduction to managing and creating databases through Python, while we look at some sites that are worth visiting. The idea is to create our own database with tourist destinations and for this we will choose sites at random to be registered in the database. Then, this information will be extracted and presented.

We know that there are many ways to store data. For example when we handle txt files, xlsx which in turn are simple, easy ways to save data. In this case we will handle SQLite. This is a database that does not demand many resources, it is flexible and the best thing is that it can be managed with Python. If you want to know a little more about SQLite, I invite you to check the blog What Is SQLite and How To Install It? that is published at DataSource.ai.

Now that we know that we will handle SQLite through Python, let's give you an overview of what we will need to start looking at some tourist sites in LATAM. 

We already have our plan to follow. It's time to start this journey through the steps mentioned above, and get started in database management with Python.

First, we download virtualenv to configure our virtual space. For that we will use a pip command which is pip install virtualenv. Also, you can check the following link https://pypi.org/project/virtualenv/     

Once downloaded virtualenv, we create a directory in our PC. This, to organize our workspace. You can do it with "mkdir virtual_env" and once you are in the path of that directory, just follow the steps below. These were executed in a windows operating system with Powershell.

To create our virtual environment, we run this command.

virtualenv virtual_env/virt1

Then, we activate this virt1 environment with the following command.

.\virt1\Scripts\activate

Afterwards, we will see that the virtual environment stands out like this (virt1). Also, we use the pip list command to list what you have in that virtual environment.

The virtual environment virt1 has already been created, now let's download SQLite3 and create our database. To do this just run pip install db-sqlite3. You can see the link below. 

https://pypi.org/project/db-sqlite3/

Then, we will be able to observe SQLite3 installed. After that, we just create a file named database.db and also our notebook where we will use Python to manage our database.

Once everything is installed, just open Jupyter notebook and import SQlite3 as follows.

Then, we create our connection to the previously created database.

The next step is just to create our table. This will be called "Destinos" and will be composed of the following columns.

It is important to remember that the scores are simply random values, since every destination is worth visiting.

With the connection.execute command, we can perform many actions such as create tables, insert data, delete and select. Let's look at an example of how to insert our first destination.

It is important to note that it is not enough to just run INSERT INTO to save our data. For that, we must run connection.commit() later to save any changes established in our database.

Let's see, if it's true that we were able to save our first destination. To do so, the query will be stored in a variable called data.

What type will this variable be?

As we can see, this variable is of the Cursor type. Then, we will see that we can instantiate a Cursor type object to select and show our data. Now, that we know that data is of type Cursor, we can call one of its methods to show the output of the SELECT.

As we can see this was the first destination we stored, and it shows it as a tuple type variable.

What if we wanted to use data.fetchone() once again, to show Argentina as destination?

The result is a None as an answer, this is because we have to execute the SELECT command to execute the query again and show the output once more.       
 
Since I know that many of you don't want to go to Argentina again, let's insert more destinations to visit. For this, we will create a list called destination_data.

These may be some suggestions, add more destinations according to your preference.

The next step is to just iterate in the list and add the previously saved destinations.

Remember, we need the connection.commit() to save the changes.

Let's run a SELECT and save the result in a variable called all_data. Then, we'll loop through it and see what we get.

Also, we can make use of the fetchall() method, to show our query. To do this we'll store the result in a variable called gather_data.

Let us suppose that by mistake an INSERT INTO was executed to the same destination. In this case by mistake we save Argentina again as a destination and we have the same entries twice.

Let's look at the first and last entry of all_data. We can see that this Argentina twice and we really want another destination since Argentina was the first one. To do this, we'll delete the last entry with the DELETE command.

Now, we check.

In this last part, we will create a Cursor object as we had mentioned at the beginning. Then, we'll show the data and finally use Matplotlib to graph it.

It is important to execute the command connection.close() to close the connection to the database if you have finished executing any action.

Finally with Matplotlib we visualize our destinations and their respective scores.

Every journey has its beginnings, I hope this introduction to the Python database will take you to greatest destinations.