Blog_Banner_Asset
    Homebreadcumb forward arrow iconBlogbreadcumb forward arrow iconData Sciencebreadcumb forward arrow iconHow to Fetch Data From Database in Python? Importing Data Using Python

How to Fetch Data From Database in Python? Importing Data Using Python

Last updated:
9th Mar, 2021
Views
Read Time
7 Mins
share image icon
In this article
Chevron in toc
View All
How to Fetch Data From Database in Python? Importing Data Using Python

As a professional in the field of data management and analysis, mastering the skill of retrieving data from a database in Python is essential. In today’s data-driven world, accessing and extracting information from databases efficiently using Python can significantly enhance productivity and decision-making processes. 

In this article, I will guide you on how to fetch data from a database in Python, empowering you with the knowledge and skills necessary to harness the full potential of Python for data manipulation tasks. Whether you’re a seasoned data analyst or a beginner in the field, understanding how to interact with a database in Python opens a world of opportunities for data exploration, analysis, and reporting. 

By the end of this tutorial, you’ll have a solid understanding of the fundamentals of database interaction in Python, enabling you to extract, manipulate, and analyze data with ease. Let’s dive in and explore the power of Python in database management and data extraction. 

Data Extraction with Python Database 

Data extraction entails retrieving data from various sources, and sometimes processing it further, and migrating it to repositories for further analysis. So, some kind of data transformation happens in the process. And python is one of the leading programming languages for such data science tasks. There are about 8.2 million users of this general-purpose and scripting language across the world. 

In the following guide, we will discuss extraction methods using PostgreSQL, an open-source relational database system. It provides a ROW_TO_JSON function that returns the result sets as JSON objects, which are surrounded by curly braces {}. JSON data types would help you manipulate query results more conveniently. But before we begin, make sure that you have installed a virtual environment, such as psycopg2-binary. 

Our learners also read: Top Python Courses for Free

Python Database Basics

Suppose you have a PostgreSQL database of the American National Football League (NFL). This would include information about the players, coaches, and teams’ tables. Also, note the following details to get clued up about the stored data:

  • Players’ data table houses details like athelete_id, which is the primary key, players’ first and last names, jersey numbers, weight (in kg), height (in m), and their country of origin. It also holds the team_id, a foreign key indicating each athletes’ team. 
  • The data table on coaches has coach_id (primary key), along with the first and last names, and team_id (a foreign key referencing the teams’ table field).
  • Finally, there is the teams’ table that describes every football team with a name, conference, their rank, and total wins and losses (bifurcated into ‘home’ and ‘away’). Here, the primary key is team_id, which is referenced in the tables above. 

Now that you are familiar with the dataset, let us explore how to write an SQL query to retrieve a list of teams. For example, you need football teams ordered according to their conference and rank. You also want to extract the number of athletes or players in each team along with the names of their coaches. You may also want to know the number of the teams’ wins and losses, both at home and away. 

Follow the steps below to start this process:

SELECT

f.name,

f.city,

f.conference,

f.conference_rank,

COUNT(a.player_id) AS number_of_athletes,

CONCAT(c.first_name, ‘ ‘, c.last_name) AS coach,

f.home_wins,

f.away_wins

FROM athletes a, teams f, coaches c

WHERE a.team_id = f.team_id

AND c.team_id = f.team_id

GROUP BY f.name, c.first_name, c.last_name, f.city, f.conference, f.conference_rank, f.home_wins, f.away_wins

ORDER BY    f.conference, f.conference_rank

After this, you can warp the query inside the JSON function we mentioned earlier (ROW_TO_JSON). This will save the data to a file called query.sql in your current directory. Now, continue with the steps given below.

Read our popular Data Science Articles

SELECT ROW_TO_JSON(team_info) FROM (

SELECT

f.name,

f.city,

f.conference,

f.conference_rank,

COUNT(a.athelete_id)AS number_of_atheletes,

CONCAT(c.first_name, ‘ ‘, c.last_name) AS coach,

f.home_wins,

f.away_wins

FROM athletes a, teams f, coaches c

WHERE a.team_id = f.team_id

AND c.team_id = f.team_id

GROUP BY f.name, c.first_name, c.last_name, f.city, f.conference, f.conference_rank, f.home_wins, f.away_wins

ORDER BY    f.conference, f.conference_rank

) AS team_info

You would observe that each row has the structure of a python dictionary. The keys are just the field names returned by your query. 

Moreover, to avoid exposing your environment variables in plain sight, you can apply some changes to your initialization files. Choose any of the following methods, depending on your needs:

  • For Windows: Control panel → System → Advanced System Settings → Advanced Tab → Environment variables.
  • For a Unix-like environment: Append two lines about your username and password to your initialization file.

With this, you are all set to write python code. At the very outset, we will import some modules and functions to prevent errors. These statements can help you accomplish that:

import os

import psycopg2 as p

from psycopg2 import Error

Then, we will instantiate the connection by loading the contents of query.sql. Open the SQL database file using open and read commands, and connect with the NFL database using the connect function by specifying your database user, password, host, and port number.

Also Read: Python Projects on GitHub

upGrad’s Exclusive Data Science Webinar for you –

Transformation & Opportunities in Analytics & Insights

Explore our Popular Data Science Courses

How to Fetch Data From a Database in Python?

Once you have established the database connection, you can proceed with query execution. You need to use a control structure called ‘cursor’. It is as easy as writing “cursor = conn.cursor()” and subsequently, “cursor.execute(query)”. The result would then contain a list of tuples (one-element) in a dictionary format. 

result = cursor.fetchall()

At this stage, you can attempt iterating over the result. You can manipulate the contents as you want, insert or feed them into spreadsheets, HTML tables, etc. Don’t forget to wrap and clean your code while you finish. You can do so with a try-except-block and adding a ‘finally’ sentence. 

When you are handling large datasets, relational or otherwise, you feel the need for some basic tools to query the tables, especially when you also want to manipulate the results. Such data transformation is easy to achieve with python.

Therefore, most postgraduate programs of study include the knowledge of these techniques as a part of the curriculum. Some examples include the Associate Diploma in Data Science (IIIT-Bangalore) and Global Master Certificate in Business Analytics (Michigan State University). 

Checkout: Python Open Source Project Ideas

Top Data Science Skills to Learn

Conclusion

Mastering data extraction with Python database basics has been an enlightening journey for professional in data management and analysis. Learning how to fetch data from a database in Python has empowered to streamline workflow, boost efficiency, and make more informed decisions based on data-driven insights. 

Exploring the fundamentals of Python database basics and following the step-by-step guide on data extraction has provided with a solid foundation for leveraging Python’s capabilities in handling data manipulation tasks. With practice and application, I am confident that you can enhance your data-handling abilities and contribute more effectively to the organization’s success. 

By implementing the knowledge gained from this tutorial, I believe you will get to know how to fetch data from a database in Python also taking data analysis skills to the next level and stay ahead in today’s competitive business environment. 

Learn data science courses from the World’s top Universities. Earn Executive PG Programs, Advanced Certificate Programs, or Masters Programs to fast-track your career.

 

 

Profile

Pavan Vadapalli

Blog Author
Director of Engineering @ upGrad. Motivated to leverage technology to solve problems. Seasoned leader for startups and fast moving orgs. Working on solving problems of scale and long term technology strategy.

Frequently Asked Questions (FAQs)

1How do you pull data from an API using Python requests?

When you wish to receive data from an API, you must make a request from the server, just like when you interact with conventional websites. We'll need to use the requests package to get data from an API using Python. In Python, Requests is the standard library for making HTTP requests. Because of its abstractions, it's really easy to use, especially when working with APIs.
When we use the requests library to run a request, we get a request object that contains the data we want to extract as well as a requests status code. The status code informs us about the status of the request, and it is part of every request we make. Depending on the information they return, the codes are divided into hundreds of different values.

2How to connect SQLite with Python?

a. We must import sqlite3 in order to use SQLite.
b. Then, using the connect method, make a connection and provide the name of the database you would like to access; if a file with that name exists, it will be opened. Python will create a file with the provided name if you don't specify one.
c. Following that, a cursor object is created that may send SQL commands. Cursor is a control structure for traversing and retrieving database records. When dealing with Python, the cursor is really important. The cursor object will be used to execute all commands.
d. Create an object as well as write the SQL statement in it with comments to create a table in the database. Example: - sql_comm = SQL statement.
e. And running the command is a breeze. Execute the cursor method, passing the name of the sql command as an argument. Save a list of commands as the sql_comm variable and run them. After you've completed all of your tasks, save the modifications to the file by committing them, and then disconnect.

3Is Python good for databases?

Python is especially well suited for structured tabular data that can be obtained with SQL but then requires additional manipulation that would be difficult to accomplish with SQL alone.

Explore Free Courses

Suggested Blogs

Python Developer Salary in India in 2024 [For Freshers & Experienced]
908765
Wondering what is the range of Python developer salary in India? Before going deep into that, do you know why Python is so popular now? Python has be
Read More

by Sriram

21 May 2024

Binary Tree in Data Structure: Properties, Types, Representation & Benefits
89096
Data structures serve as the backbone of efficient data organization and management within computer systems. They play a pivotal role in computer algo
Read More

by Rohit Sharma

21 May 2024

Data Analyst Salary in India in 2024 [For Freshers & Experienced]
22359
Summary: In this Article, you will learn about Data Analyst Salary in India in 2024. Data Science Job roles Average Salary per Annum Data Scient
Read More

by Shaheen Dubash

20 May 2024

Python Free Online Course with Certification [2024]
134915
Summary: In this Article, you will learn about python free online course with certification. Programming with Python: Introduction for Beginners Le
Read More

by Rohit Sharma

20 May 2024

13 Interesting Data Structure Projects Ideas and Topics For Beginners [2023]
248454
 In the world of computer science, understanding data structures is essential, especially for beginners. These structures serve as the foundation for
Read More

by Rohit Sharma

20 May 2024

Top 30 Python Pattern Programs You Must Know About
41081
Summary Pattern in Python or “Python patterns” is an essential part of Python programming, especially when you are just starting out with using algor
Read More

by Rohit Sharma

19 May 2024

16 Best Data Science Project Ideas & Topics for Beginners [2024]
956550
Summary: In this Article, you will learn about 16 exciting data science project ideas & topics for beginners. 1. Beginner Level | Data Science P
Read More

by Rohit Sharma

16 May 2024

Binary Tree vs Binary Search Tree: Difference Between Binary Tree and Binary Search Tree
63164
Introduction Sorting is the process of arranging the data in a systematic order so that it can be analysed more effectively. The process of identifyi
Read More

by Rohit Sharma

16 May 2024

Top 12 Fascinating Python Applications in Real-World [2024]
157694
It is a well-established fact that Python is one of the most popular programming languages in both the coding and Data Science communities. But have y
Read More

by Rohit Sharma

16 May 2024

Schedule 1:1 free counsellingTalk to Career Expert
icon
footer sticky close icon