Travel, Database and Python. Let The Adventure Begin!

Danilo Galindo
May 06, 2020


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. 

  • Create a virtual space to store all the information such as libraries, database and the notebook.
  • Download SQLite.
  • Create the database.
  • Connect to the database.
  • We will create a table. 
  • We will store data.
  • We will save and present data.

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.

  • País
  • Sitio 
  • Puntaje 
  • Descripción
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. 

“Travel, Database and Python. Let The Adventure Begin!”
– Danilo Galindo twitter social icon Tweet


Share this article:

0 Comments

Post a comment
Log In to Comment

Related Stories

Nov 25, 2021

5 Tips To Ace Your Job Interview For A Data Scientist Opening

5 Tips To Ace Your Job Interview For A Data Scientist Opening.PNG 795.94 KBImage SourceAspiring data scientists have a bright future ahead of them....

Daniel Morales
By Daniel Morales
Nov 12, 2021

When to Avoid Deep Learning

IntroductionThis article is intended for data scientists who may consider using deep learning algorithms, and want to know more about the cons of i...

Matt Przybyla
By Matt Przybyla
Oct 16, 2021

6 Advanced Statistical Concepts in Data Science

The article contains some of the most commonly used advanced statistical concepts along with their Python implementation.In my previous articles Be...

Nagesh Singh Chauhan
By Nagesh Singh Chauhan
Icon

Join our private community in Slack

Keep up to date by participating in our global community of data scientists and AI enthusiasts. We discuss the latest developments in data science competitions, new techniques for solving complex challenges, AI and machine learning models, and much more!

 
We'll send you an invitational link to your email immediatly.
arrow-up icon