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
| Top Data Science Skills to Learn | ||
| 1 | Data Analysis Course | Inferential Statistics Courses |
| 2 | Hypothesis Testing Programs | Logistic Regression Courses |
| 3 | Linear Regression Courses | Linear Algebra for Analysis |
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.
![Most Common PySpark Interview Questions & Answers [For Freshers & Experienced]](/__khugblog-next/image/?url=https%3A%2F%2Fd14b9ctw0m6fid.cloudfront.net%2Fugblog%2Fwp-content%2Fuploads%2F2020%2F09%2F991.png&w=3840&q=75)
![Inheritance in Python | Python Inheritance [With Example]](/__khugblog-next/image/?url=https%3A%2F%2Fd14b9ctw0m6fid.cloudfront.net%2Fugblog%2Fwp-content%2Fuploads%2F2020%2F12%2F1434.png&w=3840&q=75)
![Sorting in Data Structure: Categories & Types [With Examples]](/__khugblog-next/image/?url=https%3A%2F%2Fd14b9ctw0m6fid.cloudfront.net%2Fugblog%2Fwp-content%2Fuploads%2F2020%2F05%2F493-Sorting-in-Data-Structure.png&w=3840&q=75)
