Introduction

This tutorial provides a basic introduction and steps on how to work with DBeaver and Python to effectively execute SQL queries.

We'll start our journey by looking at how to effectively execute SQL statements using DBeaver's built-in console. We will also show you how to use Python to more easily handle your queries on databases.

Check out our video that creates a full environment for you to learn SQL. All you need is to run a single Docker command you get a PostgreSQL database, a real world dataset and SQL client in a browser.

DBeaver

About:

DBeaver is a SQL client software application and a database administration tool. It is also known as a Universal Database Tool. It's free and is a multi-platform database tool for developers, database administrators, analysts and all people who need to work with databases.

It supports all popular databases like MySQL, PostgreSQL, SQLite, Oracle, DB2, SQL Server, Sybase, MS Access, Teradata, Firebird, Apache Hive, Phoenix, Presto and more.

Installation:

Visit the following link to download the FREE community edition of DBeaver:

Application Window Overview:

This is the user interface of the Dbeaver application.

Running Queries:

• Step 1: We need to first create a new Database Connection.

1. Go to 'Database' in the Menu Bar and click on 'New Database Connection'.

2. Find your Database Driver in the pop-up box.

3. Give the path of the file on your desktop. You can even click on 'Browse'.

4. Once you have selected the file, click on 'Finish'.

5. The file will be reflected on the left, under Database Navigator.

• Step 2: Learning where to write queries.

1. We write our queries under the 'Editor' space. (refer to the application window overview diagram above.)

2. The resultant tables/output will be displayed at the bottom.

This is essentially how you write queries in DBeaver. As you could see, performing these kinds of queries is very easy.

Python: An Alternate

We can use Python as well to write SQL queries. Python already understands the syntax and the working around SQL queries. Moreover, you can query pandas DataFrame directly using only SQL queries or syntax in Python.

For the same, we use the library pandasql .

Pandasql allows us to query pandas DataFrames using SQL syntax.

Let's look at the step by step process on how to write a simple query in the Python environment.

• Step 1: Installing the library

!pip install pandasql

• Step 2: Importing useful libraries

import pandas as pd
import pandasql as ps
from IPython.display import HTML

• Step 3: Control the rows and decimal places to be displayed

Use the below two lines of code to explicitly control the number of rows and the number of decimal places to be displayed.

pd.set_option('display.max_rows', 100)
pd.options.display.float_format = '{:.5f}'.format

• Step 4: Reading the .csv file

df = pd.read_csv("Path_of_the_file.csv")
vehicles = df.sample(frac=0.05)

• Step 5: Writing and displaying the queries

Syntax:

sql_query = """
***WRITE YOUR QUERY HERE***
"""

res_df = ps.sqldf(sql_query, locals())
HTML(res_df.to_html(escape=False))

For example:

sql_query = """
SELECT AVG(price) as average_price ,region
FROM vehicles 
WHERE price BETWEEN 1 and 655000
GROUP BY region 
ORDER BY AVG(price) LIMIT 5; 
"""

res_df = ps.sqldf(sql_query, locals())
HTML(res_df.to_html(escape=False))

The final table will look like this:

Wrap Up

In this article, we learnt about how to run basic SQL queries using Dbeaver, as well as Python and discussed the steps in depth.

Which one would you rather work with? Which one would you enjoy learning more? The choice between the two will always be one of personal preference.

When starting out with SQL, you should initially focus on the platform that is more familiar to you, while also exploring the other.

We hope this article was helpful to you and answered your questions.

You can also do our SQL Exercises for Beginners, by clicking on the link below, to practice and sharpen your SQL skills more.

Getting Started with SQL Queries - Exercises for Beginners Part-1

Getting Started with SQL Queries - Exercises for Beginners Part-2

Getting Started with SQL Queries - Exercises for Beginners Part-3

Getting Started with SQL Queries - Exercises for Beginners Part-4

We will keep you updated on similar tutorials from our side.

If you are looking for jobs in AI and DS check out Deep Learning Careers

So, don't miss out! See you!