When working with data, especially at scale, spreadsheets can quickly reach their limits. This is where databases come in.
A database is an organized collection of structured data designed to make data storage, retrieval, modification, and deletion efficient and reliable. Databases are a foundational tool in data analysis, data engineering, and software development.
Why use databases for data wrangling?
Databases offer several key advantages when handling data. They are fast and optimized for performance, even when working with large datasets. They provide administrative features such as access controls, which help protect sensitive information. They also enforce data integrity rules, ensuring that data is entered in the correct format, which is essential for reliable data wrangling.
Types of databases
Databases generally fall into two main categories: relational and non-relational. Relational databases are the most popular and widely used.
Relational databases organize data into tables made up of rows and columns, similar to Excel spreadsheets, but with stricter rules. Each column must have a unique name, all values in a column must share the same data type, and using clear, descriptive column names is considered best practice.
Common examples of relational databases include SQLite, PostgreSQL, MySQL, and SQL Server.
Non-relational databases, often referred to as NoSQL databases, are designed for more flexible or unstructured data, such as documents or key-value pairs. While powerful, they are outside the scope of this introduction.
What is SQL?
SQL, or Structured Query Language, is the standard language used to interact with relational databases. SQL allows users to read, manipulate, and modify data efficiently.
Some common SQL commands include CREATE, which creates a new table in a database; DROP TABLE, which removes a table; SELECT, which retrieves data that matches specific conditions and is also known as a query; and FROM, which specifies the table from which the data should be retrieved.
Using SQL with Python
In Python, libraries such as SQLAlchemy make it easy to work with databases. SQLAlchemy allows you to connect to a database, execute SQL queries, load the results into a pandas DataFrame, and store processed data back into the database.
This creates a powerful workflow that combines the speed and reliability of SQL with the simplicity and flexibility of pandas.
Important things to remember
It is possible to work directly with databases using tools like sqlite3 without SQLAlchemy. SQLAlchemy also supports many database systems beyond SQLite, including PostgreSQL.
Finally, data wrangling does not always have to be done in pandas. In many professional environments, data cleaning and transformation are performed directly in SQL. The best tool often depends on company infrastructure, performance needs, and scalability requirements.
Understanding databases and SQL is a fundamental skill for anyone working with data. Even if pandas is your primary tool, knowing how databases work and how to query them effectively will make you a stronger and more versatile data professional.