Skip to content

Reading Data in Python: CSV, JSON, Excel and Databases

A toolkit for every data source: when the standard library is enough, when pandas pays off, and how to keep encoding, memory and SQL injection from becoming your downfall.

≈ 13 min readSoftwarePythonData

A reader is not a transport function. csv.DictReader, json.load, pandas.read_excel – they all turn foreign bytes into Python objects, and in that transition it is decided whether an ä stays an ä, whether a required column is filled and whether an input will later wipe out an entire table. And the sources are rarely as tidy as you’d like: a CSV file someone pulled from an export. A JSON response from an API. An Excel workbook with three sheets and a header that only starts in row four. Or an SQLite file sitting quietly in the project folder, waiting to be queried.

Which reader I pick for this is the smaller decision. The bigger one is where trust is established: Is the encoding right, are required fields filled, do the types match? A wrongly decoded umlaut or an unchecked input rarely costs anything at read time – the bill comes later, in the report to the customer, in the database, in the model trained on garbage. Reading data is therefore the boundary where data quality is decided.

The step is unspectacular, and yet a surprising number of projects get stuck on it. It isn’t hard, just confusing: Python has its own way for every format, and it’s easy to lose track of which one is right when. This article puts together a small toolkit for it: the standard library for direct, row-by-row access, pandas for tabular analysis work, and a parameterized database query that never leads into the most common security trap in the first place. The tools are quickly explained – more interesting is the attitude behind them: treating data as a domain whose quality you actively own.

Data is a domain, not a sideshow#

Before we get to readers, it’s worth taking a step back. If reading data is seen as transport – bytes in, objects out, move on – that is an expensive view, because it puts trust in the wrong place. If I only check late – in the report, in the analysis, in the production endpoint – then I’m checking data that has long since traveled through half my system. The error is then no longer a line in a file but an incident with a name: wrong revenue for the customer, a record that skews an analysis, an input that wipes a table.

That’s why I treat reading data as its own small domain with a clear job: turn unknown bytes into trustworthy, typed objects – and reject everything that doesn’t meet that bar. Three questions decide the product risk here, and all three arise at the system boundary. Encoding: Is a character really the intended character? An ä instead of ä is not a cosmetic flaw. It is silent data damage that propagates through every later search, every comparison, every export. Trust: Who owns the source? I treat my own export differently from user input or a third-party API – for anything coming from outside, the assumption is that it may be hostile. Validation: Are the fields there, and do they have the expected shape?

The trade-off lies in validation, because checking takes time. The further the data travels and the more people depend on it in the end, the earlier and stricter I check. A throwaway script for a one-off analysis can be lenient. A reader that feeds a customer database or a recurring report checks at the boundary and visibly rejects faulty data instead of passing it on without complaint. The gain is concrete: a problem shows up where it originates, with file and line – instead of three layers later as mysterious misbehavior that you have to trace backwards through half a system.

The two camps: standard library or pandas#

Before we write code, a rough classification helps. Python already ships everything you need for the most common formats – csv, json, xml.etree and sqlite3 are part of the standard library, with nothing to install. These modules work close to the raw data: you get lists, dictionaries, individual rows. That’s ideal when you want to process a file row by row, have little memory available or simply don’t want to pull an external dependency into your project.

pandas belongs to the other camp. It’s an additional library, and it pays off whenever your data is tabular and you want to analyze, filter or aggregate it. Instead of maintaining a separate mental model for each format, in pandas you always end up in the same DataFrame – no matter whether the source was a CSV, an Excel workbook, a JSON document or an SQL query. read_csv, read_excel, read_json, read_sql: one reader per format, one common target. On top of that come type inference and a rich set of operations.

The following overview shows the path from source to target structure:

flowchart LR
  CSV[CSV] --> STD[Standard library<br/>csv, json, etree, sqlite3]
  JSON[JSON] --> STD
  XML[XML] --> STD
  DB[(SQLite/DB)] --> STD
  CSV --> PD[pandas<br/>read_csv, read_excel, read_sql]
  XLSX[Excel .xlsx] --> PD
  DB --> PD
  STD --> DICTS[dicts / lists]
  PD --> DF[DataFrame]

The classification I decide by is short: row by row, small, no extra dependency – standard library. Tabular, analytical, with filters and aggregates – pandas. And you can only sensibly read Excel in the modern .xlsx format through pandas anyway (with a helper library), because the standard library can’t do it. What matters is not the most convenient API but the data volume and the work that is supposed to happen with the data afterwards.

CSV with the standard library: the classic#

CSV is the format you’ll run into most often, and at the same time the one with the most little pitfalls. The standard library offers two ways: csv.reader returns a list per row, csv.DictReader returns a dictionary per row and automatically uses the header row as keys. In practice I almost always reach for DictReader, because access through meaningful column names makes the code more readable.

import csv

with open("customers.csv", newline="", encoding="utf-8") as f:
    reader = csv.DictReader(f)          # header row becomes dict keys
    rows = [row for row in reader]      # each row is a dict

for row in rows[:3]:
    print(row["name"], row["email"])

Two details on this open line are more important than they look. First, newline="": the csv module handles line breaks inside fields itself, and if you don’t open the file with an empty newline, rows can get torn apart under certain conditions. The documentation says so, and it’s one of those rules you learn once and never forget – usually after the first broken record.

Second, encoding="utf-8". More on that in a moment, because this is where the pitfall lies that causes the most confusion in projects – hardly any data error shows up as reliably.

A nice side effect of DictReader: because it iterates over the file, you can also use it for large files without loading everything into memory at once. Instead of materializing the list with a comprehension, you then process row by row in the loop and keep only what you really need.

JSON: from file to Python object#

JSON is pleasantly straightforward in Python, because the structure maps almost one to one onto dictionaries and lists. json.load reads from an open file object, json.loads from a string. The result is a perfectly normal Python object you can work with right away.

import json

with open("config.json", encoding="utf-8") as f:
    data = json.load(f)         # dict or list, depending on the document

print(data["version"])
for item in data["items"]:
    print(item["id"], item["title"])

For writing back, there are the counterparts json.dump and json.dumps. When writing, ensure_ascii=False is usually worth it so umlauts end up in the file as real characters rather than \u escapes, along with indent=2 for readable formatting. When reading, you don’t need these options – JSON is refreshingly undemanding there.

Excel: here the path leads through pandas#

As soon as real tables come into play – several sheets, number formats, header rows – pandas becomes the tool of choice. read_excel reads an .xlsx into a DataFrame, and you can pick a specific sheet with sheet_name.

import pandas as pd

df = pd.read_excel("report.xlsx", sheet_name="Sales")  # openpyxl engine by default
print(df.head())
print(df["revenue"].sum())

One point many people stumble over the first time: for .xlsx, read_excel needs an additional library called openpyxl. Since pandas 1.2.0 (end of 2020), openpyxl has been the default engine for this format – the older xlrd library no longer supports modern .xlsx files and is only responsible for the old .xls files. So if you get an error message about a missing engine, the fix is almost always pip install openpyxl.

If you pass sheet_name=None, pandas returns a dictionary of all sheets as DataFrames – handy when you want to read a whole workbook. And if your header isn’t in the first row, the header parameter helps you set the right row as column names.

Databases: sqlite3 and the most important security note#

SQLite is my default tool for database access in small projects and prototypes, because it needs no server and the sqlite3 module is already part of the standard library. The flow is always the same: open a connection, get a cursor, run a query, fetch the result, close the connection.

import sqlite3

conn = sqlite3.connect("shop.db")
cur = conn.cursor()

category = "books"
cur.execute(
    "SELECT name, price FROM products WHERE category = ?",
    (category,),
)
result = cur.fetchall()

for name, price in result:
    print(name, price)

conn.close()

The crucial part is in the execute line: the question mark as a placeholder and the value as a separate tuple in the second argument. This is not a matter of style but a matter of security. If you build the value directly into the SQL string instead – via concatenation or an f-string – you open the door to SQL injection. A manipulated input field can then rewrite the query, read data or delete entire tables.

The comparison makes the difference clear:

# unsafe: never do this
cur.execute(f"SELECT name, price FROM products WHERE category = '{category}'")

# safe: the driver handles quoting and escaping
cur.execute(
    "SELECT name, price FROM products WHERE category = ?",
    (category,),
)

Besides security, the parameterized way has a second advantage: the database driver handles quoting and escaping for you. You don’t have to worry about quotation marks in the value. One important detail: the placeholder ? appears without surrounding quotation marks in the SQL text – the driver does the quoting, not you.

If you want more convenience or want to switch between different databases, SQLAlchemy comes into play. With create_engine("sqlite:///shop.db") you set up a connection, and pandas.read_sql(query, con) reads the result directly into a DataFrame. That closes the loop between database and analysis: the query comes from SQL, and the result lands straight in the familiar pandas model.

And XML? Don’t forget it, but you’ll rarely need it#

You’ll run into XML less often than you used to, but it still shows up in configurations, exports and older interfaces. The standard library covers it with xml.etree.ElementTree. You parse the source into a tree, get the root and navigate with find, findall and iter to the elements you care about.

import xml.etree.ElementTree as ET

tree = ET.parse("catalog.xml")
root = tree.getroot()

for product in root.findall("product"):
    name = product.find("name").text
    price = product.get("price")        # read an attribute
    print(name, price)

For simple structures, that’s entirely sufficient. With more complex XML documents with namespaces or deep nesting, it quickly gets fiddly – then I like to reach for specialized libraries. For everyday work, though, ElementTree covers most cases.

Which reader for which format?#

To help you keep track, here is the mapping as a compact list:

  • For CSV, use csv.DictReader from the standard library for row-by-row reading and pandas.read_csv as soon as it gets analytical.
  • For JSON, json.load and json.loads from the standard library are enough; pandas.read_json helps when the JSON is tabular.
  • For Excel in the .xlsx format, the path leads through pandas.read_excel with openpyxl installed – the standard library can’t handle this format.
  • For databases, sqlite3 covers direct, parameterized access; pandas.read_sql or SQLAlchemy come in when analysis or portability is needed.
  • For XML, xml.etree.ElementTree from the standard library is enough for manageable documents.

Three pitfalls that strike again and again#

In practice, it’s rarely the readers themselves that cause problems, but three recurring topics around reading data.

Encoding#

Why encoding is a product risk was covered above – here is the practical part. The default character set when opening a file depends on the platform, on Windows often cp1252 instead of UTF-8. If umlauts appear as ä or ö, or a UnicodeDecodeError shows up, the encoding is almost always to blame, and the fix is to specify it explicitly: encoding="utf-8". For files exported from Excel, you often need encoding="utf-8-sig", because Excel puts a so-called byte order mark at the beginning, which plain utf-8 misreads as a special character.

Loading large files entirely into memory#

It’s tempting to simply load a CSV in full with pandas.read_csv("big.csv") – until the file is several gigabytes and memory overflows. pandas offers chunksize for this: instead of a DataFrame, you then get an iterator that lets you work through the file in blocks.

import pandas as pd

total = 0
for chunk in pd.read_csv("big.csv", chunksize=100_000):
    total += chunk["amount"].sum()     # process one block at a time

print(total)

In the standard library you achieve the same by iterating row by row with DictReader instead of materializing everything into a list. One more note: read_excel has no native chunking – for very large tables it’s worth converting the data to CSV or into a database beforehand.

SQL injection#

Important enough to repeat: never build user input into an SQL query via string concatenation or an f-string. Always use placeholders and pass the values separately. It doesn’t cost an extra line of code and saves you from an entire class of security vulnerabilities.

The following decision tree sums up how I choose the path in practice:

flowchart TD
  A{Tabular<br/>analysis needed?} -->|yes| B[pandas]
  A -->|no| C{Row by row,<br/>small, no dependency?}
  C -->|yes| D[Standard library]
  C -->|no| B
  B --> E{Very large file?}
  E -->|yes| F[chunksize / streaming]
  E -->|no| G[read directly]

Conclusion#

The choice of tool is quickly made: for row-by-row, direct access without an extra dependency, the standard library with csv, json, xml.etree and sqlite3 is enough; as soon as things get tabular and analytical, or Excel is involved, pandas pays off with its uniform DataFrame model. The difference between a script that runs and one you trust doesn’t come from the readers but from the attitude: encoding, trust and validation belong at the system boundary.

If you draw this boundary deliberately, you change the economics of the project. Errors show up early and with a name instead of late and mysteriously – that shortens debugging cycles and takes friction out of further work. The data that gets through is the data you can trust, which raises the quality of every later step. And the three reflexes that go with it cost almost nothing: always set the encoding explicitly so umlauts don’t fall apart. With large files, think early about chunksize or row-by-row streaming instead of filling up memory. Database queries always with placeholders, never with assembled SQL. The clear upper limit matters too: not every script needs full schema validation, and where effort and risk don’t match, leniency is the right choice. If you decide this instead of leaving it to chance, you reach your goal quickly and safely – and can turn to what’s actually interesting: what’s in the data.

Further reading#

Revised and translated from German with the help of AI.